Skip Navigation
Expand
Transaction fields in custom reports
Answer ID 906   |   Last Review Date 01/11/2024

When configuring a custom report, what are the fields that I can add from the transactions table?

Environment:

Analytics, Transaction fields
Oracle B2C Service

Resolution:

The transaction fields available when configuring a custom report correspond to the information listed when you view the Audit Log of a record.  For example, when viewing the audit log for an incident, the transactions include the actions Incident Assigned to, Incident Forwarded, or Response Sent to.

The Audit Log / Transactions are the history of actions associated with a particular record. This includes saving a record, reassigning a record or in the case of incidents sending a response.  Each transaction includes the date and time, the type of transaction, and who performed the action.

Note: The ma_trans table includes transactions associated with outbound mailings from the Outreach module. When adding this field to a custom report, it is listed as the Marketing Activities table in the Data Dictionary section when you right-click to select a table.

 

Table and Field Descriptions

Descriptions of the fields and values included in the transaction table are listed in our online documentation (see Transactions Table). To access Oracle B2C Service manuals and documentation online, refer to the Documentation for Oracle B2C Service Products

For information on accessing the data dictionary, see Answer ID 2611: Accessing the data dictionary.
   

Custom Reporting

Transaction fields allow you considerable flexibility in defining custom reports. To become familiar with the transaction fields and their values, consider generating a simple report that lists the various fields. Refer to Custom Reports section in the online documentation for assistance.

Note: If you need assistance with creating a specific custom report for your business needs, consultants on our Oracle Consulting team are able to help. For more information on pricing and options, please contact your sales account manager.

From a basic report, you can begin experimenting with adding additional transaction fields as selection criteria or even incident fields as either search criteria or report output. By keeping the Transaction-Created filter to a small date and time range (such as a few hours), you can generally keep the number of records to a comparatively small number to ensure that the custom report is working properly.

Example:  You can create a simple report for incidents created in a specific timeframe or for transactions created in a specific time period such as the previous day or previous hour.  This example will create a transactions report for incidents. By including a run-time filter for the transactions date created field, you can control the amount of data returned with this report.

Data Set / Table Instances
Add the Incidents table and join the Transactions table

Run-time Selectable Filters
Name: trans (or whatever you want to name it)
Expression: Transactions > Date Created
Operator: between
Value: define a specific range or a relative date range for a single hour or a single day.

Report Output Columns
Incidents > Reference Number
Transactions > Date Created
Transactions > Type
Transactions > Account ID
Transactions > Description
Transactions > Attribute 1
Transactions > Attribute 2
Transactions > Attribute 3

Result Ordering Columns
Any of the Output Columns above

 

Similarly, to review mailing transactions, you can also create a simple report that joins the ma_trans table to the contacts table and then include a filter based on a campaign ID or mailing ID:

Table Instances
Add the Contacts table and join the Marketing Activities (ma_trans) table

Run-time Selectable Filters
Name: campID (or mail_ID)
Expression: Marketing Activities > Campaign ID (or Outbound Mailing ID)
Operator: in list
Value: select a specific campaign or outbound mailing.

Report Output Columns
Contact > Full Name
Transactions > Date Created
Transactions > Type
Transactions > Account ID
Transactions > Description
Transactions > Attribute 1
Transactions > Attribute 2
Transactions > Attribute 3

Result Ordering Columns
Any of the Output Columns above