Writing Your First Statement

To write your first statement:

  1. Create a new SQL file in your text editor of choice.
  2. Write the following SQL statement: SELECT * FROM unifier_ui
  3. To manage the amount of data generated, select only the columns you want to include in your report.
    • See Review Unifier Database Table Structure for a list of all column names.
    • Instead of using the asterisk, or star, ( * ) operator, specify which fields you want to include in our query result.
  4. Replace the asterisk, or star, ( * ) operator in your statement with a few column names (for example, SELECT project_id, ID, record_no, title, status, UVEVENDORNAMETB50, amount FROM unifier_ui)
  5. Assign an alias to your table to limit the typing that you need to do.

    Example

    Name you Invoice table "inv" by adding "inv" after the table name. You can then refer to each column using the alias, instead of the full name. Your statement should look like the following:

    Note: The Query Builder re-formats the layout of your SQL.

    SELECT inv.PROJECT_ID,

    inv.ID,

    inv.RECORD_NO,

    inv.TITLE,

    inv.STATUS,

    inv.UVEVENDORNAMETB50,

    inv.AMOUNT

    FROM unifier_ui inv

  6. Add aliases to the column names to make the column names easier to understand when building the BI Publisher report

    Example

    SELECT inv.PROJECT_ID,

    inv.ID AS inv_ID,

    inv.RECORD_NO AS inv_record_no,

    inv.TITLE AS inv_title,

    inv.STATUS AS inv_status,

    inv.UVEVENDORNAMETB50 AS vendor,

    inv.AMOUNT AS inv_amount

    FROM unifier_ui inv

  7. (Optional) Choose any other columns from the invoice table that you want to include in your report.

    Note: Unifier requires a field called "PROJECT_ID" since Unifier uses the filed for data filtering (for example, Only the Invoices from the project that you are running the report from are shown). Always leave a field called PROJECT_ID in your main Data View to prevent receiving an error message when creating the Custom Report.

Related Topics

Creating a Custom Report in Primavera Unifier

SQL JOIN

SQL WHERE

SQL ORDER BY Keyword

SQL Final Statement

Download and Install BI Publisher Desktop for Microsoft Office

Review Unifier Database Table Structure

Connect to Unifier Database

Adding Line Items

Creating a New Custom Report

Building Report Template

Creating a Report with Line Items

Advanced BI Publisher Functions

Search Parameters and Additional Parameters



Legal Notices
Copyright © 2016, 2018, Oracle and/or its affiliates. All rights reserved.

Last Published Wednesday, March 28, 2018