After the database connection is created, the data sources can be created for the connection as follows:

1. In eMACH-Sigma Studio, click Connections on the left pane.
   The existing connections are listed in the Connections and Data Sources on the right pane.
   
   

2. Click the Data Source  icon on an existing database connection.
   
   
   
   The Create Datasource screen appears.
   
   
   
   

3. In the Create Datasource screen, enter an unique data source name in the Data Source Name field.
   
   

   
   
   

4. Optional: If you want to create a data source passing SQL queries, perform the following steps:
   
   

   1. Enable the Is SQL? toggle option.
      
      
      
      

   2. Enter the query in the Data Query field.
      
      

      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.

      To apply constructed queries for the data source, refer Applying constructed queries for SQL Data Sources. 

      
      
      

      It is mandatory to add ‘#’ symbol at the front of the query parameter.

   3. Click Parameter Configuration icon to configure the parameter settings.
      
      
      
      

   4. Select access category and define data types for the parameters on Category and Data Type fields. You can also set a default parameter value in the Default Value field if required.
      
      
      
      

   5. Click outside the window to save the parameter values.

   6. If required, you can make use of the SQL Model lookup field to assign the default wrapping of the provided query statements.
      

      The SQL Model is an optional field. The available options are: SQL Wrapper Model and SQL Without Wrapper Model.

      
      
      
      

   7. On making use of the SQL Wrapper Model, the wrapping of the query statements will take place and the filters applied for the queries will get wrapped automatically. This enables the applied filters to get invoked in a wrapped format, so that the specified filter queries get executed along with the entire query statement.
      

      It is essential that you specify the WHERE 1 = 1 condition, if you make use of the prepend logical operators in your queries. A sample query statement is provided as follows:

      select BENE_ACC_NO AS bacc_no, BENE_NAME from demo_ct_tran_summary_data WHERE 1 = 1 
      {{applyFilters({ 
      "dbColumnId":"BENE_ACC_NO", 
      "filterColumnId":"bacc_no", 
      "prependLogicalOperator":"AND" 
      })}}

      
      
      
      

   8. On making use of the SQL Without Wrapper Model, the wrapping of the query statement will not take place and it is essential that the filters and sorting are applied individually for each of the sub-queries, which are used within the main query statement. It is necessary that the applied filter and sort queries are individually invoked, so that the nested queries embedded with the relevant WHERE clause fetch the data in a proper way. As the Without Wrapper model is used, the queries fetch the data, depending on the expressions, filter conditions and functions used, in accordance with the specified query statements.
      

      It is essential that you specify the WHERE 1 = 1 condition, if you make use of the prepend logical operators in your queries. A sample query statement is provided as follows:

      select BENE_ACC_NO AS bacc_no, BENE_NAME as bname 
      from demo_ct_tran_summary_data WHERE 1=1
      {{applyFilters({
      "dbColumnId": "BENE_ACC_NO" , 
      "filterColumnId": "bacc_no",
      "prependLogicalOperator": "AND"
      })}}
      {{applySortBy({
      "bname": "BENE_NAME",
      "bacc_no": "BENE_ACC_NO"
      })}}

      
      
      
      

   9. Proceed to step 9.
      
      
      

5. Click the Object Name field and select the table, view, or stored procedure name from which you need to fetch the data. 

   Stored procedures are enabled for reports only and are yet to be enabled in App Designer. The input parameters for stored procedure can be passed during the configuration of the base report in eMACH-Sigma Studio and the values can be altered in eMACH-Sigma screen as well. Refer Creating a Data Source using Stored Procedure for more information on creating a datasource using stored procedure.

   
   
   

6. Click the Column IDs field and select the columns to be fetched.

   Ensure that the column names in a particular table don't get separated by blank spaces, since the column names with blank spaces will be deemed as void columns and henceforth, no column names should be separated with blank spaces, but however, the underscore symbol can be used to separate lengthy column names for a particular table. To quickly select all the columns for the report, click the Select All () icon. To remove all the selected columns, click the Remove All () icon. To select specific columns, select the checkbox adjacent to the column names as shown in the following screen shot:

   

7. Mandatory: Provide the name of the bundle key associated with the property file in the Bundle Key field to make use of resource bundle entries, e.g. common.

   By default, the Bundle Key value is specified as common. If you want to make use of a different bundle key associated with the property file to make use of resource bundle entries, then you can provide a bundle key of your choice in accordance with the relevant key associated with the property file.

   
   
   

8. Optional: You can make use of the Add Labels  option to manage resource bundle entries and provide the display names for the data columns fetched from a table, stored procedure, DB view or any other data element and even for the SQL data sources.
   
   
   
   
9. Optional: On the Resource Bundle Entries screen, you can provide the display names of your choice for the data columns fetched from a table, stored procedure, DB view or any other data element and even for the SQL data sources.
   
   
   
   

10. Optional: Enter the condition for filtering the data in the Where Clause field. For example, the where condition, Price > (select avg(Price) from Products) can be given in the Where Clause field.

    If suppose, you want to fetch data based on certain values, you will have to specify the exact values in the Where Clause field. For example, consider the following SQL query:

    select * from Products
    where Product_ID = 'P001'

    Here, Product_ID = 'P001' can be given in the Where Clause field.

11. Optional: Enter the column names by which you want to sort the data in ascending or descending order in the Order By field.
    
    
    
    

12. If required, select the Generate Form checkbox to generate the form for the configured data source.
    

    Once the data source is configured, ensure to select the Generate Form checkbox to generate the form and then proceed to save the data source. A form will get generated for the configured data source (ACCOUNT_SUMMARY_DS). You can find the generated form in the Form Definition List.

    
    
    
    

13. Click Save to save the data source.

    When you try to exit the Create Datasource screen before saving the configured DB data source, a warning pop-up message appears as shown in the following screen shot:
    

    • Click Cancel, if you do not want to exit the Create Datasource screen.
    • Click Ok, if you want to exit the Create Datasource screen.

    
    
    
    The following confirmation message appears:
    
    
    
    

14. Click Ok.