Question

• How can I create a custom job status for my jobs in BI reports?

Answer

If you want to measure job status based on your own criteria, you can do so in BI.

1. Navigate to your report in BI. Select Edit > Design
2. Click " Add Calculated Field" above your list of data sources
   
   
   
3. Give the column name the appropriate title (ex: Job Status)
4. In the expression section, use the CASE WHEN function
   
   
   
5. Think of this expression as asking a question - ex: is Date of Majority Completion filled in on a job? If yes, then set the status as 'Job Done'. If no, set the status as 'Job Not Done'. Therefore WHEN Date Majority Completion is greater (>) than 0 (so it has been filled in DASH), THEN display 'Job Done' as the status, ELSE display 'Job not Done' as the status. 
   • This expression would look like

   • CASE WHEN ([Date of Majority Completion] > 0) THEN 'Job Done' ELSE 'Job Not Done' END

   • This cheat sheet / manual contains all of the expressions that you can use to do your calculations (ex: >, <, =)
6. NOTE: spacing does not matter with expressions, if it makes it easier for you to read, you can add spacing in the calculation
7. Set your Data Type = Text
8. Once you've saved your Calculated Field successfully, you can search for it from the fields/data source menu on the left hand side or scroll down to the "Calculated Fields" folder in your data source list. Drag and drop it into your columns for your report.
   
   

Questions? Contact Us

Looking for more guided assistance with complex BI calculations? Check out our bi-weekly BI webinar with our BI subject matter experts.

Questions? Contact Us!