Skip to main content
Conga Support

Creating a Cross-Tab Analytics Report

This page applies to: Analytics Add-on module

A cross-tab, or cross-tabulation, report summarizes information in your Novatus database using a grid, with rows representing one field or dataset and columns representing another field or dataset. The summarized information is displayed at the intersection of the rows and columns.

In creating a cross-tab report, fields are added to both the Columns list box and the Rows list box in the Report Builder. However, at least one aggregated field must be used in a cross-tab report, and that aggregated field can be added to either the Columns or Rows list box.

A cross tab report cannot be used to create a KPI Report.

An Analytics Cross-Tab Report is accessed and created from the Report Browse screen. In this example, a report is being created to track the invoices that were paid in a timely manner, allowing discounts to be taken in accordance with the terms of the contract. The report also tracks the amount paid for each invoice, as well as the discounted dollar amount per invoice.

1

Navigation Toolbar

Click on Reports to display a menu, and then select Reports to display the Report Browse screen.

2

New Report Types button

Click to display a menu, and then select Analytics Report to display a Novatus Analytics prompt.

3

Entity field list

  • Click in the field to display the Clause, Company, Project, and Request folders, and click on Company to select this entity.
  • Click on the image177.png arrow for Company to display the items in this folder. Scroll down and click on Contract to display the items in this folder. Scroll down to the Payment Stream folder, click on the folder to display the sub-folders, and then click on Payment.

4

Run button

Click to display the Analytics Report Builder containing the available fields for your report.

5

Columns list box

Drag-and-drop the following fields into the list box:

  • Contract Number – this field is listed alphabetically in the Contract folder and is used to distinguish payments by contract number.
  • Paid Prior to Invoice Date – this is a calculated field that displays the number of days between the Invoice Due Date and the Invoice Paid Date. Then, apply the Count Distinct aggregate to the field so that all the invoices that were paid within the same time frame will be grouped together.

 To simplify this example, only two (2) contracts have outgoing payments.

6

Rows list box

Drag-and-drop the following fields into the list box:

  • Payment Paid Amount – this field is listed alphabetically in the Payment folder and identifies the total amount paid on the associated invoice.
  • Discounted Amount – this is another calculated field that shows how much of a discount was taken on a payment in dollars.
  • Payment Paid Date – this field is listed alphabetically in the Payment folder and shows the date the payment was made.
  • Payment Invoice Number – this field is listed alphabetically in the Payment folder and provides the invoice number for the amount paid, which includes any discounts taken.

7

Filters panel

A filter was added using the Payment Stream Name field, which was set to Is Not Null so that only the contracts that have payment streams display.

8

Report Preview

View the information provided by the cross-tab report.

9

Output tab

Click to display the full report.

In this example, aggregating the Paid Prior to Due Date affects the display of all the other fields in the same row as this field. Firstly, the aggregated field displays as a header over the remaining fields in the row. Secondly, the display of data in the other fields is aggregated based on the Count Distinct aggregation of the Paid Prior to Due Date field:

  • Payment Paid Amount – only distinct payment amounts are shown (no duplicate payment amounts).
  • Discounted Amount (calculated) – only distinct dollar amounts are shown (no dollar values are repeated).
  • Payment Paid Date – dates shown include all those dates on which the value in the Payment Paid Amount and Discounted Amount fields match the payment made/discount taken on the Payment Paid Date.
  • Payment Invoice Number – reflects the invoices associated with the date fields in the Payment Paid Date field.

10

Contract Number field

View the contracts represented by the report. In this example, there are there are only two contracts showing outgoing payments (IND-VER-00157 and MAS-PAR-00109).

  If a "1" appears in the Contract Number column, the report record is valid for that contract. If a "0" appears, that report record is not valid for that contract. This 1/0 system is used whenever a field appears in the same column (or row) as another field that has been aggregated.

11

Paid Prior to Due Date (Days) – Count Distinct field

This calculated field represents the number of days prior to the invoice due date that the payment was made (i.e., Invoice Due Date – Invoice Paid Date). It is aggregated as Count Distinct so that any duplicate values are grouped. An aggregated field displays as a header above all the fields impacted by the aggregation and the data in these impacted fields is also aggregated.

12

Discounted Amount field

Conditional Formatting Alerts were applied to the field in the Data view in order to highlight the amount of the discount taken when an invoice was paid in a timely manner. Three colors were chosen to identify low discounts (red), a medium-range discount (yellow), and high discounts (green).

13

Conditional Formatting Legend

Color-coding for the conditional formatting alerts is automatically added to the report once the alerts are configured.

14

Column Total fields

Using the Column Quick Menu, which is accessible from any field, a Total function was applied to the Contract Number field to identify the number of payments recorded for each contract.

15

Data tab

Click to return to the Data view to view the report in Preview mode and save the report.

  You can also save the report from the Output screen by selecting the Report tab, and then selecting Save in the menu that display.

16

Report menu

Click on Report to display a menu, and select Save to display a Save Report window.

17

Save Report window

Give the report a name and enter a description, and then click Save.

  • Was this article helpful?