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].
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 textsearch 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”.
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 User Parameter Defaults menu option.
Once a template is selected it is also possible to store default parameter values on a template level using the Actions > Save Template 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.
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.
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.
Note: For scheduled reports, the output file is sent only if the report retrieves data. If the output file is empty (zero rows), the delivery is suppressed. Profile option ‘Blitz Report Suppress Empty File Delivery’ allows to change this default behavior.
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 file format. Possible options: CSV, TSV, XLSX
You can restrict the number of row in the output file by indicating that option.
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:
Run window options time limit
Profile option on user level
Time limit on report level
Profile option on responsibility level
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 APPSNode
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”.
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.
To add a new custom postprocess script name to the runtime options LOV, add it to XXEN_REPORT_POSTPROCESS_SCRIPT lookup as demonstrated on the following screenshot:
This button is used to set runtime options to the default values based on the profile option or report level settings.
When checking the freeze flag, option values are not changed or reset to their defaults when navigating to a different report.
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.
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
Public flag to allow the template to be shared with other users. Public templates can only be modified by the owner or a user with developer access
Global Default flag to set this template as the default for all report users
Distinct flag to suppress duplicate rows in the report
Owner to show who created and owns this 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 filter – used to quickly find specific available or displayed columns
Aggr. – for summary reports, how to aggregate the data (the choices are average, count, min, max, sum)
Sort Order – used to determine the order of the output data (ascending / aescending)
Sort Priority – Used to determine 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 for moving data between the “available” and the “displayed” column, 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 layout of the lists
Hide All button to deselect and move all data off of the “selected” columns and back to the “available” column.
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:
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
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
Pictures like company logo,
Modified cell format like font, cell fill color, etc,
Excel functions like Subtotal, Sum, SumIf, etc,
Additional rows added above the data column header,
Freeze pane applied
Datasheet template having
Pictures like company logo,
Modified cell format like font, cell fill color, etc,
Excel functions like Subtotal, Sum, SumIf, etc,
Additional rows added above the data column header,
Freeze pane applied
Upload the saved Excel file including modifications to the datasheets.
Run the report again with parameters as required. The output is generated with the uploaded datasheet template having refreshed with new data.
Note: If the excel functions are used in the datasheet template then hit “CTRL + SHIFT + R” to refresh them in the report output.
2.6 Scheduling a report
Blitz Reports can be scheduled as background processes to run at a certain date or time. This feature can be used to run time-consuming data extractions during non-office hours or to use a scheduled Blitz Report as an outbound interface or a very simple and flexible data warehouse.
The easiest way to schedule a report is to run the Blitz Report first, and to then copy the submitted concurrent request and schedule it:
Run a report from the Blitz Report run window with parameter restrictions as required
Navigate to the menu > view > requests > submit a new request > single request > Copy > Find > select the concurrent request submitted in step 1
Update schedule and delivery options as required and re-submit the request
Alternatively, you can submit a new ‘Blitz Report’ concurrent request, populating its parameters manually. Select the required report name from the LOV and populate parameters 1 to 87 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.
Note: For date parameters in scheduled Blitz Reports, you can use Oracle’s increment date functionality.
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’.
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:
Unlike most reporting tools, Blitz Report creates dynamic SQL at run-time, combining the extraction SQL and the user-specified parameters in an optimal way. This allows unused parameters to be dropped at run-time, thus eliminating a frequent source of non-performant SQL – the very common WHERE clause for optional parameters ‘column_name=nvl(:bind_variable, column_name)’.
To ensure that there is no unnecessary parsing of the dynamic SQL, Blitz Report’s parameterization uses bind variables.
3.2 Anchors and binds
Anchors are ‘placeholders’ in the extraction SQL, which allow the precise placement of additional (optional) parameterized SQL clauses at run-time. These clauses are constructed from user-specified parameters, which are then inserted into the extraction SQL at run-time. There are two types of Anchors:
WHERE clause SQL anchors, such as ‘1=1’, ‘2=2’ etc. Blitz Report inserts the associated SQL text directly before these anchors, automatically adding the keyword ‘and’ and a line feed, to create valid SQL. This allows quick parameter creation – no need to consider the precise position of the ‘and’ keyword.
A typical example for a SQL text would be ‘column_name=:bind_variable’, where :bind_variable would be bound with the parameter value entered by the user.
Note: A common coding practice is to write non-Blitz Report SQL with a ‘where 1=1’ clause, usually for formatting purposes. This does no harm when importing the SQL into Blitz Report, and may actually be useful, since it serves as the obvious anchor for any parameterized WHERE clauses.
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.
Note that the two different anchor types achieve similar goals, which is to inject additional (optional) parameterized SQL clauses into the extraction SQL.
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):
Dynamic tables and columns
ORDER BY, GROUP BY
Complete subselects or EXISTS clauses
The same anchor may be used multiple times inside a report SQL. Blitz Report inserts the corresponding parameter text for each occurrence.
A lexical parameter SQL text may contain a bind variable, which will be bound with the parameter value entered by the user when running the report.
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.
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.
Some words are reserved by Oracle and can not be used as bind variables. To find a list of such words use the following query:
select keyword from v$reserved_words where reserved='Y'or res_semi='Y'orderby keyword asc;
select keyword from v$reserved_words where reserved='Y' or res_semi='Y' order by keyword asc;
Parameter SQL text
furg.user_id in(select fu.user_id from fnd_user fu where fu.user_name=:user_name)
furg.user_id in (select fu.user_id from fnd_user fu where fu.user_name=:user_name)
furg.user_id in(select fu.user_id from fnd_user fu where fu.user_name=:user_name)and1=1
furg.user_id in (select fu.user_id from fnd_user fu where fu.user_name=:user_name) and
select'msiv.'||lower(fdfcuv.application_column_name)||' "'||fdfcuv.form_left_prompt||'",' column_text
fdfcuv.descriptive_flex_context_code='Global Data Elements'orderby
'msiv.'||lower(fdfcuv.application_column_name)||' "'||fdfcuv.form_left_prompt||'",' column_text
fdfcuv.descriptive_flex_context_code='Global Data Elements'
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
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
By default the Excel output’s data sheet name is the same as the report name. A parameter referencing the :sheet_name anchor allows to define a custom sheet name. It can be set up as a standard visible parameter, but also hidden by using a negative display sequence, for example dependent on another parameter. In the example below the Sheet name parameter inherits its value from the Operating unit parameter concatenated with the word ‘suppliers’ by using the following default value: :$flex$.operating_unit||’ suppliers’
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:
upper(hp.party_name) like upper(:customer_name)
In Blitz Report, the parameterized WHERE clauses are set up separately from the report SQL. Blitz Report only inserts individual WHERE clauses into the extraction SQL (at run-time) if the user enters a value for that particular parameter.
In this example, If the user provides a value for the customer name parameter, Blitz Report would add the above WHERE clause at the position of the anchor ‘1=1’ and execute the below SQL for data extraction.
upper(hp.party_name) like upper('George Clooney') and
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.
gb.period_name period_name,sum(nvl(gb.period_net_dr,0)-nvl(gb.period_net_cr,0)) amount
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.
select gl.name gl_name
,nvl(gb.period_name, 'total') period_name
,sum(nvl(gb.period_net_dr, 0) - nvl(gb.period_net_cr, 0)) amount
from gl_balances gb
where gl.name = 'vision operations (usa)'
and 1 = 1
and gl.ledger_id = gb.ledger_id
group by gb.period_name
pivot(sum(amount) for period_name in (
3.4 Report header
Report name uniquely identifies reports. Names should be short and descriptive.
Good practice is to prefix report names with the appropriate Oracle EBS module short code.
An optional report description of maximum 4000 characters may be set up to assist users in understanding and using the report.
This Google like search functionality retrieves reports by report name, description or underlying SQL. You can search for example for a table or column name accessed by a SQL, or by parts of the report name or description.
Retrieve reports by category.
Defines whether a report is visible to end users. Disabling a particular report would remove it from the end user report list. This may be useful during the development and testing phase. Even if a report is disabled, it may still be run by users having user admin, developer or system access.
Double click on a report version number to review the change history and previous report SQLs. Note that a new report version number is added and stored automatically at each update of a report’s SQL. Other report setup modifications such as report name, description or parameter changes are not stored in the version history.
Reports of type ‘Protected’ or ‘System’ are visible to users with access profiles set to ‘User Admin’ or ‘Developer’, but may only be edited by users with access profiles set to ‘System’.
This serves as an additional level of protection for reports providing important system functionality such as outbound interfaces or search screens.
See tutorials for examples on how to use Blitz Report for custom system functionality.
BI Publisher data definition code. When populated, Blitz Report executes the dataTrigger section e.g. beforeReportTrigger of the associated data definition XML template. This allows running BI Publisher report SQLs through Blitz Report where the data extracted is based on global temporary tables preprocessed in the before report trigger.
Double click onto the BI Code to download and view the data definition XML template.
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.
The list of available format codes is defined in lookup XXEN_REPORT_NUMBER_FORMATS, which can be extended with additional custom format codes according to your needs. Note that the lookup codes represent Microsoft’s internal style ids, which must be a numeric value above 200, as the lower range style ids are reserved for Microsoft’s standard formats.
Report Options define additional attibutes and processing options for a specific report:
The report from which it was copied
Before and after report triggers which can be used for data pre- and post processing, e.g. for table data updates before executing a report
Email to identify the author of a report
Default email to sent the report output after its completion
Default report output format
Default limitation on the number of rows returned by the report
Time limit in minutes after which the report is terminated by the ‘Blitz Report Monitor’ program
Custom unix script to be executed after the report completion
Reqest type for defining concurrent managers specialization rules
Package name containing pre and post processing functions, which can be used for example to call Oracle standard PLSQL code or to run additional processes before or after report execution. The functions need to follow the naming convention: afterpform, beforereport, afterreport and return the boolean data type.
An example of how such functions can be used is the FA Depreciation Projection report, which requires running the Oracle standard ‘Depreciation Projection’ concurrent program first, before the report retrieves the data. The Blitz Report references a custom database package XXEN_FASPRJ, which submits and waits for completion of the Depreciation Projection program, before executing the actual report SQL.
Parameter values are passed to the custom package through variables, which appear in the list of Blitz Report parameter anchors.
The runtime values of these parameters can be used inside the package body.
Here are screenshots of the parameter values and logfile showing the values assigned to the package variables before executing function xxen_fasprj.beforereport.
Email address of the report author.
Additional information can be found on registered authors in our online library.
Default email address for sending output files. If different email address are set up on different levels, the default email on the Blitz Report run window is derived in following order:
Note: Blitz Report’s email functionality is available from EBS version R12 onwards only.
Allows changing the output format from Excel XLSX (Excel) to CSV (comma separated values) or TSV (tab separated values).
Limits the maximum number of lines for report execution.
Maximum 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:
Allows assigning request types defined on the Concurrent>Program>Types screen to specific Blitz Reports. Request types can be used in concurrent managers specialization rules, for example to configure certain slow running Blitz Reports to be processed by a separate concurrent manager.
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
Parameter definitions consist of:
a parameter name, display sequence and optional parameter description and default value
SQL text to be inserted into the report SQL dynamically at run-time
an anchor as a reference to a position for the insertion
a parameter type and optional LOV
an optional matching value to restrict the SQL text insertion to certain parameter values
Sequence number that defines the order in which parameters are displayed.
If different WHERE clauses are used for the same parameter name, e.g. to insert a different SQL text at different SQL positions or depending on different parameter matching values, display sequence is populated only for one record and left blank for subsequent lines of that parameter name. 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 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).
Parameter specific text added dynamically into the report SQL if a value for the parameter is entered at run-time. Usually, the SQL text would contain a bind variable name starting with a colon e.g. ‘:account_number’. Blitz Report automatically detects the variable and binds it with the value entered by the user. A maximum of one bind variable per parameter is allowed. If a parameter’s SQL text contains more than one bind variables, only the first one is bound with the entered parameter value.
Blitz Report also allows the insertion of SQL text using matching values. If the SQL text does not contain a bind variable, then the field ‘Matching Value’ becomes mandatory. Blitz Report inserts the corresponding SQL text if the parameter value entered by the user matches the matching value.
If the SQL text includes a bind variable restriction and the user 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:
column_name like :bind_variable
column_name not like :bind_variable
You can also use functions, for example:
upper(column_name) like upper(:bind_variable)
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. For multiple values, it is required to have the table column on the left and the :bind on the right side of the comparison operator. A SQL text such as :bind_variable=column_name will not work.
Note: The multiple values functionality is available for parameters with anchor styles 1=1 or &lexical and for types ‘Char’, ‘Number’, ‘LOV’, ‘LOV custom’ or for ‘LOV Oracle’ with identical id and value columns in their LOV query only.
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.
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. Even if you have timezone conversions enabled, the entered parameter value will be used as a bind for SQL execution without any conversion (midnight of the date entered).
DateTime: a valid date including timestamp. If you have timezone conversions enabled, the entered parameter value will be converted to server time before it is used as a bind for SQL execution.
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).
Name of a shared LOV or Oracle standard value set.
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.
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.
In the example above a value ‘Order’ is provided for the parameter Type, so the following SQL is inserted in the report SQL text: nvl(ooha.transaction_phase_code,’F’)=’F’
Specifies a default parameter value. If the value starts with the keyword ‘select’, then Blitz Report would execute the SQL to derive the default value dynamically instead of using a fixed value. Example: To get the current date in GL period format, use the following SQL as a default value:
select to_char(sysdate,'MON-RR') from dual
Some functions can be used without selecting from dual making report development easier. Here’s the list of those functions:
:Customer_Name is not null or :Account_Number is not null
Parameters are referenced by their names (in the installed base language, usually US), prefixed with a colon and having spaces or other non-word characters replaced with a single underscore.
Blitz Report evaluates the logical expression at run-time and displays an error message in case the expression is not met. The default message text displayed is stored in FND message XXEN_REPORT_INSUFFICIENT_PARAM and, for expressions enforcing at least one parameter entry, in message XXEN_REPORT_ONE_PARAM_REQUIRED.
To display a specific error message for your logical expression, enter a message text as required.
For reports that have an advanced logical expression set up for their parameter requirements, the required button label shows ‘Advanced’ instead of ‘Required’. One expression can be set up per report.
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 examples.
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.
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 custom Blitz Report icon
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.
OE Concurrent Programs
Profile ‘Blitz Report Restrict Assignments by Operating Unit Level’=No
Users can access a report if they have a responsibility that is either linked to the receivables application or that has access to operating unit ‘Vision Australia’ or that is linked to request group ‘OE Concurrent Programs’.
Profile ‘Blitz Report Restrict Assignments by Operating Unit Level’=Yes
Users can access a report if they have a responsibility that is either linked to the receivables application or request group ‘OE Concurrent Programs’ and that responsibility has access to operating unit ‘Vision Australia’.
Please note that assignments control report access for normal users only. Users having their access profile set to ‘User Admin’, ‘Developer’ or ‘System’ can access all reports in the system, irrespective of report assignments, and can run them, depending on report type and access profile as described here.
Blitz Report shows by default all reports accessible from all active responsibilities of a particular user, regardless of the current login responsibility. If profile option ‘Blitz Report Filter Reports by Responsibility’ is set to ‘Yes’, only reports assigned to the current login responsibility are shown like in the below screenshot:
However if a user has developer access then all reports are still displayed on the Setup window because it’s required for development. For developers only the Run window shows the restricted list of the reports. It may be useful if a developer wants to check if a particular report is available for users under a certain responsibility. Furthermore a developer can run any report selected on the Setup window by clicking the Run button. But the Run window reports LOV is still restricted by a current responsibility. So a developer will not be able to see a report that was just run in the list of avaiable reports in the Run window and has to run it always from the Setup window.
The forms assignment feature allows opening Blitz Reports directly from any Oracle EBS standard form.
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 custom icon, with parameters defaulted as defined.
The Blitz Report installation includes several hundret reports developed by Enginatics, and their assignments to Oracle standard applications and forms. These default assignments allow business users to start working with the included reports without creating assignments for them individually.
We recommend that you review and adapt the default assignments according to your specific user access requirements. The default assignments are only loaded automatically during the first installation of Blitz Report, not when upgrading. If you would like to update the assignments during an upgrade, you can manually import them from file: /content/assignments.xml, which is included in the Blitz Report installation .zip file.
If you would like to create assignments for many reports automatically, you can do this by directly inserting records into the xxen_report_assignments table.
The following scripts can be used as an example to mass assign reports to applications, request groups or responsibilities:
If you have more than one language installed, Blitz Report offers multi-language support via the Oracle EBS translation menu icon for the following data:
The below screenshot shows an example of setting a parameter translation
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.
Note: If you install an additional language in Oracle applications, in addition to running the adadmin ‘Maintain multi-lingual tables’ process, you need to run a concurrent request Blitz Report Maintain Multilingual Tables.
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 can consume a license, and which users have access to the report setup window
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.
Functionality \ Access Profile
Create modify or delete categories
Edit licensing information
Run reports (Standard or Protected)
Run reports (System)
Assign reports to users or categories (Standard or Protected)
Assign reports to users or categories (System)
Create modify or delete reports (Standard)
Create modify or delete reports (Protected or System)
Create modify or delete column translations
Create modify or delete templates
1. 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.
2. Depending on the setup of profile option ‘Blitz Report Template Access’, users can create and modify only their own templates or modify other owner’s templates.
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.
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.