Let's set up caching for the sales commission report. What caching does, it allows Report Manager to run the SQL query one time and store the data for subsequent use. The length of time the data is stored is based on the length of time set in the properties. Parameter changes will also affect the length of time the query remains cached. This is why we made the SalesPersonID a report parameter and not a parameter in the stored procedure.
Let's set the cache properties so that the data will be available for half an hour after the data is requested. This will provide enough time for all the reports to be generated for our data driven subscription that we will set up later. First, we need to specify that we want to use caching:
- Hover to the right of the report name until an upside-down arrow appears, then click on the upside-down arrow.
- Select the Manage menu item.
- Click on the Processing Options menu item on the left-hand side.
- Select the second option button labeled: Cache a temporary copy of the report. Expire copy of report after a number of minutes.
- We will use the default value of
30. - Click on the Apply button at the bottom of the screen:
Now, we can set up a cache plan for the first of each month:
- Click on the Cache Refresh Options menu item on the left.
- Click on the toolbar item called new Cache Refresh Plan.
- Set the Description property to
Monthly Subscription Cache Plan. - Select the Shared schedule option button.
- Select the shared schedule we created, called First of Month at 2 am.
- Select the Use Default checkboxes for both parameters.
- Click on the OK button:
