Data Source

You can use an SQL stored procedure or fetch data as data sources for your reports.

Add a Data Source

  1. From the Main Menu, click Analytics > Data Sources. The Data Sources List page appears.
  2. Click Insert. The Add Data Source page appears.
  3. Enter the Name of the data source that is used by the system.
  4. Enter the Display Name of the data source that is displayed in the UI.
  5. If your data source is a stored SQL procedure, tick the Use Stored Procedure checkbox, and in the Stored Procedure field, enter the name of the SQL procedure following this convention: procedure_name_as_stored_in_DB @Id @EntityName @UserId. For more information, see Use Stored Procedures.
  6. If your data source is fetch data, tick clear the Use Stored Procedure checkbox and fetch the data. For information on how to fetch data, see Use Fetch Data.

Use Stored Procedures

Prerequisite: Create the SQL procedure that you want to use.

The following parameters of the stored SQL procedure are automatically mapped to specific values, as described in the table below:

Parameter Value mapped to
@userId The ID of the user that runs the Report linked to the Report Document.
@businessUnitId The ID of the business unit that the user belongs to.

 

If you want to use other parameters declared within the stored SQL procedure, you have to save and reload the Add Data Source page, and add the parameters in the PARAMS section first. For information on how to add parameters, see Add Report Parameters.

Use Fetch Data

Prerequisites:

  • Make sure there are at least two entities in the system. For information on how to add entities, see Create Entities.
  • Create relationships between the entities on which you do the fetch. For more information on relationships, see Entity Relationships.
  • Add custom attributes to each entity for which you do the fetch; you can use the attributes when defining the fetch. For information on how to add attributes, see Adding Attributes.

To fetch the data, you can write the fetch directly into the Fetch Object Expression field.

You can also use the Fetch Designer:

  1. Click Show Fetch Designer.

  2. Choose the criteria and conditions for clustering the database.

  3. Choose the data to be included in the report.

Basic fetch:
Copy
return {
    "entity": {
        "alias": "base",
        "name": "entity",
        "attributelist": null
    }
}

Change the values that you want to be dynamically replaced by the parameter values by using getParamValue.

Dynamically change property values with the parameter values when generating the report.

In the example above, we are going to change the value of the name property as follows:

Copy
return {
    "entity": {
            "alias": "base",
            "name": "entity",
            "attributelist":null
    },
    where:{
            type:"and",
            conditionlist:[{
                first:"base.defaultEntityStatusId",
                type:"equals",
                second:"val(<span style="background-color: #fff0f0">getParamValue(entityStatus</span>))"
            }]
    }
}

When generating the report, the system uses the value of the entityStatus parameter for the name property.

The platform supports multiple parameters at once, therefore you can use getParamValue to dynamically change properties value with the parameters value as many times as you need.
NOTE  You have to save and reload the Add Data Source page and in the PARAMS section, add the parameters whose values replace the values of properties as defined in the fetch. For information on how to add parameters, see Add Report Parameters.