Forum Discussion

Jim123456789jim's avatar
Jim123456789jim
Frequent Visitor
9 years ago
Solved

Displaying status in Dashboard

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

Need Help
  • Eric_Zhang's avatar
    Eric_Zhang
    9 years ago
    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")

     

5 Replies

  • Jim123456789jim's avatar
    Jim123456789jim
    Frequent Visitor
    9 years ago

    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's avatar
      Eric_Zhang
      Icon for Microsoft Employee rankMicrosoft Employee
      9 years ago
      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's avatar
        Jim123456789jim
        Frequent Visitor
        9 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    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.