Blitz Report™ User Guide
- 2.1 Selecting a report
- 2.2 Parameter values
- 2.3 Running and viewing the output
- 2.4 Output formats
- 2.5 Email
- 2.6 Scheduling a report
- 3.1 Report creation steps
- 3.2 Anchors and binds
- 3.3 Dynamic SQL example
- 3.4 Report header
- 3.5 Parameters
- 3.6 Assignments
- 3.7 Categories
- 3.8 Multi-language support
- 3.9 Access control
- 4.1 LOVs
- 4.2 Assignments
- 4.3 Categories
- 4.4 Copy Report
- 4.5 Export
- 4.6 Import
- 4.7 Upload Large SQL
- 4.8 Column Translations
- 4.9 Dynamic Column Translations
- 4.10 License Key
- 5.1 Debugging
- 5.2 Run Oracle EBS with Firefox
- 5.3 Outbound Interface
- 5.4 Data Warehouse
- 5.5 MS Excel and CSV files
- 5.6 Disable protected view
Blitz Report is an Oracle Forms-based software, fully integrated with Oracle E-Business Suite. It enables your IT support team to easily store and edit SQL scripts for reports, and to make them available to your business users. Blitz Report runs as a concurrent process and generates output files in XLSX or text delimited CSV format. Upon completion, reports automatically download and open in Excel.
When developing Blitz Report, our aim was to create the most simple and efficient operational reporting tool for Oracle EBS – optimized for skilled IT professionals to better organize and maintain reporting SQLs and for business users to quickly extract application data without having to learn new skills.
We hope that you enjoy Blitz Report and welcome your feedback to firstname.lastname@example.org.
2. Running Blitz Report
Basic steps to run a Blitz Report are:
2.1 Selecting a report
When opening Blitz Report, a selection of available reports is displayed automatically. The list can be restricted further by typing the first characters of the desired report name.
Reports can also be selected by:
- Performing a text search by typing into the report name field. This executes a full text search on report name, description, parameters and/or SQL definition, enabling you to search by column names, for example. The search is not case sensitive and allows creation of logical expressions using keywords ‘and’ or ‘or’ and use of brackets to group sub-expressions.
- A double click into the report name field
- Using the LOV attached to the report name
- Pressing the F1-key
- Picking a Category from the dropdown list
- Using the find/torch menu symbol
- Running a query on the report name or description fields via F11, Ctrl+F11
By default, users can select from reports assigned to their current logon responsibility only.
The LOV of reports can be extended by setting profile option ‘Blitz Report Filter Reports by Responsibility’ to ‘No’. This way, users will see all the reports assigned to them, regardless of their current logon responsibility.
Text search example:
Scrolling further to the right in the search results shows the matching score and fields in which the keywords are found.
When entering multiple keywords, the search matches all keywords by default.
Above example lists all reports containing both words, ‘item’ and ‘invoice’.
To search on either of the keywords, combine them with ‘or’.
2.2. Parameter values
Report parameters filter, limit or define the extracted data. Parameters can have lists of values that either enforce selection of one particular value (e.g. account number) or that allow matching by wildcards such as ’%’ or ‘_’.
To display the LOV for a parameter, double click either into the parameter value field or on the three LOV dots on the right.
Some parameters may allow entry of multiple values, depending on the definition of the parameter (see here for details).
To run a report for a list of account numbers for example, tick the ‘Multiple Values’ checkbox next to the parameter and either select account numbers one by one from the LOV or enter a semicolon-separated list of numbers manually.
Larger lists of multiple parameter values up to a length of 32000 characters can be entered using the ‘Edit Field’ menu function.
Multiple values can be separated by semicolon or line feed. Using the ‘Edit Field’ menu icon, you can for example copy and paste a list of parameter values directly from an Excel sheet.
Users can store their own default parameter values using the Actions → Save Parameter Defaults menu option.
2.3 Running and viewing the output
A click on the ‘Run’ button launches a background concurrent process to extract the report data. The output XLSX file opens automatically upon program completion.
While the report concurrent process is pending or running, the request status is displayed on the output button. Once the report is complete, the output button can also be used to re-download the output file.
2.4 Output formats
Blitz Report extracts data in XLSX format as a default. Use profile option ‘Blitz Report Output Format’ to extract data to delimited text formats CSV or TSV.
While XLSX output is convenient to use, its output size is limited to 1048575 rows.
CSV output, on the other hand, is not limited in file size and offers maximum extraction performance.
Tick the email checkbox to send the Blitz Report output file as an email attachment. The email address defaults to the user’s email address and can be changed manually. Multiple email addresses can be entered as a comma-separated list. A double click into the email-address field opens a bigger editor window to facilitate entry.
It is also possible to set up a default email address for particular reports or via profile option ‘Blitz Report Default Email Address’ (see here for details).
The default email subject for Blitz Reports is stored in FND message XXEN_REPORT_EMAIL_SUBJECT. To change this text, navigate to Application Developer → Application → Message → query message XXEN_REPORT_EMAIL_SUBJECT and edit the message text.
You can use the following placeholders for automated replacement at run-time:
&REPORT_NAME &ROW_COUNT &DB_NAME &USER_NAME &REQUEST_ID &SITE_NAME
You can also create new custom messages. Prefix these with XXEN_EMAIL and use them as default email subjects by setting the profile option ‘Blitz Report Email Subject’.
2.6 Scheduling a report
Blitz Reports can be scheduled as background processes to run at a certain date or time. This feature can be used to run time-consuming data extractions during non-office hours or to use a scheduled Blitz Report as an outbound interface or a very simple and flexible data warehouse.
The easiest way to schedule a report is to run the Blitz Report first, and to then copy the submitted concurrent request and schedule it:
- Run a report from the Blitz Report execution window with parameter restrictions as required
- Navigate to the menu → view → requests → submit a new request → single request → Copy → Find → select the concurrent request submitted in step 1.
- Update schedule and delivery options as required and re-submit the request
Alternatively, you can submit a new ‘Blitz Report’ concurrent request, populating its parameters manually. Select the required report name from the LOV and populate parameters 1 to 98 with values in exactly the same order as they appear on the Blitz Report execution window. For example, if the third parameter is ‘Operating Unit’ with a value of ‘Vision Operations’, then Parameter3 of the concurrent request would need to be populated with this value in order to filter the report data by operating unit.
A multiple values string can be entered by using the keyword <multiple_values> (see below).
To schedule the report ‘Customer Sites’ and extract the data as shown in the previous example, concurrent request parameters would need to be entered as follows.
3. Building a Blitz Report
3.1 Report creation steps
With Blitz Report you can easily leverage existing SQL to make a robust report for your users.
A Blitz Report consists of a main extraction SQL and optional parameters that enable users to control or restrict data returned by a report.
To create new Reports, the profile option ‘Blitz Report Access Level’ must be set to either ‘Developer’ or ‘System’. With this profile setting, the Blitz Report execution window shows an additional ‘Setup’ button, which opens the setup window as shown below.Basic steps to create a new report are:
Unlike most reporting tools, Blitz Report creates dynamic SQL at run-time, combining the extraction SQL and the user-specified parameters in an optimal way. This allows unused parameters to be dropped at run-time, thus eliminating a frequent source of non-performant SQL – the very common WHERE clause for optional parameters ‘column_name=nvl(:bind_variable, column_name)’.
To ensure that there is no unnecessary parsing of the dynamic SQL, Blitz Report’s parameterization uses bind variables.
3.2 Anchors and binds
Anchors are ‘placeholders’ in the extraction SQL, which allow the precise placement of additional (optional) parameterized SQL clauses at run-time. These clauses are constructed from user-specified parameters, which are then inserted into the extraction SQL at run-time. There are two types of Anchors:
WHERE clause SQL anchors, such as ‘1=1’, ‘2=2’ etc. Blitz Report inserts the associated SQL text directly before these anchors, automatically adding the keyword ‘and’ and a line feed, to create valid SQL. This allows quick parameter creation – no need to consider the precise position of the ‘and’ keyword.
A typical example for a SQL text would be ‘column_name=:bind_variable’, where :bind_variable would be bound with the parameter value entered by the user. In case you require the parameter value to show up as lexical text in the SQL, e.g. to purposefully enforce reparsing for different parameter values, you can use placeholder <parameter_value> as shown in below example.
Lexical parameter references work in the same way as lexical parameters in sqlplus or Oracle reports. Blitz Report replaces these placeholders completely with the parameter SQL text at run-time. To replace a lexical with the user entered parameter value, use the string in the SQL text field. If a parameter value is left blank, the corresponding reference is removed before SQL execution.
However, the ‘n=n’ anchor can only be used for WHERE clauses (since the ‘n=n’ syntax remains in the run-time SQL).
The ‘&lexical’ anchor can be used to add whatever SQL ‘snippets’ are required by the report, featuring (but not limited to):
- WHERE clauses
- Dynamic tables and columns
- ORDER BY, GROUP BY
- Complete subselects or EXISTS clauses
The same anchor may be used multiple times inside a report SQL. Blitz Report inserts the corresponding parameter text for each occurrence.
A lexical parameter SQL text may contain a bind variable, which will be bound with the parameter value entered by the user when running the report.
Similar to other reporting solutions, Blitz Report also supports the use of bind parameters. To avoid performance issues due to due to nvl(:bind_variable, column_name) coding for optional parameters however, it is recommended to use one of the above anchors for dynamic SQL instead.
|Anchor Type||Report SQL||Parameter SQL text||Run-time SQL|
3.3 Dynamic SQL example
A query on parties and accounts (see below) should allow users to extract all customers’ information or to restrict the data by optional parameters such as customer name or account number.
select hp.party_number, hp.party_type, hp.party_name, hca.account_number from hz_parties hp, hz_cust_accounts hca where 1=1 and hp.party_id=hca.party_id
A restriction to customer name would require addition of a WHERE clause:
upper(hp.party_name) like upper(:customer_name)
In Blitz Report, the parameterized WHERE clauses are set up separately from the report SQL. Blitz Report only inserts individual WHERE clauses into the extraction SQL (at run-time) if the user enters a value for that particular parameter.
In this example, If the user provides a value for the customer name parameter, Blitz Report would add the above WHERE clause at the position of the anchor ‘1=1’ and execute the below SQL for data extraction.
select hp.party_number, hp.party_type, hp.party_name, hca.account_number from hz_parties hp, hz_cust_accounts hca where upper(hp.party_name) like upper(:customer_name) and 1=1 and hp.party_id=hca.party_id
3.4 Report header
Report name uniquely identifies reports. Names should be short and descriptive.
Good practice is to prefix report names with the appropriate Oracle EBS module short code.
An optional report description of maximum 4000 characters may be set up to assist users in understanding and using the report.
This Google like search functionality retrieves reports by report name, description or underlying SQL. You can search for example for a table or column name accessed by a SQL, or by parts of the report name or description.
Retrieve reports by category.
Defines whether a report is visible to end users. Disabling a particular report would remove it from the end user report list. This may be useful during the development and testing phase. Even if a report is disabled, it may still be run by users having user admin, developer or system access.
Double click on a report version number to review the change history and previous report SQLs. Note that a new report version number is added and stored automatically at each update of a report’s SQL. Other report setup modifications such as report name, description or parameter changes are not stored in the version history.
Reports of type ‘Protected’ or ‘System’ are visible to users with access profiles set to ‘User Admin’ or ‘Developer’, but may only be edited by users with access profiles set to ‘System’.
This serves as an additional level of protection for reports providing important system functionality such as outbound interfaces or search screens.
See tutorials for examples on how to use Blitz Report for custom system functionality.
BI Publisher data definition code. When populated, Blitz Report executes the dataTrigger section e.g. beforeReportTrigger of the associated data definition XML template. This allows running BI Publisher report SQLs through Blitz Report where the data extracted is based on global temporary tables preprocessed in the before report trigger.
Double click onto the BI Code to download and view the data definition XML template.
Display format for numeric values in XLSX output files. By setting the number format, column values can be shown with thousands separator and a fixed number of two decimals for example.
The display format can also be set for individual report columns or on profile option level, see column translations.
Email address of the report author.
Additional information can be found on registered authors in our online library.
Default email address for sending output files. If different email address are set up on different levels, the default email on the Blitz Report execution window is derived in following order:
- Default email setup on report level
- Profile option ‘Blitz Report Default Email Address’
- FND user’s email
- HR person’s email
Allows changing the output format from Excel XLSX (Excel) to CSV (comma separated values) or TSV (tab separated values).
Limits the maximum number of lines for report execution.
Maximum execution time limit in seconds. The Blitz Report Monitor concurrent program automatically cancels reports exceeding the set time limit. This avoids excessive server load if a user submits a report with insufficient parameter restrictions for example.
A time limit may also be set up using profile option ‘Blitz Report Time Limit’, which would take precedence over a setting on individual report level.
Disable Column Translations
Disables language specific column header name translations. This might be required for outbound interfaces, where the receiving system expects consistent column namings, irrespective of the session language of the user running the interface Blitz Report.
The report extraction SQL must start either with the word ‘select’ or ‘with’. Blitz Report does not parse the SQL syntax for validity. SQL entry through the form is limited to 32767 characters. To create a report with a larger SQL, use the Upload Large SQL functionality from the tools menu.
Parameter definitions consist of:
- a parameter name, display sequence and optional parameter description and default value
- SQL text to be inserted into the report SQL dynamically at run-time
- an anchor as a reference to a position for the insertion
- a parameter type and optional LOV
- an optional matching value to restrict the SQL text insertion to certain parameter values
Sequence number that defines the order in which parameters are displayed.
If different WHERE clauses are used for the same parameter name, e.g. to insert a different SQL text at different SQL positions or depending on different parameter matching values, display sequence is populated only for one record and left blank for subsequent lines of that parameter name.
Parameter identifier. Click on the LOV to copy existing parameter definitions from other reports.
Parameter specific text added dynamically into the report SQL if a value for the parameter is entered at run-time. Usually, the SQL text would contain a bind variable name starting with a colon e.g. ‘:account_number’. Blitz Report automatically detects the variable and binds it with the value entered by the user. A maximum of one bind variable per parameter is allowed. If a parameter’s SQL text contains more than one bind variables, only the first one is bound with the entered parameter value.
Blitz Report also allows the insertion of SQL text using matching values. If the SQL text does not contain a bind variable, then the field ‘Matching Value’ becomes mandatory. Blitz Report inserts the corresponding SQL text if the parameter value entered by the user matches the matching value.
If the SQL text includes a bind variable restriction and the user ticks the multiple values checkbox, Blitz Report automatically replaces the restriction with an IN-clause instead. This replacement works for the following WHERE clause restrictions using an ‘equal’ or ‘like’ operator:
column=:bind_variable column like :bind_variable upper(column) like upper(:bind_variable) lower(column) like lower (:bind_variable) column<>:bind_variable column not like :bind_variable upper(column) not like upper(:bind_variable) lower(column) not like lower (:bind_variable)
If the comparison between column and bind variable is done by a different operator e.g. column>=:bind_variable or, if the bind variable is encapsulated in additional functions, automated IN-clause replacement is not possible and the multiple values functionality is not available for this parameter.
The position inside the report SQL where the parameter SQL text is inserted. The LOV for this field shows all anchors used in the SQL. See here for detailed explanation of Anchors.
Type of parameter used to control the LOV for parameter values at run-time. For parameter records with blank display sequence, type remains blank.
The following Types may be used:
- Char: free text
- Date: a valid date
- DateTime: a valid date including timestamp
- Number: a valid number
- LOV: a list of values shared among different reports and parameters, see Tools → LOVs
- LOV custom: a list of values used by one parameter only
- LOV Oracle: an Oracle standard list of values
Name of a shared or Oracle standard LOV.
SQL definition for a list of values. Double click on this field to open the LOV definition window. Valid SQLs need to have the columns ‘value’ and ‘description’ selected.
If the parameter value entered at run-time matches the matching value, then the corresponding SQL text is inserted.
Matching values may contain wildcard characters. If, for the same Anchor, the parameter value entered by the user matches more than one matching value due to use of wildcards, then the SQL text of the best (longest string) match is inserted.
Specifies a default parameter value. If the value starts with the keyword ‘select’, then Blitz Report would execute the SQL to derive the default value dynamically instead of using a fixed value. Example: To get the current date in GL period format, use the following SQL as a default value:
select to_char(sysdate,'MON-RR') from dual
Additional parameter description displayed in the bottom left message area of the Blitz Report execution window.
The required flag enforces a parameter value entry by the user, for example to prevent accidental report submission with insufficient parameter restrictions.
Advanced logical expression
Using the ‘Required’ button, you can define an advanced definition for required parameters by entering a logical expression based on parameter names.
This allows creating an either-or logic, for example, if at least one, but not all parameters are required.
An example of a logical expression forcing the user to enter either the parameter ‘Customer Name’ or ‘Account Number’ would be:
:Customer_Name is not null or :Account_Number is not null
Parameters are referenced by their names (in the installed base language, usually US), prefixed with a colon and having spaces or other non-word characters replaced with a single underscore.
Blitz Report evaluates the logical expression at run-time and displays an error message in case the expression is not met. The default message text displayed is stored in FND message XXEN_REPORT_INSUFFICIENT_PARAM and, for expressions enforcing at least one parameter entry, in message XXEN_REPORT_ONE_PARAM_REQUIRED.
To display a specific error message for your logical expression, enter a message text as required.
For reports that have an advanced logical expression set up for their parameter requirements, the required button label shows ‘Advanced’ instead of ‘Required’. One expression can be set up per report.
Access to individual Blitz Reports for normal users can be controlled using the following levels:
- Site: all users in the system
- Application: users having a matching application (via their responsibilities)
- Operating Unit: users having access to the assignment operating unit (either via Oracle’s MOAC / security profiles or profile ‘MO: Operating Unit’)
- Request Group: users having a responsibility linked to the assignment request group
- Responsibility: users having the assignment responsibility
- User: inclusion or exclusion by specific user
Exclusions take precedence over inclusions. A report included on Site level, but excluded for application ‘Receivables’, for example, would be accessible by all users in the system except from users only having responsibilities linked to the receivables application.
By default, inclusion assignments are implemented as a union, which means that the user can access a report if there is a match on any of the assignment levels.
For assignments on operating unit level in combination with request group or application however, it may be required to limit access to the combination or intersect of assignment level values instead of adding them.
This can be achieved by profile option ‘Blitz Report Restrict Assignments by Operating Unit Level’.
If set to ‘Yes’, users would, in addition to an assignment application or request group, require to have access to the assignment operating unit in order to access a particular report.
|Include||Operating Unit||Vision Australia|
|Include||Request Group||OE Concurrent Programs|
- Profile ‘Blitz Report Restrict Assignments by Operating Unit Level’=No
Users can access a report if they have a responsibility that is either linked to the receivables application or that has access to operating unit ‘Vision Australia’ or that is linked to request group ‘OE Concurrent Programs’.
- Profile ‘Blitz Report Restrict Assignments by Operating Unit Level’=Yes
Users can access a report if they have a responsibility that is either linked to the receivables application or request group ‘OE Concurrent Programs’ and that responsibility has access to operating unit ‘Vision Australia’.
Please note that assignments control report access for normal users only. Users having their access profile set to ‘User Admin’, ‘Developer’ or ‘System’ can access all reports in the system, irrespective of report assignments, and can run them, depending on report type and access profile as described here.
Blitz Report shows by default all reports accessible from all active responsibilities of a particular user, regardless of the current login responsibility. If profile option ‘Blitz Report Filter Reports by Responsibility’ is set to ‘Yes’, only reports assigned to the current login responsibility are shown.
Assignments can also be edited via Tools → Assignments
If you have a large number of reports in your system, category assignments will help users to find the reports they require via the category drop-down list on the execution window.
To create a new category, navigate to the menu Tools → Categories.
3.8 Multi-language support
If you have more than one language installed, Blitz Report offers multi-language support via the Oracle EBS translation menu icon for fields visible to the end users on the execution window. These fields are:
- Report name
- Report description
- Parameter name
- Parameter description
- LOV description
- Column translation
User interface translations
The labels shown on the Blitz Report user interface, e.g. on the execution window can be translated via Application Developer → Application → Lookups → Application Object Library, query Lookup XXEN_REPORT_TRANSLATIONS and enter translations for the lookup code descriptions as required.
3.9 Access control
The access to update and run reports also depends on their type. There are three different types: ‘Standard’, ‘Protected’ and ‘System’, and functionality is available according to following table.
|Functionality \ Access Profile||User||User Admin||Developer||System|
|Create modify or delete categories||no||yes||yes||yes|
|Edit licensing information||no||no||yes||yes|
|Run reports (Standard or Protected)||yes*||yes||yes||yes|
|Run reports (System)||no||no||no||yes|
|Assign reports to users or categories (Standard or Protected)||no||yes||yes||yes|
|Assign reports to users or categories (System)||no||no||no||no|
|Create modify or delete reports (Standard)||no||no||yes||yes|
|Create modify or delete reports (Protected or System)||no||no||no||yes|
Users with access profile set to ‘User Admin’, ‘Developer’ or ‘System’ can access all reports in the system, irrespective of report assignments, and can run them, depending on report type and access level as shown above.
4. Tools menu
Unique name for shared LOVs.
Description for shared LOVs. This description is displayed in the bottom left message area of the execution window, if parameter description is left blank.
Validate From List
If checked, the parameter validation enforces selection of one record from the LOV and does not allow use of wildcards. If unchecked, the parameter value is not validated against the LOV and use of wildcards is allowed.
Filter Before Display
If checked, the LOV prompts the user to enter a (partial) value before LOV display. This is the recommended setting for LOVs containing a large number of records, such as customer names or transaction numbers. For performance reasons, execution of an unrestricted query on all LOV records should be avoided.
However, for better usability of LOVs with a small number of records, it is recommended to leave ‘Filter Before Display’ unchecked. The LOV then displays all values immediately the user clicks on the LOV icon next to the parameter value field.
The ‘Used By’ button shows all reports and parameters referencing the LOV.
SQL query for LOVs, selecting the two mandatory columns ‘value’ and ‘description’.
Define categories to help users find reports, using predefined lists.
4.4 Copy Report
Creates a new copy of an existing report.
The Blitz Report export functionality allows to generate either XML files or SQL scripts for automated load of report definitions, LOVs, categories and other Blitz Report related setup for migration purposes.
Following items can be exported as SQL load scripts:
- Reports from Category
- Reports except Category
- Column Translations
- Dynamic Column Translation Rules
- All Content (Reports, LOVs, Categories, Column Translations)
- Menu Entries
- Profile Option Values
- All Setup (Assignments, Menu Entries, Profile Option Values)
Following items can be exported as XML files:
- Reports from Category
- Reports except Category
To generate an XML file for migration of a single report, choose ‘Report’, report name from the list of values and XML as export file format.
To generate a SQL script for migration of all reports and their related setup, but without user and responsibility specific assignments, choose ‘All Content’ and SQL as export file format.
To generate a SQL script to migrate all Blitz Report function menu entries and request group assignments for the Blitz Report background concurrent program, choose export type ‘Menu Entries’ and SQL file format.
Blitz Report Library
To download XML files from the Blitz Report library, find the desired report and click on the XML icon in the ‘Download’ column.
To export all reports from a specific category, search for this category
and once the category or several categories are selected, an option to download all reports will appear
- If a report already exists, the load script or import from the XML file updates it with the new definition while keeping the previous SQL in the version history.
- Report version numbers are generated automatically in each environment and thus may differ between environments. Reports imported into an environment for the first time start with version number 1.
- Report load scripts and XML files contain current report SQLs versions only. They do not include previous versions SQL history.
- The load of parameters based on shared LOVs requires the referenced LOV to be imported first. If a referenced LOV does not exist, the parameter is loaded with a custom LOV instead.
The Blitz Report ‘Import’ menu option allows import of reports from XML files generated via export or downloaded from the Blitz Report library, or reports developed in other technologies such as BI Publisher, Oracle Discoverer or other PLSQL programs.
During import, reports are assigned to categories automatically, depending on the originating concurrent program’s application module, controlled by lookup XXEN_REPORT_APPS_CATEGORIES.
Reports from application modules listed in the lookup code column are assigned to the category listed in the description column. There are four special lookup codes ALL, ALL_BI_PUBLISHER, ALL_CONCURRENT_PROGRAM, ALL_DISCOVERER_WORKSHEET which can be used to assign all imported reports of a particular type to a specific category.
With XML upload, you can migrate reports from other EBS environments or downloaded from the Blitz Report library.
Please see section 4.5 Export for more details on how to get report XML files from other EBS instances or the Blitz Report library.
The screenshot below shows the XML upload option in the Import window.
Once this option is chosen, a separate browser window is opened asking to provide the path to the XML file on the local file system.
At the same time a new form window is opened notifying that upload is in progress and providing an option to cancel the operation.
After successful upload, the Blitz Report setup window is opened and the uploaded reports are shown.
Blitz Report imports BI Publisher reports of java executable XDODTEXE (XML Publisher Data Template Executable) by importing the report SQL from the corresponding XML data source. If the originating data source contains multiple SQLs, the largest one is imported.
Reports imported from BI Publisher show the original source code in field ‘BIP Code’ and Blitz Report uses this value to identify and execute beforereport triggers from the original XML data source. Executing such triggers before running the report SQL is required for reports, which rely on PLSQL code to populate data into global temporary tables for example.
You can double click on the BIP Code to download and review the XML data source.
BI Publisher reports can also be migrated into Blitz Report via a PLSQL script using the following API:
xxen_api.import_concurrent_program( p_application_short_name in varchar2, p_concurrent_program_name in varchar2, p_include_assignments in varchar2, x_report_id out pls_integer, x_message out varchar2 );
You can use example script mass_import_bi_publisher_reports.sql to import all custom BI Publisher reports with a datasource starting with XX%.
For concurrent programs other than BI Publisher, only report name, description, parameter setup and request group assignments are imported and the report SQL would usually need to be transferred manually. If you run the concurrent request to import, e.g. an Oracle standard report, directly before the import however, the Blitz Report code attempts to retrieve the concurrent program’s SQL from the database SGA. While this doesn’t work 100% and only retrieves the concurrent program’s largest SQL, it facilitates the migration process into Blitz Report.
Note: To retrieve all SQLs of a concurrent program that has been run, you can use report DBA SGA SQL Performance Summary, restricted by module type ‘Concurrent Request’ and the module name of the concurrent program to import.
Select ‘Discoverer Worksheet’ to bring up the LOV of worksheets available for import from the selected end user layer. Columns ‘Execution Count’ and ‘Last Executed’ are populated for recently executed worksheets. The default timeframe to retrieve values for these columns is 90 days, and can be adjusted by profile option ‘Blitz Report Discoverer Import LOV Access History Days’.
During import, Blitz Report derives parameter types from the Discoverer EUL items and creates LOVs automatically for the item classes used by the workbook parameters.
Prior to importing Discoverer worksheets, you may want to analyze which reports are frequently used and by whom. This will allow you to do a cleanup of the Discoverer reports as part of the import.
To analyze the worksheet usage history and content of your current End User Layer, use the Discoverer analysis reports from the Blitz Report library (starting with DIS %) .
Mass import of Discoverer Worksheet SQLs can be done via a SQL script using the following API:
procedure import_discoverer_worksheet( p_workbook_owner_name in varchar2, p_workbook_name in varchar2, p_worksheet_name in varchar2, p_eul in varchar2 default 'eul_us', p_include_assignments in varchar2 default null, x_report_id out pls_integer, x_message out varchar2 );
Example script mass_import_discoverer_worksheets.sql imports all Discoverer Worksheet SQLs which have been accessed within the past 180 days into Blitz Report.
In case you need to rerun the import e.g. in case of errors), you can use script mass_delete_discoverer_reports.sql to remove all imported Discoverer reports again.
Register eul_trigger$post_save_document function
Blitz Report imports Discoverer worksheet SQLs from EBS table AMS_DISCOVERER_SQL. This table is updated with the latest SQL, each time a worksheet is saved in Discoverer Desktop or Discoverer plus. The update is done by a discoverer trigger called eul_trigger$post_save_document, which calls database PLSQL function AMS_DISCOVERER_PVT.EUL_TRIGGER$POST_SAVE_DOCUMENT to perform the update.
If you don’t see any worksheet SQLs in Blitz Report’s Discoverer Worksheet import option, or if you only see very old ones having the execution count and last executed columns blank, or if the EUL you are looking for is not available, then above trigger does not exist or does not work.
To correct the trigger setup, you need to re-register the PL/SQL function with Discoverer Administrator. To load the required setup automatically, download file amsregfuncrfo.eex (right click: Save link as..) and import it using Discoverer Administrator → File → Import, after connecting to the target EUL.
Alternatively, you can import the function from the database by navigating to Tools → Register PL/SQL Functions → Import to show all functions from owner APPS. Then select function APPS.AMS_DISCOVERER_PVT.EUL_TRIGGER$POST_SAVE_DOCUMENT. Note: You can start typing the first characters of the function name ‘apps.ams_dis’ to navigate quicker than scrolling through the complete list.
After Import, modify the display name from upper to lowercase (see Oracle’s Discoverer Administrator Guide).
After import, click the validate button to ensure that the function is working. Ensure that the discoverer EUL user has execution and update/insert permissions on the AMS package and table.
With this PL/SQL function in place, every workbook update triggers an update of the included worksheet SQLs to table AMS_DISCOVERER_SQL and makes them available for import into Blitz Report. To automatically trigger recently used workbooks (and to avoid manually saving them to trigger the update), you can enable parameter ‘SaveLastUsedParamValue’, which saves a workbook each time it is used in Discoverer plus or viewer (see ‘Discoverer server configuration file’ further down).
Windows registry settings
Blitz Report’s Discoverer import uses information from table EUL5_QPP_STATS, which is populated by Discoverer Desktop or Discoverer plus with a history of worksheet query execution statistics. The information is written to the table upon exit from above applications.
To ensure that the statistics are written, a registry setting for Discoverer parameter QPPCreateNewStats needs to be added or adjusted, as described in Oracle’s Discoverer Administrator Guide. Open the Windows registry editor.
Go to the following path (Replace Discoverer 11 with your version):
If the setting for key QPPCreateNewStats already exists, ensure that it’s value is set to ‘1’.
If the key does not exist, create it by using Edit → New → DWORD (32-bit) Value:
Discoverer server configuration file
For Discoverer plus and viewer users, following actions need to be performed which are also outlined in the following Oracle document, section “9.4 How to set default user preferences for all users”. Below instructions will be provided with examples from our own demo Discoverer server installation.
Open pref.txt file under $ORACLE_INSTANCE/config/PreferenceServer/$DISCO_COMP_NAME
Update or add required parameters and save the file:
SaveLastUsedParamValue = 1 QPPCreateNewStats = 1
Run script applypreferences.sh under $ORACLE_INSTANCE/Discoverer/$DISCO_COMP_NAME/util
Restart the Discoverer_[instance_name] ias-component:
$ORACLE_INSTANCE/bin/opmnctl stopproc ias-component=Discoverer_asinst_1 $ORACLE_INSTANCE/bin/opmnctl startproc ias-component=Discoverer_asinst_1 $ORACLE_INSTANCE/bin/opmnctl status
For better import performance, we recommend creation of following index on eul5_documents.doc_name.
create index <eul_schema>.xxeul5_documents_n1 on <eul_schema>.eul5_documents (doc_name) tablespace apps_ts_tx_idx;
4.7 Upload Large SQL
To upload report SQLs larger than Oracle’s Forms limit of 32767 characters, select the ‘Upload Large SQL’ menu entry to open a browser window and select a SQL file for upload. If the SQL file contains non ANSI characters, it must be uploaded in UTF-8 encoding.
A notification window indicates that an upload is in progress and allows to cancel.
After file upload, the notification window closes and the uploaded SQL is shown on the setup screen. Note that SQLs larger than 32767 characters are greyed out and can be modified via the SQL upload functionality only.
While the form displays the first 32767 characters only, a double click on the SQL downloads the full SQL text as a file.
4.8 Column Translations
Column Translations provide multi-language support for SQL column headers and allows specifying number formats for numeric columns. The number of existing translations is shown in column ‘Count’. If you have a report selected before navigating to Tools → Column Translations, the columns are shown for that report only. You can query all existing column translations via Ctrl+F11.
4.9 Dynamic Column Translations
Dynamic column translations allow dynamic header column names based on an individual SQL. This can for example be used to generate reports with column names corresponding to actual date values e.g. Mar-17, Feb-17, Jan-17 etc.
4.10 License Key
Blitz Report Setup → Tools → License Key
Enter license information and review license status.
Double click on the active users count to open a detailed list of active Blitz Report users.
Note: If you are using the free version of Blitz Report without a license key, you can still use Blitz Report’s full functionality for storing and maintaining SQLs, but Blitz Report will generate an output for the 30 most recently created reports only (custom reports take precedence over Enginatics reports).
5. Tips and tricks
If you encounter an error during Blitz Report execution e.g. the message ‘Blitz Report output file creation failed.’, then this is usually due to an error encountered during SQL execution. Review the report concurrent logfile for error messages and try to execute the SQL as shown in the logfile directly in a development tool such as Toad or SQL Developer for further debugging. The logfile also shows the user entered parameter values and corresponding bind variables.
5.2 Run Oracle EBS with Firefox
We recommend using Firefox instead of Internet Explorer to access Oracle EBS, as it has better performance and allows automated download and opening of output files without repeatedly prompting for confirmation.
Since Firefox stopped supporting NPAPI plugins in their latest version however, you either need to have Java Web Start installed, or use the last supported 32bit ESR version 52, which can be downloaded from the Mozilla version history.
After installation, ensure that you have disabled automated updates in Options → Advanced → Update
And check that the Java plugin shows up in Add-ons → Plugins
If the Java does not appear in this list, check that you have the 32 bit version of the Java RE installed and confirm the 32 bit version 52 of Firefox by navigating to Help Menu → Troubleshooting Information and check that the User Agent string contains ‘WOW64’
5.3 Outbound Interface
Integration with Oracle’s concurrent delivery options allows scheduling a Blitz report as an outbound interface or monitoring tool. If the requirement is to transfer incremental data changes only, you can restrict the query to records modified since the previous run of a scheduled request by a parameter SQL like this:
rctla.last_update_date>= (select fcr0.actual_start_date from fnd_concurrent_requests fcr, fnd_concurrent_requests fcr0 where fcr.request_id=fnd_global.conc_request_id and fcr.parent_request_id=fcr0.request_id)
Oracle’s delivery options allow sending the report output e.g. as an email attachment, to an FTP location or uploading it to a WebDAV cloud storage.
If a scheduled report does not retrieve any data, Blitz Report does not send an empty output file. In case you also want to send empty output files for scheduled reports, set profile option ‘Blitz Report Suppress Empty File Delivery’ to ‘No’.
If you want to give your outbound interface report an additional level of protection and allow modifications by users with ‘System’ access profile only, set it’s type to ‘Protected’.
5.4 Data Warehouse
Blitz Report’s output files can also be used as a simple datawarehouse, e.g. by scheduling reports and writing the output files in XLSX or CSV format to a server directory.
The location of these files is controlled by profile option ‘Blitz Report Additional Output Directory’, which can be set for the application or the database server.
Profile option ‘Blitz Report Additional Output Filename Pattern’ defines the ending of the output filename, and allows, for example, to overwrite an existing XLSX file with refreshed data by a scheduled Blitz Report concurrent request.
Setting this profile to a date format string _’DD-Mon-YYYY’ and scheduling a report every 30 minutes, for example, would write one separate file per day which get’s refreshed with current data every 30 minutes.
This generates a time series of data files, which can then be used as data warehouse for analysis by other tools such as Microsoft Power BI, Qlik Sense, Tableau or OBIEE.
5.5 MS Excel and CSV files
When opening a comma-separated CSV file with Microsoft Excel, the data gets parsed and arranged into different columns automatically. For this parser to work, the delimiter used in the output file needs to match the delimiter defined in the client computer’s regional settings.
In some countries, the default regional settings use a semicolon instead of a comma separator. To open output files correctly for these cases, there are three options:
Option1: Set the output format for Blitz Reports to TSV, tab-separated-values. Note: As Microsoft Excel does not associate itself with TSV file extensions upon installation, this requires an additional setup step on client computers to associate these file types to Excel.
Option2: Change the client computer’s regional settings from a semicolon to a comma.
Option3: Set profile option ‘Blitz Report CSV Delimiter’ according to the regional settings.
Column type detection – retain leading zeroes
When opening CSV files, Microsoft Excel automatically detects column datatypes and formats the data accordingly.
If you have alphanumeric data with numeric content, such as serial numbers for example, Excel’s automated type detection would import the data as number format and lose any leading zeroes.
This problem can be avoided by prefixing column data with an equal sign and enclosing it in double quotes to retain the alphanumeric format:
Macro to filter and freeze top row
One of the most common repetitive tasks when dealing with Excel spreadsheets is adding a filter and freezing the top row. For increased productivity, we recommend creating a Visual Basic macro for this task using Excel’s personal macro workbook (see Microsoft’s documentation on how to create a VB macro in PERSONAL.XLSB).
Example Visual Basic code:
Sub filter_and_freeze() ' ' macro to apply a filter and freeze the first line ' ' assign shortcut: ctrl+j ' Selection.AutoFilter With ActiveWindow .SplitColumn = 0 .SplitRow = 1 End With ActiveWindow.FreezePanes = True Rows("1:1").Select Selection.Font.Bold = True Range("A1").Select Dim myColumn As Integer Dim iColumn As Integer Cells.Select With Selection .WrapText = False End With Range("A1").Select myColumn = Cells.SpecialCells(xlCellTypeLastCell).Column Range(Cells(1, 1), Cells(1, myColumn)).EntireColumn.AutoFit For iColumn = 1 To myColumn If Columns(iColumn).ColumnWidth > 40 Then _ Columns(iColumn).ColumnWidth = 40 Next iColumn End Sub
5.6 Disable protected view
By default, MS Excel is configured to open downloaded files in protected view mode. To open files for editing without additional mouse click, we recommend disabling the protected view. Navigate to File → Options → Trust Center → Trust Center Settings → Protected View → untick: Enable Protected View for files originating from the Internet.
6. Profile options
The profile option ‘Blitz Report Access Level’ should be set to ‘Developer’ for users with the SQL skills required for creating Blitz Reports.
The following optional profile options can be set to configure Blitz Report functionality according to your business and user requirements:
|Profile option||Description||Default if not set|
|Blitz Report Access Level||Access level to Blitz Report:
User: run assigned reports only
User Admin: access all reports and assignment setup plus read only report definition
Developer: full access except system reports
System: full access including system reports (see Access control for more information)
|Blitz Report Additional Output Directory APPS Node||Directory path on the applications node to write output files to (in addition to witing them to $APPLCSF/out)|
|Blitz Report Additional Output Directory DB Node||Directory name on the database node to write output files to (in addition to witing them to $APPLCSF/out)|
|Blitz Report Additional Output Filename Pattern||Filename postfix pattern for output files written to a directory on the DB or apps node, if profile ‘Blitz Report Additional Output Directory %’ is set.
Use a date format string in single quotes e.g. ‘DD-MM-YYYY’ or request_id as a keyword.
|Blitz Report CSV File Delimiter||Delimiting character in Blitz Report CSV output files||,|
|Blitz Report Default Email Address||Default email address for Blitz Reports|
|Blitz Report Disable Column Translations||Set to ‘Yes’ to disable automated column header translations. Per default, SQL column headers get translated automatically to strings defined in Tools->Column Header Translations.||No|
|Blitz Report Disable Run Button after Submission||After report submission, the run button is disabled to prevent accidental resubmission of the same report with the same parameters. Switching this profile to ‘No’ keeps the run button enabled all the time.||Yes|
|Blitz Report Discoverer Import LOV Access History Days||Number of access history days for the Discoverer worksheet import LOV||show 90 days of history|
|Blitz Report Discoverer Import Preserve Original SQL||Set to ‘Yes’ to turn off Blitz Report’s additional structural code enhancements when importing and converting Discoverer worksheet SQLs. This will make the import more robust, however the converted SQLs will be less ‘pretty’.||No|
|Blitz Report Email Attachment Size Limit (bytes)||Maximum attachment size for Blitz Reports sent via email. If the output exceeds this limit, the email will be sent without attachment and show a corresponding message in the email.||no limit|
|Blitz Report Email Subject||Message name for Blitz Report outbound emails. Message names must start with XXEN_REPORT_EMAIL%.||XXEN_REPORT_EMAIL_SUBJECT|
|Blitz Report Filter Reports by Responsibility||When set to ‘Yes’, the LOV of Blitz Reports is filtered to records assigned to a user’s current login responsibility only.
When set to ‘No’, all reports available to a user (also from others than the current login responsibility) are displayed.
|‘Yes’ for users with standard user access, otherwise ‘No’|
|Blitz Report From Email Address||Email address that Blitz Report emails are sent from, as some email servers allow sending from certain email accounts only.||Email address from user or employee record|
|Blitz Report Include Parameters in CSV Output||Defines whether parameter values are displayed at the beginning of Blitz Report CSV and TSV output files. Output to XLSX format always includes parameters on a second sheet.||No|
|Blitz Report Include SQL in XLSX Output||When set to ‘No’, the report SQL is not included on the parameter tab of Blitz Report XLSX output files||Yes|
|Blitz Report Log Retention Days Error||Number of days that log data for errored or cancelled report runs is kept. When left blank, no automated purge of log data is done.||no purge of log data|
|Blitz Report Log Retention Days Standard||Number of days that log data for successful standard report runs is kept. When left blank, no automated purge of log data is done.||no purge of log data|
|Blitz Report Log Retention Days System||Number of days that Blitz Report log data for successful system type report runs is kept. When left blank, no automated purge of log data is done.||no purge of log data|
|Blitz Report Output Button Refresh Interval||Blitz Report output button refresh interval in seconds. If set to zero, no automatic refresh is done.||1|
|Blitz Report Output Button Refresh Time Limit||Time limit in seconds within which Blitz Report checks for report completion and opens the output file automatically||no limit|
|Blitz Report Output Format||Blitz Report output file format (CSV, TSV or XLSX)||XLSX|
|Blitz Report Restrict Assignments by Operating Unit Level||If set to ‘Yes’, inclusion assignments on operating unit level work restrictive in combination with assignments on application or request group level, performing an intersect to these levels rather than adding them as a union.||No|
|Blitz Report Rownum Limit||Default limit for maximum number of rows returned by Blitz Report if not set up on individual report level.||no limit for CSV and TSV, 1048575 for XLSX|
|Blitz Report Show LOV When Opening Form||When set to ‘Yes’, the LOV of available reports opens automatically when opening Blitz Report.
Yes: automatically show LOV when opening Blitz Report
not set: show LOV when opening the execution window, but not when opening the setup window
No: don’t show LOV when opening Blitz Report
|show LOV when opening the execution window only|
|Blitz Report Show Tooltip Help||Enable or disable tooltip help display||Yes|
|Blitz Report Start Tab||Start tab when opening the Blitz Report setup window||SQL|
|Blitz Report Start Window||Start window when opening Blitz Report. This affects access levels other than ‘User’ only:
Execution: start with the Blitz Report execution window. Useful for users with development access, but daily tasks requiring mainly to run reports rather than maintaining them
Setup: start with the Blitz Report setup window
|Blitz Report Suppress Empty File Delivery||For scheduled Blitz Reports, file delivery through Oracle standard’s delivery options is suppressed if the report is empty. If set to ‘No’, also empty files are send for scheduled Blitz Reports||Yes|
|Blitz Report Time Limit||Default maximum execution time limit in seconds||no limit|
|Blitz Report XLSX Column Header Color||Hexadecimal code e.g. EAEFF5 for the column header background color in Blitz Report excel spreadsheets.||EAEFF5|
|Blitz Report XLSX Column Width Mode||Defines if the column width in Blitz Reports XLSX output files gets aligned to the column headers, the data or both.||both|
|Blitz Report XLSX Date Format||Date format mask for Blitz Report XLSX output files. If not set, profile ‘ICX: Date format mask’ is used.||value from profile ‘ICX: Date format mask’|
|Blitz Report XLSX Font||Blitz Report XLSX output file font||Calibri|
|Blitz Report XLSX Font Size||Blitz Report XLSX output file font size||10|
|Blitz Report XLSX Max Column Width||Maximum column width for Blitz Report XLSX output files||60|
|Blitz Report XLSX Min Column Width||Minimum column width for Blitz Report XLSX output files||2|
|Blitz Report XLSX Number Format||Numeric column format string for Blitz Report XLSX output files||General|
|Blitz Report XLSX Use Long Date Format||When set to ‘Yes’, dates in Blitz Report output XLSX files show the full year and leading zero for single digit days e.g. 05-Jan-2018 as opposed to 5-Jan-18||No|
|LOV||List of Values|