For every parameter in your report format, you must add a Report Parameter to your report setup to pass those values to the report. Report parameters can be required or optional, and may or may not prompt the user for entry.
Report parameters are managed from a child form on the report.
Report: Name of parent report
Prompt User: enter when user needs to view/enter a parameter value
Caption: the descriptive caption of the item. For example, instead of "cpo_code" you will enter Committee Position Code.
Parameter Name: the name of the parameter, for example @mb_expire_date.
Has through value: NOT USED CURRENTLY. Functionality for this setting is pending research for the 2006.02 build.
Default Value: used for defaulting the value if you are on a current record. EG, if the report is run directly from an Update Batch page, then you will probably want to default the parameter to be the value of the Batch you are updating. To do so, enter the value with braces around it and use the column name, for example {asn_code}. SQL syntax is not supported.
Default Value Through: NOT USED CURRENTLY. Functionality for this setting is pending research for the 2006.02 build.
Column Template: specify a SQL column name to utilize the meta data formatting stored in md_column (e.g. control class, width, max width, etc.). This setting is only effective if a value is not chosen for "Control Class" (see next item).
Control Class: enter the type of Control Class, for example: textbox, dropdown, checkbox, listbox etc.
Display Width: In the 2006.02 build release, this setting will control the size of the parameter display based on the number of characters set for this setting.
Maximum Width: In the 2006.02 build release, this will set the max number of characters accepted by the textbox
Causes Postback: If user enters or change a value for this field, then checking this checkbox will repost the parameter form. This is useful when this is a dropdown that controls the choice of values in another dropdown (eg, if you change the country, then you need to re-populate the state/province dropdown with new states based on the value of the newly-chosen country)
Required: check this if the parameter is required.
RDL Parameter: For any parameter that exists in the RDL, there must be a parameter record in netFORUM , and this setting must be checked. You can have parameters setup in netFORUM that are not passed to the RDL, and for those parameters this checkbox should be turned off.
An example would be a Session Attendee List report, where the only parameter value that needs to be passed to the RDL (and subsequently to the stored procedure) is a session key (a GUID that identifies the session). Rather than creating a huge dropdown of all the sessions ever entered in netFORUM , you can have 2 parameters – one that presents a dropdown of events and another that generates a dropdown of sessions based on the event chosen in the first parameter. The event parameter would be a "post-back" parameter, and this is covered below in the section pertaining to the "Where" section. The event parameter would have the "RDL Parameter" setting turned off, and the session parameter would have it turned on, and only the session value chosen would be passed to the RDL upon execution.
The next set of fields are used when the parameter is to be displayed as a DropDownList (single-select) or a ListBox (multi-select). Underneath this image you will see what this parameter will look like when prompting an end user running the report.
Value from table: When the parameter is a DropDownList, enter the name of the table from which the values are to be pulled. In advanced scenarios, you may enter a series of tables with JOIN conditions.
You may also enter "SQL" for this field, and then enter an entire SQL statement in the "where" field, as shown in the image further below.
When you have a SQL-driven parameter based on a parsed value, like sub_prd_key in this example, netFORUM will not generate a NULL entry if the parameter is not required. By placing the entire SQL statement in the "where" field, a NULL entry may be generated and UNION’d with the remainder of the drop-down values.
It is still necessary to enter the "value column" and "text column" references, and they should comply by name with the select list within the "where" field.
Value column: Enter the name of the Column from the value from table whose value will be passed into the parameter entered in the parameter name field. Typically this will be a Key or a Code.
In the example above, the actual name of the Parameter in the report (and accompanying stored procedure) is @evt_key. The SP is expecting to receive a GUID that is a valid evt_key. Therefore,the value column in this example is evt_key.
It is very critical that you ensure that the data that will appear in the value column is of the same datatype and the same underlying data source that the stored procedure's parameter is expecting. If your stored procedure is expecting a GUID, then be sure that your value column is a GUID). If your stored procedure is expecting an alphanumeric code, then be sure that your value column is set to be a code (and not a key).
Text column: Value you want for the entries in the dropdown that the user sees. If the code is clear to user, then enter same as in value column or enter more SQL to have a more descriptive value in the dropdown. Example: {{{1}}}.
In the example illustrated above, we want the end user running the report to see the event names, which is contained in the column evt_title.
Where: Use this to filter the results of your dropdown. Remember to include the Hide/Disabled expression abc_delete_flag=0 and if the data in this dropdown is conditional based on another parameter (using "Causes Postback") you can do this as well. For example, if you have a parameter for Event (parent) and Session (child) then the WHERE clause for session will be:
ses_delete_flag = 0AND ses_evt_key = {evt_key}
The value for {evt_key} will automatically look to the parameter for @evt_key and netFORUM will parse it.
Note: You do not need to put the actual word WHERE; netFORUM will do this automatically.
Order By: How you want to order the results in your drop-down. This will typically be the same value as your "Text Column".
Order: To order parameters from top to bottom on the report page. 10, 20, 30, etc.
Some report parameters may not require a prompt. A good example is a report that is run from the Links Bar on a profile record. Below is an example of a non-prompt parameter that is defaulted from the cst_key of the record the user currently is on, which in this case is the Invoice By Customer report run from the Individual Profile form.
There is a checkbox control class setting available for parameters. When implemented, netFORUM will pass a "0" for unchecked or "1" for checked. The value passed will be a string character, so your RDL and stored procedure should be set up to receive it accordingly. The recommended approach is to set your corresponding parameter in the SP as char(1). The value may be converted to a bit or int value if the programmer desires, but it may be left as a character value as well.
netFORUM will pass the values of a multi-select parameter (ListBox) to an SP as one line or string separated by commas. Therefore, the parameter datatype for the multi-select values should be large enough to accommodate all the incoming values. For this reason it is safest to make the parameter value be varchar(8000), which is the maximum allowed by SQL Server for a varchar value.
In the body of your SP, you must parse out the comma-separated values in the parameter. netFORUM has a user-defined function (UDF) named av_SelectStringFromString that will do this for you. See the following example:
CREATE procedure rpt_test_report_with_listbox @gla_gat_code nvarchar(4000) AS SET nocount ON SELECT gla_code, gla_description, gla_gat_codeFROM ac_gl_account (nolock)JOIN dbo.av_SelectStringFromString(@gla_gat_code, ’,’) ON item = gla_gat_codeORDER BY 3, 1, 2GO
The dbo.av_SelectStringFromString UDF receives as values the string to be delimited and the delimiter. Listbox parameters passed from netFORUM will always be comma-delimited, but the function receives the delimiter as a value so that the function may be used for other scenarios where a different delimiter needs to be used. The function returns a table that contains all the values in the delimited string argument.
Note that by putting a maximum of 8000 characters on the parameter, this report will be limited to a certain number of values. Suppose the parameters are GUIDs, then you will be able to accept up to 216 values for the ListBox(36 characters for each GUID plus 215 commas is 7991 characters). Presumably there will be few reports where so many parameter values are selected from a ListBox.
Note: If you need to know more about parsed values, please contact us and reference issue 11576
If you have two or more drop-down list parameters on a page, and there is a postback on any of parameters, then the 2nd and subsequent drop-down lists will parse values (if any) in the parameter definition in the following way: