Set dashboards RAG status based on average related measures RAG status

[ebaileyybs]

Hi,

I am currently building a Dashboard application with related Measures. The Measures will each have a RAG status and the RAG status of the Dashboard will be an average of the related Measures.

For example;

Dashboard 1 has 3 related measures

Measure 1 RAG = Green

Measure 2 RAG = Green

Measure 3 RAG = Amber

Dashboard 1 RAG = Green

--------------------------------------------------------

Dashboard 2 has 5 related measures

Measure 1 RAG = Red

Measure 2 RAG = Red

Measure 3 RAG = Amber

Measure 4 RAG = Red

Measure 5 RAG = Amber

Dashboard 2 RAG = Red

The measures will be related to the dashboard via a m2m related list. I would like to have a rule in place which works out the average RAG of all related measures and populates the RAG status field on the overall dashboard.

Can anyone offer any advice on how to achieve this please?

Kind regards,

Emma

[ebaileyybs]

Hi Travis,

In reference to the business rule below, where do I pick up the sys_id for the measure please?

Will this work across several different dashboards with different related measures?

1. // This script could be used in a Business Rule on u_measure when a RAG field changes in order to update all associated Dashboards  
2. var du = new DashboardUtil();  
3. var dashboards = du.getDashboardsForMeasure('changed measure sys_id here');  
4. while (dashboards.next()) {  
5.         du.recalculateDashboard(du.u_dashboard.sys_id);  
6. }  

Thanks for your help.

Emma

[ebaileyybs]

Hi Travis,

I wondered if you had chance to look at this please?

Thank you for all of your help.

Kind regards,

Emma

[tltoulson]

Hi Emma,

So sorry for the delay in response.   First of all, no the RAG field does not need to be a reference field, it can be a Choice List.

Second of all (and I don't know why I didn't just do this in the first place), you should be able to get the sys_id of the measure by using the following:

1. var dashboards = du.getDashboardsForMeasure(current.sys_id);

As long as the business rule is on the u_measure table, this should work.   I have edited this in the original script above.

For your last question, this should work across multiple dashboards for multiple measures.   Here is the rundown of how the business rule will work with the script include:

1. A u_measure record is inserted/updated (depending on how you configure the rule)
2. The business rule runs:
3. The business rule calls the DashboardUtil script include
4. DashboardUtil gets all the dashboards associated with the u_measure by looking at the m2m table
5. For each associated dashboard, DashboardUtil recalculates the Dashboard by averaging all values from all of its associated u_measure records

Caution:   If you are calculating a bunch of measures in sequence (in a scheduled job for instance), the business rule idea might not be the best approach for performance reasons.

[ebaileyybs]

Hi Travis,

Thank you for your help with this. I have amended the script include and the business rule but I can't seem to get it to work.

UI Script Include:

var DashboardCurrentUtil = Class.create();  

DashboardCurrentUtil.prototype = {  

      initialize: function() {  

                  this.m2m = 'u_m2m_measures_dashboards';  

                  this.ragLookup = {  

                          'Red' : 0,  

                          'Amber' : 1,  

                          'Green': 2  

                  }  

                  this.revRagLookup = ['Red', 'Amber', 'Green'];  

      },  

      /*

                  Returns GlideRecord for m2m table for all Dashboards related to a given Measure

                  use:   If a measure changes, this retrieves all affected Dashboards

                  measure: (String) sys_id for the u_measure record

      */  

      getDashboardsForMeasure: function(measure) {  

                  var gr = new GlideRecord(this.m2m);  

                  gr.addQuery('u_current_rag', measure);  

                  gr.query();  

                  return gr;  

      },  

      /*

                  Returns GlideRecord for m2m table for all Measures related to a given Dashboard

                  use: Retrieves all Measures for a given Dashboard which allows calculation of the Dashboard RAG

                  dashboard: (String) sys_id for the u_dashboard record

      */  

      getMeasuresForDashboard: function(dashboard) {  

                  var gr = new GlideRecord(this.m2m);  

                  gr.addQuery('u_current_rag', dashboard);  

                  gr.query();  

                  return gr;  

      },  

      /*

                  dashboard: (String) sys_id for the u_dashboard record

      */  

      recalculateDashboard: function(dashboard) {  

                  var measures = this.getMeasuresForDashboard(dashboard);  

                  var total = 0;  

                  var count = 0;  

                  var avgRagScore = 0;  

                  while (measures.next()) {  

                          curRagScore = this.ragLookup[measures.u_measures.u_current_rag]; // Looks up a numeric value for the RAG string, this may not be needed if you are already storing RAG as an integer  

                          count = count + 1;  

                          total = total + curRagScore;  

                  }  

                  avgRagScore = total / count; // Average RAG  

                  avgRagScore = Math.round(newValue); // Or you could use floor or ceiling functions  

                  var gr = new GlideRecord('u_dashboards');  

                  gr.get(dashboard);  

                  gr.u_current_rag = this.revRagLookup[avgRagScore]; // Since we rounded to an integer, this will lookup the RAG string by index  

                  gr.update();  

      },  

      type: 'DashboardCurrentUtil'  

}

Business Rule:

Sorry, I do not know how to copy the script in the same way as you did above.

Can you spot any obvious errors?

Thank you for your help.

Kind regards,

Emma

[tltoulson]

Hi Emma,

I think I may have made a mistake in the business rule part.   Try changing line 5 to:

du.recalculateDashboard(dashboards.u_dashboard.sys_id);

In that line, you are passing the sys_id of the dashboard record to the utility script include to recalculate the Dashboard record.   So the u_dashboard should be the name of the field on your m2m table that stores the dashboard, which I am assuming is u_dashboard.