This guide walks you through how to build a report for the add-on case management module, using Report Designer (licence required).
|
Steps to follow
- Go to Reporting > Report Admin > Report Designer
- Insert New Record
Step 1 > Start > Design a new report > Next
Step 2 > Fields > select the Master Table called Cases
| Table | Field |
|---|---|
| Cases | Created Date |
| Cases | Description |
| Cases | Outcome Date |
| Cases | Outcome Notes |
| Cases | Name (Case Number) |
| Case Outcome Type | Name |
| Case Priority | Name |
| Case Status | Name |
| Case Type | Name |
| Personal Details (Employee) | Job Title |
| Personal Details (Employee) | Name |
| Personal Details (Owner) | Name |
| Personal Details (Escalator) | Name |
| Note: If you have configured any further information (FI) fields, they will appear in the Cases Master table for selection. |
- Step 3 > You do not need to select any data rules, but you may prefer to to only include corresponding Personal Details records if you have a large amount of data in your system
- Step 4 > Do not include any additional tables
- Step 5 > Select subset as required (‘use a subset’ recommended)
- Step 6 > Select a filter as required
- Step 7 > Finish your report by including a Title, Description and selecting a Report Group
- It is recommended to run this report as a Dynamic Spreadsheet type so you can amend the layout and add pivot tables/formulae (see next section)
Edit your report template
- Now that you have completed your report you can edit your excel export and apply formulae. It's recommended to rename your columns in your Base Data then add in the order below for your pivot table.
|
Tip: Where there are duplicate column names such as 'Name', click in the header cell and then check the field name box top left to see which table it is from to rename accordingly. You can drag the field name box to expand as needed. |
| Order | Field | Renamed column header |
|---|---|---|
| 1 | Case opened | Created Date |
| 2 | Name (Employee) | Employee |
| 3 | Job Title (Employee) | Job Title |
| 4 | Case number | Case Number |
| 5 | CaseTypeName | Case Type |
| 6 | Description | Case Description |
| 7 | CasePriorityName | Priority |
| 8 | CaseStatusName | Status |
| 9 | CaseOutcomeTypeName | Outcome |
| 10 | Outcome Date | Outcome Date |
| 11 | Outcome Notes | Outcome Notes |
| 12 | Name (Owner) | Case owner |
| 13 | Name (Escalator) | Case escalator |
- Then check your template back in to save the changes
- Once happy with your report, click Edit Settings to enable it and grant access to the appropriate user roles for them to be able to run it from the Reports menu. See the linked guides for more details
Recommended Excel formula - case duration
This formula calculates how long each case has been open. If the case is still open, it measures from the created date to today. If the case is closed, it measures from the created date to the outcome date.
Insert a column into the Base Data and call it Case duration (days). Add the formula below:
=IF(ISBLANK(closed), TODAY()-opened, closed-opened)
Replace closed with the Outcome Date cell reference and opened with the Created Date cell reference for the row.
How it works
-
ISBLANK(closed)- checks whether the Outcome Date cell is empty - If empty, the formula returns
TODAY() minus opened- the number of days the case has been open so far - If not empty, the formula returns
closed minus opened- the total case duration in days
Related articles
Footer
Comments
0 comments
Article is closed for comments.