Enabling Drop-down for Stored Procedure Input Parameters
To enable drop-downs for stored procedure input parameters for in reports, the following are necessary:
- A grid widget (App) that displays the data needed for the input parameters must be created in Studio.
- The grid widget and the column need to be specified for the input parameter during the report configuration in Sigma Studio.
For example, the following Sigma Application screen shot shows the drop-down values for the Currency input parameter for in a report:Anchor
Let's see how to configure drop-downs for stored procedure input parameters for in reports.
To fetch the grid widget data for the stored procedure input parameters in reports, perform the following steps in Sigma Studio:
On the home page of Sigma Studio,
right-clickclick the Data Source
and select the Create Data Source option%20-%203.png?version=1&modificationDate=1658912553361&cacheVersion=1&api=v2)
icon on an existing database connection.
...
Note It is necessary that you create separate data sources for the grid widget and the report. In the given example here, the data source for the grid widget is a DB table data source (SP DS), whereas the data source for the report is a DB stored procedure (SP DS 6).
...
.png?version=1&modificationDate=1658912576497&cacheVersion=1&api=v2&width=900)
- Create the DB table data source for the grid widget. For example, SP DS.
...
- Create the DB stored procedure data source for the report. For example, SP DS 6.
...
- Access App Designer and create a grid widget with the DB table data source (SP DS).
- Click Proceed to configure the grid widget.
- Click Proceed to configure the grid widget.
...
- Drag and drop the required data columns to the design area and click the Save
(
) icon to save the widget (e.g. ACCOUNTS).
...
- Create a report with the DB stored procedure data source (SP DS 6) and provide other the necessary configuration details as neededper your needs and requirements.
...
.png?version=1&modificationDate=1656567692693&cacheVersion=1&api=v2&width=900)
Select Click the
Default Configuration tab and then click the Primary Ds Procedure Parameters
icon.
- In the Procedure Parameters Configuration pop-up window, select the appropriate grid widget (e.g. ACCOUNTS) from the Load data from Widget lookup field and choose the appropriate key and description columns from the Key Column and Description Column lookup fields.
- Select the filter criteria of your choice from the Apply Mandatory Filter As drop-down list.
...
Parameters Configuration (
) icon to configure the applied procedure parameters for the report.%20-%202.png?version=1&modificationDate=1656567733713&cacheVersion=1&api=v2&width=900)
In the Params Configuration screen, the applied procedure parameters of ACCOUNT_NO, AVAI_BALANCE, FD, TD and CURRENCY appear.Note In this example, the DB stored procedure data source has following five input parameters:
- ACCOUNT_NO (Account Number)
- AVAI_BALANCE (Available Balance)
- FD (From Date)
- TD (To Date)
- CURRENCY (Currency)
Among these five parameters, the values for the ACCOUNT_NO, AVAI_BALANCE, and CURRENCY parameters must be fetched from the grid widget
...
(ACCOUNTS), whereas the FD and TD parameters
...
must be provided with the custom date values.
...
The
...
parameters can be made mandatory using the Is Mandatory option and the Equals filter criteria is applied as
...
the Mandatory Filter Operator for the listed procedure parameters. However, both the
...
Is Mandatory and Mandatory Filter
...
Operator are optional fields.
...
%20-%202.png?version=1&modificationDate=1656565191741&cacheVersion=1&api=v2&width=900)
...
- Click Save to save the report.
- Access the relevant report (e.g. REPORT) in the Sigma Application.
The FD and TD parameters display the default values, which can be changed by the end-user, whereas the other three parameters display drop-downs from which the end-user can select values.
- For the Account No procedure parameter, click the parameter value drop-down list and select an account number, e.g. 5500000848.
- For the Balance procedure parameter, click the parameter value drop-down list and select the balance amount, e.g. 15476.00.
- For the Currency procedure parameter, click the parameter value drop-down list and select a currency value, e.g. INR.
- After you provide the relevant parameter values for the applied procedure parameters, you can proceed to click Run to generate the report.
- Access the Generated Reports tab and then download the generated report.
...
- Click the ACCOUNT_NO parameter and select the appropriate grid widget (e.g. ACCOUNTS) from the Load data from Widget lookup field and choose the appropriate key and description columns from the Key Column and Description Column lookup fields. Proceed to click Update to update the parameter configurations.
.png?version=1&modificationDate=1656565411793&cacheVersion=1&api=v2&width=900)
Click the AVAI_BALANCE parameter and select the appropriate grid widget (e.g. ACCOUNTS) from the Load data from Widget lookup field and choose the appropriate key and description columns from the Key Column and Description Column lookup fields. Proceed to click Update to update the parameter configurations.
.png?version=1&modificationDate=1656565573674&cacheVersion=1&api=v2&width=900)
Click the FD (From Date) parameter and select Custom from the Source drop-down list to provide the relevant date of your choice. In the Parameter Value field, select the relevant date, e.g. 02/02/2022. Proceed to click Update to update the parameter configurations.
Note You can configure the minimum and maximum ranges for date columns in the Min Value Expression and Max Value Expression fields respectively. For example, let’s assume the current date is 01 July 2022. For allowing dates within a month period as minimum and maximum, the Min Value Expression and Max Value Expression fields can be set as -1m and +1m respectively. In this case, end-users of Sigma Application can specify the minimum date as 01 June 2022 and the maximum date as 31 July 2022. Both the Min Value Expression and Max Value Expression fields are optional.
.png?version=1&modificationDate=1669202051692&cacheVersion=1&api=v2&width=900)
Note When a stored procedure with date input parameters is used as a datasource for a report, the Dependency Field, Validation and Dependency Range fields appear. These fields can be used to assign the dependencies and validations for the date parameters.
In the given example,
- The 'To Date' parameter is dependent on the 'From Date', so for the FD (From Date) parameter, the TD (To Date) parameter is selected as a dependent from the Dependency Field drop-down list.
- The 'From Date' must be less than the 'To Date' in all cases, so the Less than condition is selected from the Validation Condition drop-down list.
- The Dependency Range is an optional field. If required, you can assign the dependency date range for the FD (From Date) parameter to enforce date selection within the specified date range.
- For instance, if the current date, is 01 July 2022 (01/07/2022) and if the Dependency Range is set as 1m, then the FD (From Date) parameter must be within one month range, which is 31 July 2022.
.png?version=1&modificationDate=1669202102969&cacheVersion=1&api=v2&width=900)
Click the TD (To Date) parameter and select Custom from the Source drop-down list to provide the relevant date of your choice. In the Parameter Value field, select the relevant date, e.g. 04/02/2022. Proceed to click Update to update the parameter configurations.
Note When a stored procedure with date input parameters is used as a datasource for a report, the Dependency Field, Validation and Dependency Range fields appear. These fields can be used to assign the dependencies and validations for the date parameters.
In the given example,
- The 'From Date' parameter is dependent on the 'To Date', so for the TD (To Date) parameter, the FD (From Date) parameter is selected as a dependent from the Dependency Field drop-down list.
- The 'To Date' must be greater than the 'From Date' in all cases, so the Greater than condition is selected from the Validation Condition drop-down list.
- The Dependency Range is an optional field. If required, you can assign the dependency date range for the TD (To Date) parameter to enforce date selection within the specified date range.
- For instance, if the current date, is 01 July 2022 (01/07/2022) and if the Dependency Range is set as 1m, then the TD (To Date) parameter must be within one month range, which is 31 July 2022.
.png?version=1&modificationDate=1669202243638&cacheVersion=1&api=v2&width=900)
Click the CURRENCY parameter and select the appropriate grid widget (e.g. ACCOUNTS) from the Load data from Widget lookup field and choose the appropriate key and description columns from the Key Column and Description Column lookup fields. Proceed to click Update to update the parameter configurations.
.png?version=1&modificationDate=1656566902896&cacheVersion=1&api=v2&width=900)
Click the Save (
%20-%202.png?version=1&modificationDate=1656566971127&cacheVersion=1&api=v2)
) icon to save the configured procedure parameters.%20-%203.png?version=1&modificationDate=1656567007540&cacheVersion=1&api=v2&width=900)
Info If suppose any of the input parameter is an Integer datatype and you want to specify a minimum and maximum range for the parameter value, you can achieve by specifying the range of values as shown in the following screen shot:
Once you apply the procedure parameter configurations, a toast message appears, indicating a successful save of the applied procedure parameter configurations..png?version=1&modificationDate=1656567635041&cacheVersion=1&api=v2&width=900)
- Click Save to save the report.
Refer the following page: