Schedule and Run a Report as an specific User from Apex

Question

Background

What we are trying to accomplish is to run daily some specific reports as an specific user.

So far

From a lightning component we are allowing the user to select a reports list. Once the user saves that list we are scheduling our apex class which will run those report, since the schedule is created as that user, it will run ok. The running report code is something like:

        Reports.ReportMetadata reportMd = ReportHelper.getReportMetadata(reportId);
        Integer aggregateFieldPosition = ReportHelper.getAggregateFieldPosition(reportMd, aggregateFieldName);
        Reports.ReportFilter filter = reportMd.getReportFilters()[0];
        filter.setValue('some filter');
        Reports.ReportResults results = Reports.ReportManager.runReport(reportId, reportMd);

That works fine on a simple context.

Problems

• We potentially would have more than 1k users that will run 5 reports each.
• The is a limit of 200 Schedule Apex Jobs
• Schedule Apex Jobs have a failure rate where they are canceled or deleted by the platform. We could have a monitor process to re-schedule them but that will run as system user.
• We can not ask to create user filters since most of users are sharing the same 5 corporate's reports and them are using the org sharing model.
• We have a good design but is based on the existence of RunAS() method. That does not exist.

Questions

1. Is there any workaround to run reports as any user. Maybe through Dashboards or Reports API ?
2. What is the max number of schedule jobs recommended to avoid platform weird behaviours.
3. Any other idea of running several reports from apex considering the sharing model ?

Solution POC

I will add here the solution made using the guidance of the incredible answer given by @David Reed (Thanks a lot)

After create a connected app and give the users a permission set that is allow in that connected app, the sample code is like:

//get the accessToken by username using JWT standard class
Auth.JWT jwt = new Auth.JWT();
jwt.setSub(username); 
jwt.setAud('https://login.salesforce.com');         
jwt.setIss(consumerKey);
Auth.JWS jws = new Auth.JWS(jwt, certificateName);
String token = jws.getCompactSerialization();
String tokenEndpoint = 'https://login.salesforce.com/services/oauth2/token';
Auth.JWTBearerTokenExchange bearer = new Auth.JWTBearerTokenExchange(tokenEndpoint, jws);
String accessToken = bearer.getAccessToken();

//call the report api using that credentials
Http h = new Http();
HttpRequest req = new HttpRequest();
req.setMethod('GET');
req.setEndpoint('https://jwt-rocks-dev-ed.my.salesforce.com/services/data/v44.0/analytics/reports/'+reportId+'?includeDetails=false');
String authorizationHeader = 'Bearer  ' + token;
req.setHeader('Authorization', authorizationHeader);
req.setHeader('Content-Type', 'application/json');
HttpResponse res = h.send(req);
string resData =  res.getBody();


//process the result
parseReportResponse(resData);

And that's it :)

Answer 1

I want to sketch out a possible design, as this is a question I've been turning over myself. For the record, I have not had time to build this out myself, even as a proof of concept, but I think something along these lines would work. See proof of concept at the end of this post.

As an introduction from a previous question of mine on a similar topic:

The Classic Schedule Future Runs functionality is represented under the hood as CronTrigger and CronJobDetail records, which cannot be created programmatically.

I've also looked into creating Lightning report subscriptions using the Analytics [Reports and Dashboards] API. However, that API appears to only create subscriptions for the logged-in user, and the API returns an error if a subscription already exists for the logged-in user. This makes it impossible to subscribe other users individually using the API.

The most promising avenue appears to be the Reports and Dashboards API, because it offers an endpoint to create an Analytics Notification (a Lightning report subscription). The maximum subscription count for a user is 5.

This endpoint offers a phantom of hope for an easy solution, in the sense that it allows you to add recipients of the report notification. The problem is that you, like me, are relying on the org's visibility settings to control the report content - you need each outbound report notification to be based on the user's own record-level access, not some other user's.

Since the Reporting and Analytics API doesn't support setting the context user for the subscription, we're stuck with controlling the user as whom we authenticate to the API.

With 1,000+ users to schedule, creating individual Named Credentials to authenticate as each user back into the same instance does not seem like a fruitful approach. Instead, I'm suggesting the one route of which I'm aware that does not require each user to individually authenticate and approve access: the JWT OAuth flow. I'll rough out below what this architecture would look like, using an external script to authenticate and update report subscriptions in Salesforce.

I'm quite honestly unsure of whether you could do this in pure Apex or not; I've never tried to implement JWT from scratch. I would certainly be concerned about secret storage if attempting to build this all on-platform.

On Platform: UI and Data Model

First, though, the UI side. Have your Lightning component persist the user's report selections into a custom object - call it Report_Subscription__c. Include the details (API name) of the selected reports on that object, and a status ('New', 'Current', or 'Cancelled'). Make the Org-Wide Default Private.

Create a Connected App and configure it for JWT authentication in Salesforce, in the same fashion as you would for using SFDX for continuous integration.

Add the profiles of each of the users who are to be subscribed to the Connected App as a pre-approved Profile, or assign all of those users to a Permission Set and assign that Permission Set as pre-approved on the Connected App.

Additionally, ensure that some designated administrator or API user is pre-approved on the Connected App. This user will be employed by the scripts to query for users who need subscriptions maintained, and should have View All permission on Report_Subscription__c (or a superseding permission like View All Data).

Off Platform: Subscription Maintenance Scripts

Then, the backend scripts. This involves some off-Salesforce machine.

You can use SFDX to authenticate with JWT and get an access token, just like one does in setting up a Continuous Integration pipeline, or you can implement JWT within your own application. You'll need to do this repeatedly - once for each user to be subscribed, and once at the beginning of the process to locate all of the users who are to be subscribed.

The first job of the script is to authenticate to Salesforce as your administrative user. It should query for all Report_Subscription__c records and the usernames of their owners.

Then, it must iterate over the users who have these records present.

For each user, it authenticates to Salesforce as them, again using JWT authentication against our Connected App. For each of that user's Report_Subscription__c records that is in status 'New', it should call the Reports and Dashboards API to initiate the subscription as the current user, and update the record to 'Current'. For each that is 'Cancelled', it should call the Reports and Dashboards API to cancel the subscription, and delete the Report_Subscription__c record.

This script or scripts will need to run on a regular basis, perhaps nightly. You could do this in a number of ways:

• Run manually on a local machine.
• Run in a cron job on your on-prem or cloud server.
• Run in Heroku.
• Repurpose a Continuous Integration pipeline on the Git repository where your scripts are kept to execute it on a schedule (for example, on CircleCI using scheduled workflows).

The key here is that you have to be able to protect your secrets - the private key file for your JWT authentication. I'm most familiar and happy with doing that in a CI context, where you encrypt the key file in your repo and make the passphrase available only through environment variables. But the actual architecture can take a number of different forms.

Personally, I would probably do this as two Python/simple_salesforce scripts, calling out to SFDX to handle the authentication and grabbing the auth token out of sfdx force:org:display, and I'd have my CI solution execute the script every night, because that's the most efficient use of the architecture I already work with. But there's a lot of ways to get it done, and your organization's architecture (and security needs) will dictate a lot of those choices.

As I mentioned, I've not done the full build-out of an app like this, but I've been thinking about it recently and I believe it's fundamentally feasible. (Edit: I've done a small PoC, below). Feedback and critique would be quite welcome.

Proof of Concept

I've thrown together a tiny demo that this approach is practical.

Get your Connected App setup for JWT and place your server key in a file server.key. Put the username of the user you want to subscribe (must be pre-authorized to the Connected App) in $USERNAME and your Connected App's Consumer Key in $CONSUMERKEY. Then run

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'])")

That establishes 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.

Then execute this Python script

python add-subscription.py $REPORTID

where $REPORTID is the Salesforce Id of the report you wish to subscribe the user for, and add-subscription.py is the following script:

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
)

And then if we log in as that user in the UI, we'll find a shiny new Lightning report subscription established for them. Note that it's set for daily at 0300, as specified in the example JSON.

Limits Notes

The Reports and Dashboards API has relatively low limits. As noted above, your users can only subscribe to 5 reports. If you're actually running the reports yourself via the API, be aware of the limits involved (trimmed here):

• The API can process only reports that contain up to 100 fields selected as columns.
• Your org can request up to 500 synchronous report runs per hour.
• The API supports up to 20 synchronous report run requests at a time.
• A list of up to 2,000 instances of a report that was run asynchronously can be returned.
• The API supports up to 200 requests at a time to get results of asynchronous report runs.
• Your organization can request up to 1,200 asynchronous requests per hour. Asynchronous report run results are available within a 24-hour rolling period.
• The API returns up to the first 2,000 report rows. You can narrow results using filters.