There are three types of visualization APIs defined in the SAS Viya REST API reference documetation: Reports, Report Images and Report Transforms. You may have seen the posts on how to use Reports and Report Images. In this post, I'm going to show you how to use the Report Transforms API. The scenario I am using changes the data source of a SAS Visual Analytics report and saves the transformed report.
Overview of the Report Transforms API
The Report Transforms API provides simple alterations to SAS Visual Analytics reports, and it uses the 'application/vnd.sas.report.transform' media type for the transformation (passed in the REST API call header). When part of a request, the transform performs editing or modifications to a report. If part of a response, the transform describes the operation performed on the report. Some typical transformations include:
- Replace a data source of a report.
- Change the theme of a report.
- Translate labels in a report.
- Generate an automatic visualization report of a specified data source and columns.
To use the Transforms API, we need to properly set the request body and some attributes for the transform. After the transform, the response contains the transformed report or a reference to the report.
Prepare the source report
This step is very straight-forward. In SAS Visual Analytics, create a report and save it to a folder (instructions on creating a report are found in this video). For this example, I'll use the 'HPS.CARS' table as the data source and create a bar chart. I save the report with name 'Report 1' in 'My Folder'. I'll use this report as the original report in the transform.
Generate the request body
I will use PROC HTTP to call the Transforms API using the 'POST' method and appending the URL with '/reportTransforms/dataMappedReports'. The call needs to set the request body.
- Get the ReportURI: In an earlier post I outlined how to get the reportURI via REST API, so I won't go into details. If you'd like an easy way, try this: in SAS Visual Analytics, choose 'Copy Link…'item from the menu. In the pop-up dialog, expand the 'Options' and choose 'Embedded Web Component', and you see there is a string in the form reportUri='/reports/reports/…', that's it. In the request body, we set the string to the 'inputReportUri' to specify the original report - the 'Report 1'.
- Decide on changes to the data source: Here I’d like to change the data source from ‘HPS.CARS’ to ‘CASUSER.CARS_NEW’. The new table uses three columns from ‘HPS.CARS’ as mapped below.
- Specify the data sources in the request body: The request requires two data sources, 'original' and 'replacement', respectively, representing the data sources in original report and the transformed report. Note that the 'namePattern' value is used to enumerate the way of identifying the data source. If it is set to 'uniqueName', the data source is identified by its unique data item name in the XML file of the report. If it is set to 'serverLibraryTable', the data source is identified by the CAS Server, CAS Library and Table names together. The snippets below show the data source section in the request body. I like to use the 'serverLibraryTable' to specify the data source for both original and transformed report, which is clear and easy.
- useSavedReport specifies whether to find the input (original) report as a permanent resource. Since I am using the saved report in the repository, I will set it to true.
- saveResult specifies to save the transformed report permanently in the repository or not. I am going to save the transformed report in the repository, so I set it to true.
- failOnDataSourceError specifies whether the transform continues if there is a data source failure. The default value is false, and I leave it as such.
- The validate value decides if the transform will perform the XML schema validation or not. The default value is false, and I leave it is as such.
/* data source identification for original report */ { "namePattern": "serverLibraryTable", "purpose": "original", "server": "cas-shared-default", "library": "HPS", "table": "CARS" } /* data source identification for transformed report */ { "namePattern": "serverLibraryTable", "purpose": "replacement", "server": "cas-shared-default", "library": "CASUSER", "table": "CARS_NEW", "replacementLabel": "NEW CARS", "dataItemReplacements": [ { "originalColumn": "dte", "replacementColumn": "date" }, { "originalColumn": "wght", "replacementColumn": "weight" }, { "originalColumn": "dest", "replacementColumn": "region" } ] } |
Set more attributes for transform
Besides the request body, we need to set some other attributes for the transform API when changing the data source. These include 'useSavedReport', 'saveResult', 'failOnDataSourceError' and 'validate'.
Decide on a target report and folder
I'll save the transformed report with the name 'Transformed Report 1' in the same folder as the original 'Report 1'. I set the 'resultReportName' to 'Transformed Report 1', and set the 'resultReport' with 'name" and 'description' attributes. I also need to get the folderURI of the 'My Folder' directory. You may refer my previous post to see how to get the folderURI using REST APIs.
Below is the section of the settings for the target report and folder:
"resultReportName": "Transformed Report 1", "resultParentFolderUri": "/folders/folders/cf981702-fb8f-4c6f-bef3-742dd898a69c", "resultReport": { "name": "Transformed Report 1", "description": "TEST report transform" } |
Perform the transform
Now, we have set all the necessary parameters for the transform and are ready to run the transform. I put my entire set of code on GitHub. Running the code creates the 'Transformed Report 1' report in 'My Folder', with the data source changing to CASUSER.CARS_NEW', containing the three mapped columns.
Check the result
If the API failed to create the transformed report, the PROC SQL statements returns error codes and messages. For example, if the replacement data source is not valid, it returns errors similar to the following.
If the API successfully creates the transformed report, “201 Created” is displayed in the log. You may find more info about the transformed report from the response body of tranFile from PROC HTTP. You can also log into the SAS Visual Analytics user interface to check the transformed report is opened successfully, and the data sources are changed as expected. Below is the screenshot of the original report and transformed report. You may have already noticed they use different data sources from data labels.
Finally
There are a wealth of other transformations available through the Report Transform APIs. Check out the SAS Developer site for more information.
14 Comments
Hello,
Thanks a lots for this page which is helping us but do you know if we can find somewhere an example about changing the data sourceS of a report? I put the S in upcase because it is OK with only one but we are facing problems when having more than one to update. If I try to update 2 datasources, I don't find samples describing the correct order ({OLD1},{OLD2},{NEW1},{NEW2} or {OLD1},{NEW1},{OLD2},{NEW2} , etc...) and I have syntax errors. And if I decide to have only {OLDx},{NEWx} in order to update all one by one, it seems that after the first update I keep only the updated datesource and loose the other ones..
Hi Frank,
Someone asked similar questions offline years ago. For now, the api only exposes the support mapping one data source at one time. So I would suggest you do some coding to repeatedly replace one data source and iterate for other data sources in your report. You may get the cas data sources of your report by running below codes. Hope this helps!
-----
%let BASE_URI=%sysfunc(getoption(SERVICESBASEURL));
FILENAME rptFile TEMP ENCODING='UTF-8';
PROC HTTP METHOD="GET" oauth_bearer=sas_services OUT=rptFile
URL = "&BASE_URI/reports/reports/ffb113b7-9cfa-434b-99f1-9b3fe5a9f340/content";
HEADERS "Accept" = "application/vnd.sas.report.content+json";
RUN;
LIBNAME rptFile json;
data datasources;
set rptFile.datasources_casresource;
run;
------
Hi,
it's a great entry. thank you a lot.
I would like to copy a report from a folder to another in batch mode.
How can we do it with rest API ? I didn't find the good rest function to do that.
regards,
Pascal
Hi Pascal,
To copy a report from a folder to another in batch, SAS Viya provides the CLI (Command-line Interfaces) to export and import reports easily.
If you need to use restAPI, you have to do some coding work. Please refer the Folder API in CoreServices, such as get a folder member, add a member to a folder or update a folder member. I put the link for your quick reference: https://developer.sas.com/apis/rest/CoreServices/#get-a-folder-member
Hope this helps.
Hi Cindy,
Thank you for your answer.
I'm going to try with the CoreServices API.
The export / Import of CLI works correctly if we don't need to change the folder path of report.
Regards,
Pascal
I have an existing report, created by another user, that I'd like to run from code and export to Powerpoint via ODS.
Currently we are using the SAS Powerpoint Add In to import reports and it is very slow.
I know how to retrieve the URI of a report, thanks to your code. But it doesn't seem as though there is a way to use that URI to run the report programmatically, say from an HTML form. I'd be curious to know if you think this can be done.
Rick, surely you can open the report from some HTML form, by using the SAS VA SDK. I paste a simple html below (replacing the and with yours.). Note, the authenticationType is guest, and you need the report be accessible by the guest. For more, please refer to https://support.sas.com/content/dam/SAS/support/en/sas-global-forum-proceedings/2020/4309-2020.pdf.
-----
<sas-report authenticationType="guest" url="http://" reportUri="">
Seems my simple html codes was "eaten" by the site. Please refer to this https://github.com/sbjciw/RESTAPI/blob/master/4Rick.html.
Thanks so much for doing that. I tried your code with our SAS server and the report URI pasted in, but got 'Object not found.'
I'm double checking with our SAS admin to make sure that the report is accessible by guest.
Make sure the reportUri="" string contains the leading '/reports/reports/', I believe VA SDK would work if all settings are correct.
If you are sure the report is accessible by guest, check if the needed configuration is set on the SAS server. Please refer to the 'SAS VIYA SETUP' section in the https://support.sas.com/content/dam/SAS/support/en/sas-global-forum-proceedings/2020/4309-2020.pdf.
It does have '/reports/reports/' in the string, but I'm still finding out about guest access. This is the actual error message that comes up in the HTML form:
{"errorCode":0,"message":"The file could not be found.","details":["traceId: 120735edfcbd52f6","path: /files/files/cf71003c-dde1-4503-8fc2-189e292335e0/content"],"links":[],"version":2,"httpStatusCode":404}
Thank you again.
Never happened to me, seems the content was not found from the error message.
I would advice to make sure you get the correct reportUri. Please follow the description above the screenshot of 'Report URI from SAS Visual Analytics' in this article. Use the copied reportUri from that to replace the reportUri="" in the https://github.com/sbjciw/RESTAPI/blob/master/4Rick.html.
Could you post the complete code for this either here or on GitHub? Thank you.
Rick, you may find the link for complete code in the 'Perform the transform' section in the article. Or, the direct github link is: https://github.com/sascommunities/sas-users-blog/blob/master/va-reports-transform/report_transform_datasource.sas.