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

[tltoulson]

Hi Emma,

I may need some clarification but it sounds like you have a custom application with 3 tables set up like so:

Dashboard <=> m2m <=> Measure

And if I understand correctly, both Measures and Dashboards have a RAG status field.   And you want a change in the Measure's RAG status to change the overall Dashboard RAG status.   Am I on track so far?

[ebaileyybs]

Hi Travis,

Yes, that's correct. I have 2 tables (u_dashboards & u_measures) and I have a m2m relationship between the 2 tables.

Both the Dashboards and Measures tables have a RAG field.

Thanks,

Emma

[tltoulson]

Ok, great, thank you for clarifying.   So basically, we need to query all the u_measure records that fall under the u_dashboard record, calculate the average, and then update the u_dashboard record.   I would create a Script Include to handle the leg work and break up each key part into functions:   getDashboardsForMeasure, getMeasuresForDashboard, recalculateDashboard.   You could then use this script include either when calculating measures (in Scheduled Job, Event, Business Rule, etc) or in a Business Rule on the u_measure table when the RAG field is updated.   Below is a stub for the Script Include to give you a jumpstart.

The Script Include is not tested in any way and definitely needs some changes by you to get it working but it should give a good idea of the process.   Encapsulating the work in a Script Include like this will allow more flexibility and easier maintenance.   If you have further questions, or if I've gone way out in left field somewhere let me know!     Hope this helps.

Kind regards,

Travis

var DashboardUtil = Class.create();

DashboardUtil.prototype = {

       initialize: function() {

                   this.m2m = 'm2m table name here';

                   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_measure reference field', 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_dashboard reference field', 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_measure.u_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_dashboard');

                   gr.get(dashboard);

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

                   gr.update();

       },



       type: 'DashboardUtil'

}

Sample Use:

// This script could be used in a Business Rule on u_measure when a RAG field changes in order to update all associated Dashboards

var du = new DashboardUtil();

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

while (dashboards.next()) {

         du.recalculateDashboard(du.u_dashboard.sys_id);

}

[ebaileyybs]

Hi,

Thank for this, I'll giver this a try now.

Just quick question - the field I have set up for RAG status are choice fields. Do they need to be reference fields and what do I need to reference?

The part of the script I am referring to is;

1. getDashboardsForMeasure: function(measure) {  
2. var gr = new GlideRecord(this.m2m);  
3.                   gr.addQuery('u_measure reference field', measure);  
4.                   gr.query();  
5. return gr;  
6.       },  
8. /*
9.                   Returns GlideRecord for m2m table for all Measures related to a given Dashboard
10.                   use: Retrieves all Measures for a given Dashboard which allows calculation of the Dashboard RAG
11.                   dashboard: (String) sys_id for the u_dashboard record
12.       */  
13.       getMeasuresForDashboard: function(dashboard) {  
14. var gr = new GlideRecord(this.m2m);  
15.                   gr.addQuery('u_dashboard reference field', dashboard);  
16.                   gr.query();  
17. return gr;  

Apologies, I have limited scripting knowledge. Your help is greatly appreciated

Kind regards,

Emma