Applying parameters for SQL Data Sources

Sigma now enables you to apply parameters for SQL data sources. On providing the appropriate queries with the dynamic binding of specified parameters, the key values for the specified parameters can be assigned individually on the basis of the provided parameters. Once the parameters are appropriately defined in the specified queries, the data types for the defined parameters can be individually selected in accordance with the key values that determine the usage of the provided parameters in the specified queries.

To apply parameters for SQL data sources, perform the following steps in Sigma Studio:

1. Click the Data Source  icon on an existing database connection. For detailed information on creating Connections, refer Create a Database Connection.
   
   
   
   The Create Datasource screen appears.
   
   
   
   

2. In the Data Source Name field, provide a unique data source name, e.g. BENEFICIARIES DS.

   The Connection Name is a non-editable field.

   
   
   
   

3. Enable the Is SQL? toggle option.
   
   
   
   

4. In the Data Query field, provide the appropriate queries with a relevant parameter, as shown in the following code snippet:

   SELECT * FROM DEMO_BENE_DATA WHERE BENE_ACC_NO = #{ACCOUNTNO}

   In the aforementioned code snippet, the ACCOUNTNO serves as the parameter in the defined Where Clause of the provided query. The specified table name is DEMO_BENE_DATA, whereas the defined column name is BENE_ACC_NO. To apply constructed queries for SQL data sources, refer Applying constructed queries for SQL Data Sources. To apply parameters for SQL data sources with pipelined table functions, refer Applying parameters for SQL Data Sources with Pipelined Functions.

   
   
   
   

5. To specify the parameter configuration for the configured SQL data source, click the Parameter Configuration  icon.
   
   

   
   
   

   The Parameter Configuration window appears.

   The Parameter Name is a non-editable field.

   
   
   

6. In the Parameter Configuration window, click the Category drop-down list and select the relevant parameter category, e.g. User. 

   The available parameter categories are: System, User and Procedure Out Parameter.

   
   
   
   

7. Click the Data Type drop-down list and select the relevant data type of the specified parameter, e.g. String.

   The available parameter data types are: String, Date, Integer, Float and Rate.

   
   
   
   

8. Enter the relevant key value of the specified parameter in the Default Value field, e.g. 10000002345.

   The specified parameter is ACCOUNTNO and the key value (Default Value) of the provided ACCOUNTNO parameter is 10000002345.

   
   
   
   

   On clicking outside the Parameter Configuration window, the configured parameters will get saved successfully and a success toast message appears, as shown in the following screen shot:
   
   
   
   
9. To save the configured data source, click Save.
   
   
   
   A confirmation message, indicating a successful save, appears.
   
   
   
   
10. Click Ok.

11. Access the Information Report screen and configure the report with the created SQL data source (BENEFICIARIES DS), as shown in the following screen shot:

    For detailed information on creating reports, refer Configuring Reports.

    
    
    
    

12. Once the report is configured with essential attributes, click Save to save the report.
    
     
    
    

13. Access the relevant report, e.g. BENEFICIARIES in the Sigma Application and click Run to generate the report.

    For detailed information on generating the reports in Sigma Application, refer Generating a Report.

     
    
    
    

14. When the report generation status is displayed as Published to Repository, click the Download icon  to download the generated report.
    
    
    
    The fetched data of the BENEFICIARIES report is displayed in the PDF file, as shown in the following screen shot: