Blitz Report™ User Guide

Introduction

Blitz Report is an Oracle Forms based software, fully integrated with Oracle E-Business Suite. It enables your IT 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.

With Blitz Report, we created the most efficient and easy to use operational reporting solution for Oracle EBS. Optimized for skilled IT professionals to better organize and maintain their reporting queries, and for business users to quickly access EBS data in a format they love without having to learn new skills.

We hope that you will enjoy working with Blitz Report as much as we do, and we welcome your feedback to [email protected].

1. Running Blitz Report

Basic steps to run a Blitz Report are:

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

1.1 Selecting a report

When opening Blitz Report for the first time, a selection of available reports is displayed automatically. The list can be restricted further by typing the first characters of the desired report name.

When subsequently opening Blitz Report, the last report executed will default in the report name field. If you open the reports LOV, all of your “most frequently used” reports will be listed at the top of the list. You can still scroll down, or use the following selection options for finding and selecting reports:

• Perform a text search by typing into the report name field. This executes a full text search on report name and description, parameters, SQL definition, template name and description, 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.
• Double click into the report name field
• Use the LOV attached to the report name
• Press the F1-key
• Pick a Category from the dropdown list
• Use the find/torch menu symbol
• Run 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, but 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”.

1.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 User Parameter Defaults menu option.

Once a template is selected it is also possible to store default parameter values against a template using the Actions > Save Template Parameter Defaults menu option.

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

1.4 Options

The Options window is accessed by clicking on the grey options field. It allows controlling the report generation process and the output format at run time, for example email delivery and output formats. Some options are available only for developers. The runtime options inherit their default values from the Blitz Report Setup Window options with the same names.

User view:

Developer view:

Email

Enter an e-mail address that you wish to send the report output to. Multiple email addresses can be entered as a comma-separated list. Developers can define a default email address for individual reports, or you can set up a default email throught profile option Blitz Report Default Email Address.

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

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

Custom Postprocess

A custom post-processing shell script can be set up to run after report completion, for example to distribute or transform the output file to a different format, such as generating a report in PDF format.

The script must be placed under the $CUSTOM_TOP/bin/custom/ directory, for example $XXEN_TOP/bin/custom/generate_and_send_pdf.sh, and it allows the use of variables as shown in the following example:

# You can use a custom postprocess script to perform additional actions on the Blitz Report output files, e.g. scp them to a different server, transform them to .pdf via external tools etc.
# This script is called with following parameters:
# $FCP_LOGIN                                      example: APPS/w#*fe=+~12fweDg
# $p_report_name                                  example: AP Suppliers
# $p_template_name                                example: Pivot by supplier type
# $p_request_id                                   example: 6590137
# $outfile_name                                   example: /d01/oracle/VIS/inst/apps/VIS_r12/logs/appl/conc/out/AP_Suppliers_6590142.xlsx
# $output_filename                                example: AP_Suppliers_6590142.xlsx
# $additional_outfile_name                        example: /d01/oracle/VIS/inst/apps/VIS_r12/logs/appl/conc/out/AP_Suppliers_20191117.xlsx
# $parameter1
# $parameter2
# .......
# $parameter87
#
# Example of copying output xlsx file to an interface directory on a remote server:
# scp "$outfile_name" [email protected]:/interface_dir

To add a new custom postprocess script name to the runtime options LOV, add it to lookup XXEN_REPORT_POSTPROCESS_SCRIPT as shown in the following screenshot:

Output Filename

Defines the Excel output file name, downloaded to the client desktop. Tokens can be used to create a filename dynamically, for example based on report parameter values.

Additional Out. Directory on APPS Server (Developer access only)

Saves a copy of the report output file in the specified directory on the application server. This setting can be defaulted by the profile option “Blitz Report Additional Output Directory APPS Server”. Tokens can be used to create a directory path dynamically, for example based on report parameter values. If the resulting directory does not exist on the filesystem, it is created. Please ensure that the apps owner has the required permissions for the directory creation.

Additional Out. Directory on DB Server (Developer access only)

Saves a copy of the report output file in the specified directory on the database server. This setting can be defaulted by profile option “Blitz Report Additional Output Directory DB Server”. The list of the available directories is fetched from the dba_directories table.

Additional Out. Filename (Developer access only)

Naming convention for output files if additional output directories are defined. Tokens can be used to create a filename dynamically, for example based on report parameter values.

Reset button

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

Freeze flag

When checking the freeze flag, option values are not changed or reset to their defaults when navigating to a different report.

Tokens

You can use any of the below tokens to dynamically generate values for the following runtime options: Output Filename, Additional Out. Directory on APPS Server, Additional Out. Filename.

<report name> – report name
<report_name> – report name, all spaces are replaced to underscores.
<template name> – template name
<template_name> – template name, all spaces are replaced to underscores.
<request_id> – concurrent request id
<parameter name> – value of the specified parameter name
<parameter_name> – value of the specified parameter name, all spaces are replaced to underscores.
<date format string> –  actual run date in the specified Oracle date format string

Here is the example of a filename using all of the above tokens, replacing spaces to underscores.

<report_name>–<template_name>–<request_id>-for-<Operating_Unit>-on-<DD-MM-YYYY>

The resulting filename is:

AP_Suppliers-Operating_unit_only-6317575-for-Vision_Operations-on-06-10-2022.xlsx

Here is the example of the similar filename but without replacing spaces to underscores:

<report name>–<template name>–<request_id> for <Operating Unit> on <DD-MM-YYYY>

The resulting filename is:

AP Suppliers-Operating unit only-6317575 for Vision Operations on 06-10-2022.xlsx

And here is an example of a directory using tokens:

/interface/<report_name>_<DD-MM-YYYY>

It is possible to enter tokens manually or to choose them from the LOVs associated with the runtime option fields.

Manually entered tokens are validated against the LOV values, and an error message is displayed in case a token is not valid.

1.5 Templates

From the options window, you can either select an existing template to edit, or, if the template name is empty, create a new template by clicking on the ‘New’ button.

Template layout

A template allows users to select the data columns, the level of reporting (detail transactional or summarized), aggregation calculations, and to define a pivot table output. Using templates, report columns can be selected and/or de-selected, summarized, and sorted as well as pivoted.

The template window contains the following elements:

Name of the template

Description of the template

Global Default sets the template as the default for all report users (available for developers only)

Distinct suppresses duplicate rows in the report

Owner shows who created and owns the template

Available Columns – list of columns that are available for display, but are not selected yet

Displayed Columns – list of columns that are selected and included in current report output

Column search can be used to quickly find specific available or displayed columns

Aggr. is used for summary reports and specifies how to aggregate the data (the choices are average, count, min, max, sum)

Sort Order determines the order of the output data (ascending / descending)

Sort Priority determines the sort priority if more than one sort order is selected

Sheet Break allows splitting the output file into different sheets for every column value change

Arrow buttons move columns between “available” and “displayed”, as well as changing their display order. You can use the Ctrl and Shift keys to select groups of columns (multi-select) and move them simultaneously to and from either column. You can use double-click to move data from one column to the other.

Reset button to recover the initial column layout

Hide All button to deselect and move all data off of the “selected” columns and back to the “available” column

Pivot table

The Filters, Columns, Rows and Values fields are used to create and deliver the data in pivot table format with full drill down to details. The data must be selected to print on the report in order to be used in the Pivot table. The pivot table section of the template screen is divided into 4 sections:

Filters – These are the columns you will use to filter the data. Filters are things like GL account, Department, Subaccount, Customer, Vendor, Project or Product, to name a few popular choices.

Columns – This will define which data should appear in separate columns. Period, Project, Subaccount, to name a few popular choices.

Rows – This will define the data summary level for each row in the pivot table.

Values – This will define the numeric data that will be summarized to provide the totals for the level defined in the Row columns.

Aggr. – For the information selected in the “values” section, how to aggregate the data (the choices are average, count, min, max, sum)

Sample pivot table output:

Excel upload

The Excel template upload functionality allows creation of additional sheets with graphs, pie charts, macros or any other Excel functionality. To use this feature, execute the following steps:

• Run a report and open the output
• Create sheets with additional pivots, graphs and pie charts or include macros

• Upload the saved Excel file including additional sheets or macros to the template

• Run the report again with parameters as required and the output is generated with additional sheets and refreshed with new data

Datasheet

The excel template upload functionality also allows custom template definitions to the datasheet. The excel functions, pictures, screenshots, charts, header footer texts, comments, notes and freeze panes can be defined in the datasheet. To use this feature, execute the following steps :

• Run a report and open the output
• Modify the datasheet based on the required template. Examples provided below
  • Datasheet template having
    1. Pictures like company logo,
    2. Modified cell format like font, cell fill color, etc,
    3. Excel functions like Subtotal, Sum, SumIf, etc,
    4. Additional rows added above the data column header,
    5. Freeze pane applied

• • Datasheet template having
    1. Line chart,
    2. Pictures like company logo,
    3. Modified cell format like font, cell fill color, etc,
    4. Excel functions like Subtotal, Sum, SumIf, etc,
    5. Additional rows added above the data column header,
    6. Freeze pane applied