• 0 Vote(s) - 0 Average
  • 5
  • 4
  • 3
  • 2
  • 1
Thread Modes

Business Rule or Report to alert for incidents open longer than XX amount of days
#1
Hi everyone, another quick question for you all.

I need to either have some kind of alert or simply a scheduled report, doesn't matter which, that will be emailed out and include any incidents that have been open longer than (let's just say for this example) 7 days. 

Basically a report or alert of some kind that will include any incidents that have been open longer than 7 days and are NOT in Resolved or Closed status.

I tried yesterday with some SQL, but it appears you cannot use SELECT statements in reporting, only built-in functions and without a list of what is available, I'm working blind and could use some tips.

Thanks
haroldtate, proud to be a member of EV CONNECT FORUM since Oct 2016.

#2
(01-04-2018, 09:22 AM)haroldtate Wrote: Hi everyone, another quick question for you all.

I need to either have some kind of alert or simply a scheduled report, doesn't matter which, that will be emailed out and include any incidents that have been open longer than (let's just say for this example) 7 days. 

Basically a report or alert of some kind that will include any incidents that have been open longer than 7 days and are NOT in Resolved or Closed status.

I tried yesterday with some SQL, but it appears you cannot use SELECT statements in reporting, only built-in functions and without a list of what is available, I'm working blind and could use some tips.

Thanks

Hello,
To be conformtable with "Easyvista alert", first create a reporting and make sure that it works well

Create a report :
- Parent Query : (is the purpose of report) Incidents
- Filter : Create date For 7 days + Meta status incident not in (completed)
- View : (create a list)

Once it's OK, edit filter, click "avanced mode" button" and copy SQL
Create an alert with SQL.

P.ABBE
ABBE Philippe, proud to be a member of EV CONNECT FORUM since Nov 2015.

#3
We have two reports: one for Incidents, one for Service Requests. They show anything and everything that has been opened greater than 7 days. We're a pretty large company so these reports generate a big long list... and unfortunately big reports cause a hang-up in the Scheduled Reports feature for us. I end up running these every Tuesday and just sending it out the old fashioned way. If your reports aren't as big then you may be able to schedule them. 

NOTE: Our numbering scheme for Incidents and Service Requests may be different from yours. Our Incidents start with an I (capital i) such as I123456. Our Service Requests start with an R such as R123456. This is reflected in the filter formula below, so change as needed or eliminate if you would like all tickets in one report. 

-------------------------------------------------------------------------------------------------------
Report Building Details: 
Report Name: Aged Incidents > 7 Days
Theme: Operation
Parent Query: Action Operation
Filter Name: All Incidents
  • Scroll all the way down to the bottom of the box and hit the "Advanced Mode" button then paste the following formula. 
  • Formula: (SD_REQUEST.RFC_NUMBER LIKE 'I%') and (AM_ACTION.END_DATE_UT IS NULL) and SD_REQUEST.STATUS_ID <> '8' AND (DATEDIFF (day, SD_REQUEST.CREATION_DATE_UT,GETUTCDATE()) > 7) AND AM_ACTION.ACTION_LABEL_EN NOT IN ('Recipient Validation', 'Customer Satisfaction Survey')
  • Formula Translation (for non-SQL people): Ticket Numbers that start with I, tickets without End Dates, tickets that do not have a status of Closed, tickets that were created more than 7 days ago, and tickets whose current action is not equal to 'Recipient Validation' or 'Customer Satisfaction Survey'
View Name: All Incidents
  • Displays: Number, Category (Last Level), Current Incident Status, Action Details*, Ticket Creation Date*, Group, Support Person, Recipient, Group Manager*
  • * = Custom field added to Parent Query. 

Scheduling a Report (incase anyone reading needs):
  • Go to Administration > Reports > Scheduled Reports. 
  • Click the plus (+) sign up at the top. 
  • Input desired name, how often you'd like the report to run, and who you would like it sent to. Note: in my experience, the system seems to be picky about this and wants you to actually search for the individuals via the icon to the right of the box instead of typing in the email. 
  • Towards the bottom, select the plus (+) sign under the work Reports. 
  • Click the first drop-down box that appears and locate the report you created, then select the applicable Filter and View. 
  • Choose the format that you would like to send the report in. Note that CSV will need to be imported into an Excel or Google Sheets spreadsheet. It will also send in a zipped file if the report is large. 
  • Click Finish. 
  • Make sure that the checkbox to the right of the report has a checkmark inside (enabled). 

#4
(01-08-2018, 10:11 AM)betsy.mioduch Wrote: We have two reports: one for Incidents, one for Service Requests. They show anything and everything that has been opened greater than 7 days. We're a pretty large company so these reports generate a big long list... and unfortunately big reports cause a hang-up in the Scheduled Reports feature for us. I end up running these every Tuesday and just sending it out the old fashioned way. If your reports aren't as big then you may be able to schedule them. 

NOTE: Our numbering scheme for Incidents and Service Requests may be different from yours. Our Incidents start with an I (capital i) such as I123456. Our Service Requests start with an R such as R123456. This is reflected in the filter formula below, so change as needed or eliminate if you would like all tickets in one report. 

-------------------------------------------------------------------------------------------------------
Report Building Details: 
Report Name: Aged Incidents > 7 Days
Theme: Operation
Parent Query: Action Operation
Filter Name: All Incidents
  • Scroll all the way down to the bottom of the box and hit the "Advanced Mode" button then paste the following formula. 
  • Formula: (SD_REQUEST.RFC_NUMBER LIKE 'I%') and (AM_ACTION.END_DATE_UT IS NULL) and SD_REQUEST.STATUS_ID <> '8' AND (DATEDIFF (day, SD_REQUEST.CREATION_DATE_UT,GETUTCDATE()) > 7) AND AM_ACTION.ACTION_LABEL_EN NOT IN ('Recipient Validation', 'Customer Satisfaction Survey')
  • Formula Translation (for non-SQL people): Ticket Numbers that start with I, tickets without End Dates, tickets that do not have a status of Closed, tickets that were created more than 7 days ago, and tickets whose current action is not equal to 'Recipient Validation' or 'Customer Satisfaction Survey'
View Name: All Incidents
  • Displays: Number, Category (Last Level), Current Incident Status, Action Details*, Ticket Creation Date*, Group, Support Person, Recipient, Group Manager*
  • * = Custom field added to Parent Query. 

Scheduling a Report (incase anyone reading needs):
  • Go to Administration > Reports > Scheduled Reports. 
  • Click the plus (+) sign up at the top. 
  • Input desired name, how often you'd like the report to run, and who you would like it sent to. Note: in my experience, the system seems to be picky about this and wants you to actually search for the individuals via the icon to the right of the box instead of typing in the email. 
  • Towards the bottom, select the plus (+) sign under the work Reports. 
  • Click the first drop-down box that appears and locate the report you created, then select the applicable Filter and View. 
  • Choose the format that you would like to send the report in. Note that CSV will need to be imported into an Excel or Google Sheets spreadsheet. It will also send in a zipped file if the report is large. 
  • Click Finish. 
  • Make sure that the checkbox to the right of the report has a checkmark inside (enabled). 

That did what I wanted - thank you
haroldtate, proud to be a member of EV CONNECT FORUM since Oct 2016.

#5
In EasyVista reporting, think of the "Parent Query" as your "from" and "join"
the "Filter" is your "where" statement (if you use the "Advanced Mode" to type in the sql, don't type "where". E.G. instead of
WHERE AM_ACTION.END_DATE_UT IS NULL and
just type
AM_ACTION.END_DATE_UT IS NULL AND.....)
finally, "View" is your "select", and also allows "group by"
brosenber, proud to be a member of EV CONNECT FORUM since Oct 2015.






Users browsing this thread: 1 Guest(s)