As of version 9.7.4 and later, it's possible to configure secured reports to cache retrieved items. Previously, when a report was refreshed, the core would query the database, assemble all items according to the configured dataview, then apply security and filtering before rendering. Now, when configured to do so, fetched report items are now stored in the database, ready to be retrieved the next time the report is executed, reducing rendering time.
Note that existing reports will not cache their items and will continue to run as usual (that is, retrieving items upon report execution, then purging them from the memory).
To use this feature, follow the instructions below.
- Navigate to the location of your reporting services.
- Click the report you wish to cache, then select Download to download the report's RDL (XML) file.
Note: You can also edit the report by using the Report Builder.
- Open the RDL file with a text editor such as Notepad ++.
- In the DataSet element of the file, create a new ReportId query parameter below the existing DataViewId and UserId parameters. Doing so will distinguish items between different reports for the same user on the same page, which may have the same dataview.
Note: If more than one DataSet element appears in the file, repeat steps 4 and 5 to update each additional element.
- Enter a comma and space after @DataViewId, then type @ReportId on the first line of the <CommandText> section to pass the ReportId query parameter into the user context stored procedure.
- Create a new ReportId parameter (i.e. <ReportParameter Name="ReportId">) below the DataViewId parameter.
- Create a <DataType> tag in the ReportId parameter and enter a value of Integer.
- Create a <Prompt> tag in the ReportId parameter and enter a value of ReportId.
- Save and close the file.
- Return to reporting services, then click Upload File.
- Click Browse and select the edited RDL file.
- Select the Overwrite item if it exists checkbox.
- Open a dashboard in WRM where the report is in use and refresh the view to see the new ReportId parameter, which will return the same data as it did previously.
- To hide the ReportID parameter, mark it as hidden in the report builder or modify the RDL file by creating a <Hidden> tag in the ReportId parameter (i.e. <ReportParameter Name="ReportId">) with a value of true.
The report will now be caching the items it retrieves in the security tables each time it runs by checking for data before querying the Kairos item tables upon refresh. To check the cached item retrieval process, review the WRM core log while a report is being refreshed.
Cached reports will produce log entries showing attempts to retrieve cached item header records for each itemType used in the report. Header records will only expire if there are modifications made in WRM to the security, dataview, or filter. Records that aren't set to expire will display a log of "Publish of cached security items for the report..."
