Last time, I talked about a very useful new feature of SSRS, KPI reports. These reports stand alone in the web portal, and they also run in the mobile Power BI apps along with Mobile Reports. To view the reports, you just launch the web portal and navigate to the folder where they are published. You could also add them to your Favorites folder to save some time.

Because these reports automatically show the data, the reports show cached data only. Imagine if hundreds or even thousands of report users brought the web portal page up each day causing the KPI reports to hit the database even when the report user was not interested in seeing the KPI reports at that time. That is why Microsoft decided to use cached data only in these reports. 

When the data changes, the KPI report will continue to show the same information unless you configure a cache refresh plan on the dataset. Follow these instructions so that the KPI data will refresh on a scheduled basis.

1.       Credentials must be saved in the data source for this to work, so configure that first if needed.

2.       Start up SQL Server Agent on the SQL Server instance hosting the Reporting Services databases if it is not running.

3.       Navigate to the dataset properties and click Caching.

4.       Select “Cache copies of this dataset and use them when available.”

5.       Under “Cache refresh plans” click Apply.

6.       Once you click Apply, you will see a link “Manage refresh plans”. Click it.

7.       Click “+ New cache refresh plan.”

8.       Fill in the schedule properties and click “Create cache refresh plan.” Be sure to consider how often the data is updated when creating the schedule.

You have now created a cache plan for the dataset so that the KPI data will stay up to date. Dataset caching is very similar to SSRS subscriptions. A SQL Agent job will be used to update the dataset. Just because the job runs successfully does not mean that the dataset was successfully refreshed. View the caching page to see the actual status.

There is one other interesting thing to know about KPIs and dataset caching. It’s possible to use a parameterized dataset with KPIs. In that case, a default parameter must be entered in the cache refresh plan properties.

In the KPI dataset properties, you will also supply a parameter value. It must be the same value as was entered into the cache refresh property.

IMPORTANT: When I first tried using a parameterized dataset with a KPI report, running the refresh job didn’t update the KPI. Microsoft corrected this recently with the release of the first cumulative update after Service Pack 1. Be sure to use apply this CU or avoid using a parameterized dataset with a KPI.

I saw several presentations and blog posts about the new KPIs but not many mention that dataset caching must be set up. This is important information to know if you plan to implement this feature at your company.