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 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 defining column or pivot templates, email delivery and output formats.

Template

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.

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

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

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.

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

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

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 the examples table below.

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

An explanation of how to use Microsoft’s custom number format codes can be found in this blog or on the Microsoft website.

Report Options

Using Report Options it possible to define default processing options for the selected report:

1. The report from which this was copied
2. Before and after report triggers which can be used for data pre- and post processing, e.g. for table data update before executing report. Can be used the same way as in BI Publisher
3. Email, which will be used to contact reports’ author
4. Email that will be used to sent report after its completion
5. Restrictions on the number of rows in the resulting document
6. Time limit in minutes after which the report is terminated by the ‘Blitz Report Monitor’ program

Author email

Email address of the report author.

Additional information can be found on registered authors in our online library.

Default email

Default email address for sending output files. If different email address are set up on different levels, 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

Output Format

Allows changing the output format from Excel XLSX (Excel) to CSV (comma separated values) or TSV (tab separated values).

Row Limit

Limits the maximum number of lines for report execution.

Time Limit

Maximum run 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 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

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.

SQL

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.

‘Blitz Report Information’ descriptive flexfield

New ‘Blitz Report Information’ descriptive flexfield allows to store additional information, e.g. for change management

3.5 Parameters

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

Display Sequence

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. Example: To achive better performance, report FND Concurrent Requests uses different WHERE clauses for for parameters Phase and Status, depending on the parameter value entered by the user (Matching Value).

Negative display sequence numbers are used to define hidden parameters. These can be used to populate a &lexical with a SQL text dyamically before report execution. Report AR Transactions and Lines, for example, uses a hidden Ledger parameter to dynamically generate a string for the revenue account columns, depending on the chart of accounts segment setup.

Parameter Name

Parameter identifier. You can use the LOV to copy existing parameter definitions from other reports.

If you need a different SQL text in different SQL positions for one parameter, you can have more than one entries for the same parameter name, but only one of them can have a display sequence, parameter type and list of values setup (example: FND Concurrent Requests).

SQL Text

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.

Multiple Values

If the SQL text includes a bind variable restriction and the user checks the multiple values checkbox, Blitz Report automatically replaces the restriction with an IN-clause during SQL execution. This replacement works for restrictions using ‘equal’, ‘like’, ‘not equal’ or ‘not like’ operators such as:

You can also use functions, for example:

If the parameter SQL text is too complex or there is a different operator used e.g. column>=:bind_variable, automated IN-clause replacement is not possible and the multiple values checkbox is protected from updates for this parameter.

Anchor

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.

Parameter Type

The parameter type definition controls validation of parameter values at run-time and there are the following types:

• Char: free text / no validation
• Date: a valid date
• DateTime: a valid date including timestamp
• Number: a valid number
• LOV: If you select this type, the form prompts to pick one of the already existing stored Blitz Report list of values. After selecting the LOV name, you can double click on the LOV Query field to review or modify the SQL query of the LOV (see Tools > LOVs). Note that changes to the LOV affect all parameters referencing it. If you want to modify the LOV for the current parameter only without affecting other parameter validations, switch the parameter type to LOV Custom before modifying the query.
• LOV custom is used to create an ad hoc LOV based on an SQL statement for validation of the current report parameter only. After selecting this type, double click on the LOV Query field to enter the SQL query for validation. The LOV SQL must select the two columns ‘value’ and ‘description’, where ‘value’ is used to bind the parameter restriction for report execution. If you want to re-use the query for other report parameters, click on the ‘Save as shared LOV’ button to create a shared LOV.
• LOV Oracle allows selecting Oracle standard value sets for parameter validation. Oracle standard LOVs use an ‘id’ column to bind parameter restrictions and compared with the two Blitz Report specific types, LOV and LOV custom, they have the limitation that they do not allow use of multiple parameter values (see Parameters > Multiple Values).

LOV Name

Name of a shared LOV or Oracle standard value set.

LOV Query

SQL statement of a list of values. Double click in this field to open the LOV definition window. The LOV query must select the columns ‘value’ and ‘description’, and the value returned from the ‘value’ column is used as the parameter’s :bind value for report execution.

Checkboxes ‘Validate From List’ and ‘Filter Before Display’ control the style of the LOV, see Tools > LOVs.

Matching Value

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.

Default Value

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:

Description

Additional parameter description displayed in the bottom left message area of the Blitz Report run window.

Required

The required flag enforces a parameter value entry by the user, for example to prevent accidental report submission with insufficient parameter restrictions.
Validate_From_List

Advanced Required Parameters

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:

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.

Dependent Parameters

Similar to Oracle standard’s dependent parameter functionality, you can define parameter dependencies in LOV queries and default values using the syntax:

where parameter_or_lov_name is a reference to either the parameter name in US language or to the LOV name of the parameter, which the query depends on. The match to parameter or LOV name is case insensitive and spaces or other non word characters are replaced with an underscore as in the following example.

In case you want to use multiple values functionality for a parameter which the query depends on, xxen_util.contains function can help. In the following example AP Supplier LOV will return values depending on multiple values in the Operating Unit parameter.

Dynamic parameter SQL text

Blitz Report provides a possibility to create parameters with dynamic SQL text which depends on a runtime value of a parameter.
In the example below GL Balance Detail (pivot) report contains ‘Show Full Year’ parameter. This parameter produces a list of columns for the resulting SQL statement dynamically depending on its own value and a valuve of the ‘Period’ parameter. Each column represents a month in a year period.

3.6 Assignments

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
• Form: make report available from an Oracle standard form through the zoom functionality

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.

Example:

• 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

Form assignment

The forms assignment feature allows opening Blitz Reports directly from any Oracle EBS standard form using the zoom functionality.

To integrate a Blitz Report to a form, first identify the standard form name through the top menu > Help > About Oracle Applications > Current Form > Form Name, as shown in the following example for the GL Enter Journals standard form.

Navigate to the assignment setup of the Blitz Report that you would like to integrate, and select the form name from the list of values. If the Blitz Report should be accessible from a specific navigation block of the standard form only, or if you need to pass parameter values from different item names, depending on the current navigation block, enter the name of that block in the Block Restriction field. If the Blitz Report should be available on all blocks of the assigned form and the passed parameter values are identical, leave the Block Restriction empty.

You can pass default parameter values from the assigned form to the Blitz Report. In this example, the GL Batch and Journal names are passed to the GL Account Analysis report to allow direct drilldown from journals to subledger transactions.

The Blitz Report can then be accessed from the Oracle standard form through the zoom icon, with parameters defaulted as defined.

3.7 Categories

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 run window.

To create a new category, navigate to the menu Tools > Categories.

3.8 Multi-language support

Report data

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 run window. These fields are:

• Report name
• Report description
• Parameter name
• Parameter description
• Category
• LOV description
• Column translation

The below screenshot shows an example of setting a parameter translation

User messages

To add translations for user-facing messages, navigate to Application Developer > Application > Messages > query messages starting with XXEN and add translations for a different language as required.

User interface translations

The labels shown on the Blitz Report user interface, e.g. on the run 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 Security and user profiles

Blitz Report provides the following levels of security:

• Assignments control which business users have access to which reports
• The ‘Blitz Report Access’ profile option controls which users have access to the report administration / SQL setup user interface
• Access to data within reports is secured through restricted LOVs (recommended) or by using Oracle’s secured views and synonyms
• Access to sensitive data, e.g. to prevent developers from accessing HR data, can be restricted through additional VPD policies

The ‘Blitz Report Access’ profile option is used to control access to Blitz Report functionality, and to distinguish business users with limited access from developers with full access to create new reports and update existing ones.

Developers typically have their access profile option set ‘Developer’ on user level, which allows them to access all reports and the setup window from all login responsibilities. The access to modify reports also depends on the type of report. If you have a large development team and certain reports require an additional level of protection, such as important outbound interfaces, you can set the type of these reports to ‘Protected’ to allow only developers with the highest access profile setting ‘System’ to modify them.

Access to the three different report types ‘Standard’, ‘Protected’ and ‘System’ and Blitz Report functionality is available according to the following table.

* Users can see and run reports restricted to their assignments only.

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 profile as shown above.

3.10 Data access security

For increased flexibility and maintainability, we recommend using “_all” tables in report SQL queries, for example ap_invoices_all, instead of Oracle’s VPD secured synonyms, such as ap_invoices. Security is then applied by adding a required Operating Unit parameter in Blitz Report with an LOV that contains the allowed Operating Units only. This approach allows greater flexibility, e.g. to enable certain users, such as in shared service centers, to see all data in the system, or to test SQL queries through database access tools, without having the application user session context initialized.

Example:

• Report that selects AP invoice information from the not secured base table ap_invoices_all.

• “Operating Unit” is introduced as a required parameter. Records of the query are filtered based on the parameter value.

• When submitting a report, the “Operating Unit” LOV is limited to organizations available in the current login user responsibility only.

3.11 Securing sensitive information with Oracle Virtual Private Database

With Blitz Report, you can use Oracle Virtual Private Database (VPD) to control access to sensitive data. VPD policies are set up on database objects to automatically add restrictions before SQL execution, thus preventing visibility of sensitive information. This can be used for example to prevent users and developers from querying sensitive HR data, such as payroll information when running queries through Blitz Report.

Database policies can be set up either on row or on column level and Blitz Report includes the following objects to maintain these:

• Lookup XXEN_REPORT_VPD_POLICY_TABLES to define the tables or columns to be secured
• Concurrent program Blitz Report Update VPD Policies to create or update VPD policies for the tables and columns defined in the lookup
• Concurrent program Blitz Report Remove VPD Policies to completely remove all Blitz Report VPD policies
• Database package XXEN_VPD containing the policy function code
• Profile option Blitz Report VPD Policy Rule to control data access

Perform following steps to secure your data. Note: Using this method secures data access through the Blitz Report concurrent program only, not through other access methods such as direct queries through database access tools or Oracle standard EBS processes.

1. Set up tables or column names in lookup XXEN_REPORT_VPD_POLICY_TABLES

Application Developer > Application > Lookups > Application Object Library: Query lookup type XXEN_REPORT_VPD_POLICY_TABLES and enter one lookup value for each table or table column that need to be secured. Choose a unique lookup code and enter the owner, table and optional column name as the lookup meaning.

2. Run concurrent program ‘Blitz Report Update VPD Policies’

System Administrator > Concurrent > Requests: Run concrrent program ‘Blitz Report Update VPD Policies’. This program first removes all possibly existing Blitz Report VPD policies, creates the policy function package XXEN_VPD and then creates database policies for all tables and columns referenced in lookup XXEN_REPORT_VPD_POLICY_TABLES.

3. Optionally set profile option ‘Blitz Report VPD Policy Rule’

System Administrator > Profile > System: Set profile option ‘Blitz Report VPD Policy Rule’ to ‘Full access’ for responsibilities or users who should have access to run Blitz reports on secured data.

4. Tools menu

4.1 LOVs

Name

Unique name for shared LOVs.

Description

Description for shared LOVs. This description is displayed in the bottom left message area of the run 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

The Filter Before Display setting is used to avoid performance issues for large LOVs. If unchecked, which is the more user-friendly default, Blitz Report queries all possible parameter values from the LOV in the background when selecting a report on the run window. As this can be slow for large LOVs, checking the ‘Filter Before Display’ setting prompts the user to enter a (partial) value before LOV display, and the form then queries a restricted dataset instead of all records.

Used By

The ‘Used By’ button shows all reports and parameters referencing the LOV.

LOV Query

SQL query for LOVs, selecting the two mandatory columns ‘value’ and ‘description’.

4.2 Tools > Assignments

The Assignment function in the Tools menu allows to mass-assign different reports to one assignment level, or to review existing assingments through the assignment value LOV.

Defines access to reports for users on a particular assignment level.

4.3 Tools > Categories

Define categories to help users find reports, using predefined lists.

4.4 Copy Report

Creates a new copy of an existing report. This functionality should be used if user want to do any changes to the existing Blitz report.

Seeded reports should not be modified as all updates will be removed as soon as new version of Blitz Report will be installed.

4.5 Export

The Blitz Report export functionality allows to generate 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:

• Report
• Reports from Category
• Reports except Category
• Reports from Application
• Reports matching a search pattern with the use of wildcards
• LOVs
• Categories
• Column Translations
• Dynamic Column Translation Rules
• All Content (Reports, LOVs, Categories, Column Translations, Assignments)
• Assignments
• Menu Entries
• Profile Option Values

Note: For security reason, following items can only be exported and re-imported as SQL scripts:

• Menu Entries
• Profile Option Values

When choosing one of the first four Report export options, you can use the checkboxes to decide if you want to include LOVs, Categories or Assignments of the exported reports in the XML file.

Examples:

To generate an XML file for migration of a single report, choose ‘Report’ and select the report name from the list of values.
To migrate the complete Blitz Report metadata from one EBS system to another, choose the ‘All Content’ export option, which will include all reports and their related setup such as LOVs, categories, column translations and assignments in the exported XML file.
To generate a SQL script to migrate all currently set up Blitz Report related profile option values to a different environment, choose the ‘Profile Option Values’ export type.
To generate an XML file for migration of reports matching a search patter choose ‘Report by Search pattern’ and provide a search expression.

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

Notes:

Export API

To export Blitz Reports programmatically, e.g. for scripted report migrations, you can use the following function in package XXEN_API:

To export a single report, you can use the following function in package XXEN_API:

When called from linux, you can use the dbms_xslprocessor.clob2file procedure to automatically write Blitz Report XML files to the database filesystem, as done in the following example, exporting all reports containing the word “test” in their name:

4.6 Import

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, Enterprise Command Center or other third party tools.

During import, reports are assigned to categories automatically, depending on the originating concurrent program’s application module. This automated category assignment is defined by lookup XXEN_REPORT_APPLICATIONS.

Reports from application modules listed in the lookup code column are assigned to the category listed in the description column. There are two special lookup codes BI_PUBLISHER and CONCURRENT_PROGRAM which can be used to assign all imported reports of a particular type to a specific category.

XML Upload

With XML upload, you can migrate reports exported 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.

Note: To avoid incompatabilities due to possible file format changes between different Blitz Report versions, please ensure that source and destination environment have the same or latest Bitz Report version installed.

Import API

To import Blitz Report XML files programmatically, e.g. for scripted non-interactive deployments, you can use the following import_xml command from Linux:

Or to import an XML as a clob through PLSQL, you can use the following function in package XXEN_API:

The function returns null for success or an error message in case of failure. Note that it does not include a commit, which needs to be executed from the calling code.

BI Publisher

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.

Import API

BI Publisher reports can be migrated programmatically into Blitz Report through the following PLSQL procedure:

You can use the example script mass_import_bi_publisher_reports.sql to import all custom BI Publisher reports with a datasource starting with XX%.

Concurrent Program

This option allows import of parameter definitions, LOVs and request group assignments of any concurrent program, and can be used to help migrating other reporting technologies, such as Oracle Reports .rdf files or custom report programs into Blitz Report.

Note that the report SQL can usually not be imported automatically for such technologies, and would 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 memory (SGA). While this might not work 100% reliably as it only retrieves the program’s largest SQL, it facilitates the migration process to Blitz Report. You can also use report DBA SGA SQL Performance Summary restricted by module type ‘Concurrent Request’ and the module name of the concurrent program to identify the SQL statements executed by the program to import.

Discoverer Worksheet

Select ‘Discoverer Worksheet’ to import worksheets available from the selected end user layer. By default, the LOV shows worksheets that ran within the History Days timeframe only. To select all worksheets, including ones that were not executed in the past, clear our the value from the History Days field.

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 %) .

Import API

Mass import of Discoverer Worksheet SQLs can be done via a SQL script using the following PLSQL procedure:

The 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, for example with a different setting for custom view expansion, you can use script mass_delete_discoverer_reports.sql to remove all imported Discoverer reports before re-importing them.

Discoverer Folders

The ‘Discoverer Folders’ import option allows consolidation of different workbooks during migration to Blitz Report by importing distinct folder or view object combinations only. If you have many different workbooks accessing the same views or folders, this import option can significantly decrease the number of Blitz Reports to migrate and simplify subsequent report maintenance.

The LOV of reports to import shows one record for each distinct folder combination, and the number of different workbooks and sheets using these folders. During import, the different workbooks and their selected columns are converted to Blitz Report templates.

Import API

Mass import of Discoverer folders can be done via a SQL script using the following PLSQL procedure:

The example script mass_import_discoverer_folders.sql imports all Discoverer folder combinations of Worksheets 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.

Discoverer Import Prerequisites

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. Login to Discoverer Administrator, connecting to the database end user layer, and navigate to Tools > Register PL/SQL Functions > Import, then show all functions from owner APPS. Note that the function LOV for the APPS user is quite large and it took about 4 minutes to bring it up on our PCs. Then select function APPS.AMS_DISCOVERER_PVT.EUL_TRIGGER$POST_SAVE_DOCUMENT.

Note #1: You can start typing the first characters of the function name ‘apps.ams_dis’ to navigate quicker than scrolling through the complete list.
Note #2: If the function doesn’t appear in the list please provide the following grant to the EUL user. Example:

grant execute any procedure to eul_us;

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

Computer > HKEY_CURRENT_USER > Software > Oracle > Discoverer 11 > Database

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:

Run script applypreferences.sh under $ORACLE_INSTANCE/Discoverer/$DISCO_COMP_NAME/util

Restart the Discoverer_[instance_name]  ias-component:

Index creation

For better import performance, we recommend creation of following index on eul5_documents.doc_name.

Excel4apps Reports Wand

Blitz Report imports custom Excel4apps Reports Wand reports through the import menu option.

Import API

To mass import Exel4apps Reports Wand concurrent programs into Blitz Report via a SQL script, use the following PLSQL procedure:

as shown in the example script mass_import_excel4apps_reports.sql.

Enterprise Command Center

Blitz Report imports Oracle’s EnterpriseCommand Center dataset queries, allowing users to access ECC data of unlimited size and real-time in Excel. You can import data sets through the menu option

or you can use the following API for mass import.

Import API

The example script mass_import_ecc_reports.sql imports all ECC queries automatically into Blitz Report.

If you upgrade the command centers to a new version, you can use the following script to delete all imported ECC blitz reports, before re-importing Oracle’s latest dataset queries again: mass_delete_ecc_reports.sql.

Polaris Reporting Workbench

Blitz Report imports Polaris Reporting Workbench reports either through the import menu option or an API.

The import process consolidates different RWB reports, which are based on the same database views, into single Blitz Reports. The RWB report specific column selections are imported as individual Blitz Report templates.
During import, you can set the option ‘Expand Custom Views’ to expand the SQL text from the underlying database views into the imported Blitz Report. This increases flexibility as the SQL text can be maintained through the Blitz Report form instead of compiling a view into the database.

Import API

To mass import Polaris Reporting Workbench reports into Blitz Report via a SQL script, use the following PLSQL procedure:

The example script mass_import_polaris_reporting_workbench.sql imports all RWB reports that were executed within the given number of days automatically into Blitz Report.

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 report parameters and allows specifying number formats for numeric columns/parameters. The number of existing translations is shown in column ‘Count’. If you have a report selected before navigating to Tools > Column Translations, the columns/parameters 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 translation of parameter/header column names based on an individual SQL.

SQL output should contain two columns. First for column/parameter name, second for translation.

This can for example be used to show names of GL segments based on the selected in parameters ledger. Translations are applicable for both parameters and header columns in the report.

4.10 License Key

Blitz Report Setup > Tools > License Key

Enter the company name and license key information.

Double click on the active users count to open a detailed list of active Blitz Report users.

Users License Assignment

There are several ways how user access to Blitz Report can be granted and controlled.

1. (Recommended) All active users can open Blitz Report form, but can submit only those reports that are assigned to them.
   Setup:
   • Profile option “Blitz Report Access” is set to “User” on the site level
   • BlitzReport form is assigned to all responsibilities that are granted to at least one user. That can be done via automatic script.
   • User access to the reports is controlled via assignments in Blitz Report form.
2. Setting access for certain users only by setting up “Blitz Report Access” profile option. User can submit any report, but is not able to see the data in the output file if access is not granted.
   Setup:
   • Profile option “Blitz Report Access” is blank on the “Site” level
   • BlitzReport form is assigned to all responsibilities that are granted to at least one user. That can be done via automatic script.
   • Profile option “Blitz Report Access” is set to a value different from blank on the “User” level.

In addition to that, additional limitations can be introduced via assignments in Blitz Report form.

3. Setting access for certain users only by setting up custom responsibilities and assigning Blitz Report form to them only.
   Note: time consuming way of setting up user access as responsibilities and user assignment should be maintained over time.

   Setup:

   • Setup custom responsibilities for every role that should be granted access to the Blitz Report form.
   • Assign newly created responsibilities to the users that should be able to submit Blitz reports.

In addition to that, additional limitations can be introduced via assignments in Blitz Report form.

Examples of setting the profile option “Blitz Report Access” are presented on the following screenshots:

5. Tips and tricks

5.1 Debugging

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 Using Firefox with Oracle EBS

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.

Download historic version

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.

Disable automatic updates

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’

Disable additional browser tab opening for each download

In the default setting, Firefox opens every downloaded file in a new browser tab, so that you might end up with many additional tabs opened for each Blitz Report.

The opening of such tabs can be avoided by applying following setup in Firefox:

1. Enter about:config in the Firefox URL
2. Search for string browser.link.open_newwindow
3. Change the value from 3 (default) to 1

5.3 Incremental 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 as in the following example:

You can find an example of such parameter restriction in our seeded report FND Concurrent Requests, which uses this logic in parameter ‘Incremental Alert Mode’ to monitor concurrent request activity and send an alert email e.g. only in case of errors that occured since the last scheduled report run.

Delivery options and output distribution

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

Default delimiter

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:

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:

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.

5.7 Blitz Report production deployment

High level steps for your Blitz Report migration from test to production are presented below

• Upgrade the test environment to the latest Blitz Report code by downloading the installation .zip file and running install.sh.
• Export all report metadata from the test environment Setup->Tools->Export->All Content

• Optionally, if there are many Blitz Report profile option values set up in the test environment and you don’t want to set them up manually in production, export the Blitz Report related profile option values, which creates a SQL script, which can be run on production to load them automatically

• Install Blitz Report in production using instructions from the installation guide.
• In the production Blitz Report, navigate to Setup->Tools->Import->XML Upload and select the XML file exported previously.
• Optionally, run the SQL script generated previously to load the Blitz Report profile option values

6. Profile options

The profile option ‘Blitz Report Access’ 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:

7. APIs and integration

7.1 Export

To make migration tasks easier Blitz Report offers the following Export APIs

• Blitz Report Library Export API

7.2 Import

To make migration tasks easier Blitz Report offers the following Import APIs

• XML Upload Import API
• BI Publisher Import API
• Discoverer Worksheet Import API
• Discoverer Folders Import API
• Excel4apps Reports Wand Import API
• Enterprise Command Center Import API
• Polaris Reporting Workbench Import API

7.3 Useful DB functions

There is a list of useful function from custom package XXEN_UTIL that can be used during creation of Blitz report.

XXEN_UTIL.CLIENT_TIME (p_date in date)

This function allows converting date from server’s timezone to client local timezone.
It checks if there is difference in time zones between client and server. If any, it converts time from server timezone to client timezone.

• Input parameter: Date in server timezone
• Result: Date  in client timezone

Server timezone derived from profile value ‘Server Timezone’:

Client timezone derived from profile value ‘Client Timezone’:

Example:

Assume we have:

• Server Timezone set to GMT+1
• Client Timezone set to GMT+2
• Server’s current time is 2020.09.03 09:00:00

select 
to_char(xxen_util.client_time(to_date('2020.09.03 09:00:00', 'YYYY.MM.DD HH24:Mi:Ss')), 'YYYY.MM.DD HH24:Mi:Ss')
from dual

2020.09.03 10:00:00

XXEN_UTIL.SERVER_TIME (p_date in date)

This function works in the similar way as one described above. It checks if server timezone differs from client timezone and converts date if needed.

Example:

Assume we have:

• Server Timezone set to GMT+1
• Client Timezone set to GMT+2
• Client current time is 2020.09.03 09:00:00

select 
to_char(xxen_util.server_time(to_date('2020.09.03 09:00:00', 'YYYY.MM.DD HH24:Mi:Ss')), 'YYYY.MM.DD HH24:Mi:Ss')
from dual

2020.09.03 08:00:00

XXEN_UTIL.TIME (p_seconds in number)

This function converts number of seconds to the time in format “67d 14h 45.3s”.

So, it shows the number of days, hours, minutes and seconds. It does not show number of moths or years.

• Input parameter: number of seconds
• Result: string of characters showing how many days, hours, minutes and seconds there are in givens number of seconds

Examples:

select xxen_util.time(3600) from dual

1h 0m 0s

select xxen_util.time(361012) from dual

4d 4h 16m 52s

select xxen_util.time(36101200) from dual;

417d 20h 6m 40s

XXEN_UTIL.USER_NAME (p_user_name in varchar2)

This function converts user name to the full description of the user.
Example: Sysadmin would be converted to System Administator.

• Input parameter: User Name
• Result: User Description

Example:

select xxen_util.user_name('SYSADMIN') from dual

SYSADMIN (System Administrator)

XXEN_UTIL.USER_NAME (p_user_id in pls_integer)

Similar to the function above returns user name and user description based on user id.

• Input parameter: user id
• Result: user name and user description

Example:

select xxen_util.user_name(0) from dual

SYSADMIN (System Administrator)

XXEN_UTIL.USER_ID (p_user_name in varchar2)

Function returns user id based on user name.

• Input parameter: user name
• Result: user id

Example:

select xxen_util.user_id('SYSADMIN') from dual

0

XXEN_UTIL.CALENDAR_DAY_OFFSET ()

Function returns next working day from indicated calendar based on start date and the number of the offset days. Offset days parameter is optional with default value equal 0.

• Input parameters:
  • p_date in date – start date
  • p_calendar_code in varchar2 – the code of the calendar where working and non-working days are marked
  • p_offset_days in number – the offset form the start date. Can be positive or negative.
• Result: next working day based on input parameters

Examples:

Examples are based on assumption that:

• 2020.09.04, 2020.09.07, 2020.09.08 are working days
• 2020.09.05, 2020.09.06 are non-working days

select xxen_util.calendar_date_offset(to_date('2020.09.08', 'YYYY.MM.DD'),'Vision01') from dual

08-SEP-20

select xxen_util.calendar_date_offset(to_date('2020.09.08', 'YYYY.MM.DD'),'Vision01', -2) from dual

04-SEP-20

select xxen_util.calendar_date_offset(to_date('2020.09.06', 'YYYY.MM.DD'),'Vision01') from dual

07-SEP-20

select xxen_util.calendar_date_offset(to_date('2020.09.06', 'YYYY.MM.DD'),'Vision01', 1) from dual

08-SEP-20

8. Glossary