...
This document describes how a Ticket information is stored in the Data base so that Customers can generate their own reports with ease.
It also explains the Best Practices and Guidelines for creation of the Reports. It is recommended to follow these practices and guidelines while executing a query, generation of reports.
ISM Data base consists of many Tables and Views. However, there are 19 main Tables / Views based on which Customers can generate reports related to Tickets.
...
UI Field Name | View Name | Column Name | Remarks | Join Key |
Phase | VAPP_HISTORY_APPROVALS | APPR_PHASE |
| VAPP_HISTORY_APPROVALS.SLICE=VAPP_ITEM.SLICE AND VAPP_HISTORY_APPROVALS.ITEM_ID=VAPP_ITEM.ROW_ID |
Approver Type | VAPP_ITEM_APPROVERS | appr_role |
| VAPP_ITEM_APPROVERS.SLICE=VAPP_ITEM.SLICE AND VAPP_ITEM_APPROVERS.ITEMappr_item_IDid=VAPP_ITEM.ROW_ID |
Status | VAPP_HISTORY_APPROVALS | APPR_STATUS |
|
|
Action on approval | VAPP_ITEM_APPROVER_COMMENTS | COMMENT_TEXT | The review decisions, observations, suggestions, or additional information requirements regarding the submitted change |
|
Action on rejection | VAPP_ITEM_APPROVER_COMMENTS | COMMENT_TEXT | The review decisions, observations, suggestions, or additional information requirements regarding the submitted change |
|
In approval state ? | VAPP_ITEM_APPROVER_COMMENTS | COMMENT_TYPE | 0 – Approved 1- Rejected |
|
Approver Type | VAPP_ITEM_APPROVERS | appr_role |
|
|
Name | VAPP_ITEM_APPROVERS | last_name. first_name |
|
|
Group | VAPP_ITEM_APPROVERS | group_name |
|
|
Decision | VAPP_ITEM_APPROVER_COMMENTS | decision |
|
|
Decision date | VAPP_ITEM_APPROVER_COMMENTS | decision_date |
|
|
Comments | VAPP_ITEM_APPROVER_COMMENT_LIST | COMMENT_TEXT |
| VAPP_ITEM_APPROVER_COMMENT_LIST.SLICE=VAPP_ITEM.SLICE AND VAPP_ITEM_APPROVER_COMMENT_LIST.APPR_ITEM_ID=VAPP_ITEM.ROW_ID |
...
Best Practices / Guidelines
Do’s
Views on Tables and Pivot Tables have been created for Custom reports and these are to be used for generating your own reports
Slice ID should be used as filter in queries or reports for optimal performance
VAPP_ITEM Table is the base table for all the ticketing information. Whenever it is require to join VAPP_ITEM with any table, Slice ID and ROW_ID of VAPP_ITEM should be joined with ITEM_ID column of respective Join table.
Always use Slice ID in joins
Derived tables should be used with caution and only used when necessary.
Whenever SQL functions are used, these will give new names to newly generated columns by default. However, appropriate names can be given
Always use table aliases wherever SQL statement involves more than one source.
Use DM_ tables for all reporting needs (if they are available in Jasper domain or VPN Database)
Create joins with INNER JOIN not with WHERE clause
Create calculated fields at domain level wherever possible. For more information on this, please refer article
Don’ts
Do not use SELECT * in your queries, Use only required columns in the derived tables or reports, instead of all columns.
Do not use column numbers in the ORDER BY clause.
Avoid extensive sub queries, use not more than 2 level sub queries or nested queries.
Avoid using multiple and IF and Else conditions.
Avoid SELECT DISTINCT usage, instead use SELECT DISTINCT, select more fields to create unique results.