Your impressive VA report may have a lot of calculated dataitems and it can be useful to document the calculations in some way or other. Luckily there is a way to extract those programmatically using the Visual Analytics Reports REST API: Reports | SAS for Developers More specific we're going to use the REST endpoint for report content: getContent | SAS for Developers The report content is the JSON file that defines everything in your report. The fileformat of this JSON file is not documented, but we will use the JSON libname engine to easily convert it into a set of tables that can be examined and queried. Let's get back to the endpoint itself: GET /reports/{reportId}/content In order to use this endpoint we need the {reportId}. This can be found using the content panel in the SAS Environment Manager by navigating to your report and look at it's URI under it's details:
If you have a browser where you are already logged into your Viya environment you can try to get the content of the report by using and URL like this (substitute with your hostname and the reportID of your report) which will return the JSON definition of your report to the browser:
Well, enough of the explanation for now, let's look at the code:
/*************************************************************************************
*
* Example program that will get the report definition of a given VA report and
* turn that definition into a data structure (a set of tables in a SAS library).
* It will then query a couple of those tables (if they exists) to obtain all dataitems
* in the data panel in the VA report, including the expression used by the report designer
* for any calculated items.
*
*
*/
/* Get the hostname and root of the Viya server. Used as a prefix to the URIs to build the REST API endpoints */
%let BASE_URI=%sysfunc(getoption(SERVICESBASEURL));
/* A file to store the JSON file containing the report definition. * The "temp" indicates that it will use the folder where your WORK library lives.
* Consequently this file will be deleted when your session closes.
*/
filename rcontent temp;
/** assign the report URI for the report you want to analyze in the reportURI macrovariable -
* the report URI is on the form: /reports/reports/<reportID>
* This can be found in the "Share and collaborate" application under properties for the report.
* It can also be found by going into "Manage Environment"->"Content" and navigate to the report and select it.
* The URI can then be found under the details.
*/
%let reportURI=/reports/reports/07636979-26e7-4e89-beed-1f69350cfbd6;
/**
* This is the proc http request to the REST endpoint.
* Don't be fooled by the proc http name, the BASE_URI macro contains the protocol to be used as well
* most likely for your environment Viya is configured with SSL and hence BASE_URI starts with https://
*
* The oauth_bearer=sas_services tells it to use your current login/session that you got when you
* logged on to Viya.
* If you want to run this program from elsewhere you would need to handle the oauth tokens yourself.
*
* The /content after the report URI is the REST endpoint for getting the reports content (read: the report
* definition as a json string).
*
* out=rcontent indicates that we want to store the response string (jspon) into the rcontent filename we
* defined earlier.
*/
proc http oauth_bearer=sas_services method="GET"
url="&BASE_URI.&reportURI./content"
/* place response in filenames */
out=rcontent;
headers "Accept"="application/vnd.sas.report.content+json";
run;
/* If the library rcontent already exists in this session the line below will clear the library -
* You do not want remnant tables from a previous run linger and mix with tables from a new run on
* a different report.
*
* If your the library don't exists (ie. your first run of this program in this session) the line below
* will generate a warning in the log.
*/
libname rcontent clear ;
/* Use the JSON libname engine to connect to the JSON file and have it's content be
* represented as a set of tables found in the rconnect library
*/
libname rcontent json;
/* A left join betwen the table businessitemsfolder_item (that contains all dataitems in the report)
* and items_editorproperties that contains the user-written expressions for calculated items.
* If your report does not contains any calculated items this would fail as the items_editorproperies
* tables will not exist.
*
* The %if %syfunc(exists()) call checks if the tables exists before executing the code
* between %then %do and %end.
*/
%if %sysfunc(exist(rcontent.items_editorproperties)) and
%sysfunc(exist(rcontent.businessitemfolder_items)) %then %do;
/* Quick summary of some of the columns in the resulting work.dataitem dataset:
* The name column contains the internal pointer to the data element,
* xref refers to the name the data element have in the source table,
* label refers to the name the dataitem is given in VA.
* Calculation contains the Viya expression while the
* expression columns contains the expression the report designer entered in the
* expression editor in VA.
*/
proc sql;
create table work.dataitems as
select a.*, b.value as expression
from rcontent.businessitemfolder_items as a left join
rcontent.items_editorproperties as b
on a.ordinal_items=b.ordinal_items
order by a.ordinal_items;
quit;
%end;
A natural extension of this would be to have a program traverse through all your reports and document the dataitems along with the report path and name. This blog post would a good starting point for this: Discover Visual Analytics Report Paths with REST APIs - SAS Users
... View more
