Table of Contents
BI publisher is used extensively in Oracle E-Business Suite. The standard Oracle design for the report layout often prevents the report from being exported to Excel in a usable format. Making changes to the displayed columns or parameters usually requires the submission of time consuming change requests that often sit in long queues waiting for development resource availability.
Oracle BI Publisher report development requires specialist developer resources to build / modify the XML layout designs. The end to end process typically takes a few days per report plus the test and debugging cycles.
From an operational view-point; waiting for such requests will result in a manual re-work process including V-Lookup and reformat of reporting data. This wastes time and effort and often leads to mistakes and or incorrect results.
In this article we will demonstrate a much faster method, taking less than half a day per report, or just a few minutes as per this example. This includes importing the standard Oracle BI report to Blitz Report and modification of the output. We also demonstrate the addition of run-time parameters. All of this without the need for the laborious time-consuming XML template re-design.
Time saving steps:
1. Download and install Blitz Report to your development instance.
2. Import a standard Oracle BI Report, in this example we will use the ‘All Inventories Value’ report.
3. Make the report changes needed.
4. Validate the report to the standard Oracle report.
5. Import the Blitz Report to your UAT test instance.
Step 1 – Install Blitz Report
Install the free to try Blitz Report from the download link. Contact us for a guided installation if needed, the installation process, typically takes less than fifteen minutes, but we are happy to help your database team if needed.
Step 2 – Import the BI Report to Blitz Report
The installation process, will automatically add the Blitz Report form function to each of your implemented responsibilities. The latest version of Blitz Report will also add a forms personalization option so that Blitz Report is also available to export form data without delays caused by data volumes.
Once the Blitz Report development form opens, click on the Setup button and then select Import from the Tools menu bar. Always use the same responsibility as per the original Oracle report has been registered.This ensures that all the Oracle profiles and organization access are correctly assigned.
Select the the Oracle BI Publisher report to import into Blitz Report. In this example we will use the ‘All Inventories Value Report (XML)’ report.
Step 3 – Make the report changes needed
The query from the BI Publisher package CSTRAIVR_XML will be imported into the Blitz Setup form. In this report example, Oracle designed the report as a single query which simplifies the overall development process.
The parameters and assignments for the original Oracle report are automatically converted by Blitz Report which reduces development time for the developer.
For some of the more complex standard Oracle reports, the developer may need to analyze the structure of the package and re-work the query before it can be successfully run in Blitz Report. This is achieved by double clicking on the BIP Code field to open the original package from Oracle.
To test the report, the developer can immediately run it to validate that the SQL is working correctly, any failures will be sent to a log file which will be presented to the developer in the Blitz Report form.
Using the correct responsibility is important, for example, if you were to run an inventory costing report in the system administrator responsibility then it would not be able to access the inventory organization data and hence would fail or not allow selection of mandatory parameters.
The Blitz Report offers the ability to select Multiple Values from the run-time parameters, e.g. if you would like to report on multiple item category values, or you can simply leave this checkbox blank.
Click on the Run button to start the report. This will open the report parameters form which we will leave as per the default for now. Next, click on the Run button to launch the report and open the Blitz Report output in Excel.
You do not need to open the concurrent manager requests form, unless you close the Excel file, in which case it will be available to re-open from the View Requests form.
The Blitz Report columns are automatically formatted by data type with filters to avoid the user having to alter anything in the output. A time-saving feature.
The developer can re-work the imported SQL to remove any unwanted columns, or instead they can hide fields using the Blitz Report Column Template function.
This is done by clicking on the Options field in the parameters form to move the column from the right Displayed Columns and move it to the left pane of Available Columns.
Click the New button within the Options form to open the column selector and then move the column as highlighted in the image using the left arrow.
Give the template a meaningful Name per your organizations naming conventions.
After the change; the report will no longer display the column: Decode(:P Sort Option,1,Fnd Fl.
It is also possible to change the column sequence using the up and down arrows.
Each assigned user, can run the report the default report template or they can create their own template.
This helpful feature is available to the User level to provide further flexibility of the Blitz Report layout.
The revised report layout appears as follows:
To add new parameters to the report, click on the Setup button and change to the Parameters tab.
Now scroll to the last parameter and click the down arrow to create a new Display Seq number, which in this example should be assigned with the number 160.
It is possible to create new or to save time, you can select from the existing parameters list as used by other Blitz Reports. To re-use an existing parameter, double clicking in the Parameter Name field to list all the available report parameters. In this example, we will add the warehouse code to the report, achieved by selecting a suitable parameter such as Organization Code and then changing the Anchor field to :p_organization.
Test that the new parameter functions, by selecting a Default Value, in this case M1 has been selected as shown below. This can then be removed and saved without the default value in place depending on your requirement.
Example:
Step 4 – Validate the report against the standard version
Run the completed report and compare the values with the standard version and once this process is completed add version history by clicking on the Version field at the top right of the form.
Step 5 – Migrate report to production
Once all testing is complete, use the Blitz Report Export function in your development instance to create an xml file ready for importing into the next instance, either UAT, Pre-prod or production, etc.
To export the report, select the Export function from the Tools menu on the top menu bar. The function opens with the default values: Report, XML together with the name of the new report. This creates a file which can be saved in a convenient place on your computer in readiness for migration to your next Oracle instance.
Once the export process is completed, change to the next instance and click on Import from the Tools menu and import the XML file by selecting XML and then enter the filename created from the development instance.
The process is very fast and will create the report together with all the changes and parameters.
A message should appear in your browser advising of successful completion as shown below.
Summary
The summarized steps are as follows:
1. Download and install the free version of Blitz Report
2. Identify the BI Publisher report and import to Blitz Report
3. Modify the report
4. Test and add version control to the new Blitz Report
5. Migrate to test and or further instances.
For more Blitz Report great ideas and report examples, why not visit our Enginatics YouTube Channel.
For more information, contact us for a live demo with your individual request.
We think the best way to find out what Blitz Report can do for your business is a live demonstration. Depending on your location we can schedule an onsite appointment or a remote video call. Or in the meantime, you can review the short video overview below.
The Blitz Report™ software is free to use for your first 30 reports, you can plan a 30 minutes guided installation with the team at enginatics.com