Introduction

This document introduces Reports Wand and sets out a suggested process to get started. For additional information see the Online User Guide by clicking the help icon from Wands for Oracle (or from the Product Releases section of Customer Support Community) and see the Reports Wand for Oracle instructional videos in the My Training (LMS) section of Customer Support Community.

Product Description

Reports Wand publishes Oracle E-Business Suite data directly into Excel. We have created two different ways, depending on your requirements to get you started. Your organization will have unique reporting requirements which cannot simply be met with out-of-the-box reports.  You can adjust the out-of-the box reports to suit your needs and/or create your own reports.

1. Reports Wand insights, which will generated report definitions based on Oracle e-Business Suite views.

2. A number of out of the box sample reports to fast track your deployment of Reports Wand.  The reports are somewhat generic in nature and serve as a starting point.

Reports Wand provides:

• An Excel interface where you can define your report layouts, formatting, links to other charts and formulas etc.

• The ability to refresh reports at the click of a button while maintaining all of your custom Excel formatting and links.

• A tool set enabling you to create report definitions based on your own SQL statements along with input parameters and responsibility based data security.

• Ability to use many user friendly Wands for Oracle functions such as parameter list, list of values, and drill on function.

• A custom Excel function which can execute SQL and return a value into an Excel cell. Double clicking the cell drills down to the detail.

• Access to Customer Support Community and example report definitions.

With Reports Wand you simply need to do the following:

1. Decide on the SQL for your particular reporting requirements.

2. Use the Create Report Definition wizard to capture the SQL, parameters, and filters on your Oracle system.

3. Assign the report to users.

Reports Wand insights

Reports Wand insights exposes a number of standard Oracle database views, across a variety of modules as report definitions. The report definitions are generated and enriched to facilitate easy user search and report creation. It doesn’t require administrators to write or understand SQL and provides you with instant insights into your E-Business Suite data.

In addition, Advanced insights reporting packs are also available to further extend Reports Wands capabilities. Reports Wand Advanced insights is premium content developed by Oracle Financials subject matter experts. The content is delivered as a number of pre-built Reports Wand report definitions rolled up into an Advanced insights report pack. Each report definition includes a SQL query, metadata and business value description explaining how to use the definition and suggesting the types of report you can use it for.

Prerequisites

In order to use Reports Wand insights you need to be running Wands for Oracle version 6.1.0 or higher. Reports Wand insights is only supported on Oracle e-Business Suite 12.1.x and 12.2.x.

Advanced insights report packs are supported from version 6.2.0 or higher.

As Reports Wand insights requires the creation of database objects, please ensure you have the correct Oracle database license to facilitate it.

Designate a Reports Wand Administrator

Please see the Reports Wand Administrator section for on instructions on designating an administrator.

Create Database Objects

In order to use Reports Wand insights you need to create custom database tables in your Oracle E-Business Suite Database.

If the DBA performing the Wands for Oracle installation selected the option to create custom database objects, then running the scripts below is not require, as the database tables will already be created.

Execute the following SQL script to create the required tables:

Tip: Note: You can modify the script to change the ownership of the table. If you choose to do so, ensure the APPS database user has SELECT, UPDATE and DELETE privileges on the table and create the required synonym to allow the APPS user to access the table without having to fully qualify the table name using the table owner.

CREATE TABLE "APPS"."XXE4A_REPORT_DEFINITIONS"

(

"REPORT_DEFINITION_ID" NUMBER(15,0) NOT NULL ENABLE,

"CONC_PROGRAM_APPLICATION_ID" NUMBER(15,0) NOT NULL ENABLE,

"CONCURRENT_PROGRAM_NAME" VARCHAR2(30 BYTE) NOT NULL ENABLE,

"TYPE" VARCHAR2(8 BYTE) NOT NULL ENABLE,

"FEATURED_FLAG" VARCHAR2(1 BYTE) NOT NULL ENABLE,

CONSTRAINT "XXE4A_REPORT_DEFN_CK_1" CHECK (TYPE IN ('SEEDED', 'ADVANCED')) ENABLE,

CONSTRAINT "XXE4A_REPORT_DEFN_PK" PRIMARY KEY ("REPORT_DEFINITION_ID") ENABLE,

CONSTRAINT "XXE4A_REPORT_DEFN_UK1" UNIQUE ("CONC_PROGRAM_APPLICATION_ID", "CONCURRENT_PROGRAM_NAME") ENABLE

);

CREATE TABLE "APPS"."XXE4A_REPORT_DEFINITIONS_TL"

(

"REPORT_DEFINITION_ID" NUMBER(15,0) NOT NULL ENABLE,

"LANGUAGE" VARCHAR2(30 BYTE) DEFAULT userenv('LANG') NOT NULL ENABLE,

"BUSINESS_VALUE_DEFINITION" CLOB,

"BUSINESS_AREA_NAME" VARCHAR2(240 BYTE) NOT NULL ENABLE,

"APPLICATION_NAME" VARCHAR2(240 BYTE) NOT NULL ENABLE,

"SEARCH_TAGS" VARCHAR2(240 BYTE),

"SUBJECT_AREA" VARCHAR2(240 BYTE) NOT NULL ENABLE,

CONSTRAINT "XXE4A_REPORT_DEFN_TL_PK" PRIMARY KEY ("REPORT_DEFINITION_ID", "LANGUAGE") ENABLE,

CONSTRAINT "XXE4A_REPORT_DEFN_TL_FK1" FOREIGN KEY ("REPORT_DEFINITION_ID") REFERENCES "APPS"."XXE4A_REPORT_DEFINITIONS" ("REPORT_DEFINITION_ID") ENABLE

);

CREATE TABLE "APPS"."XXE4A_REPORT_DEFN_COLUMNS"

(

"COLUMN_ID" NUMBER(15,0),

"REPORT_DEFINITION_ID" NUMBER(15,0) NOT NULL ENABLE,

"COLUMN_NAME" VARCHAR2(30 BYTE) NOT NULL ENABLE,

"SEQUENCE" NUMBER(4,0) NOT NULL ENABLE,

CONSTRAINT "XXE4A_REPORT_DEFN_COLUMNS_PK" PRIMARY KEY ("COLUMN_ID") ENABLE,

CONSTRAINT "XXE4A_REPORT_DEFN_COLUMNS_FK1" FOREIGN KEY ("REPORT_DEFINITION_ID") REFERENCES "APPS"."XXE4A_REPORT_DEFINITIONS" ("REPORT_DEFINITION_ID") ENABLE

);

CREATE TABLE "APPS"."XXE4A_REPORT_DEFN_COLUMNS_TL"

(

"COLUMN_ID" NUMBER(15,0),

"LANGUAGE" VARCHAR2(30 BYTE) DEFAULT userenv('LANG'),

"COLUMN_LABEL" VARCHAR2(30 BYTE) NOT NULL ENABLE,

"DESCRIPTION" VARCHAR2(240 BYTE),

CONSTRAINT "XXE4A_REPORT_DEFN_COLS_TL_PK" PRIMARY KEY ("COLUMN_ID", "LANGUAGE") ENABLE,

CONSTRAINT "XXE4A_REPORT_DEFN_COLS_TL_FK1" FOREIGN KEY ("COLUMN_ID") REFERENCES "APPS"."XXE4A_REPORT_DEFN_COLUMNS" ("COLUMN_ID") ENABLE

);

/

Human Resource Store Procedure (Optional)

If you wish to use date tracked report definitions from the Oracle Human Resources module you will need to create the Reports Wand Database Package containing the following procedure:

PROCEDURE XXE4A_HR_INITIALIZE (v_effective_date DATE)

AS

BEGIN

hr_integration_utils.add_or_update_session(nvl(v_effective_date,sysdate));

END XXE4A_HR_INITIALIZE;

You will be required to assign this stored procedure to the relevant Human Resource report definitions in order for them to work correctly.

More information regarding the Reports Wand Database Package can be found in the user guide.

Enable insights

A Wands for Oracle Administrator is required to enable the Reports Wand insights functionality. This is done from the Wands (Oracle) Ribbon > Admin Options > Configuration > Reports Wand > Enabled Reports Wand insights.

Generate Report Definitions

The generated definitions include additional columns displaying user friendly values rather than hard to understand database id’s making your data easier to use and understand. Additional insights report definitions and enrichment information can be added or updated later using a report definition package.

Generate Report Definitions

On the Reports Wand Ribbon navigate to insights > Generate. Select the Oracle E-Business Suite Views generation type, and click on Next to continue.

You can choose to generate all or only individual report definitions for an Oracle Application Module. Select the Generation Option and click Next to continue.

Select the Oracle Application Module required and click Next. The number of views eligible for generation is displayed in brackets after the application name.

Tip: Note: The generation process can be intensive. It is recommended to be run during times of low system usage when generating all the eligible definitions for an application module.  Network performance can also affected the generation so we recommend ensuring a reliable local connection.

Enrich Generated Report Definitions

The generated report definitions will be enriched with a featured flag and additional search tags. Featured report definitions have been flagged by Wands as report definitions we think will be useful and add-value. The additional search tags aid users identifying report definitions that could meet their reporting requirements.

On the Reports Wand Ribbon navigate to insights > Generate.

Select the insights Report Definition Packages generation type and click on Next to continue.

Use the Explorer to select the name and location of the Package file on your computer and then click Finish to proceed enrichment process.

Tip: Note: The insights report definition package file is included in the main Wands installation file that was downloaded and installed.

Once the process is complete a summary totaling the number of the report definitions enriched is displayed.

Generate Advanced insights Report Definitions

On the Reports Wand Ribbon navigate to insights > Generate. Select the insights Report Definition Packages generation type and click on Next to continue.

Use the Explorer to select the name and location of the Advance insights report pack on your computer and then click Finish to proceed enrichment process.

Tip: Note: To obtain the Advanced insights report pack contact your account manager to discuss adding it to your Wands for Oracle deployment.

Once the process is complete a summary totaling the number of the report definitions enriched is displayed.

Confirm Generated Report Definitions

Login to Customer Support Community and search for “Oracle Reports Wand insights Generated Report Definitions” (include quotes) to locate the article. The article includes a report definition, which can be used to create and run a report showing all generated report definitions.

View the article to download an Excel workbook containing the report definition. Click to download the file.

Create the report definition

1. Logon to Wands for Oracle.

2. Open the Reports Wand insights Reports Upload Excel file.

3. On the RW Template sheet, set the Processing Action to Create for the report definition.

4. Process all reports from the Reports Wand ribbon. Reports Wand > Mass Administration > Process All.

Run the report

1. In the workbook opened above, please select the Reports Wand insights Generate sheet.

2. Open the Parameter List (Reports Wand > Parameter List), and optionally select an application module from the list of values.

3. Click Execute to run the report.

Assign Reports to Users

Please see the Assign Reports section for instructions on how to assign report definitions to Oracle request groups.

Standard Sample Reports

We have developed a number of sample reports which cover many of the Oracle E-Business Suite modules. These reports are available for Oracle 11i and for Oracle R12. The report definitions are saved in an Excel workbook. Reports Wand Administrator functionality is used to load these reports into your Oracle system by following these easy steps:

1. Prerequisite Setup

2. Upload Report Definitions

3. Assign Reports to Users

4. Test the Reports

Prerequisites

In order to use Reports Wand you need to be running the Wands for Oracle version 5.7 or higher. In order to load the sample reports on your Oracle system please perform the following steps.

Designate a Reports Wand Administrator

Please see the Reports Wand Administrator section for more information on how designate an administrator.

Create Report Categories

Use categories to organize and group your reports.  Users with System Administrator responsibilities (or similar access) can create and modify categories. 

The following categories are typically used:

To create or modify categories log into Oracle EBS, select a system administrator responsibility, and navigate to Concurrent > Program > Types.

After creating or modifying report categories within Oracle EBS exit Wands for Oracle and then launch again to see or enable the categories within Wands for Oracle.

HR Report definition prerequisite steps (Optional)

If you would like to use our HR and Payroll report definitions set up two custom Value Sets for the HR and Payroll reports exactly as noted below.

Select a system administrator responsibility and navigate to Application > Validation > Set

Upload the report definitions

Login to Customer Support Community and search for “Oracle Reports Wand Sample Reports” (include quotes) to locate the article. 

View the article to get a link to a zip file with the latest report templates.  Click to download the files and unzip to a folder.

1. Logon to Wands for Oracle.

2. Unzip the Reports Wand Sample Reports file.

3. Depending on your Oracle E-Business Suite release, navigate to the 11i or R12 folder.

4. Open the Reports Wand Sample Reports Upload Excel file.

5. All the Processing Types for each report will be set to Create. You can change this value to No Action if you do not want to load all the reports.

   1. _{Note: If you did not load the value sets for HR and Payroll you need to change the Processing Type for the HR and Payroll reports to No Action.}

6. Process all reports from the Reports Wand ribbon. Reports Wand > Mass Administration > Process All.

Review the status of the load

To ensure the load was successful:

1. Check the error column (c) in the Reports Wand template workbook. If a report definition does not load an error message will appear in the Error column of the Reports Wand Administrator Template.  Click the cell showing the error message and view the cell comment for details about the error.  Update the report definition as needed by clicking Edit (in the Report Definition section) on the Reports Wand ribbon to open the report definition wizard.  After correcting the problem click the Action column and row for the report and navigate to Reports Wand ribbon > Process (in the Mass Administration section) > Process Single.  Repeat this process as needed until all report definitions are uploaded.

2. Confirm that the new concurrent programs were created and are assigned to the Reports Wand custom application by querying them in Oracle under System Administrator responsibility > Concurrent > Program > Define.

Assign the reports to users

After importing Wands for Oracle as report definitions it is necessary to assign request groups to the reports.  Please see the Assign Reports section for instructions on how to assign report definitions to Oracle request groups.

Test the reports

With the report definitions loaded and responsibilities assigned please click Refresh List on the Reports Wand toolbar or exit Wands for Oracle using the Exit icon on the Wands (Oracle) ribbon and then re-start Wands for Oracle. You can use the sample Excel report templates included in the zip file to run reports based on the report definitions you loaded. You’ll find templates for 11i and R12 reports in the same folder as the Reports Wand Sample Reports Upload Excel file used to create the report definitions. Before running a report please select a responsibility which grants access to the report.

For instructions on how to use Reports Wand to refresh the reports see the Online User Guide by clicking the help icon from Wands for Oracle and see the Reports Wand for Oracle instructional videos in the My Training (LMS) section of Customer Support Community.

Report Inventory

A list of sample reports is provided below.  For an updated list of reports and the actual report definitions please login to Customer Support Community and search for “Oracle Reports Wand Sample Reports” (include quotes) to locate the article with a link to the files. 

Oracle 11i

Oracle R12

Reports Wand Administrator

The user(s) configuring Reports Wand require Reports Wand Administrator access privileges.  Your Wands for Oracle System Administrator can launch Wands for Oracle into Excel and navigate to Wands (Oracle) Ribbon > Admin Options > User Permissions to establish Reports Wand Administrators.

A Reports Wand Administrator has the ability to perform the following:

• Create and/or edit Reports Wand definitions.

• Upload Reports Wand definitions to Oracle.

• Import Reports Wand definitions from Oracle to Excel.

Assign report definitions to Request Groups enabling user access based on Responsibilities assigned to the request groups.

Assign Reports

Users with a responsibility assigned to the request group will be able to create and run reports based on the report definitions. 

To ensure an efficient assignment of the report definitions to responsibility and request group combinations please plan carefully before assigning the definitions.

Multiple report definitions can be assigned to a request group using the Assign Reports feature on the Reports Wand toolbar. 

Within the Maintain Report Definition Assignments dialog select the desired request group or responsibility and then select the report definitions to assign to it.  Please note this process assigns report definitions to request groups so any responsibility sharing the request group will be able to access the report. 

To assign a group or multiple report definitions to a request group highlight multiple reports and click Add.  Click ‘Assign’ to complete the report definition assignment.

Creating your own reports

You can use report definitions to create your own reports in Excel.  Select Create from the Report grouping on the Reports Wand tool bar and follow the instructions set forth in the user guide and Customer Support Community LMS Videos to create your own reports.

As a Reports Wand Administrator you can also create your own report definitions.  First create the underlying SQL statement and then from the Reports Wand ribbon select Create in the Report Definition grouping and follow the steps in the Create Report Definition Wizard.

The detailed steps are as follows

Other resources

For more information please refer to:

• The Online User Guide by clicking the help icon from Wands for Oracle (or from the Product Releases section of Customer Support Community)

• Reports Wand for Oracle Learning Management System (LMS) videos in the My Training (LMS) section of Customer Support Community.

• Have a question?  Share it in Customer Support Community or email e4asupport@insightsoftware.com