Dataset caching/filtering question
I have a database that is populated daily. It contains a large amount of data. Ideally what I would like to do one retrieval of the data and then allow the users to filter this data within the report without having to make a call to the database. Ideal solution would be a report snapshot which you could use parameters on to filter the dataset, but would really only filter the dataset that was obtained when the snapshot was created. Is this possible? Thanks for any suggestions. Sam
December 16th, 2010 4:20pm

Hi Sam, It is possible. Since the database is populated daily, we can first set the Execution property of the report to Render this report from a report execution snapshot. Then, configure a schedule to the snapshot which is executed daily. After that, the report snapshot will execute the query and produces the intermediate format in advance of the user’s request to view the report. When the report snapshot executes the query, it retrieves all required data from the database. So, if use parameters to filter the data in report, it will not execute the query again. However, please be aware we are not allow to create snapshot on the report which includes query parameter. For more information about snapshot in Reporting Services, please refer to the link below and check the section Running Reports From Snapshots: http://msdn.microsoft.com/en-us/library/ms159241(v=SQL.100).aspx Thanks, Tony Chain Tony Chain [MSFT] MSDN Community Support | Feedback to us Get or Request Code Sample from Microsoft Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Free Windows Admin Tool Kit Click here and download it now
December 20th, 2010 3:03am

Hi Sam, It is possible. Since the database is populated daily, we can first set the Execution property of the report to Render this report from a report execution snapshot. Then, configure a schedule to the snapshot which is executed daily. After that, the report snapshot will execute the query and produces the intermediate format in advance of the user’s request to view the report. When the report snapshot executes the query, it retrieves all required data from the database. So, if use parameters to filter the data in report, it will not execute the query again. However, please be aware we are not allow to create snapshot on the report which includes query parameter. For more information about snapshot in Reporting Services, please refer to the link below and check the section Running Reports From Snapshots: http://msdn.microsoft.com/en-us/library/ms159241(v=SQL.100).aspx Thanks, Tony Chain Tony Chain [MSFT] MSDN Community Support | Feedback to us Get or Request Code Sample from Microsoft Please remember to mark the replies as answers if they help and unmark them if they provide no help.
December 20th, 2010 3:03am

Hi Tony. Thanks for the reply. I am not fully understanding how to accomplish this. I need the user to be able to modify parameters and filter the results to meet their needs. In my head I am thinking that a report snapshot with all data, and then parameters that really just filter the already retrieved dataset would be the ideal solution. I just do not think this is possible, or haven't figured out how to accomplish this.
Free Windows Admin Tool Kit Click here and download it now
December 20th, 2010 12:39pm

Hi Sam, As you posted, a report snapshot is a report instance that contains layout information and data that is retrieved when the snapshot was created. After that, all requests use the same cached copy, and they can use parameter to filter the dataset in the report snapshot. For example, we can first create a report with parameterized filter. 1. Add a new data source to the report which connects the database. Add a dataset to retrieve data from data source. In my report, the dataset contains 3 data fields: SubCategoryKey, SubCategory Name and CategoryKey. 2. Add a table to the report, drag and drop these 3 data fields to the cells in the detail row. 3. Add a parameter @Category to the report and set the data type to Integer. 4. Add a filter to the dataset. Specify the CategoryKey field to Expression, = to Operator and the parameter @Category to Value. After that, the report only shows sub categories of the category which user specified in the parameter. Then, please deploy the report to the Report Server, and then we will configure the execution snapshot. 1. Open the Report Manager and navigate to the report Properties page. 2. In order to create snapshot, we need use stored credentials for the data sources. In the Data Sources tab, select Credentials stored securely in the report server. Then type in the User name and Password. If the account is a windows account, please also check Use as Windows credentials when connecting to the data source. 3. We also need to specify a default parameter value. In the Parameters tab, check the Has Default option, and type in a Default Value. 4. In the Execution tab, select Render this report from a report execution snapshot, and check Use the following schedule to create report execution snapshots. 5. Click Configure to set a schedule for generating the snapshot. In the Schedule details page, configure the schedule based on your requirement, then click OK. 6. Click Apply. After a snapshot has been generated, all user will see the same copy and use Category parameter to filter the sub category without access the database. Thanks, Tony ChainTony Chain [MSFT] MSDN Community Support | Feedback to us Get or Request Code Sample from Microsoft Please remember to mark the replies as answers if they help and unmark them if they provide no help.
December 22nd, 2010 4:11am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics