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:
   
   

   When writing SQL statements in the Data Query field, avoid giving aliases for column names. Use the Add Labels option  to give aliases for the columns.

   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.

   • Here, System is the default parameter configuration present in the system.
     The available default parameters are: 
     
     INPUT_LANGUAGE_ID
     INPUT_GCIF
     INPUT_USER_NO
     
     

     For example

     select * from users_table where user_no=#{INPUT_USER_NO}

   • User parameter configurations are fetched from the user in Sigma Application.

   • Procedure Out Parameter uses the OUT parameter value returned by the procedure used in the report. The parameter name used in the procedure out parameter has to be used in the query to fetch the data.
     
     

     For example, If PARAM1 is the Procedure Out Parameter, it will be used as

     select * from users_table where uer_no=#{PARAM1}

   
   
   
   

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: