Parameters in subreport

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Reporting Services » Reporting Services » Parameters in subreport

Parameters in subreport

Rate Topic

Display Mode

Topic Options

Author

Message

dzonny

Posted Tuesday, January 15, 2013 8:53 PM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, January 16, 2013 11:16 PM
Points: 3, Visits: 16

I have main report and subreport that is called from the main report. Subreport can be called multiple times depending on how many users the report is generated for, therefore I was thinking to use shared datasets to reduce the number of database calls.

The shared datasets are working fine as far as I am not passing UserID into the subreport (which I want to use to filter the output), otherwise the query is called more than once. I can't use page breaks as the report has quite specific layout and the output has to be broken down per user.

So in short the question is: what should I do to to ensure that the query in shared dataset is called just once?

Many thanks

Post #1407561

anthony.green

Posted Wednesday, January 16, 2013 1:33 AM

SSCertifiable

Group: General Forum Members
Last Login: Friday, April 12, 2013 3:51 AM
Points: 5,075, Visits: 4,831

You could cache the shared data set, that way when the subreport runs it runs against the cache instead of the database for each user.

Might not be feasible if the report is looking at real time data.

Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1407668

foxxo

Posted Wednesday, January 16, 2013 1:35 AM

Say Hey Kid

Group: General Forum Members
Last Login: Today @ 1:08 AM
Points: 707, Visits: 1,048

I believe you can just set the caching timeout and it should work. http://prologika.com/CS/blogs/blog/archive/2009/11/16/shared-datasets.aspx
Or you could you use a temp table and base it on that, instead of having a shared dataset?

Post #1407670

dzonny

Posted Wednesday, January 16, 2013 11:21 PM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, January 16, 2013 11:16 PM
Points: 3, Visits: 16

Temp tables are probably more correct solution, but in my case caching is definitely making the trick. Thanks Smile

Post #1408155

« Prev Topic | Next Topic »

Permissions