2Analytics

This chapter contains the following:

Introduction to Oracle Transactional Business Intelligence for Financials

In this example, you view an analysis called the Payables Aging Summary. This analysis was created using Oracle Transactional Business Intelligence, an ad hoc reporting tool. You can use the tool to create analyses, dashboards, and infolets using information from your Oracle Financials Cloud applications.

Viewing an Analysis

  1. Let's start from the Financial Reporting Center and search for the Payables Aging Summary.

  2. Click the Expand icon to see details like type and location.

    In this example, the type is Analysis. The location shows you where the analysis is stored.

  3. Click the title.

    Now you can see the analysis. The information here is real time and based on the latest transaction information.

    This analysis has bars and a table that show totals by aging categories like Current, Overdue 21 - 60 days, and so on. You can use different views like time series lines, bars, tables, and heat maps in your analyses. An analysis can use more than one type of view to help arrange information in a way that's useful for your audience.

  4. Click the Overdue 91-180 Days bar.

    A table shows the invoices that make up the total for this category. You can see invoice details like supplier name, invoice due date, invoice number, and unpaid amount.

  5. Click the Buckets drop-down list to change categories.

    Now you see the details that make up another aging total.

  6. Go back to the previous page.

  7. Click the Analysis icon after the title.

  8. Click the Edit link to open the layout.

    You can make changes here and save them in the same location, or you can save them in another area that you have access to. You can also create another analysis. Just click the New menu to get started.

Explore Financial Subject Areas in Oracle Transactional Business Intelligence

In this example, you examine Financial subject areas in Oracle Transactional Business Intelligence. Here's the list of Financial products with subject areas: Assets, Budgetary Control, Cash Management, Expenses, General Ledger, Intercompany, Payables, Receivables, Revenue Management, and Subledger Accounting.

  1. Start from the Financial Reporting Center.

  2. Click Tasks.

  3. Click Open Workspace for Financial Reports.

  4. Select Navigate > Applications > BI Catalog.

  5. You have to create an analysis to see the subject areas you have access to:

    1. Click the New icon.

    2. Click Analysis.

    Subject areas are organized in folders and the information they contain varies based upon reporting requirements. General Ledger subject areas cover account balances, journals, and period statuses.

  6. Select the General Ledger Transactional Balances - Real Time subject area. The term Real Time in the subject area name indicates the information is up-to-date as of the latest general ledger journal posting. You can use this subject area to create trial balances and general queries to review changes in account balances over time. Here are some of the folders that appear in the Subject Area pane.

    • Balancing Segment

    • Cost Center Segment

    • Currency

    • Time

    • Ledger

    • Ledger Set

    You can expand folders to see their components. For example, the Time folder contains accounting period and year. If you have more than one ledger, you can create an analysis that filters by ledger and ledger set. You can query a specific general ledger balance, or groups of general ledger balances.

  7. Click the Add or Remove Subject Areas icon in the Subject Areas toolbar to view more subject areas and the type of information they hold.

    For example, with the Payables subject areas you can examine information about suppliers, supplier balances, and invoices, including freight and tax charges. You can also see how invoices and payments affect the general ledger. An analysis can show which invoices were paid on time and whether checks have cleared.

    The Receivables subject areas include customer information, customer balances, and invoice details such as terms and receipts, credit memos, and revenue adjustments. Similar to Payables, you could analyze how receipts affect general ledger account balances.

Overview of Analytics Creation and Modification

You can edit and create your own analytics on your transactional data. The predefined analyses and dashboards help answer many of your business questions. But you can also create your own to meet your requirements.

Note: You can't use Direct Database Query in your analysis. Direct Database Query isn't supported in SaaS OTBI. To create a direct database SQL report, you can create a BI Publisher SQL data model and then create a report.

This table lists a few examples.

Task Example

Create an analysis

Your team needs a simple list of all your accounts, sorted by account ID. You include the account name, ID, and address in a new analysis, and sort the ID column.

Create a view

A predefined analysis has a bar graph. You save a new version of the analysis with a table view added to the graph.

Create a view selector

You later decide you want to toggle between viewing a table and a graph. You add a view selector that includes the table and graph views.

Edit a dashboard prompt

A predefined dashboard has a Start Date prompt. You make a copy of the dashboard and replace Start Date with a date range prompt.

Create a dashboard

You create a dashboard that includes an analysis and a report, which you can view together. You also add a dashboard prompt to filter both the analysis and the report.

Tip: When you create or edit analyses, you can use a wizard in the Reports and Analytics work area and panel tab.

Flexfield Attributes

Administrators can modify the business intelligence (BI) repository to determine the columns available for you to use in analyses.

Use the wizards to quickly create and edit your analytics. You can use a wizard to create and edit most of your analytics, for example to select columns, add filters or views. You can also use advanced business intelligence features to create or edit dashboards or manage analyses and other objects in the catalog.

Create an Analysis

  1. Click Tools > Reports and Analytics in the Navigator.

  2. In the Reports and Analytics work area or the Reports and Analytics panel tab (if available), click Create and select Analysis.

  3. Select the subject area with columns you want to include.

  4. Optionally, click Add/Remove Subject Areas and, in the Add/Remove Subject Areas dialog box, select more subject areas or remove any that you no longer need, then click OK.

  5. Select the columns to include, set options for each column, and click Next.

  6. Optionally, enter a title for the analysis.

  7. Select the type of table or graph to include, specify the layout of the views, and click Next.

    Note: At any point after this step, you can click Finish to go to the last step, to save your analysis.
  8. Optionally, set more options for the table or graph, and click Next.

  9. Optionally, add sorts or filters based on any of the columns you included, and click Next.

  10. If you have a table, optionally define conditional formatting for select columns, for example to display amounts over a certain threshold in red. Click Next.

  11. Enter the name of your analysis and select a folder in the Custom folder or My Folder to save it in.

  12. Click Submit.

Edit an Analysis

  1. Click Tools > Reports and Analytics in the Navigator.

  2. Select your analysis and edit it. In the Reports and Analytics work area, click More for the analysis and select Edit. In the Reports and Analytics panel tab, click the analysis, then click Edit.

  3. Perform steps 4 through 10 from the preceding Create an Analysis task, as needed.

  4. To update an existing analysis in the Custom folder, select the same name in the same folder. To save this analysis as a new copy, either name it with a new name or save it in a new folder in the Custom folder.

  5. Click Submit.

Create and Edit a Financial Analysis Using a Wizard

You can use a wizard that guides you through creating and editing analyses. Even though the wizard doesn't give you all available features, you can still use it to make typical changes, for example, adding views and filters.

Note: The wizard isn't available for dashboards and you can't use it to delete analyses.

Creating an Analysis

  1. Navigator > Tools: Reports and Analytics.

  2. Click Create and select Analysis.

  3. Select a subject area that you want to analyze, for example, the General Ledger - Journals Real Time subject area.

  4. Select the columns to include:

    • Journal Batches: Batch Details: Journal Batch

    • Journal Headers: Header Details: Journal Header Description

    • Journal Lines: Line Details: Line

    • Journal Lines:

      • Accounted Debit

      • Accounted Credit

    • Time: Accounting Period Name

  5. Set the following options:

    • Interaction: Default

    • Hidden: Leave unchecked

  6. Click Next

  7. Enter a title of Journal Report to appear at the beginning of the analysis.

  8. Select the type of table and click Next.

    Tip: At any point after this step, you can click Submit to go to the last step, to save your analysis.
  9. Enter the name of your analysis By Cost Center and select the My Folder.

  10. Click Submit.

Editing an Analysis

  1. Navigator > Tools: Reports and Analytics.

  2. Select your analysis By Cost Center in the pane and click Edit.

  3. Add Posting Status: Posting Status Meaning.

  4. Click Submit: Save your analysis with the same name in the same folder.

    Tip: You can create a copy of the analysis, whether you edited it or not, just by saving it either with a new name or in a new folder.

Manage Analytics with Advanced Features

Wizards are an easy way to create or edit analyses. But you might have to use advanced features for complicated analyses or specific requirements. For example, you can create view selectors so that users can toggle between views within an analysis, or define criteria for filters using SQL statements.

You can also perform other actions on analyses, for example delete them or copy and paste them within the business intelligence catalog.

How to Create and Edit Analytics

  1. In the Reports and Analytics work area or the Reports and Analytics panel tab (if available), click the Browse Catalog button.

  2. Click the New button, select Analysis in Analysis and Interactive Reporting, and select a subject area.

    Or, select your analysis in the catalog and click Edit.

  3. This table lists the ways you can use the tabs.

    Tab Task

    Criteria

    Select and define the columns to include.

    Add filters.

    Results

    Add views and set options for results.

    Prompts

    Define prompts to filter all views in the analysis.

    Advanced

    View or update the XML code and logical SQL statement that the analysis generates.

    Set options related to query performance.

  4. Save your analysis.

More Actions on Analytics

  1. In the Reports and Analytics work area or the Reports and Analytics panel tab (if available), select your analysis and click Action and select More..

  2. Click More for your analysis and select the action you want, for example Delete or Copy.

Add an Analysis to the Project Performance Dashboard

This example shows how to add an analysis to the Project Performance Dashboard. This dashboard is a page in the application, not a dashboard in the business intelligence (BI) catalog.

You can add predefined or user-defined analyses to any desktop page that you can personalize or edit.

The following table summarizes key decisions for this scenario.

Decisions to Consider In This Example

Which analysis do you want to add to the dashboard?

Project Income Statement

Caution: Ensure that the analysis doesn't query a large number of records. If it is, then the Project Performance Dashboard page can take a long time to open after you add the analysis.

Do you have to change the layout of the page?

Yes, to a two-column layout that is wider on the right side.

Note: The one- or two-column layout gives enough space to properly display analyses.

Do you want all or only one view of the analysis?

All views.

Are these changes for you only, or for all users of the Project Performance Dashboard?

You only.

Adding an Analysis

  1. Open the Project Performance Dashboard.

  2. Click your name in the global header and from the Administration submenu select Edit Pages....

  3. You need to be in an active sandbox session to add an analysis. Activate a sandbox if you're not already in a sandbox session. Click Activate Sandbox.

    If you are already in an active sandbox session, steps 3 to 7 are not required.

  4. On the Manage Sandboxes window, select a sandbox and click Set as Active.

  5. After you activate the sandbox, the application redirects you to the home page. On the Warning window, click Yes to continue.

  6. Open the Project Performance Dashboard.

  7. Click your name in the global header and from the Administration submenu select Edit Pages....

  8. Click Change Layout and select the Two columns, narrow left option.

  9. Click Add Content for the wider column.

    The Reports and Analytics folder in the Add Content dialog box contains what's in the BI catalog.

  10. Click through the folders in the catalog until you see the Project Financials Sample Reports folder. Click the name of the analysis in this folder, and click Add to include all views of the analysis.

    If you click Open or Project Income Statement, you can select a specific view to add.

  11. Click Close after you see the analysis added to the top of the wider column on the dashboard.

  12. Click Close to close the composer view and view the dashboard.

Create and Edit Dashboards

You can create and edit dashboards and define their content and layout to organize your analytics and other objects to create meaningful and navigable palettes of information. In addition to objects in the business intelligence (BI) catalog, such as analyses, reports, and prompts, you can add text, sections, and more to a dashboard.

Create a Dashboard

  1. Open the Reports and Analytics work area, or the Reports and Analytics panel tab (if available).

  2. Click Browse Catalog.

  3. Click New and select Dashboard under Analysis and Interactive Reporting.

  4. Enter the dashboard's name and description, and select a folder to save in.

  5. With the Add content now option selected, click OK.

  6. Optionally, add more pages, or tabs, within the dashboard.

  7. Drag and drop items from the Dashboard Objects or Catalog pane to add content to a page.

  8. Click Save.

Note: The first dashboard page is saved with the page 1 name by default. To rename this page:
  1. Click the Catalog link.

  2. In the Folders pane, select your dashboard.

  3. For page 1, click More and select Rename.

  4. Enter the new name and click OK.

Edit a Dashboard

  1. In the Reports and Analytics work area or the Reports and Analytics panel tab (if available) select your dashboard in the pane and click More.

  2. Select your dashboard in the pane and click More.

  3. Click Edit.

  4. Perform steps 6 and 7 from the preceding Creating Dashboards task, and make other changes as needed, for example:

    • Remove content from the dashboard.

    • Drag and drop within a page to move content around.

    • Change the layout of a page.

Copy the Payables Invoice Audit Listing Dashboard

This example shows how to copy a predefined dashboard so that you can edit the copy and not the original. If you have appropriate roles, then you can (only if necessary) create dashboards or edit predefined ones directly. If not, then you must copy predefined dashboards and edit the copies.

The following table summarizes key decisions for this scenario.

Decisions to Consider In This Example

Which predefined dashboard are you copying?

Payables Invoice Audit Listing

Is the copied version for yourself only or for multiple users?

Multiple users

To copy the dashboard and get it working:

  • Make a copy of the Payables Invoice Audit Listing dashboard.

  • Copy the components of the dashboard, the analysis and prompt.

  • Edit the copied dashboard so that it contains the copied analysis and prompt instead of the predefined ones.

Save your copies under Shared Folders > Custom in the business intelligence (BI) catalog. You must create folders within Custom so that the copies have a folder path similar to the originals.

Copying the Dashboard

  1. Open the Reports and Analytics work area.

  2. In the Contents pane, select Shared Folders > Financials > Payables > Invoices > Payables Invoice Audit Listing > Invoice Audit Listing, and click the More link.

  3. With the Payables Invoice Audit Listing dashboard selected in the Folders pane, click the Copy button on the toolbar.

  4. In the Folders pane, select Shared Folders > Custom > Financials.

  5. On the toolbar, click the New button and select Folder.

  6. Enter Payables in the Name field and click OK.

  7. Create an Invoices subfolder within the new Payables folder.

  8. Click Paste on the toolbar to copy the dashboard into the Invoices folder.

Copying the Prompt and Analysis

  1. With the Invoices folder still open, click New on the toolbar and select Folder.

  2. Enter Prompts in the Name field and click OK.

  3. Create another folder with the name Report Components. (In this context, report refers to the dashboard.)

  4. In the Folders pane, select Shared Folders > Financials > Payables > Invoices > Prompts.

  5. For the Payables Invoice Audit Listing dashboard prompt, click the More link and select Copy.

  6. In the Folders pane, select Shared Folders > Custom > Financials > Payables > Invoices > Prompts, and click Paste on the toolbar.

  7. In the Folders pane, select Shared Folders > Financials > Payables > Invoices > Report Components.

  8. For the Payables Invoice Audit Listing analysis, click the More link and select Copy.

  9. In the Folders pane, select Shared Folders > Custom > Financials > Payables > Invoices > Report Components, and click Paste on the toolbar.

Editing the Copied Dashboard

  1. In the Folders pane, select Shared Folders > Custom > Financials > Payables > Invoices > Payables Invoice Audit Listing.

  2. Click the Edit link for the Invoice Audit Listing dashboard.

  3. Click the Delete button for the Payables Invoice Audit Listing dashboard prompt within the Search region.

  4. In the Catalog pane, select Shared Folders > Custom > Financials > Payables > Invoices > Prompts > Payables Invoice Audit Listing and move it into the Search region.

  5. Delete the Payables Invoice Audit Listing compound view within the Section 1 region.

  6. In the Catalog pane, select Shared Folders > Custom > Financials > Payables > Invoices > Report Components > Payables Invoice Audit Listing and move it into the Section 1 region.

  7. Click Save.

Repository and Session Variables for Business Intelligence

This table shows repository and session variables available for use in your analyses.

Variable Name Type Usage Offering

CURRENT_DAY

Session Variable

Returns the value of Current Date.

All

CURRENT_MONTH

Repository Variable

Returns the value of Current Month in the YYYY/MM format.

All

CURRENT_WEEK

Repository Variable

Returns the value of the current week in YYYY Weeknnn format.

All

CURRENT_QUARTER

Repository Variable

Returns the value of Current Quarter in YYYY Q n format.

All

CURRENT_YEAR

Repository Variable

Returns the value of Current Year in the YYYY format.

All

NEXT_MONTH

Repository Variable

Returns the value of Next Month in the YYYY / MM format.

All

NEXT_WEEK

Repository Variable

Returns the value of Next Week in the YYYY Week nn format.

All

NEXT_QUARTER

Repository Variable

Returns the value of Next Calendar Quarter in the YYYY Q nn format.

All

NEXT_YEAR

Repository Variable

Returns the value of Next Year in the YYYY format.

All

PREVIOUS_MONTH

Repository Variable

Returns the value of Previous Month in the YYYY/MM format.

All

PREVIOUS_WEEK

Repository Variable

Returns the value of Previous Week in the YYYY Week nn format.

All

PREVIOUS_QUARTER

Repository Variable

Returns the value of Previous Quarter in the YYYY Q nn format.

All

FAQs

What happens to modified analytics and reports when a release update is applied?

Updates don't affect the Custom folder or My Folder in the catalog. Anything saved there is preserved, including the analytics and reports you edited or created. But anything saved outside those folders, in the predefined catalog, is preserved only if the update doesn't include a new version of those BI objects.

If an update includes a new version of a predefined object that you edited outside the Custom folder, the changes you made are saved as a new object. The new version from the update overwrites the existing predefined object. And a copy of the existing object (with your edits) is automatically created in the same folder, with a new name that indicates it's a new version.

If the update includes a new version of both the predefined object and a folder in its file path, the new folder name, along with the new version of the object, overwrites the existing predefined folder and object. And a copy of the existing folder (along with your edited object) is automatically created. The folder is renamed to indicate that it's a new version, but your edited object isn't renamed.

Note: Future updates won't affect renamed objects or anything within a renamed folder.