Forum Discussion
Displaying status in Dashboard
Jim123456789jim wrote:
Hi,
I have a requirement to display status of few SQL agent jobs in Power BI dash board. As we do not have something like Gauge, I'm using Pie chart. I have written a sql to get the job status from MSDB and want to show GREEN when the status is completed and RED when it is failed and AMBER when it is running.
Please suggest how to implement the conditional formating for Pie chart in Power BI.
OR please suggest a better visualization or approach to display the JOB run status in Power BI.Thanks much!!
Cheers
JimYou can use CASE WHEN in your SQL query to get the status as 1,0,-1 and use conditional format in a table visual.
Alternatively, you can also create a calculate column and set it as image URL to show status as images.
ImageUrl = SWITCH(Table1[Status],-1, "https://upload.wikimedia.org/wikipedia/commons/thumb/0/07/Button_Icon_Red.svg/240px-Button_Icon_Red.svg.png",0,"https://encrypted-tbn0.gstatic.com/images?q=tbn:ANd9GcRE5JPCqBtrGkJLVkxojSObXv9Ha7dzLzn-qwNB_EJBLEgluRjn","https://camtools.cam.ac.uk/access/content/public/Status%20Panel/Resources/warning_256.png")
Hi,
I have a requirement to display status of few SQL agent jobs in Power BI dash board. As we do not have something like Gauge, I'm using Pie chart. I have written a sql to get the job status from MSDB and want to show GREEN when the status is completed and RED when it is failed and AMBER when it is running.
Please suggest how to implement the conditional formating for Pie chart in Power BI.
OR please suggest a better visualization or approach to display the JOB run status in Power BI.
Thanks much!!
Cheers
Jim
Microsoft Employee
Jim123456789jim wrote:
Hi,
I have a requirement to display status of few SQL agent jobs in Power BI dash board. As we do not have something like Gauge, I'm using Pie chart. I have written a sql to get the job status from MSDB and want to show GREEN when the status is completed and RED when it is failed and AMBER when it is running.
Please suggest how to implement the conditional formating for Pie chart in Power BI.
OR please suggest a better visualization or approach to display the JOB run status in Power BI.
Thanks much!!
Cheers
Jim
You can use CASE WHEN in your SQL query to get the status as 1,0,-1 and use conditional format in a table visual.
Alternatively, you can also create a calculate column and set it as image URL to show status as images.
ImageUrl = SWITCH(Table1[Status],-1, "https://upload.wikimedia.org/wikipedia/commons/thumb/0/07/Button_Icon_Red.svg/240px-Button_Icon_Red.svg.png",0,"https://encrypted-tbn0.gstatic.com/images?q=tbn:ANd9GcRE5JPCqBtrGkJLVkxojSObXv9Ha7dzLzn-qwNB_EJBLEgluRjn","https://camtools.cam.ac.uk/access/content/public/Status%20Panel/Resources/warning_256.png")
Thanks Eric. It worked. One small question. Is it possible to have the images like .png or .JPEG in a folder and use that path in the SWITCH case instead of the http:// link?
The folder can be machine drive like C: or D: .. or even if possible will create a folder within power bi solution, copy paste the images and use it in the Dashboard. Pls suggest if it is possilbe. Thanks!!
- Eric_Zhang
Jim123456789jim wrote:
Thanks Eric. It worked. One small question. Is it possible to have the images like .png or .JPEG in a folder and use that path in the SWITCH case instead of the http:// link?
The folder can be machine drive like C: or D: .. or even if possible will create a folder within power bi solution, copy paste the images and use it in the Dashboard. Pls suggest if it is possilbe. Thanks!!
The report won't recogize those c: or d: paths when published to Power BI service. Maybe you can convert those images as base64 string, see this thread.