Orginze Report "by week" on multi level pivot or PA table

QX
Tera Contributor

Hi - I am trying to build reports using the report generator and PA, and I cant find a way to orginze incidents on a same fashion I do on powerbi. I want them on the column, to be organized by week, or month, something like the one below. 

 

Basically column has the totals of incidents on each of last 4 weeks, and then the rows I will have either assignment group, category or whatever breakdown I choose. The blow screen shot is from powerbi, and Id like something similar on SN PA or Reports. 

 

Thanks. 

 

QX_0-1684446239228.png

 

3 REPLIES 3

AndersBGS
Tera Patron
Tera Patron

Hi @QX ,

 

I think that the pivot or muti-pivot table in the report designer is the best option - though, I do not think that will fulfil your requirement. In general, if what you would like to obtain is the above report, I would recommend you to stick with PowerBI, as the tool is visualization perspective have a lot more options. 

 

If my answer has helped with your question, please mark my answer as accepted solution and give a thumb up.

 

Best regards

Anders

If my answer has helped with your question, please mark my answer as the accepted solution and give a thumbs up.

Best regards
Anders

Rising star 2024
MVP 2025
linkedIn: https://www.linkedin.com/in/andersskovbjerg/

QX
Tera Contributor

I appreciate your time on this. I have several users that just want things straight out of SN reports and dont have access to our BI reports, thats the reason I started all this. I created a field in the incident table called u_week_nr and then a BR on insert/update to populate that based on the sys_created_on so this is currently working on incidents that are not closed.

 

(function executeRule(current, previous /*null when async*/) {
var gdt = new GlideDateTime(current.sys_created_on);//Get the current week number
var gYear = gdt.getYear();
var gWeek = gdt.getWeekOfYearLocalTime();
current.u_week_nr='Week ' + gdt.getWeekOfYearLocalTime() +'-'+ gYear; //set the week number based on created date. 
})(current, previous);

Question is, what can I do to set this on all incidents? I tried a scheduled job on Dev, and then it fired "incident closed notification" on like 20k closed incidents. Not sure how would I run this on all closed incidents on PROD without triggering any notification. 

 

QX_0-1684521826219.png

 

jmayone
Tera Contributor

@QX Thanks for the tip RE: Week Numbering.  I'm curious if you ever resolved your unintended notification, or came up with another way to get to Week number.  Can you let us know if you have a moment?  ServiceNow should really have this attribute OOB so I applause your efforts 🍻