You can create parameter objects by accessing Parameter Object Editor.
On the Parameter Object Editor screen,
Figure 2: Parameter Object Editor
- Specify values of properties of a new parameter on the Parameter Object Editor that opens. The details on this screen are discussed in the below section.
- After providing all the required values, click Save As button to save the parameter with a unique name under a specific category.
- Click the Add New button to add multiple parameters if required. This button gets enabled after saving a parameter.
Details on the Parameter Object Editor
Name: A parameter has a unique name, which is used to reference it across the application.
Caption: Caption is ‘descriptive label’ text of the parameter. It will be displayed on Input Parameter Form (IPF) that opens when running the report. This will help user understand what value should be entered for the parameter.
Data Type: This defines the data type of the parameter value. Select among:
- Char: Alphabets, numbers and special characters.
- Number: Digits and decimal point.
- Date: Date or part of date (like day, month, year).
- Boolean: A parameter that will accept one of the two pre-set values. Appears on screen as a checkbox.
Size: Number of digits or characters that user should be allowed to enter (only for Char and Number data type).
Usage Format: Select the format for the value of parameter. Refer Appendix A to know more about Data Formats.
Prompt Format: Select the format in which user should provide value for a Date type parameter at runtime.
For Date or Time data formats, you can choose Apply Locale Default to select the date format of current locale. When Apply Locale Default checkbox is checked, you can also select Append Time to append a time format for date type data. Refer Appendix B to know more about Date and Time Formats.
Default Prompt Value: Specify a value that is most likely to be provided by the user. This value will appear selected while executing the report.
For a date type parameter, Default Prompt Value will become editable dropdown. Click calendar icon to select a specific date. You may specify a date in it or select a value from the options:
- CURRENT_DATE: The date on which report will be generated.
- MONTH_START_DATE: First day of the month in which report will be generated.
- YEAR_START_DATE: First day of the year in which report will be generated.
You can also set a default value that is relative to any of the three options listed above. For example, to set default date as 3 days after CURRENT_DATE, specify CURRENT_DATE + 3. To set default date as 5 days before month start, specify MONTH_START_DATE – 5.
To provide relative values as above, select the option from editable dropdown and suffix it by + or – and the number.
For example, if report print date is August 13 2009 and default date is MONTH_START_DATE – 5, IPF at run time will display July 27 2009 (in the selected date format).
You can also specify ‘month’ and ‘year’ date offsets with respect to Current Date, Month Start Date or Year Start Date in Date type parameters while executing the report. Example, for the current date as February 17 2017, if you specify CURRENT_DATE + 2M – 5y as default value, the date at run time would be April 17 2012 (i.e. 2 months added and 5 years subtracted from the current date).
Use Input Source: Checking this check box would select the first value of the specified column in ValueColumn as the Default Value. The value can be selected from the drop-down list populated from the SQL in Dynamic Source Statement.
Mandatory: Check to make it mandatory for user to specify value for this parameter to generate the report. On IPF, mandatory parameters will appear marked with an asterisk sign.
Visible: This property defines if parameter will be visible on IPF or not. By default, this checkbox is checked so that it will be displayed on IPF. Some reports do contain parameters, but the values are passed without direct human interaction – like sub reports and hyperlinked reports. If this parameter is used in such reports, then its value will be passed by the calling report. If this is the case, uncheck this checkbox. Uncheck this checkbox if you want to run report using the default parameter value only.
Enable: To define if this parameter should be enabled for user input on IPF or not. By default it is checked. Report designers may choose to disable it on this page, but may enable it at report run time through scripting.
Advanced Properties of parameters as shown under the Advanced tab are discussed below:
Figure 3: Advanced tab
Below are the parameter properties under the Advanced tab:
ID: A parameter also has a unique ID that application auto generates when parameter is saved.
Database Time Zone: This is useful when different users access application from different time zones. In such cases, it may happen that date/time data stored in database may be in one specific time zone and user may be accessing application from a different time zone. In this situation, application can convert date / time type data from one time zone to another time zone.
In Database Time Zone, select the time zone in which date / time data was entered in the database (to convert to). Select SYS_CONN_TZ to use time zone set on Database page. Select SYS_SERVER_TZ to use time zone set on Server Properties page (Report Server’s time zone).
User Time Zone: Select the time zone from where user is expected to access the application and provide the parameter value in that time zone (to convert from). Select SYS_USER_TZ to use time zone applicable at run time (depending pre-set priority by the application). Select SYS_SERVER_TZ to use time zone set on Server Properties page (Report Server’s time zone).
Check Prompt User Time Zone checkbox if user needs to provide time zone at run time when providing value for this parameter on IPF.
For time zone conversion to take place, value for Database Time Zone and User Time Zone needs to be provided.
Description: Specify information about parameter that will help user in providing the desired parameter value. Description will be displayed in a popup on IPF when user clicks the Info icon next to the parameter. Description is also displayed on Save Parameter Object dialog and Open Parameter Object dialog.
Restrict to List: This is applicable for parameters for which Input type is Dropdown. By default, it is checked to make sure users select value(s) only from the list.
Pass Values Using Tables: Check this checkbox when you want to pass multiple parameter values through a table. This is done especially when number of values that can be passed (total number of bytes of selected values) as part of stored procedure or SQL is more than what is allowed.
Add Script: You can add a validation script for a parameter. Click Add Script button to open Script Editor dialog and write the script.
Refer Scripting Parameter Form section to know more about scripting.
Parameter Object Formats
Each of the parameters have specific formats in which they are represented as explained below:
In business world, data carries a business value associated to name, percent, month, year, profit, etc. Below are the formats used to commonly represent data:
- Title case for cities:New Delhi
- Percent symbol: 56%
- Year represented in 4 digits:2009
- Date in United States of America: 12/27/2009 (MM/dd/yy)
- Date in India: 27/12/2009 (dd/MM/yy)
- Currency symbol:$ 99
Format is applied to a field during report generation. Prompt format set for a parameter is displayed in the field on Input Parameter Form. It helps the user in understanding the format in which user should provide the input.
Format properties for a field or a parameter is stored in the form of format codes (also known as format string).
Use this dialog box to apply preset output format to a field and input format to a parameter. Selected format will be converted in format code and displayed in Output Format text box (on Query Object page) and Format text box (on Parameter Object page).
Preset data formats
Application offers a number of preset formats to choose from. When you specify a format, a sample and the format code it applies is displayed on the dialog.
Apply Locale Default: While specifying output format of a Number, Currency, Date and Time type field, check this checkbox to format the output based on the default format as per Locale active at report generation time. Leave the checkbox unchecked to set format that should be applied on the output.
Note: Default format to be used for a locale is automatically set by the application. However, this can be customized by specifying in localeconfigurations.xls located on Report Server.
Select this option when data is not expected in any specific format preferences. When applied as output format, report will be generated using data in the format it is received from database. When applied as input format, user may enter value in any format.
The following pre-set format properties are available for numbers:
- Decimal Places: The number of digits on the right side of decimal point. For example, if you select 2, the number 12 will be displayed as 12.00 and the number 12.345 will be displayed as 12.34
- Use 1000 Separator: Check this checkbox to insert a comma after every thousand. For example 1,000,000
- Negative Numbers: Negative number can be enclosed in brackets, e.g. (874.98) or it can have a dash (negative sign) on the right, e.g. -874.98
If the numeric data is “currency” like sales or salary or profit, you may select a currency symbol from Currency dropdown. This is in addition to the preset properties available for Number format.
When you select a format, dialog displays a sample value and the format code that it will auto-generate for the field.
Application offers a number of preset date formats to choose from. Select a format to view a sample of date in selected format. To know more about date and time format codes, refer to Appendix B.
A number of pre-set time formats are available to select from. Select a format to view a sample of how time will appear in selected format. To know more about date and time format codes, refer to Appendix B.
To denote a number as percentage, select this format. You can further choose the Decimal Places in a number after which the percentage symbol (%) be displayed
Decimal places: The number of digits on the right side of decimal point.
Select this format to represent a value in scientific notation (also known as exponential notation). In computer applications, exponential part of the number is separated by E. For example, 123.456 in scientific format will be 1.23456 E+2
This is mostly used as output format. Select from (None), Upper Case and Lower case. Text value will be converted accordingly at the time of report generation.
Date and Time Format codes
On all the pages of portal, wherever you are expected to enter date or time, it needs to be entered in applicable format. Format is displayed in respective entry box through codes. The table given below provides explanation of each of the format code.
Example: Friday, December 26, 2008, 17:46:13 hours.
|Character(s)||Use||Effect as per example|
|ddd||Day in 3 characters||Fri|
|dddd||Complete day name||Friday|
|HH||Hour of time in 2 digits||17 (or 05)|
|mm||Minute of time in 2 digits||46|
|MM||Month in number in 2 digits||12|
|MMM||Month name in 3 characters||Dec|
|MMMM||Complete month name||December|
|ss||Seconds in 2 digits||13|
|yy||Year in 2 digits||08|
|yyyy||Year in 4 digits||2008|
|a||time in 12 hours format||AM (or PM)|