Pivot Report Example

Overview
This section will contain a tutorial to create a Pivot report and show the results. This can be used as a template so you can see the sample output and learn to change the report to display what you need.

Report Specifications
For this report we want to summarize invoice totals by month and the accounts they belong to. For this type of report we will need a pivot table.

Step 1. Set Report Name, Entity, and Position

Begin the report creation wizard. The screenshot below will show how we filled out the fields to reach our end goal.


Use the below table to see why fields were set as they are.

Field Name Description
Report Name This can be anything you wish, we named ours "Monthly Invoice Total" to describe what the report will contain.
Entity Name Here we selected invoices as this is as far down the hierarchy we need to go. According to the entity map (linked to on the Report page here) this entity is below accounts so it will cover the account data we will need in the report.
Report Position We selected "Customer Specific Reports" category so it will be displayed on the UI in the Reports section. It can be placed anywhere you need.


Step 2. Complete Report Settings

Here we will set the fields that drive the report to show the information we need. Please see the completed form below.

Field Name Description
Show Grand Total Do not use this box as we are creating a tabular report. This will be used for summary style reports.
Show Subtotal Do not use this box as we are creating a tabular report. This will be used for summary style reports.
Report Fields
  •  Field Name - Here we chose three fields to report on, invoice closed date, grand total amount, and account id. These will be the fields we include in the final report.
  • Convert Function - We want to truncate the closed date to a smaller format so we choose "Trunc_YYYY_MM" in the row for closed date.
  • Report Label - Here we provided easy to recognize labels that will be displayed on the UI. This will not affect the functionality of the final report.
  • Summary Function - Here we need to specify which field we need to sum for the pivot table. As we are looking to find invoice totals for each month, we will set "SUM" on the row for the grand total field. For a pivot field at least one row will need to be set to "SUM".
  • Pivot - This is where the pivot fields are determined. We want to summarize invoice totals on a monthly basis. For this we need to set pivot to "YES" for the fields Grand Total and Closed Date. These two fields are selected so we can pivot the dates columns, and summarize the invoice totals in those columns.
  • Sort Order - Here we want the list to be sorted by account ID so we set the priority to 1. 
  • Sort Direction - We want the account IDs to be sorted ascending, so we choose "asc".
  • Field Format - We only want to format the numbers coming through for the grand total field. We set this here by selecting "$###,###,###.00".
Report Date Filters 
  • Field Name - We want to be able to filter the report by invoice closed dates. We select the closed date for both rows as we want to set upper and lower limits.
  • Report Label - This is simply a user friendly label that will appear on the UI.
  • Condition - Set the rule for comparison. For upper and lower limits we will use "Greater Than" and "Less Than"
  • Default Value - We do not want this field to be pre-populated, so we leave it blank.
  • Required - We do not want the user to be required to filter by date, only if they want to, so this is left blank.
Report Non Date Filters
  • Field Name - We want to be able to filter the report by payment status, so we select the payment status field.
  • Report Label - This is simply a user friendly label that will appear on the UI.
  • Condition - Set the rule for comparison. We want to match payment status so we select the "EQUALS" rule.
  • Default Value - We want to filter by non paid invoices by default. We enter our default search query here by entering "NOT PAID".
  • Required - We do not want the user to be required to filter by payment status, so we leave this blank.

Use the table above to explain why the selections were chosen.


Step 3. Set Report Permissions

Here we want the report to be usable only to application administrators.



We set the permissions for BRM_ACCOUNT_ADMIN to "YES".

Select Save to complete the report.

The Result
Here we can run our new report. In the screenshot below you can see all accounts listed. in the columns to the right of the accounts IDs you will see a column for each month. In the field intersecting the month and the account you will see the sum of grand totals for the entire month. This type of report allows you to easily see unpaid invoice totals for each month for each account.


Have more questions? Submit a request

Comments

Powered by Zendesk