Running Reports as Selected Users with JWT OAuth and the Reports and Dashboards API

Salesforce reporting introduces some fascinating complexities to data visibility and exposure, particularly for organizations using Private Organization-Wide Defaults.

The key complicating factor is this: when a Salesforce report is run, it’s run in the context of some user or another, and the records that are shown on the report are the ones that are visible to that user. This means that report distribution solutions have to be very careful to only show each user a report run in their own context - not someone else’s.

Suppose your organization builds a critical report that many users will need to review. It’s built to show “My Opportunities”, so each user will see only their own Opportunities, and the Opportunity Organization-Wide Default is Private. You add a criterion to the report to only show Opportunities that have your internal “Needs Attention” Checkbox set. Now: how do you make sure your users are regularly updated when they have Opportunities that require their review?

A naive solution would create one subscription to this report, say for Frank Q. Exec, and add all of the users who need to receive it as recipients:

Lightning Subscription

But this runs afoul of the principle mentioned above: the report’s context user is Frank, and the recipients of the report will see data as if they were Frank. From Salesforce:

IMPORTANT Recipients see emailed report data as the person running the report. Consider that they may see more or less data than they normally see in Salesforce.

This is unlikely to be an acceptable outcome.

Further, we can’t simply have Frank create many subscriptions to the same report, adding one user as both the recipient and the running user to each: Frank only gets five total report subscriptions, and he can only have one subscription to each report.

Of course, users can schedule reports themselves, in their own context, and they can run them manually, and we can build dynamic dashboards (which come with their own limits). But what if we really need to create these subscriptions for our users automatically, or allow our admins to manage them for thousands of users at a time? What if, in fact, we want to offer the users a bespoke user interface to let them select subscriptions to standard corporate reports, or run reports in their contexts to feed into an external reporting or business intelligence solution?

This is a question I’ve struggled with before, and I was excited to see Martin Borthiry propose the issue on Salesforce Stack Exchange. Here, I’d like to expand on the solution I sketched out in response to Martin’s question.

Background

There are two report subscription functionalities on Salesforce, and they work rather differently. Report subscriptions are summarized in the Salesforce documentation under Schedule and Subscribe to Reports.

On Classic, one can “Subscribe” to a report, and one can “Schedule Future Runs”. The nomenclature here is confusing: a Classic “Subscribe” asks Salesforce to notify us if the report’s results meet certain thresholds, but it’s not for regularly receiving copies of the report. We’re not going to look at this feature. “Schedule Future Runs” is equivalent to a report subscription in Lightning and is the feature corresponding to the business problem discussed above.

Classic Subscription

On Lightning, we simply have an option to Subscribe, as we saw above. There’s no Lightning equivalent to the Classic “Subscribe” feature.

So what happens when we subscribe to a report?

The Classic Schedule Future Runs and the Lightning Subscribe functionality is represented under the hood as CronTrigger and CronJobDetail records with the CronJobDetail.JobType field set to 'A', for Analytics Notification. You can find them in queries from the Developer Console or Workbench via queries like

SELECT CronExpression, OwnerId, CronJobDetail.Name FROM CronTrigger WHERE CronJobDetail.JobType = 'A'

Unfortunately, knowing this doesn’t help us very much. Neither CronTrigger nor CronJobDetail can be created directly in Apex or via the API, and the objects provide very little detail about existing report subscriptions. The Report Id, for example, is notable by its absence, and the Name field is just a UUID.

A more promising avenue for our use case is the Reports and Dashboards API, because it offers an endpoint to create an Analytics Notification.

POST /services/data/vXX.0/analytics/notifications

with a JSON body like this

{
  "active" : true,
  "createdDate" : "",
  "deactivateOnTrigger" : false,
  "id" : "",
  "lastModifiedDate" : "",
  "name" : "New Notification",
  "recordId" : "00OXXXXXXXXXXXXXXX",
  "schedule" : {
    "details" : {
      "time" : 3
    },
    "frequency" : "daily"
  },
  "source" : "lightningReportSubscribe",
  "thresholds" : [ {
    "actions" : [ {
      "configuration" : {
        "recipients" : [ ]
      },
      "type" : "sendEmail"
    } ],
    "conditions" : null,
    "type" : "always"
  } ]
}

The feature set shown here in JSON is at parity with the user interface, and has the same limitations. Adding a recipient for the subscription over the API, for example, suffers from the same visibility flaws as doing so in the UI. And the API doesn’t let us do what we truly want to - create report subscriptions for other users that run as those other users - because we cannot set the owner of the subscription programmatically.

… or can we?

While the Reporting and Analytics API doesn’t support setting the context user for a subscription, it always takes action as the user as whom we authenticate to the API. And that we can control.

While an admin can Login As a user to create a one-off subscription, we’re more interested here in industrial-strength solutions that can support thousands of users. So we’re going to build a script to create subscriptions by talking to the Reports and Dashboards API, using the Javascript Web Token (JWT) OAuth authentication mechanism. Why? Because the JWT flow is our only route to seamlessly authenticating as any (admin-approved) user, with no manual intervention or setup required on a per-user basis.

Setup: Connected Apps and Certificates

Setting up the JWT flow involves building a Connected App in Salesforce, under which our scripts will authenticate. JWT is secured using a certificate and associated public key/private key pair - Salesforce holds the public key, our script holds the private key.

This is the same mechanism used for authentication in many Continuous Integration solutions. I’m not going to rehash all of the details here, because they’re well-covered elsewhere. You can follow Salesforce’s steps in using SFDX for continuous integration, or read through my own article about setting up CircleCI with Salesforce DX.

When you’re finished building the Connected App, add the Profiles of each of the users who are to be subscribed to reports to the Connected App as a pre-approved Profile, or assign all of those users a Permission Set and assign that Permission Set as pre-approved on the Connected App. This ensures that we can authenticate to the API as those users without any intervention.

Building the Scripts

We’re going to stick to sketching out a solution here that can be adapted to many different business problems, as we discussed earlier. For simplicity, we’ll use Salesforce DX to handle the JWT authentication, even though we’re not using SFDX for development here. Because it’s my preferred scripting workflow, I’ll be using Python with simple_salesforce, but you could just as easily achieve this in Ruby, Java, JavaScript, or even just bash and curl.

The main job of our script is to login as a user and create a report subscription for them. We might build this towards a specific business process by adding scaffolding to, for example, query a custom object out of Salesforce to define which reports should be subscribed automatically for which users, but we’ll leave that elaboration to a later date. Once we’ve got that core functionality achieved, we can wrap it in the logic we need for specific applications.

Let’s put the key field (private key) from our JWT setup in a file called server.key. Put the username of the user we want to subscribe (who must be pre-authorized to the Connected App) in the environment variable $USERNAME and the Connected App’s Consumer Key in $CONSUMERKEY.

Then we can get an Access Token to make an API call into Salesforce, letting SFDX do the heavy lifting:

sfdx force:auth:jwt:grant --clientid $CONSUMERKEY --jwtkeyfile server.key --username $USERNAME -a reports-test
export INSTANCE_URL=$(sfdx force:org:display --json -u reports-test | python -c "import json; import sys; print(json.load(sys.stdin)['result']['instanceUrl'])")
export ACCESS_TOKEN=$(sfdx force:org:display --json -u reports-test | python -c "import json; import sys; print(json.load(sys.stdin)['result']['accessToken'])")

(If you have jq installed, you can simplify these one-liners).

Now we’ve established an authenticated session as $USERNAME, even though we do not have that user’s credentials or any setup for that user besides preauthorizing their profile on the Connected App, and we have the values we need (the Access Token and Instance URL) stored in our environment.

Now we’ll switch over to Python. A quick script grabs those environment variables and uses simple_salesforce to make an API call to generate the report subscription.

import simple_salesforce
import os
import sys

outbound_json = """
{
  "active" : true,
  "createdDate" : "",
  "deactivateOnTrigger" : false,
  "id" : "",
  "lastModifiedDate" : "",
  "name" : "New Notification",
  "recordId" : "%s",
  "schedule" : {
    "details" : {
      "time" : 3
    },
    "frequency" : "daily"
  },
  "source" : "lightningReportSubscribe",
  "thresholds" : [ {
    "actions" : [ {
      "configuration" : {
        "recipients" : [ ]
      },
      "type" : "sendEmail"
    } ],
    "conditions" : null,
    "type" : "always"
  } ]
}"""

# Use an Access Token and Report Id to add a Lightning report subscription for this user
# such that the report will run as that user.

access_token = os.environ['ACCESS_TOKEN']
instance_url = os.environ['INSTANCE_URL']

report_id = sys.argv[1]

sf = simple_salesforce.Salesforce(session_id=access_token, instance_url=instance_url)

sf.restful(
    'analytics/notifications',
    None,
    method='POST',
    data=outbound_json % report_id
)

Execute the script

python add-subscription.py $REPORTID

where $REPORTID is the Salesforce Id of the report you wish to subscribe the user for, and then if we log in as that user in the UI, we’ll find a shiny new Lightning report subscription established for them.

Lightning Final Subscription

Note that it’s set for daily at 0300, as specified in the example JSON.

Next Steps

We’ve got a proof-of-concept in place showing that we can in fact schedule results for users run as those users. In an article to follow soon, we’ll look at operationalizing this approach and building out business processes atop it.

Handling URLs in Lightning Data Tables

The <lightning:dataTable> component has built-in support for displaying links in table columns. The syntax looks something like this:

{
    label: 'Case Number', 
    fieldName: 'My_URL_Field__c',
    type: 'url', 
    typeAttributes: { 
        label: {
            fieldName: 'CaseNumber'
        } 
    },
    sortable: true 
}

typeAttributes.label.fieldName identifies a field on each row to utilize as the title of the link, while fieldName at the top level specifies the URL field itself.

In many cases, though, what we have in our sObject data isn’t a fully-qualified URL: it’s a Salesforce Id, a lookup to this record or to some other record, and we’d really like to display it sensibly as a link with an appropriate title. Unfortunately, <lightning:dataTable> doesn’t have an Id column type, and the url type is not clever enough to realize it’s been handed a record Id and handle it.

Instead, we need to generate the URL ourselves and add it as a property of the line items in the source data. (This is a bewildering shift for seasoned Apex programmers: we can just add fields to our sObjects?!) In the callback from the Apex server method querying our sObjects, we generate one or more synthetic properties:

cases.forEach(function(item) {
    item['URL'] = '/lightning/r/Case/' + item['Id'] + '/view';
}

Our column entry will end up looking like this:

{
    label: 'Case Number', 
    fieldName: 'URL',
    type: 'url', 
    typeAttributes: { 
        label: {
            fieldName: 'CaseNumber'
        },
        target: '_self'
    },
    sortable: true 
}

Then, the result’s just what you might think:

Lightning Data Table

The Case Number column is hyperlinked to open the related Case record.

Note that we’re using the Lightning form of the record URL (/lightning/r/<sObject>/<id>/view), and we’ve added the target: '_self' attribute to the typeAttributes map. This results in the link opening in the current tab, in non-Console applications, and loading a new Console tab in Sales or Service Console. The default behavior, if target is not specified, is to open a new browser tab, even in Console applications, which will often not be the desired behavior.

Using the Classic form of the record URL (/<id>) does work, but redirects through a translation URL. For future compatibility, it’s best to just use the current-generation URL format.

This process of synthesizing new fields for <lightning:dataTable> can be repeated arbitrarily, both for URL fields and for other types of calculated data, like icon columns. It’s important to remember, of course, that these synthetic properties cannot be persisted to the server because they’re not real sObject fields. Input to server actions must be constructed appropriately.

The JavaScript’s loosey-goosey type system and object model can be confusing for Apex programmers, but it offers a lot of freedom in return - and the ability to do things with sObjects we’d need a wrapper class to handle in Visualforce.

What Can You `GROUP BY`?

The Salesforce documentation is notably terse in describing Considerations When Using GROUP BY. The guidance provided for determining which fields can be grouped is simply:

The Field object associated with DescribeSObjectResult has a groupable field that defines whether you can include the field in a GROUP BY clause.

This is a rather roundabout way to point to the method DescribeFieldResult.isGroupable(); for example, Account.Description.getDescribe().isGroupable() returns false.

Further, the document states that

You can’t use child relationship expressions that use the __r syntax in a query that uses a GROUP BY clause.

This gives us very little to go on, without examining the Describe information for every single field we might want to group on (doubly challenging in a Dynamic SOQL context). So which field types do, in fact, permit grouping? And does the final sentence prohibit the use of custom relationships in GROUP BY?

Failure to use a properly groupable field yields the error

field ‘FIELD_NAME__c’ can not be grouped in a query call

It turns out that groupability breaks down pretty cleanly along type lines, with a few interesting nuances. The underlying SOAP type appears to be the primary, but not the sole, determinant. Some fields within the same SOAP type differ in groupability based on other facets. Further, some formula fields can be used as groupings - but not the ones you might naively expect from other Salesforce Platform limitations!

Types are listed below by UI type, with the SOAP type in parentheses. This information was derived from inspection of numerous field describes via Workbench and the Tooling API.

Groupable Field Types

  • Checkbox (boolean)
  • Phone (string)
  • Picklist (string)
  • Email (string)
  • Text (string)
  • Text Area (string)
  • URL (string)
  • Number (int). Does not include custom fields, only standard Number fields with SOAP type int, like Account.NumberOfEmployees.
  • Lookup (id)
  • Id (id)
  • Date (date)
  • Direct cross-object references to groupable fields, up to 5 levels from the root object (SOQL limit), as in SELECT count(Id) FROM Contact GROUP BY Account.Parent.Parent.Parent.Parent.Name. Both custom and standard references are groupable.
  • Formulas of type Checkbox and Date, including cross-object formulas across standard and custom relationships.

Non-Groupable Field Types

  • Address Compound Fields
    • Components of Address compound fields are groupable if their types otherwise allow it.
  • Geolocations, both custom and standard, and whether or not defined as having decimal places, including the compound field and components (location/double)
  • Long Text (string)
  • Rich Text (string)
  • Auto Number (string)
  • Multi-Select Picklist (string)
  • Number (double), including custom Number fields with or without decimal and regardless of scale.
  • Percent (double), including custom Percent fields with or without decimal and regardless of scale.
  • Currency (double), including custom Currency fields with or without decimal and regardless of scale.
  • Roll-Up Summary Fields (double), including COUNT rollups.
  • Encrypted Text Fields (Classic Encryption; string)
  • Date/Time (dateTime)
  • Time (time)
  • Formulas of types other than Checkbox and Date, including the otherwise-groupable String type.

This post grew out of an interesting question on Salesforce Stack Exchange. I was intrigued by the lack of definition to this facet of SOQL and spent some time putting together a community wiki answer, which revealed that my original answer was mistaken: GROUP BY is stranger than I thought.

Dreamforce '18 Retrospective

Dreamforce ‘18 featured some truly outstanding sessions on the next generation of Salesforce technologies and development practices. Standouts included the excellent sessions showing how technologies like Platform Events, Change Data Capture, Unlocked Packages, and Force-DI lead to modular, loosely-coupled, and event driven Salesforce applications.

My session, Continuous Integration and Salesforce DX: Concepts and Connections, had over 350 registrants and yielded some excellent and important questions about moving to Salesforce DX practice. It was a great experience, and the talk is now available on YouTube.

Dreamforce Session Information

I successfully completed the Salesforce Data Architecture and Management Designer certification, which means I’ve reached the Certified Application Architect level. This was my primary goal for 2018 and I’m thrilled to complete one half of the Architect pyramid. On to System Architect, and Dreamforce 2019!

Application Architect

Continuous Integration and Salesforce DX at Dreamforce '18

I’m thrilled to be returning to Dreamforce next week as a speaker for the second time.

Dreamforce Session Information

If you’re interested in adding continuous integration with Salesforce DX to your development lifecycle, join me for a technical, conceptual dive into CI architecture and practice.

Bookmark the session here.