create a scheduled report for a single score report?

[dbrennan]

Hey everyone,

I am trying to create a scheduled report that will return the total number of users in a table I have set up.   I chose the single score type for the report as it returns the current total number of users in the table.   However, for some reason I am not able to make that report into a scheduled report. It is not an available option in the drop down box next to the save button in the upper right hand corner.

Nor can I view the report when I try to create a scheduled report from the scheduled report tab.

Are lists the only reports that you can send as a scheduled report?

I currently have a business rule set up to get the total number of current users in the table however I have it set to run and fire an event whenever someone is added or deleted from the table.   I only want this report to be sent once a week however and that didn't appear to be an option when I created the business rule/event/notification combo.

Any advice on how to do this as cleanly as possible?

Thank you,

Devlin

[Rylie Markle]

Hi! What you mentioned above is exactly what I'm trying to do. I need to send an email each month with the number of cases YTD. I was going to do this using a single score report, but that isn't possible. Would it be possible for me to script something like this in the body of an email? Total cases submitted Year to Date? 

[ldave]

Hi Rylie, yes I think you can do that with no issues.

In the body of the mail you can place the call to your mail script i.e.:

${mail_script:Get_total_cases}

the mail script will be something like this (please note that you'll have to edit table and query as per examples commented in line):

(function runMailScript(/* GlideRecord */ current, /* TemplatePrinter */ template,
/* Optional EmailOutbound */ email, /* Optional GlideRecord */ email_action,
/* Optional GlideRecord */ event) {

// Add your code here


var agg = new GlideAggregate('your ticket table'); //i.e.: incident
agg.addAggregate('COUNT');
agg.addEncodedQuery('your query'); //i.e. for tickets created this year: sys_created_onONThis year@javascript:gs.beginningOfThisYear()@javascript:gs.endOfThisYear()
agg.query();
if(agg.next()) {
template.print(agg.getAggregate('COUNT'));
}


})(current, template, email, email_action, event);

I hope this helps 🙂

[Rylie Markle]

Thank you!! I started my script and I can get it to count all HR Cases,  but I'm trying to pull only 1 type of HR service and only this year. Both "addEncodedQuery" lines where I'm trying to accomplish this are not working. Any ideas?

---

(function runMailScript( /* GlideRecord */ current, /* TemplatePrinter */ template,
    /* Optional EmailOutbound */
    email, /* Optional GlideRecord */ email_action,
    /* Optional GlideRecord */
    event) {

    // Add your code here

    var agg = new GlideAggregate('sn_hr_core_case');
    agg.addAggregate('COUNT'); // Count all Employee Recognition Cases opened this year 

    agg.addEncodedQuery('hr_service=employee_recognition');
    agg.addEncodedQuery('sys_created_onONThisyear@javascript:gs.beginningOfThisYear()@javascript:gs.endOfThisYear()');
    agg.query();

    if (agg.next()) {
        template.print(agg.getAggregate('COUNT'));
    }


})(current, template, email, email_action, event);​

[ldave]

Try using only one encoded query with everything in it.

Best way to get the encoded query is to go on a list of records (in your case on the sn_hr_core_case table), filter the list to get the set of results you want, and then right-clicking the last condition in the query and selecting "copy query".

That will place the encoded query inside the clipboard, than you can paste between brackets in the encoded query ' '.

Probably it will be something like:

'hr_service=employee_recognition^sys_created_onONThis year@javascript:gs.beginningOfThisYear()@javascript:gs.endOfThisYear()'

don't know why it converts the : to : even when in code, but it should be just :

[Rylie Markle]

Thank you so much! That worked!