1

I've been trying to get around this issue, and was hoping someone here might have paced the same problem.

My SSRS report has some default parameters that are set dynamically. What is happening is that when a report page is first opened (these reports are running from a custom web app), the 'main' report sproc is fired once, just to grab those 2-3 dynamic parameters, even though no other data from that sproc is displayed at this time (if this seems like overkill, it is...I inherited these reports from a former coworker who designed them). So, since there are 2-3 dynamic parameters, the report was designed to get these default values from a query. So, by the time the report is finally run by the user, the main sproc (just call it "report_getReportData" for simplicity) will have ran about 3-4 times already before it's ever rendered to the page.

What I did was, within the report_getReportData sproc, before doing my select * statement to display all the report data, I created a physical table to hold the 2-3 default values, and I created a new dataset in the report to just run a new sproc that just selects the value from this physical table, nothing else, so the result is the getReportData sproc only fires once, and my new sproc, let's call it 'report_getTwoParameters' fires once for each parameter, but the time cost is negligible since is just does one quick select statement.

This solved the issue of the overall report performance, but since there is now a physical table involved (report_getReportData returns data from a temp table), we face the issue of multiple users not being able to run this report simultaneously. So I guess my post has two questions:

1) Is there even a way to get around the main issue of having the report have to run report_getReportData just to set the 2-3 parameters - like maybe "multicast" the result set returned from the sproc into a couple different datasets or something?

2) If we decide to keep my solution of using the physical table, would anyone have any alternate solutions to this, in order for users to be able to run the report simultaneously but avoid bumping into this same physical table?

Ben
  • 337
  • 1
  • 6
  • 20
  • Have you tried using a table variable instead of a physical table? That should solve your multi-user problem and be fairly fast to implement if you already have a physical table. – Eric Hauenstein Oct 10 '13 at 15:02
  • Hi Eric - I am working on implementing your idea now and will let you know of questions - thanks! – Ben Oct 10 '13 at 16:45
  • Ok, I feel stupid. The 2 params in question are internal params that are being calculated and returned from the main sproc to the report's main dataset. These are aggregate values that aren't even set by the user nor displayed in the report; our report has a web link that opens up a window and downloads the report into a tab-delimited text file, sending the 2 values as URL params. Since they are already being calculated and returned, aren't set by the user, and aren't displayed on the report, I just removed them as params and I can just call them from the Fields property for the URL string. – Ben Oct 10 '13 at 19:17
  • Funny how the problem you end up solving often has no relation to the problem you start with. =) – Eric Hauenstein Oct 10 '13 at 20:17
  • Exactly! But thanks for the suggestion anyway. I didn't even think about using a table variable :) – Ben Oct 11 '13 at 14:45

0 Answers0