Development of Quick Reports

In order to support a simplified set of reports which are limited in functionality but on the other side very simple to create, the concept of Quick Report's may be used.

Quick Report's are simply a SELECT statement, that is stored in the database, and which can be accessed by the end user. The result may be browsed in a special table window, printed using 'Quick Print...', exported using different file formats (*.txt *.csv and *.htm) and, using the concept of 'Output Channels', directly move data into tools like Excel, Spotfire etc.

The development of the SELECT statement may be performed with any SQL-Editing Tool, and then packaged as a Quick Report. This statement should not contain any tables, since the end-users do not have access to tables.

Contents

• Development
• Localization
• Deployment

Development

A Quick Report is developed using a SQL file, one for each report. The file contains two sections:
A SELECT statement which constitutes the actual report.

Comments, which are separately loaded into the database and used as online help towards the end user
The filename is used as the default report title when loading the Quick Report into the database.

The file format is based on the file Template.SQL which is found under <F1-HOME>\\Development\Design.

Note: It is important that the documentation is filled in correctly to show a unified appearance to the end user.

Example:

SELECT to_char(dor.order_date, 'YYYYWW') "Order Week",
       SUM(doi.amount) "Order Amount"
FROM   demo_order_item doi,
       demo_order dor
WHERE  dor.company LIKE nvl(&Company,'%')
   AND dor.company_id = doi.company_id
   AND dor.order_id = doi.order_id
GROUP BY dor.order_date

-------------------------------------------------------------------
-- COMPONENT = FNDORD, IFS/Demo Order
-------------------------------------------------------------------
-- Description: This report will show the amount of sales for each week.
-- Company: Numeric ID for Company to show, or empty for all companies. 
-------------------------------------------------------------------

In order to give added support for proper names, the following is coded into the default handling of Quick Report.

If a column name or parameter name is in uppercase (Oracle default) or contains underscores ( _ ), then the name is made proper and underscores are transformed into spaces.

Parameter names are shown with a semicolon added after the text (in the Quick Report Query-dialog.

Localization

The localization of Quick Reports is done by creating a different set of files, one set for each language, and storing them in the distribution under a top node folder named QuickReport (see Deployment below).
The entities that should be translated are:

• Report Title: The Filename. Ex 'Invalid Customers.SQL'
• Column Titles: By changing the alias to reflect a proper name. Double quotes are used for column names, especially with unconventional, national letters. Ex "Company"
• Queries: Are specified in a proper language setting. Ex &Company
• Comments: Which are used as online help towards the end user.

Deployment

The deployment of Quick Report is performed by copying the original files forward into build and distribution sets. To be able to detect which files to treat as Quick Reports, use the following setup.

• Create a folder 'QuickReport', one for each component, in the component root directory.
• Create subfolders corresponding to national settings, ex 'en' for English versions
• Place all Quick Reports that are translated into the chosen language in the corresponding subfolder.

This setup enables Build.EXE to locate Quick Reports, and place them into a more global structure for deployment.

Query Flags

You can specify Query Flags for your Quick Report parameters. This allows an easy control of end user input and allows or disallows certain combinations. You need to provide the query flags inside open and close square braces in between the ampersand sign and the parameter name e.g. &[MCSBL]PARAMETER_NAME. All flags have opposites specified by a '-' (minus). For example the opposite of 'Single' is of course multiple values (separated by ';') which in turn corresponds to the functionality in the Query Dialog. The default value for SQL Quick Reports is [-CS-L].

M=Mandatory

C=Custom (simplified or 'values only')

S=Single flag

B=Allow Between expressions

L=Allow wildcards ('%' and '_')

SELECT * FROM fnd_user WHERE identity LIKE '&[-CS-L]Fnd_User'

Default Values

You can get the Quick Report parameter dialog to show default values such as enumerations for SQL type Quick Reports. For this you need to specify the view name along with the column name for the parameter prompt e.g. &VIEW_NAME.COLUMN_NAME. With the use of this you will get the default values and the translated prompt name for the parameter. The following will show the enumerated values for the "Customer Category" and will have the translated parameter prompt.

SELECT * from customer_info where customer_category_db = '&CUSTOMER_INFO.CUSTOMER_CATEGORY_DB'

You can combine this with Query Flags as well.

SELECT * from customer_info where customer_category_db LIKE '&[-CS--]CUSTOMER_INFO.CUSTOMER_CATEGORY_DB'