Displaying status in Dashboard

[Jim123456789jim]

Hi,
 

Admin - I posted the same thread in development forum. If that is not relavent pls close it.

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

[Anonymous]

@Jim123456789jim Hello Can you let me know how to pull sql job status information into Power BI. I ran a query to get status of jobs into a table but that table is created in MSDB system database.  However in power bi, I am not able to connect to that system database. I am not able to create that table in any other Database, though I can try importing but looking for better options.

[Jim123456789jim]

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

[Eric_Zhang · Accepted solution]

---

@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

---

@Jim123456789jim

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")

[Jim123456789jim]

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 😧 .. 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 😧 .. 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!!

---

@Jim123456789jim

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.