image_thumb.png

Refreshing KPI Data in SSRS 2016

Continuing my blog from last week, Creating KPIs in SQL Reporting Services, I am now going to explain how to configure scheduled data refresh for the KPI that was created in that blog post.

Prerequisites
1.  SQL Server Agent must be running
2  Credentials must be stored in the Data Source
3. Caching must be enabled on the Dataset

With all of these things configured, open the classic Report Manager and navigate to the dataset that acts as the source for the KPI.  You could do this from the report also.  Hover over the dataset and click the drop down arrow.  Select Manage from the list of available options and the General Properties page will open.

image

If you haven’t already done so, enable Caching.  Then select Cache Refresh Options from the left navigation section.  Click New Cache Refresh Plan in the top header.

image

On the Cache Refresh Plan page supply a Description and configure a schedule.  You can leverage an existing schedule or configure a new one.  To configure a new schedule click the button labeled Configure.

image

Schedule the refresh to meet the requirements of your organization.  Then click the button labeled OK. Next, if you have been following along, execute the following script:

UPDATE dbo.AnnualSalesAndQuota
SET AnnualSales = 381123.54
WHERE SalesYear = 2016

You can wait for the refresh job to run or you can run it manually.  Remember at the beginning when I said the SQL Agent must be running?  Well the reason is because a job is actually created and is executed at the intervals you specified in the schedule. Don’t’ believe me, open SQL Server Management Studio (SSMS) expand the SQL Server Agent and expand jobs.  What do you see?

image

In addition, to any jobs that you created, you may also see several jobs with GUIDs as names.  Hmmm.  To refresh the KPI now, you can just execute the job that was created for that particular Cache Plan, but which job is it?  Don’t worry I got you covered.  Execute this query:

SELECT
c.Name ReportName,
rs.ScheduleID JobName
FROM
ReportServer.dbo.[Catalog] c
INNER JOIN ReportServer.dbo.Subscriptions s
ON c.ItemID = s.Report_OID
INNER JOIN ReportServer.dbo.ReportSchedule rs
ON c.ItemID = rs.ReportID
AND rs.SubscriptionID = s.SubscriptionID

In the list you will see your KPI name and a Job name.  That Job name will correspond to one of the jobs in the list of agent jobs. 

image

Once you have identified that job, start it.  After it succeeds go back to the KPI in the new web portal and refresh the page, it should resemble the following:

image

Now instead of Red it should be Green and the value should reflect the updated value from the table.  Pretty cool.  As always, if you have any questions please feel free to email me at patrick@sqllunch.com.

Talk to you soon,
Patrick LeBlanc
Data Platform Solution Architect

One thought on “Refreshing KPI Data in SSRS 2016

  1. Permalink  ⋅ Reply

    Tessa Blankenship

    August 26, 2016 at 6:35pm

    The refresh works great for me on the plan. I run into an issue when I set up a dataset that uses a user filter to show the data related to a specific rep. How do I fix that issue?

Leave a Reply

Your email will not be published. Name and Email fields are required.