Search

How do I create a custom report of all incidents that have ever been assigned to a specific status?

Environment:

Analytics, Custom Report

Resolution: 

Most of the specific changes to a record are not recorded, however, the only specific changes that are stored are found in the 'trans_type' field of the 'transactions' table. Please note, you can only see values if they are a preset 'trans_type' stored in the 'transactions' table, if it is not in there, it is not configurable. Most preset 'trans_type' are a generic edit type which do not contain exactly what was changed. You might want to review some basic examples using the 'Incident Audit Log' (ID 9041), or 'Contact Audit Log' (ID 9050) standard reports.

You also might be able to create a custom report using the 'transactions' table and the 'Attribute' field. The 'transactions' table stores much, but not all, of the information that is seen in the audit log of an incident. For instance, the 'trans_type' field will store the different types of transactions that can occur, such as a status change. The 'id1' field will give the you the ID value of the status that the incident was changed to. Unfortunately, it can only store the ID value of a status and not the actual name. But you can find the IDs of your status by following the detailed steps provided below:

1. Navigate to Configuration > Application Appearance > Customizable Menus
2. System Menus > Incident Statues
3. Hover over the label for the status and give it about 5 to 10 seconds and the ID number should pop up.

The 'Attribute' field of the 'transactions' table is dependant on many different factors and how your report is designed. It varies depending on the transaction type and the type of record or item the transac­tion is associated with.

For example, if you are referring to incident statuses, you might want to use the 'transaction_type = Status changed' filter which is transaction_type 6. When looking at the 'Attribute 1 (id1)' field you can see it could be referring to 'statuses.status_id where transactions.trans_type = 6'. I think if you review the Data Dictionary in regards to the 'transactions' table and the 'Attribute 1 (id1)', you will start to see the pattern it uses.

You can find further details on using the 'transactions' table and the Data Dictionary in reports by referring to Answer ID 906: Transaction fields in custom reports and Answer ID 2611: Accessing the data dictionary.

For additional information, refer to the 'Attribute and description fields' section in the Online Help User Guide documentation. To access Oracle B2C Service manuals and documentation online, refer to the Documentation for Oracle B2C Service Products.