Combine reporting areas—ICRW

You can report across workflows and business processes by combining columns from one or more Reporting areas.

You can combine columns from one or more reporting areas in a report using operations, such as Union or Intersect. Combining reporting areas this way allows you to create a column that shows the information in a different way.

Subscription Interactive Custom Report Writer

Platform Services

Regional availability

All regions
User type Interactive Custom Report Writer: Business, Project Manager
Permissions

Interactive Custom Report Writer: Run, List, View, Add, Edit, Delete

Platform Services: Run, List, View, Add, Edit, Delete

Prerequisites

Ensure that you meet the following prerequisites for combining columns:

  • Combine columns that have the same data type and the same number of columns. The number of rows for each column can differ, however.
  • Select columns from the same Reporting area or from a different reporting area with at least one item in common.
  • Specify one operation for combined columns. For example, if you create criteria from the Projects Reporting area, you can apply only one operation to those columns. You cannot apply different operations to different columns in the report.

Limitations

Combining columns in Interactive Custom Report Writer has the following limitations:

  • You are not allowed to combine hierarchy columns, selection steps, or groups.
  • You are allowed to add prompts with standard time dimensions. However, other prompts are not supported when combining columns.

Combine columns from one or more reporting areas

System-wide formatting for a column is not used by the combined column. To use data formatting in the combined column, reapply it to the combined column.
  1. Open Interactive Custom Report Writer, create a report, and include the appropriate columns.
  2. Go to the Define tab, then in the Columns pane, select the Combine columns icon (Select this icon to combine two or more selected columns.) to open the Select reporting area dialog box.
  3. Select the reporting area that contains the columns to combine with the previously included columns.

    Boxes bordered with dotted lines appear. These boxes let you know what kind of column you can combine with the columns already included in the report. When you combine measure columns, no arithmetic operations are performed.
    Three column heading boxes with dotted line borders.

  4. In the Reporting area pane, select the columns to combine with the originally selected columns.

    The boxes that previously had dotted line borders now hold the columns that you selected. You have now specified the columns to combine.

  5. Select the dropdown menu under Preview columns and select the operation type to use for combining the columns.

    For example, select the Union All type. The resulting columns are displayed in views after applying the operation of the derived columns.

    The following table describes the operations:

    Options for combining columns
    OperationDescription
    UnionReturns only non-duplicate rows from all columns.
    Union AllReturns rows from all columns, including duplicate rows.
    IntersectReturns only common rows to all columns.
    MinusReturns only rows from the first column that is not in the second column.
  6. Select Preview columns.

    The Selected Columns pane updates, displaying the newly combined columns that you created.

    Close up of the Columns pane, to highlight the location of the Preview columns button.

You work with these columns as you do other columns in this pane.

You can rename the column headers in the Preview columns view.

Examples of combining columns from one or more reporting areas

The following scenarios show some examples of combining columns to present specific results.

You can combine two columns from a single reporting area, giving you additional data sets to provide better insight.

For example, an Accounts Receivable Reporting area could contain one set of records for Location and another for Department. You can combine these two columns, creating a third column called Department and Location. By creating a union of the two using the Union All function, the new column shows the values from both columns combined.

You can create a report to view both the expenses and revenue for each department and location. The expense amount is in the Accounts Payable Bills Reporting area, while the revenue amount is in the Accounts Receivable Invoices Reporting area.

The following task demonstrates how to combine both the expenses and revenue in one report.

  1. Create a new report, using AR Invoices as the reporting area and name it "Expenses and Revenue."
  2. Add the following columns from the AR Invoices reporting area:
    • Location name (found in the Attributes folder)
    • Department name (found in the Attributes folder)
    • Total paid (found in the Measures folder)
  3. In the Columns pane of the Define tab, select the Combine columns icon (Select this icon to combine two or more selected columns.) to open the Select Reporting area dialog box.
  4. In the Select reporting area dialog, select AP Bills.
  5. From the dropdown menu, select Union all.
  6. Add the following columns from the AP Bills Reporting Area: 
    • Location name (found in the Attributes folder)
    • Department name (found in the Attributes folder)
    • Total paid (found in the Measures folder)
  7. Select Preview columns.
  8. On the Refine tab, you can see the results of the combined expense and revenue report. The numbers in the combined columns might not make sense. You can customize the column displays. You can also add subtotals to the combined columns, as in this example:

An example of combined columns before customizing the display so the totals make sense and are easily readable.

The following example demonstrates how to add additional attributes and use a pivot table view to refine and enhance your "Expenses and Revenue" report.

  1. Add a column that indicates whether the amount is from the AR Invoices or AP Bills Reporting area.
    1. From the AR Invoices > Invoice detail > Attributes folder, add the AR account attribute.
    2. On the Define tab, select the A downward facing arrow tip to indicate a series of options that can be viewed by selecting the arrow to expand the list. (Options) to the right of the new AR account column. Then select Edit formula to open the Edit Column Formula dialog box.
    3. In the Calculation tab, replace everything in the Calculation box with 'AR'.
    4. From the AP Bills > AP bill detail > Attributes folder, add AP account.
    5. Edit the AP account column so that the Calculation is 'AP'.
  2. Select Preview columns.
  3. On the Refine tab, the new column shows the amounts that correspond to AP and AR.
  4. Add a date column to break down your data per year.
    1. From the AR Invoices > Invoice detail > Attributes folder, add the When Created attribute.
    2. On the Define tab, select the A downward facing arrow tip to indicate a series of options that can be viewed by selecting the arrow to expand the list. (Options) to the right of the new When Created column and select Edit formula to open the Edit Column Formula dialog box.
    3. In the Calculation tab, edit the Calculation so that it displays the year the invoice was created:
      year("Invoice"."WHENCREATED")
    4. From the AP Bills > AP bill detail > Related Object > AP bill folder, add the Date attribute.
    5. On the Define tab, select the A downward facing arrow tip to indicate a series of options that can be viewed by selecting the arrow to expand the list. (Options) to the right of the new Date column and select Edit formula to open the Edit Column Formula dialog box.
    6. In the Calculation tab, edit the Calculation so that it displays the year the bill was created:
      year("AP bill"."WHENCREATED")
  5. Use a pivot table to rearrange the data and show groupings and subtotals.Expenses and revenue pivot table report with logically grouped data and correct subtotals and totals.

Difference between combining columns and adding columns from related reporting areas

There are critical differences between combining columns and adding columns. Combining columns provides different results than adding columns from related reporting areas:

  • When you combine columns, the report shows a single, newly combined column.
  • When you add columns from related reporting areas, the report shows each added column individually. With the appropriate permissions, you can create a report by selecting one column from one reporting area and a second column from a related reporting area.