Enabling Drop-downs for SQL Parameters

Owned by Lakshminarayanan1 S CTOO (Unlicensed)
Last updated: Jan 06, 2023 by Ravikumar Babu CTOO (Unlicensed)

To enable drop-downs for the SQL parameters in reports, the following are necessary:

  • A grid widget (App) that displays the data needed for the SQL parameters must be created in Studio.
  • The grid widget and the column need to be specified for the SQL parameter during the report configuration in Sigma Studio.

For example, the following Sigma Application screen shot shows the drop-down values for the Name SQL parameter in a report:



Let's see how to configure drop-downs for the SQL parameters in reports.

To fetch the grid widget data for the SQL parameters in reports, perform the following steps in Sigma Studio:

  1. On the home page of Sigma Studio, click the Data Source  icon on an existing database connection.

    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 (TRANSACTIONS DS), whereas the data source for the report is a query-based statement with an applied SQL parameter (TRANSACTIONS QUERY DS).




  2. Create the DB table data source for the grid widget. For example, TRANSACTIONS DS.



  3. Create the query-based data source with the applied SQL parameter for the report. For example, TRANSACTIONS QUERY DS.



  4. Access App Designer and create a grid widget with the DB table data source (TRANSACTIONS DS).
    1. Click Proceed to configure the grid widget.




  5. Drag and drop the required data column(s) to the design area and click the Save icon to save the widget (e.g. TRANSACTION SUMMARY).



  6. Create a report using the query-based data source with the applied SQL parameter (TRANSACTIONS QUERY DS) and provide the necessary configuration details as per your needs and requirements.



  7. Click the Parameters Configuration icon to configure the applied SQL parameter for the report.



    In the Params Configuration screen, the applied SQL parameter of name (Beneficiary Name) appears.

    In this example, the query-based data source (TRANSACTIONS QUERY DS) comprises the following SQL parameter:

    • name (Beneficiary Name)

    The values for the name parameter must be fetched from the grid widget of Transaction Summary.




  8. Click the name parameter and select the appropriate grid widget (e.g. TRANSACTION_SUMMARY_APP) 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.

    1. Ensure that the Mandatory Filter Operator is selected as in the list of so that the parameter values get displayed as a drop-down list in Sigma Application.

    2. In the Number of Values field you can specify the number of parameter values from the selection needs to be displayed. For example, if there are five names in the parameter values and all are selected and Number of Values is 2, then the two selected names are shown and the remaining selections are indicated in numbers.

    3. Proceed to click Update to update the parameter configurations.





    4. Click the Save  icon to save the configured SQL parameter(s).



      Once you apply the SQL parameter configurations, a toast message appears, indicating a successful save of the applied SQL parameter configurations.




  9. Click Save to save the report.



  10. Access the relevant report (e.g. REPORT) in the Sigma Application.



    The name parameter displays the names of the beneficiaries in a drop-down list, as shown in the following screen shot:



  11. Click the name SQL parameter value drop-down list and select the beneficiary names of your choice.



  12. Proceed to click Run to generate the report.



  13. Access the Generated Reports tab and then download the generated report.



    The fetched data of the report, with the relevant data values provided to the applied SQL parameter(s), is displayed in the PDF file, as shown in the following screen shot:

Copyright © Intellect Design Arena Limited.

All rights reserved. These materials are confidential and proprietary to Intellect Design Arena Limited and no part of these materials should be reproduced, published in any form by any means, electronic or mechanical including photocopy or any information storage or retrieval system nor should the materials be disclosed to third parties without the express written authorization of Intellect Design Arena Limited.