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.
Pingback: What are SSRS 2016 KPI Reports? | Aunt Kathi's SQL Server Home
Thank you for sharing such nice article.
I was implementing caching on dataset and i already developed KPI, but at my surprise its not updated automatic, i need to refresh page to take effect , is that intended behavior ?
I thought once my cache refreshed( in my case it is every hour) KPI automatically refreshed
can you help me ?
Do you mean that the page was already loaded and you expected it to just change?
The page is not going to automatically change. You would need to refresh the page. That is expected behavior.
Then why we need to implement cache. If need to refresh page then why i create cache, does it not automatically fetch new records ?
No, it doesn’t not automatically fetch new records unless the dataset cache is implemented. This is to avoid having these KPIs query the database every time someone brings up the web portal.
Ok. Got my answer. Thanks for quick reply.
One more thing how do i include KPI in my SSRS report ? Is it possible ? Sorry this may be out of scope question but just want to try.
I don’t believe you can add the new KPIs to a traditional report, but there are several ways to add KPIs to reports using the older methods. You can use gauges for a large image or use the indicator control to add a KPI inside a tablix.