Blitz Report™ User Guide

1. Introduction

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 [email protected].

2. Running Blitz Report

Basic steps to run a Blitz Report are:

1. Select a category (optional)
2. Select a report
3. Enter parameters
4. Run the report and review the output

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’.

To search for an exact match in the order of keywords e.g. ‘item’ directly followed by ‘invoice’, enclose them into quotation marks such as “item invoice”.

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, check 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 exports data in user-friendly XLSX format as a default. Use profile option ‘Blitz Report Output Format’, if you have the requirement to export in delimited text formats CSV or TSV.

2.5 Options

The runtime options window using to control the report generation process and results format in run time. Using the runtime options window it is possible to define:

Template

Columns and their order in generated report will be displayed based on selected template. Edit button is used to create new and modify existing templates, more details can be found here.

Email

Indicate e-mail address if you want automatically send the report after it generation. For more details check here.

Output Format

Output file format. Possible options: CSV, TSV, XLSX

Row Limit

You can restrict the number of row in the output file by indicating that option.

Time Limit

Indicates timeout in seconds. Program is terminated by the ‘Blitz Report Monitor’ after indicated time. This avoids excessive server loads if e.g. user submits report with insufficient parameter restrictions.A time limit can also be set when running reports or using profile option ‘Blitz Report Time Limit’. If there are values set on different levels, the order of precedence is as follows:

1. Run window options time limit
2. Profile option on user level
3. Time limit on report level
4. Profile option on responsibility level
5. Profile option on site level

Disable Column Translations

Disabling translation of the report’s header if multi-language setup is in place. E.g.: interface requirements.

Exclude Column Headers

Removing header column from the output file. E.g. for interface requirements

Additional Out. Directory on APPS Node

The copy of the output file will be saved in the indicated directory on APPS node. Also, that directory is used by “Data Warehouse”overriding value from profile option “Blitz Report Additional Output Directory APPS Node”.

Additional Out. Directory on DB Node

The copy of the output file will be saved in the indicated directory on DB node. Also, that directory is used by “Data Warehouse”overriding value from profile option “Blitz Report Additional Output Directory DB Node”.

Additional Out. Filename Pattern

Suffix of the filename for the copies defined in 8 and 9. Also, that file pattern is used by “Data Warehouse”overriding value from profile option “Blitz Report Additional Output Filename Pattern”.

Custom Postprocess

The name of the post-processing shell script file to be executed after report generation. Script must be placed under $CUSTOM_TOP/bin/custom/ directory.
E.g. post-processor script can be used for generating report in PDF format.
The output of the script is placed on the server to the dedicated directory.

“Freeze” checkbox

Option values are not changed if new report is selected.

“Reset” button

This button is used to set runtime options to the default values based on the profile option or report level.

2.6 Email

Email address is a part of report options. 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.

The default email on the Blitz Report run window is derived in following order:

1. Default email setup on report level
2. Profile option ‘Blitz Report Default Email Address’
3. FND user’s email
4. HR person’s email

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:

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.7 Column template

The display of columns in the output Excel document can be controlled using а column selection window. Selected columns will be displayed in the resulting report document. You can select or reorder columns

The window contains the following elements:

1. Name of the column template
2. Description of the column template
3. Available Columns – list of columns that are available for display, but are not displayed yet
4. Displayed Columns – list of columns that are displayed.
5. Seq – sequence number of the column. Determine order of the columns in the output file.
6. Arrow buttons for moving columns between lists 1 and 2, as well as changing their display sequence in list 2 (Displayed columns). You can use Ctrl and Shift keyboard buttons to select groups of columns (multi-select) and move them simultaneously. Use double-click to move column
7. “Reset” button to recover initial state of the lists and “Hide All” button to move all the columns to list 1 (Available columns)
8. “Public” checkbox – determines availability of the template. Public templates can be selected, but cannot be modified by other users

2.8 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:

1. Run a report from the Blitz Report run window with parameter restrictions as required
2. Navigate to the menu > view > requests > submit a new request > single request > Copy > Find > select the concurrent request submitted in step 1
3. 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 run 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 (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.

2.9 Column/parameter translation

BlitzReport introduced functionality that allows user translate report header column names and parameter names in one form.

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’ must be set to either ‘Developer’ or ‘System’. With this profile setting, the Blitz Report run window shows an additional ‘Setup’ button, which opens the setup window as shown below.Basic steps to create a new report are:

1. Enter a report name and an optional description
2. Enter the main extraction SQL
3. Define optional parameters and corresponding WHERE clause / SQL text
4. Test the report by clicking the ‘Run’ button
5. Set up the user access rights

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:

n=n

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

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
• HINTS
• 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.

Also, ‘&lexical’ anchor can be used with  placeholder. In case you require the parameter value to show up as lexical text in the SQL. An example below shows a practical use of placeholder with ‘&lexical’.

:bind

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.

Examples

where
1=1

fu.user_name=:user_name

where
fu.user_name=:user_name and
1=1

where
1=1

furg.user_id in (select fu.user_id from fnd_user fu where fu.user_name=:user_name)

where
furg.user_id in (select fu.user_id from fnd_user fu where fu.user_name=:user_name) and
1=1

where
&account
hp.party_id=hca.party_id

hca.account_number=:account and

where
hca.account_number=:account and
hp.party_id=hca.party_id

group by
&group_by_vendor
pha.currency_code

pv.vendor_id,

group by
pv.vendor_id,
pha.currency_code

select
&columns
frv.responsibility_name

fu.user_name,
fu.email_address,

select
fu.user_name,
fu.email_address,
frv.responsibility_name

select 
xxen_util.dis_user_type(eap.ap_eu_id,'&eul') user_type, 
xxen_util.dis_user(eap.ap_eu_id,'&eul') username, 
eap.* 
from 
&eul.eul5_access_privs eap

select 
xxen_util.dis_user_type(eap.ap_eu_id,'eul_us') user_type, 
xxen_util.dis_user(eap.ap_eu_id,'eul_us') username, 
eap.* 
from 
eul_us.eul5_access_privs eap

select
&flexfield_columns
from
mtl_system_items_vl msiv

select
'msiv.'||lower(fdfcuv.application_column_name)||' "'||fdfcuv.form_left_prompt||'",' column_text
from
fnd_descr_flex_col_usage_vl fdfcuv
where
fdfcuv.application_id=401 and
fdfcuv.descriptive_flexfield_name='MTL_SYSTEM_ITEMS' and
fdfcuv.enabled_flag='Y' and
fdfcuv.display_flag='Y' and
fdfcuv.descriptive_flex_context_code='Global Data Elements'
order by
fdfcuv.column_seq_num

select
msiv.attribute1 "Late Demands Penalty",
msiv.attribute15 "Invoice UOM",
msiv.attribute14 "Graphical Link for Web Reqs",
msiv.attribute2 "Material Over-Capacity Penalty",
from mtl_system_items_vl msiv

where
fu.user_name=:user_name

where
fu.user_name=:user_name

3.3 Dynamic SQL example

n=n anchor 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.

A restriction to customer name would require addition of a WHERE clause:

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.

Pivot table in SQL

There is possibility to have dynamic pivot table described in SQL. It is done using &lexical parameter reference.

For example you need to list your balances grouped by ledger and code combination.

We want to introduce additional restriction on a ledger and build pivot table in the output based on the input list of the periods.

Ledger name restriction will be placed after WHERE clause in the place of “1=1” anchor if parameter value is provided.

List of periods columns inserted in SQL statement during execution in the place of lexical &gl_period_pivot reference.

Below you can see how parameters for pivot basis report are described.

In example below lexical reference returns the list of periods according to listed below rules:

• Period year is derived based on value indicated in Period parameter.
• List contains periods of derived year starting from the first till the one indicated in Period parameter.

All period names are enclosed with single quotes and separated by commas.

SQL code during execution looks like that:

3.4 Report header

Name

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.

Description

An optional report description of maximum 4000 characters may be set up to assist users in understanding and using the report.

Search

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.

Category

Retrieve reports by category.

Enabled

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.

Version

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.

Type

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.

BIP Code

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.

Number Format

Format for numeric value display in Excel output files. By setting the number format, you can, for example, change the number of decimals or the display style and color of negative numbers.

The number format can either be set by a profile option, for all columns in a report, or for individual columns as described in the column translations section.