Creating a formula for a Report

[jendugan]

Hello all,

Background:

I have formula indicators set up that show the following:

(([[SOM - Number of new incidents > Priority = 1 - Critical]]) * 125) + (([[SOM - Number of new incidents > Priority = 2 - High]]) * 25) + (([[SOM - Number of new incidents > Priority = 3 - Moderate]]) * 3) + (([[SOM - Number of new incidents > Priority = 4 - Low]]) * 2)

Which works perfectly:

However, I have now been asked to create a report that shows the LIVE severity score to put on a dashboard.

I see (by searching the forum) there is a possibility to create a script include but I am not sure how to accomplish this.

Any help would be greatly appreciated.

[Dennis R · Accepted solution]

To get the exact value, you'll need to know the exact data that the formula indicator is based on. Can you either paste or tell us the conditions on the SOM - Number of new incidents indicator, as well as the query that is the basis of it in your indicator source?

Once you have that info, you should be able to pretty easily create a dynamic content block on your dashboard that calculates the value you're looking for on the fly.

[jendugan]

Indicator Source

Indicator has NO additional conditions

So I took this indicator and created the Formula indicator above.

Thanks for your help

[Dennis R]

So here's what I'd do. On your dashboard, create a new Dynamic Content Block.

Click the hyperlink that says "Click here".

This will open up a dynamic content record. Name it something like "Current Severity Score", and in the Dynamic content script window, enter the following code:

<?xml version="1.0" encoding="utf-8" ?>
<j:jelly trim="false" xmlns:j="jelly:core" xmlns:g="glide" xmlns:j2="null" xmlns:g2="null">
    <g:evaluate var="jvar_count" jelly="true">
        var priWeight = [ 125, 25, 3, 2, 0 ];
        var incQuery = 'sys_created_onONToday@' +
                'javascript:gs.beginningOfToday()@javascript:gs.endOfToday()^' +
                'call_type=incident^' +
                'status!=cancelled';
        var gaInc = new GlideAggregate('incident');
        gaInc.addEncodedQuery(incQuery);
        gaInc.groupBy('priority');
        gaInc.addAggregate('COUNT');
        gaInc.query();
        var curSeverity = 0;
        while (gaInc.next()) {
            curSeverity += parseInt(gaInc.getAggregate('COUNT')) *
                    priWeight[parseInt(gaInc.priority) - 1];
        }
        curSeverity;
    </g:evaluate>
    
    <div style="font-size: 5em; text-align: center; width: 100%;">
        ${jvar_count}
    </div>
</j:jelly>

A few notes about that code:

• I'm guessing some of the field names and values given your screenshot. You might have to tweak that encoded query to get it exactly right. If you look at the XML of your indicator source that has the condition builder you screenshotted, it should have the source query that you can copy-and-paste into the code above into the incQuery variable.
• Feel free to tweak the size of the digits that show up and make them larger or smaller than 5em. You can specify a specific px value instead if you want.
• I put the priority weights in an array at the start. As the formula you provided is P1 count * 125 + P2 * 25 + P3 * 3 + P4 * 2, that should give you the number you're looking for. If this formula or the weights change, tweak the values in the array to account for the new values. Note that I put a zero multiplier for P5 incidents. If you don't use P5 incidents, then don't worry about that. I mainly included it because if somehow a P5 sneaks in, not having a weight for it will cause the formula to generate a NaN value.
• As a side note, your condition that "Call type is Incident" and "Call type is not one of Information, Inquiry / Question, Service Request" is redundant. These conditions are mutually exclusive, so if the call type is Incident, by definition it cannot be Information, Inquiry / Question, or Service Request. Feel free to leave it as-is if you want, but I just thought I'd point that out.

Hopefully this helps, let me know how it works for you.
--Dennis R

[jendugan]

Got it going!! Thanks

Cross referenced for validation and all is well.

Thanks again for your help.

[jendugan]

Dennis,

Thanks for all your help, I am working with a team member to get this setup. I will let you know how it works out!

Jen