Skip to main content

Noetix Generator for Oracle Business Intelligence User Addendum

Generated Report and Dashboard Templates

Using Generated Report Templates

The reports generated into Oracle BI Answers are meant to be templates to start from when seeking the answer to a business question. Some may answer the business question right out of the box, but most will have to be copied and modified to match your company’s specific reporting requirements.

If a report template does not meet the business need out of the box, a copy should be saved to the My Folders area or a folder outside of the Top Level Name folder under Shared Folders. Once copied, it can then be modified to meet the business need.

NOTE: The generated report templates should never be modified directly in Oracle BI Answers. Any changes made to the templates will be overridden during the next regeneration. NoetixAnswers is meant to be either run as generated, or used as a starting point to make a new report.

Prompts in Generated Report Templates

The report templates generated into Oracle BI Answers are meant to be used in conjunction with the dashboard templates generated into Oracle BI Dashboards. Many of the report templates contain filters that use Oracle BI’s is prompted operator, which offload the specification of a filter value to a global prompt.

Those global prompts are included in the dashboard page associated with the report template and will supply the values necessary to ensure that the report template is filtered appropriately.

If you want to use the generated report templates outside of the generated dashboard templates, then you must modify the is prompted filters to use a different operator and then specify a filter value, or remove the filters and create prompts in Oracle BI Answers instead.

WARNING: The report templates that contain is prompted filters should never be executed directly in Oracle BI Answers. Doing so may take up more resources on the Business Intelligence or database server than is desired and may produce a long running query. Many of the underlying Noetix views must have filters applied in order to be queried successfully.

Using Generated Dashboard Templates

The dashboards generated into Oracle BI Dashboards are meant to be templates to start from instead of fully finished dashboards. Some may meet your company’s needs out of the box, but many may need to be copied and customized to provide all of the information required.

If a dashboard template does not meet the business need out of the box, a copy should be saved to the My Dashboard area or a folder outside of the Top Level Name folder under Shared Folders. If the underlying report template in Oracle BI Answers needs to be modified as well, a copy of the report should be created as well. Once copies of the dashboard and underlying report template have been made, they can be modified to meet the business need.

NOTE: The dashboard templates should never be modified directly in Oracle BI Dashboards. Any changes made to the templates will be overwritten during the next regeneration. NoetixAnswers is meant to be either run as generated, or used as a starting point to make a new dashboard.

Writing Multi-view Reports and the JOIN ASSIST Column

When creating a report from two or more Noetix views or Noetix Analytics Operational Data Store tables, the special JOIN ASSIST column must be included to ensure that OBI properly processes the request. This column (based on SYS.DUAL.DUMMY) assures that OBI will construct the proper joins between the views or tables, thus ensuring the returned results are correct.

This column typically only needs to be used in reports that meet both of the following criteria:

  • The report uses 2 or more Noetix views or Noetix Analytics Operational Data Store tables.

  • The report does not include a measure column. Measure columns have a suffix like “_SUM” or “_COUNT”.

Each report should include only one JOIN ASSIST column. Reports with three or more views should choose the JOIN ASSIST column from the central view in the report.

This column should be included in reports as an “is equal to” filter with value set to “X” to match the value of the SYS.DUAL.DUMMY column:

This hides the column from report users and ensures that the “Limited Values” prompt list of values option will work as expected.

NOTE: If you fail to include the JOIN ASSIST column in reports that meet the above criteria, you may receive incorrect results. On the other hand, including the JOIN ASSIST column in reports that do not meet these criteria will not affect the accuracy of the results.

Reports against 1 view

There are certain instances when it will be necessary to include the JOIN ASSIST column in reports that utilize just one view. This column should be included when key flexfield columns from the selected view are included in the report.

Reports with 2 views

When joining only two Noetix views together, you can choose the JOIN ASSIST column from either view.

Reports with 3 or more views

When writing a report with 3 or more views, you must choose at least one view that can join to all the other views in your report. You should choose the JOIN ASSIST column from this view.

Below are examples from the NoetixViews for Oracle Purchasing and Assets modules to help you identify the center view of a report from the Z$ columns. In each example, it is assumed that we have already identified our reporting requirements and that the report requires columns from all views in the example. For the sake of brevity, only the Z$ columns between the views in the example are shown.

Example 1

  • PO_Buyers

    • Z$PO_Buyers

  • PO_Vendors

    • Z$PO_Vendors

  • PO_Purchase_Orders

    • Z$PO_Buyers

    • Z$PO_Purchase_Orders

    • Z$PO_Vendors

From the Z$ columns, we can see that PO_Purchase_Orders has a join to both PO_Buyers and PO_Vendors.  A diagram of the relationships between these views looks like this:

PO_Buyers

PO_Vendors

PO_Purchase_Orders

Since PO_Purchase_Orders is involved with joins to both PO_Buyers and PO_Vendors, choose the JOIN ASSIST column from PO_Purchase_Orders.

Example 2

  • PO_Buyers

    • Z$PO_Buyers

  • PO_Purchase_Orders

    • Z$PO_Buyers

    • Z$PO_Purchase_Orders

  • PO_Invoices

    • Z$PO_Purchase_Orders

In this example, we see that PO_Invoices has a join to PO_Purchase_Orders and PO_Purchase_Orders has a join to PO_Buyers. A diagram of the relationships between these views looks like this:

PO_Buyers

PO_Purchase_Orders

PO_Invoices

Again, since PO_Purchase_Orders is involved with joins to both PO_Buyers and PO_Invoices, choose the JOIN ASSIST column from PO_Purchase_Orders.

Example 3

  • FA_Assets

    • Z$FA_Assets

  • FA_Asset_Assignments

    • Z$FA_Assets

  • FA_Asset_Financials

    • Z$FA_Assets

From the Z$ columns, we can see that FA_Asset_Assignments has a join to FA_Assets, as does FA_Asset_Financials. A diagram of the relationships between these views looks like this:

FA_Assets

FA_Asset_Assignments

FA_Asset_Financials

Since FA_Assets is involved with joins to both FA_Asset_Assignments and FA_Asset_Financials, choose the JOIN ASSIST column from FA_Assets.

Was this article helpful?

We're sorry to hear that.