Join reporting areas using logical SQL statements

ICRW's Custom tab allows you to examine the logical SQL statement generated for a report. You can use the logical SQL statement to join two or more reporting areas and create a new report for comparative analysis. You accomplish this using the Custom tab.

The Custom tab is only recommended for advanced users. You must be comfortable working with SQL statements and understand report metadata and the content and structure of the underlying data sources for your reports.

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

Join reporting areas

You can create a new report using two or more existing reporting areas using the SQL statement in the Custom tab.

When you create a report by joining multiple reporting areas, we recommend that you wait to create any views (tables, pivot tables) or prompts. Also wait to perform any formatting until all columns have been created, the joins between reporting areas are complete, and the report returns accurate results. You are creating an entirely new report and all previous views, prompts, and custom formatting for the previous report are discarded.

The following task demonstrates how to join multiple reporting areas in a new report. The results allow you to view the data for enhanced comparative analysis.

  1. Create (or edit) an ICRW report that has one of the reporting areas you want to join in a report.
  2. Add the desired columns and (optionally) filters. When you have the desired results on the Refine tab, select the Custom tab.
  3. Go to the SQL Issued field, highlight, and CtrlC to copy the contents.
  4. Paste (CtrlV) the SQL statement into an ascii text editor, such as Notepad or Notepad. Do not use Microsoft Word.
  5. In the ascii text editor, remove any lines that start with ORDER BY or FETCH FIRST.
  6. Repeat steps 1 through 5 for any additional reporting areas, saving each set of SQL separately so you can find each one easily.
  7. Select New Report and delete the existing SQL to create an analysis based on the SQL code.
  8. Identify one or more columns in common between the reporting areas and paste the SQL from the reporting areas in the text editor.
  9. Use a JOIN statement to merge the sets of SQL together using those common fields in the ON clause as demonstrated in the following Example.
  10. Standard SQL JOIN statements (such as OUTER JOIN, INNER JOIN, and LEFT OUTER JOIN) are supported, as shown in the following template that joins two reporting areas to create a new report.

    11. Copy 
    SELECT
        T1.Location Location,
        T1.AP_Amount AP_Amount,
        T2.AR_Amount AR_Amount
    FROM

       (SELECT "AP bill detail Attributes"."LOCATIONNAME" as Location,
        "AP bill detail Measures"."TRX_AMOUNT" as AP_Amount
         FROM "ap:AP Bills") T1

    LEFT OUTER JOIN

       (SELECT "Invoice detail Attributes"."LOCATIONNAME" as Location,
        "Invoice detail Measures"."TRX_AMOUNT" as AR_Amount
        FROM "ar:AR Invoices") T2

    ON T1.Location = T2.Location

  11. Select OK to apply the changes.
After completing this procedure, the selection panel on the left becomes unavailable. The columns from only one reporting area can be displayed at a time, and the new report uses data from multiple reporting areas.

Example: AP purchase invoices and AR sales invoices for each location

This example demonstrates how to create a report that joins two reporting areas to show transaction amounts for AP purchase invoices and AR sales invoices for each location. This procedure starts with a simple ICRW report.

  1. Create an ICRW report from the AP purchase invoice reporting area that includes the Location Name and Transaction amount.

    An example of an AP bill detail report with Location and Transaction amount columns.

  2. Select the Custom tab.

  3. Copy the SQL code for the current AP purchase invoices report and the SQL code for the AR sales invoices reporting area. You can ignore and delete the lines that start with "ORDER BY" and "FETCH FIRST."

    Copy

    AP Bills SQL code

    SELECT
       "ap:AP Bills"."AP bill detail Attributes"."LOCATIONNAME" s_1,
       "ap:AP Bills"."AP bill detail Measures"."TRX_AMOUNT" s_2
    FROM "ap:AP Bills"


    The report and SQL code for AR sales invoices transactions would appear like this:

    An example of an AR Invoices report with Location name and Transaction amount columns.

    Copy
    AR Invoices SQL code
    SELECT
       "ar:AR Invoices"."Invoice detail Attributes"."LOCATIONNAME" s_1,
       "ar:AR Invoices"."Invoice detail Measures"."TRX_AMOUNT" s_2
    FROM "ar:AR Invoices"

  4. Select New report.

  5. Enter the following SQL statement that uses the AP purchase invoices and AR sales invoices SQL to join the two reporting areas into one report.

    Copy
    Transactions for both AP Bills and AR Invoices
    SELECT
        SubjectArea1.Location Location,
         SubjectArea1.AP_Amount AP_Amount,
         SubjectArea2.AR_Amount AR_Amount    
         
    FROM
         (SELECT "AP bill detail Attributes"."LOCATIONNAME" as Location,
           "AP bill detail Measures"."TRX_AMOUNT" as AP_Amount
         FROM "ap:AP Bills") SubjectArea1 
         
    LEFT OUTER JOIN    

         (SELECT "Invoice detail Attributes"."LOCATIONNAME" as Location,
           "Invoice detail Measures"."TRX_AMOUNT" as AR_Amount
        FROM "ar:AR Invoices") SubjectArea2
        
    ON SubjectArea1.Location = SubjectArea2.Location

  6. Select OK.

  7. Select the Refine tab. Select the Refine tab. The new report displays with transactions from both AP purchase invoices and AR sales invoices.

    Resulting example of joining AP Bill and AR Invoices in one report. Locatation names are shown in the far left column with AP Amount and AR Amount columns to the right.

Adding prompts to the new report

The following task demonstrates how to add prompts to the new report using SQL statements.

  1. In the Prompts tab, add a new Column prompt. For example, you can base the new prompt on Location.

  2. In the New Prompt window, expand Options and select SQL Results for the Choice List Values.

  3. Enter an SQL statement that generates a list of desired values.

    For example, enter the following SQL statement to generate a list of Location values.

    Copy 
    SELECT "AP bill detail Attributes"."LOCATIONNAME" 
    FROM "ap:AP Bills"

  4. Select OK.

Difference between joining reporting areas and combining columns

There are important differences between joining reporting areas using the SQL statement in the Custom tab and combining columns in the Define tab.

Join combines the data into new columns to form a new report. If data from two reporting areas are joined together, then the data from the first reporting area is displayed in columns alongside the second reporting area's columns in the same row.

A report with AP Amount and AR Amount joined reporting reporting areas. Data for each of six locations is shown in each reporting area column.

Combine (using the union operation) combines the data into new rows. If data from two reporting areas are combined, then data from the first reporting area is in one set of rows, and the data from the second reporting area is in another set of rows. The rows are displayed in the same report.

Report example of combined AR and AP reporting areas. Transaction amounts for each location are in one column, instead of separate columns for AR and AP.