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