Are report parameters cached in the report server?

Dear Anyone,

I would like to ask if the report parameters in RS2005 are cached in the server. Im worried about this because each of our reports has an average of 6 report parameters. Each of which uses 2 data sets. 1 for retrieving the list of values and the other for retrieving the default values. Each report parameters are multi-select and almost all of them will contain not less than 500 selections. We have a very significant number of users and my worry is that this might bog down on our database server which is located on a seperate box.

Any opinions or clarifications anyone?

Thanks,

Joseph

No, the results of parameter queries are not cached.|||

One problem that we're looking at if the report parameters are not cached is the strain thta it will put on our database server.

We have several reports that has an average of 10 report parameters. Each of which has 2 data sets that calls upon stored procedures for their content. Some of this report parameters have dependencies with eachother.

Do you have any suggestions on how we can optimize the performance of the report parameters so that it would not affect the performance of our database server?

|||

1 .Create a custom "parameter picker" webform (ASP.NET 2.0) for your report which utilizes SQL Cache Notification -- Essentially, your ASP.NET app will hit the various parameter value lookup tables in SQL once and store the results in session, only returning to hit the SQL box again if the values in the tables change (and normally these values remain pretty static).

2. After your user has used your "custom" parameter picker to select values, either create a URL Access string and render the report, or utilize the ReportViewer control and programatically set the parameter values before rendering.

|||

I am wondering and confused why the developers of Reporting Services (even since the 2000 version) did not inlcude any cache option the report parameters.

Its so ovious that several report parameters can be used in a report and each of them can use 2 different data sets. This would always translate to several database hits especially of there are parameter dependencies.

I hope this is a feature that will be included in the next version of Reporting Services or maybe the next service pack. I like Reporting Services alot but not having any cache option in the report parameters really stinks.

|||

I don't think we anticipated that parameter queries would be costly, so we didn't think caching them would be a significant benefit. Our intended usage was that you would run a simple query to bring back 10 or 20 valid values for the parameter.

Thanks for the feedback, we'll consider this for a future version. It's always interesting to find out how customers end up using the features!

|||

I think we found a viable alternative to the caching of several report parameters.

All we need to do is put the actual parameters (id and values) into a single table. Since all parameters will not have equal numbers, we would just need to fill them with NULLS. We would only need one data set to call this table. We would also need to set our report parameters not to allow null report values.

I think with this, we can survive without having parameter cache for quite a while... assuming we dont have several cascading parameters which this workaround is totally not applicable.

|||

This would look like this kind of a table

ParamID1 ParamValue1 ParamID2 ParamValue2 ParamID3 ParamValue3

a d f f j j

b e g g k k

c NULL h h l l

NULL NULL i i NULL NULL

if the report parameters that will use this single data set is set to not to allow nulls, we will be able to use a single data set to power several report parameters.