Report Server KPI Status

[jgilbert]

I am trying to create a dataset to control the status (red, green) of a KPI widget. I used the below query (minus count from year 1 YTD and year 2 YTD) hoping that just being a negative or positive number would provide me with my intended results however it looks like the KPI widget is expecting ecaxtly 1, 0, or -1. I am not sure how best to accomplish this.

SELECT
  (SELECT COUNT(nEventID) FROM Xyz123View WHERE YEAR(tCreationTime)=YEAR(DATEADD(year,-1,GETDATE())))-(SELECT COUNT(nEventID) FROM Xyz23View WHERE YEAR(EMXMLAgencyEventView.tCreationTime)=YEAR(GETDATE())) AS difference

[Anonymous · Accepted solution]

Hi @jgilbert ,

In Report Server Portal,when you create a KPI, ou could set "Status".This field compares the key performance indicator value and goal scores. It has only three options -1 bad, 0 (neutral) and 1 (good). At the same time, this values changes the color of the KPI.

For more details, you can read related document: How to automatically create KPIs in SQL Server Reporting Services (sqlshack.com)

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.