To show the data month wise you must map atleast 1 date field.
For now what I tested and would suggest is -
1. In the 3rd append sub-query i.e. for Open Count, just map or join the Submit Year to Submit Year.
2. Remove the Submit Date or Last Resolved Date filter from 3rd sub-query if you are using it.
3. Keep the Status filter as Not in list for Resolved\Closed\Cancelled.
4. Now you will get the open counts for each month for that year. Use the Advance Function for Open Count and the select Accumulative Total which will add the previous month's count and then show the total at the end of last row.
Let me know if this solution works, if not then will think through.
Maybe a bit off topic, but here the experts discuss this view.
Do you also have performance problems if you run the report as a normal user (not administrator and not unrestricted access)? So a user with 3-4 support groups and some module permissions.
With newRLS it takes me about 40 seconds, with noNewRLS about 20 seconds with only 76k tickets.
Do you use cache functions for the view or reports?
similar experiences have been reported by some of my customers and the BMC support Staff are looking into it. I assume you have reported a support ticket for this already? If not please do so, together with detailed information on how to reproduce.
the way I have calculated this before was to accumulate the number of submitted incidents from the first submitted incident, and the same for the first resolved incident, and the subtract these 2 numbers on a monthly basis.
The result would be:
Submitted Resolved Open(end of month)
Jan 2018 10 6 4
Feb 2018 20 10 14
Mar2018 5 19 0
Apr 2018 1 0 1
I could not get what you suggested to deliver the above results.
I have managed to accumulate submitted and resolved, but not subtract them
ACC Submitted Acc Resolved Open(end of month) Not Able to calculate
Jan 2018 10 6 10-6= 4
Feb 2018 30 16 30-16= 14
Ma r2018 35 35 35-0= 0
Apr 2018 36 35 36-35= 1
Any other thoughts of other approaches/formulas to use?
2 of 2 people found this helpful
Check the attached report that I have tested and it does provide correct results for all the counts.
I am using accumulative total for only Open Counts and not for submitted and resolved.
The attachment contains only report and not the view, folders etc.
While importing, the report need to be mapped to OOTB Incident Management view, Data Source and the folders that you want.
The report has filters for a date range, company and assigned group.
NOTE: Once import completes and you refresh it, you might see some abnormal counts. However, check the SQL Query and all fields should be from "HPD:Help Desk" form name.
You might see other form name like "HPD:WorkLog" for some fields. If this is the case, then you need to modify the report and the re-map the field by drag and drop or updating the calculated fields.
This happens because of a defect in Smart Reporting, where same name field mappings like Submit Date gets changed when importing which is not yet fixed\resolved.
Hopefully now I can say the report is complete.
Thanks for your tip, if only it were that simple.
The support so far only answers with standard phrases and of course I used them all. The problem is in the AR core and how RLS is treated. So you see the OOb incident dashboard quickly displayed to a normal user?
I will probably rather start my own thread again.