Hi,
I have to create a report for all the business days in a current month. (no matter the records are available or not in the database for the day).
If you have any idea, pls tell me
ThanksCan you be a little more specific? Do you need a separate report for each day, or do you need one report that will show all the days in the month regardless if there's a record?|||Thanks Malleyo!
The second condition in your post is my report.
My report should display all the business days in the current month and the corresponding sum of amounts from a table which contains a date field. The sum is grouped by the date for each day.
The problem here is the table doesn't contain records for all the days upto end of the month. But I have to display all the days. Now I'm using a dummy table which contains all the business days and linking this with my main table using left outer join so that I'm able to display all the dates.
But I think this is not the right way to do this?
Ex report is
Date Amount
6/1 100
6/2 50
6/3 90
6/4 150
6/7
6/8
...
...
...
6/30
Ex. The problem is table contains data upto 6/4. So while generating the report it shows the report upto 6/4. But I need upto 6/30 even though the amount field is empty.
Thanks|||I'm using a dummy table which contains all the business days and linking this with my main table using left outer join so that I'm able to display all the dates.
Joining the 2 tables sounds good to me.
Try using a Group for Date. Make sure that you don't have 'SuppressIfBlank' turned on. Make sure that the query you're running is really returning all the info you need.|||Thanks Malleyo,
One more things, I'm using MySQL as my database. While linking the tables using left outer join, it is not working properly. It returns the result of inner join.
Actually my first table contains all the business days and the second table contains records for some of the days. I linked the table on date field using left outer join. It has supposed to return all the days in the first table regardless of second table. But it 's not. It's returning only the matching records.
Any idea please,|||You may be doing the wrong kind of Join. Unfortunately I don't know a whole lot about Joins, and the knowledge that I do have is for SQL Server 7 (which I'm not sure if uses different syntax than MySQL.
Here's what my Joins typically look like:
SELECT * FROM Table1 AS T1
LEFT JOIN Table2 AS T2 ON T2.Column1 = T1.Column1
That will return all the fields in Table1 with any that match in Table2 (or NULL if there isn't a match in Table2).|||I'm also new to mysql.
I'm using the same conventions as given in mysql documentation.
My query is
select table1.date,table2.date,table2.amount from table1 left join table2 on table1.date = table2.date where table2.code = '111'
This is not working
But
select table1.date,table2.date,table2.amount from table1 left join table2 on table1.date = table2.date and table2.code = '111'
This works but it takes so much time to read the records. sometime just hung up. My table1 contains just 22 records.
While adding as tables, it's not working.|||Do you have indexes on table1.date, table2.date, and table2.code? If not, add the indexes and it should speed it up. If you already have indexes, then I'm out of ideas. You can try posting that question on another forum or search www.google.com or something.
Wednesday, March 7, 2012
Report Automation
hi
i have created a rss script for report deployment which i working pretty good but icheckout my all reports on report server and from another machine i mapped that drive from command line and run rss script then i got following error
================================================================
:\Automation>rs -i PromotionAutomation.rss -s http://fsaleem/reportserver -v en
v=Dev -v DBServer=fsaleem
Unhandled Exception: System.TypeInitializationException: The type initializer fo
r "Microsoft.ReportingServices.ScriptHost.ScriptHost" threw an exception. > S
ystem.Security.SecurityException: Request for the permission of type System.Secu
rity.Permissions.FileIOPermission, mscorlib, Version=1.0.5000.0, Culture=neutral
, PublicKeyToken=b77a5c561934e089 failed.
at System.Security.CodeAccessSecurityEngine.CheckHelper(PermissionSet granted
Set, PermissionSet deniedSet, CodeAccessPermission demand, PermissionToken permT
oken)
at System.Security.CodeAccessSecurityEngine.Check(PermissionToken permToken,
CodeAccessPermission demand, StackCrawlMark& stackMark, Int32 checkFrames, Int32
unrestrictedOverride)
at System.Security.CodeAccessSecurityEngine.Check(CodeAccessPermission cap, S
tackCrawlMark& stackMark)
at System.Security.CodeAccessPermission.Demand()
at System.Reflection.Assembly.VerifyCodeBaseDiscovery(String codeBase)
at System.Reflection.Assembly.GetName(Boolean copiedName)
at System.Reflection.Assembly.GetName()
at Microsoft.ReportingServices.ScriptHost.ScriptHost..cctor()
The state of the failed permission was:
<IPermission class="System.Security.Permissions.FileIOPermission, mscorlib, Vers
ion=1.0.5000.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"
version="1"
PathDiscovery="Z:\Automation\RS.exe"/>
End of inner exception stack trace
at Microsoft.ReportingServices.ScriptHost.ScriptHost.Main(String[] args)
================================================================
I think what's happening here is that the CAS security in .NET reduces the security privileges because you mapped the drive. If you add the mapped location to the trusted sites in IE does it work? It should work if you copy the files locally.Saturday, February 25, 2012
Report automatically runs with null params allowed. SSRS 2000
I see that other people have had a problem with this, but I never saw
a solution or a work around. Has anyone found a work around to this
issue? I want to allow nulls in all the parameters, but I don't want
the report to run by itself in Report Manager.
And does anyone know if this is fixed in SSRS 2005? We'll be
migrating to that later this year.
TIAOn Sep 17, 10:20 am, beener <rmp_c...@.hotmail.com> wrote:
> I see that other people have had a problem with this, but I never saw
> a solution or a work around. Has anyone found a work around to this
> issue? I want to allow nulls in all the parameters, but I don't want
> the report to run by itself in Report Manager.
> And does anyone know if this is fixed in SSRS 2005? We'll be
> migrating to that later this year.
> TIA
As far as I know, this is still the case in SSRS 2005. I've seen
several posts requesting this functionality. The only thing I can
think of is to have one of the parameters not have a default value.
Sorry that I could not be of greater assistance.
Regards,
Enrique Martinez
Sr. Software Consultant
a solution or a work around. Has anyone found a work around to this
issue? I want to allow nulls in all the parameters, but I don't want
the report to run by itself in Report Manager.
And does anyone know if this is fixed in SSRS 2005? We'll be
migrating to that later this year.
TIAOn Sep 17, 10:20 am, beener <rmp_c...@.hotmail.com> wrote:
> I see that other people have had a problem with this, but I never saw
> a solution or a work around. Has anyone found a work around to this
> issue? I want to allow nulls in all the parameters, but I don't want
> the report to run by itself in Report Manager.
> And does anyone know if this is fixed in SSRS 2005? We'll be
> migrating to that later this year.
> TIA
As far as I know, this is still the case in SSRS 2005. I've seen
several posts requesting this functionality. The only thing I can
think of is to have one of the parameters not have a default value.
Sorry that I could not be of greater assistance.
Regards,
Enrique Martinez
Sr. Software Consultant
Report Authoring Using Report Builder
We have deployed multiple report builder models and the number 1 user complaint is that every report builder report created can only have 1 table or 1 matrix or 1 chart in per .rdl (or report created). Anyone found any way around this? Is there a fix planned?This was an intentional design constraint for this release. There are no workarounds. We are definitely looking to include this functionality in a future release.
report authentication: windows prompt removal
Hello,
I'm writing a small program to access reports on a remote server. When simply accessing the server, using the reportviewer, I get a windows prompt for login. I would like to avoid this by passing the account information.
How should I do this? (Maybe through network credentials)
Could someone send me some example code?
Thank you.
Jos
i'm not sure but i think you mean that you want to acces it through internet explorer?
if that's the case , demanding the credentials depends on the zone your page is in
see internet explorer>extra>internet options>security
by default internet explorer demands credentials for public sites and uses integrated login for intranet, that i wouldn't change
the clue is that any address with a dot . (be it in an ip or dns notation, eg 10.2.54.56 or webserver.domain.local) is regarded by default as a public address
so either you add the adress in the intranet zone or you change the adress in wins notation style ( eg //webserver/reports)
|||Well I want to access the reports through a small asp.net program. You may see the source code in http://www.odetocode.com/Articles/95.aspx
When I access my reportserver (http://sqlreport244_9.mysite4now.com/reportserver) I get prompted for a windows login.
What I would like to do is to set the credentials on the code of the asp.net program to avoid being shown the prompt. This program will be used by several users and don't want them to have to insert the password each time they access the reports.
Thanks
Report Authentication
This is a newbie question. I have the report viewer component on one of my
aspx pages. When the user tries to open this page, he/she is prompted with a
user/password on the server. How do I set up the reporting services so that
it uses a generic account for all requests from the web. Thanks.
PremWell, the wrong way is to turn on Anonymous access to the Report Server. The
proper way is to create a custom security extension to replace the default
Windows-based security.
--
Hope this helps.
----
Teo Lachev, MCSD, MCT
Author: "Microsoft Reporting Services in Action"
Publisher website: http://www.manning.com/lachev
Buy it from Amazon.com: http://shrinkster.com/eq
Home page and blog: http://www.prologika.com/
----
"Prem" <Prem@.discussions.microsoft.com> wrote in message
news:607E279E-7CC5-4135-BA35-F786EEE2A4E6@.microsoft.com...
> This is a newbie question. I have the report viewer component on one of my
> aspx pages. When the user tries to open this page, he/she is prompted with
a
> user/password on the server. How do I set up the reporting services so
that
> it uses a generic account for all requests from the web. Thanks.
> Prem
>|||Thanks Nell. That works.
"Nell" wrote:
> You need to grant permissions to your users and groups on the report server.
> You can do this at 'http://servername/reports', select the folder you want to
> apply permissions to, then select 'properties' and 'security'. Check out this
> article on Ode to Code for more info.
> http://odetocode.com/Articles/215.aspx
> You need to give browse access to the accounts you want to be able to view
> your reports, if you're using anonymous access then you need to make sure
> that the user you have set up in IIS for anonymous access (usually
> IUSR_computername or IWAM_computername) has this level of access. Don't
> give this account any unnecessary permissions though!
> "Prem" wrote:
> > This is a newbie question. I have the report viewer component on one of my
> > aspx pages. When the user tries to open this page, he/she is prompted with a
> > user/password on the server. How do I set up the reporting services so that
> > it uses a generic account for all requests from the web. Thanks.
> >
> > Prem
> >|||You need to grant permissions to your users and groups on the report server.
You can do this at 'http://servername/reports', select the folder you want to
apply permissions to, then select 'properties' and 'security'. Check out this
article on Ode to Code for more info.
http://odetocode.com/Articles/215.aspx
You need to give browse access to the accounts you want to be able to view
your reports, if you're using anonymous access then you need to make sure
that the user you have set up in IIS for anonymous access (usually
IUSR_computername or IWAM_computername) has this level of access. Don't
give this account any unnecessary permissions though!
"Prem" wrote:
> This is a newbie question. I have the report viewer component on one of my
> aspx pages. When the user tries to open this page, he/she is prompted with a
> user/password on the server. How do I set up the reporting services so that
> it uses a generic account for all requests from the web. Thanks.
> Prem
>
aspx pages. When the user tries to open this page, he/she is prompted with a
user/password on the server. How do I set up the reporting services so that
it uses a generic account for all requests from the web. Thanks.
PremWell, the wrong way is to turn on Anonymous access to the Report Server. The
proper way is to create a custom security extension to replace the default
Windows-based security.
--
Hope this helps.
----
Teo Lachev, MCSD, MCT
Author: "Microsoft Reporting Services in Action"
Publisher website: http://www.manning.com/lachev
Buy it from Amazon.com: http://shrinkster.com/eq
Home page and blog: http://www.prologika.com/
----
"Prem" <Prem@.discussions.microsoft.com> wrote in message
news:607E279E-7CC5-4135-BA35-F786EEE2A4E6@.microsoft.com...
> This is a newbie question. I have the report viewer component on one of my
> aspx pages. When the user tries to open this page, he/she is prompted with
a
> user/password on the server. How do I set up the reporting services so
that
> it uses a generic account for all requests from the web. Thanks.
> Prem
>|||Thanks Nell. That works.
"Nell" wrote:
> You need to grant permissions to your users and groups on the report server.
> You can do this at 'http://servername/reports', select the folder you want to
> apply permissions to, then select 'properties' and 'security'. Check out this
> article on Ode to Code for more info.
> http://odetocode.com/Articles/215.aspx
> You need to give browse access to the accounts you want to be able to view
> your reports, if you're using anonymous access then you need to make sure
> that the user you have set up in IIS for anonymous access (usually
> IUSR_computername or IWAM_computername) has this level of access. Don't
> give this account any unnecessary permissions though!
> "Prem" wrote:
> > This is a newbie question. I have the report viewer component on one of my
> > aspx pages. When the user tries to open this page, he/she is prompted with a
> > user/password on the server. How do I set up the reporting services so that
> > it uses a generic account for all requests from the web. Thanks.
> >
> > Prem
> >|||You need to grant permissions to your users and groups on the report server.
You can do this at 'http://servername/reports', select the folder you want to
apply permissions to, then select 'properties' and 'security'. Check out this
article on Ode to Code for more info.
http://odetocode.com/Articles/215.aspx
You need to give browse access to the accounts you want to be able to view
your reports, if you're using anonymous access then you need to make sure
that the user you have set up in IIS for anonymous access (usually
IUSR_computername or IWAM_computername) has this level of access. Don't
give this account any unnecessary permissions though!
"Prem" wrote:
> This is a newbie question. I have the report viewer component on one of my
> aspx pages. When the user tries to open this page, he/she is prompted with a
> user/password on the server. How do I set up the reporting services so that
> it uses a generic account for all requests from the web. Thanks.
> Prem
>
Report Authenication
Hi,
I have the following scenario:
1) A database server (machine A, Windows 2000 Server Standard) within a
domain
2) A Reporting Server (machine B, Windows 2003 Server Standard) within the
same domain; the Reporting-Services-Database is also on the Database-server
3) My Client (windows XP) within the same domain.
My Problem: When I publisch a Report to the Reporting Server and I use
"Windows integrated Authentication", I get an error "NT-Authority/Anonymous
User" cant access the database. That's right, but why wouldn't be used my
Username/password instead of Anonymous? What have I to do, to pass my
Logonuser form XP over the Reporting-Services to the Database ? A always
thought "windows integrated authentication" in the shared Connection object
would be enough?
Thanks
HansIt sounds to me like you have IIS setup for anonymous access. When you do
this all users are the same user (the anonymous user as you see below). You
should only be doing this if you are implementing custom authorization.
Otherwise this is a really bad idea.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Hans" <sqlserver@.strengberg.net> wrote in message
news:O0FIBcazEHA.2600@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I have the following scenario:
> 1) A database server (machine A, Windows 2000 Server Standard) within a
> domain
> 2) A Reporting Server (machine B, Windows 2003 Server Standard) within the
> same domain; the Reporting-Services-Database is also on the
Database-server
> 3) My Client (windows XP) within the same domain.
> My Problem: When I publisch a Report to the Reporting Server and I use
> "Windows integrated Authentication", I get an error
"NT-Authority/Anonymous
> User" cant access the database. That's right, but why wouldn't be used my
> Username/password instead of Anonymous? What have I to do, to pass my
> Logonuser form XP over the Reporting-Services to the Database ? A always
> thought "windows integrated authentication" in the shared Connection
object
> would be enough?
> Thanks
> Hans
>|||If you are trying to authenticate in a scenerio which involves more than two
endpoints eg: client connects to remote RS web service which connects to a
remote database (3 endpoints) and tou're using Integrated security, NTLM
authentication will not work, you will have to setup a delegation scenerio
using kerberos authentication which supports more than one network hop. Look
here for help about setting up a asp.net app for delegation.
http://support.microsoft.com/default.aspx?scid=kb;en-us;810572
To test that this is your problem first access the report from the reporting
services console, this seems to cache your credential locally, now try from
your remote client try the same report, it should work until you log off the
rs server.
HTH,
Dan
"Hans" wrote:
> Hi,
> I have the following scenario:
> 1) A database server (machine A, Windows 2000 Server Standard) within a
> domain
> 2) A Reporting Server (machine B, Windows 2003 Server Standard) within the
> same domain; the Reporting-Services-Database is also on the Database-server
> 3) My Client (windows XP) within the same domain.
> My Problem: When I publisch a Report to the Reporting Server and I use
> "Windows integrated Authentication", I get an error "NT-Authority/Anonymous
> User" cant access the database. That's right, but why wouldn't be used my
> Username/password instead of Anonymous? What have I to do, to pass my
> Logonuser form XP over the Reporting-Services to the Database ? A always
> thought "windows integrated authentication" in the shared Connection object
> would be enough?
> Thanks
> Hans
>
>|||Thanks to all for the answers. I have disabled all anonymous and I think,
its the problem, Dan reports, because I have 3 Endpoints. I will test this.
Thanks again.
Hans
"Hans" <sqlserver@.strengberg.net> schrieb im Newsbeitrag
news:O0FIBcazEHA.2600@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I have the following scenario:
> 1) A database server (machine A, Windows 2000 Server Standard) within a
> domain
> 2) A Reporting Server (machine B, Windows 2003 Server Standard) within the
> same domain; the Reporting-Services-Database is also on the
> Database-server
> 3) My Client (windows XP) within the same domain.
> My Problem: When I publisch a Report to the Reporting Server and I use
> "Windows integrated Authentication", I get an error
> "NT-Authority/Anonymous User" cant access the database. That's right, but
> why wouldn't be used my Username/password instead of Anonymous? What have
> I to do, to pass my Logonuser form XP over the Reporting-Services to the
> Database ? A always thought "windows integrated authentication" in the
> shared Connection object would be enough?
> Thanks
> Hans
>
I have the following scenario:
1) A database server (machine A, Windows 2000 Server Standard) within a
domain
2) A Reporting Server (machine B, Windows 2003 Server Standard) within the
same domain; the Reporting-Services-Database is also on the Database-server
3) My Client (windows XP) within the same domain.
My Problem: When I publisch a Report to the Reporting Server and I use
"Windows integrated Authentication", I get an error "NT-Authority/Anonymous
User" cant access the database. That's right, but why wouldn't be used my
Username/password instead of Anonymous? What have I to do, to pass my
Logonuser form XP over the Reporting-Services to the Database ? A always
thought "windows integrated authentication" in the shared Connection object
would be enough?
Thanks
HansIt sounds to me like you have IIS setup for anonymous access. When you do
this all users are the same user (the anonymous user as you see below). You
should only be doing this if you are implementing custom authorization.
Otherwise this is a really bad idea.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Hans" <sqlserver@.strengberg.net> wrote in message
news:O0FIBcazEHA.2600@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I have the following scenario:
> 1) A database server (machine A, Windows 2000 Server Standard) within a
> domain
> 2) A Reporting Server (machine B, Windows 2003 Server Standard) within the
> same domain; the Reporting-Services-Database is also on the
Database-server
> 3) My Client (windows XP) within the same domain.
> My Problem: When I publisch a Report to the Reporting Server and I use
> "Windows integrated Authentication", I get an error
"NT-Authority/Anonymous
> User" cant access the database. That's right, but why wouldn't be used my
> Username/password instead of Anonymous? What have I to do, to pass my
> Logonuser form XP over the Reporting-Services to the Database ? A always
> thought "windows integrated authentication" in the shared Connection
object
> would be enough?
> Thanks
> Hans
>|||If you are trying to authenticate in a scenerio which involves more than two
endpoints eg: client connects to remote RS web service which connects to a
remote database (3 endpoints) and tou're using Integrated security, NTLM
authentication will not work, you will have to setup a delegation scenerio
using kerberos authentication which supports more than one network hop. Look
here for help about setting up a asp.net app for delegation.
http://support.microsoft.com/default.aspx?scid=kb;en-us;810572
To test that this is your problem first access the report from the reporting
services console, this seems to cache your credential locally, now try from
your remote client try the same report, it should work until you log off the
rs server.
HTH,
Dan
"Hans" wrote:
> Hi,
> I have the following scenario:
> 1) A database server (machine A, Windows 2000 Server Standard) within a
> domain
> 2) A Reporting Server (machine B, Windows 2003 Server Standard) within the
> same domain; the Reporting-Services-Database is also on the Database-server
> 3) My Client (windows XP) within the same domain.
> My Problem: When I publisch a Report to the Reporting Server and I use
> "Windows integrated Authentication", I get an error "NT-Authority/Anonymous
> User" cant access the database. That's right, but why wouldn't be used my
> Username/password instead of Anonymous? What have I to do, to pass my
> Logonuser form XP over the Reporting-Services to the Database ? A always
> thought "windows integrated authentication" in the shared Connection object
> would be enough?
> Thanks
> Hans
>
>|||Thanks to all for the answers. I have disabled all anonymous and I think,
its the problem, Dan reports, because I have 3 Endpoints. I will test this.
Thanks again.
Hans
"Hans" <sqlserver@.strengberg.net> schrieb im Newsbeitrag
news:O0FIBcazEHA.2600@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I have the following scenario:
> 1) A database server (machine A, Windows 2000 Server Standard) within a
> domain
> 2) A Reporting Server (machine B, Windows 2003 Server Standard) within the
> same domain; the Reporting-Services-Database is also on the
> Database-server
> 3) My Client (windows XP) within the same domain.
> My Problem: When I publisch a Report to the Reporting Server and I use
> "Windows integrated Authentication", I get an error
> "NT-Authority/Anonymous User" cant access the database. That's right, but
> why wouldn't be used my Username/password instead of Anonymous? What have
> I to do, to pass my Logonuser form XP over the Reporting-Services to the
> Database ? A always thought "windows integrated authentication" in the
> shared Connection object would be enough?
> Thanks
> Hans
>
Subscribe to:
Posts (Atom)