Scripting data source

It's easy to design data sources with the current GUI editor. However, for complex reporting scenarios where you need to calculate aggregate values, scripting is a better choice as it gives you all the flexibilities of the MongoDB Aggregation Framework.

Configure scripting data source

Enable scripting

Navigate to the Scripting tab and enable Custom Script. After that, you can start to compose the script for your data source.

image-20220121170716017

Below are the typical aggregation operations used in most data sources:

You can learn more at Practical MongoDB Aggregations Book.

Data source parameters

The inline variable called @params provides dynamic and flexible customization for a custom data source. It allows users to define input parameters that adapt a pipeline dynamically based on user-provided input. Overall enhances the reusability and responsiveness of data queries.

Key Concepts

  • @params variable:
    • A special variable that holds user-defined parameters.
  • Input flexibility:
    • Parameters can be passed to the data source from two distinct points:

A typical pipeline might look like the following when parameters are used:

[
	...
	"BalanceAsOfToDate": {
        "$sum": {
            "$cond": {
                "if": {
                    "$and": [
                        { "$gte": @params.startDate },
                        { "$lte": @params.endDate }
                    ]
                },
                "then": {
                    "$cond": {
                        "if": { "$eq": [ "$TypeOfPosting", "Credit" ] },
                        "then": { "$multiply": [ -1, "$Amount" ] },
                        "else": "$Amount"
                    }
                },
                "else": 0
            }
        }
    }
    ...
]

This snippet demonstrates:

  • The use of @params inside a data source pipeline.
  • It can use anywhere in the pipeline, a nested conditional flow like an example, not just in the $match stage.

Parameters injection points

  • Widget data query

    • User-defined parameters can be initialized in the widget using the dataSourceParameters variable. Below is an example implementation:
    DateTime startDate = rangeDateFilter["StartDate"];
DateTime endDate = rangeDateFilter["EndDate"];

var startDateBson = new BsonArray();
startDateBson.Add("$PaymentDate");
startDateBson.Add(BsonValue.Create(startDate));

var endDateBson = new BsonArray();
endDateBson.Add("$PaymentDate");
endDateBson.Add(BsonValue.Create(endDate));

dataSourceParameters["startDate"] = startDateBson;
dataSourceParameters["endDate"] = endDateBson;

  • SetParameters method

    • Input can also be provided using the SetParameters method from various query types, such as DataEnumerationQuery or DataObjectApiQuery. Below is an example implementation:
    var startDateParameter = new BsonArray();
startDateParameter.Add("$PaymentDate");
startDateParameter.Add(BsonValue.Create(DateTime.MinValue));

var endDateParameter = new BsonArray();
endDateParameter.Add("$PaymentDate");
endDateParameter.Add(BsonValue.Create(lastDayOfPreviousMonth));

var parameters = new Dictionary<string, object>
{
    { "startDate", startDateParameter },
    { "endDate", endDateParameter }
};

var query = DataSourceEnumerationQuery
    .For("CwAcParkingFeesWithCurrentBalancePerToDateForASpecificPeriodv2")
    .FilterBy(filter)
    .SetParameters(parameters);

Define the output structure

Casewhere cannot know the output structure for scripting data sources, so it needs you to specify the information. In the tab Design, select Add, and map the fields and nodes to the projection fields in your data source script.

image-20220121173310237

Hint: You should add the fields defined in the projection stage of your data source

[
	...
	{
        "$project": {
            "_id": 0,
            "Gender": "$_id.Gender",
            "AgeSpan": "$_id.AgeSpan",
            "Total": "$Total"
        }
    }
    ...
]

A simple example

Imagine you're building an intelligent-shopping application that every time a person appears in front of the camera, the application will register a record DetectedPerson (Behavior, Age, Gender) in the database. The customer now asks you to build a report visualizing shop visitors across gender and age like below:

image-20220121173515942

For the report, you will need to make a data source to aggregate the data in DetectedPerson.

[
    {
        "$match": {
            "Behavior": "GoInto"
        }
    },
    {    
        "$group": {
            "_id": {
                "Gender": "$Gender",
                "AgeSpan" : {
                    "$switch": {
                        "branches": [
                                { "case": { "$lte": [ "$Age", 5 ] }, "then": "0-5" },
                                { "case": { "$lte": [ "$Age", 10 ] }, "then": "5-10" },
                                { "case": { "$lte": [ "$Age", 15 ] }, "then": "10-15" },
                                { "case": { "$lte": [ "$Age", 20 ] }, "then": "15-20" },
                                { "case": { "$lte": [ "$Age", 25 ] }, "then": "20-25" },
                                { "case": { "$lte": [ "$Age", 30 ] }, "then": "25-30" },
                                { "case": { "$lte": [ "$Age", 35 ] }, "then": "30-35" },
                                { "case": { "$lte": [ "$Age", 40 ] }, "then": "35-40" },
                                { "case": { "$lte": [ "$Age", 45 ] }, "then": "40-45" },
                                { "case": { "$lte": [ "$Age", 50 ] }, "then": "45-50" },
                                { "case": { "$lte": [ "$Age", 55 ] }, "then": "50-55" },
                                { "case": { "$lte": [ "$Age", 60 ] }, "then": "55-60" },
                                { "case": { "$lte": [ "$Age", 65 ] }, "then": "60-65" },
                                { "case": { "$lte": [ "$Age", 70 ] }, "then": "65-70" },
                                { "case": { "$lte": [ "$Age", 75 ] }, "then": "70-75" }
                            ],
                            "default": "75+"
                        }
                    }
                },
                "Total": { "$sum": 1}
            }
        },
    {
        "$project": {
            "_id": 0,
            "Gender": "$_id.Gender",
            "AgeSpan": "$_id.AgeSpan",
            "Total": "$Total"
        }
    },
    {
        "$sort": {
            "AgeSpan": 1
        }
    }
]

After that, define the field mapping in the Design tab.

image-20220121174806607

Finally, create a custom widget using the data source. When you select the data source, the corresponding fields will be loaded and ready to use. How to make the widget is out of the scope of this example.

image-20220121174929706