Advanced reports

Advanced reports allow users to create reports formatted to meet their specific needs. Optional schedules can be created for automatic delivery of the reports.

Advanced reports require development of a custom code-service. Use the following as a sample:

function sample(req,resp){


   var reportObj = {
       columns: [{
           columnType: "text",
           field: "YOUR_FIELD",
           title: "YOUR FIELD"
       }, {
           columnType: "text",
           field: "YOUR_OTHER_FIELD",
           title: "YOUR OTHER FIELD"
       }],
       data: []
   };


   function getAllAssets() {
       var assetsCollection = ClearBladeAsync.Collection('assets');
       var query = ClearBladeAsync.Query();
       query.equalTo('type', 'YOUR_ASSET_TYPE');
       return assetsCollection.fetch(query).then(function (results) {
           return results.DATA;
       });
   }


   getAllAssets().then(function(allAssetDetails) {
       for (var x = 0; x < allAssetDetails.length; x++) {
           var row = {};
           //fields ex: id, label, custom_data, location
           row.YOUR_FIELD =allAssetDetails[x].YOUR_FIELD
           row.YOUR_OTHER_FIELD = allAssetDetails[x].YOUR_OTHER_FIELD
           reportObj.data.push(row);
       }
       resp.success(reportObj);
   }).catch(function (reason) {
       console.error('failed: ', reason);
       resp.error(reason);
   });
  
}

See here for general information on adding reports. Select Advanced to create an Advanced report.

On the INFO tab enter:

• Label

• Group (designates who will have access to the report)

• Code Service

• Type (used in Plugins)

On the SCHEDULES tab:

• Click + to add a schedule.

• Set up the schedule.

Click CREATE to create the report.

Email recipients

A schedule's Email Recipients field accepts two kinds of recipient:

• An email address — type it in directly, as before.

• A group — start typing a group name and select it. Group chips carry a group icon so they are easy to tell apart from addresses.

When a schedule names a group, the report is emailed to every user who belongs to that group at the time the schedule runs. Membership is resolved on each run, so adding someone to a group is enough to start sending them the report. You can mix addresses and groups in one schedule.

Delivery: combined or per group

Below the recipients field, Delivery controls how many emails one run sends:

• One combined report — a single email addressed to everyone the schedule names. This is the original behavior and the default.

• One report per group — one email per selected group, generated for that group alone.

The choice is explicit rather than inferred from who the recipients are. A regional manager may want the combined view while a site operator wants only their own site, and both are legitimate.

• Addresses typed in directly are included on every per-group email. They belong to no group, so they would otherwise stop receiving the report entirely.

• A selected group with no members is skipped and sends no email.

• Choosing One report per group without selecting any group falls back to a single combined send.

• Schedules created before this feature existed have no delivery setting and read as One combined report, so they keep behaving exactly as they did.

Naming the group in the subject

On a per-group schedule, put {group} anywhere in the Email Subject and it is replaced with that group's name, so each recipient can tell which group their copy covers. A subject of Weekly summary for {group} sends Weekly summary for North Region to that group and Weekly summary for South Region to the next.

On a combined send the placeholder is removed, so switching a schedule back to combined never mails a literal {group} out.

Writing a code service for per-group reports

Scheduled runs call your code service with reportStartDate and reportEndDate in req.params. A per-group send also passes reportGroupIds, an array holding the group that email covers.

function sample(req, resp) {
    var reportStartDate = req.params.reportStartDate;
    var reportEndDate = req.params.reportEndDate;
    var reportGroupIds = req.params.reportGroupIds; // undefined on a combined send

    var sql = "SELECT h.asset_id, h.num_val" +
              "  FROM asset_attr_history h" +
              "  JOIN assets a ON h.asset_id = a.id" +
              " WHERE a.type = 'YOUR_ASSET_TYPE'" +
              "   AND h.stamp >= $1 AND h.stamp < $2";
    var args = [reportStartDate, reportEndDate];

    if (reportGroupIds && reportGroupIds.length) {
        sql += " AND EXISTS (SELECT 1 FROM groups_assets ga" +
               "              WHERE ga.asset_id = h.asset_id" +
               "                AND ga.group_id = ANY($3))";
        args.push(reportGroupIds);
    }

    ClearBladeAsync.Database().query.apply(null, [sql].concat(args)).then(function (rows) {
        // build reportObj from rows
        resp.success(reportObj);
    }).catch(function (reason) {
        console.error('failed: ', reason);
        resp.error(reason);
    });
}

Assets belong to groups through the groups_assets table, which is a many-to-many relationship. Use an EXISTS subquery rather than a plain JOIN: an asset in more than one group would otherwise produce a duplicate row for every group it belongs to, silently inflating the report.

For further resources on creating code-services see here:

• Code services

• Code services tutorial

• ClearBlade async docs