The previous examples used SAS jobs to create the visual output to be displayed in the SAS Visual Analytics (VA) report. This definitely expands the possibilities, but we have to admit that the static nature of ODS output may not meet interactive requirements, and at minimum, it doesn’t fit well with the overall VA report look & feel.   This article will introduce a very creative way of leveraging SAS jobs to extend the VA capabilities, not as much to visualize the data, but to manipulate the data or enhance it with new calculations before it’s visualized in VA. There are two ways this can be done: The SAS job streams back a table to the DDC (Data-Driven Content), most likely in JSON format using PROC JSON, and the DDC uses a third-party visualization package to display the results. This isn’t much different from the way that DDC’s are normally used. The key difference is that the data that the DDC displays is passed to and transformed by the job, instead of coming directly from VA. The SAS job stores the processed resulting table in memory (CAS server), and VA uses that table as any other source table that has been loaded in memory. This allows for using out of the box VA objects to visualize the data, perfectly blended into the report. Because of the similarities of #1 above with traditional usage of DDC, we are not going to explore that option. The only thing that might be new to you in this approach is the part that streams data in JSON format back to the DDC, but you will see some of that happening in option #2.     HelloCASWorld Example   This example is built on top of the example called HelloBigWorldFormatted, introduced in the previous article. As said above, it will produce a table loaded in CAS so it can be explored in VA as any other in-memory table. In addition to that, the example will also explore a few ideas for enhancements: Treat errors that might occur in the SAS job. This is especially important because the job doesn’t produce any visual content, and therefore any feedback to the DDC and ultimately to the VA report about the status of the job execution is very important. Make the job reusable in many different reports by passing the name of the table to be loaded in memory as a parameter in the JSON message that VA sends to the DDC and the DDC sends to the job. Make the DDC reusable with any job that produces a CAS table as the output by passing to it parameters such as the name of the job to be executed and a message to be displayed while the DDC waits for the job to execute. Those changes make the DDC to work as a proxy, where its solely function is to make the connection between VA and the job. Instead of exploring this new example with all the enhancements added at once, you will first be introduced to the basic version of the job and the DDC code that are able to load a table in memory, followed by the final enhanced version with all the bells and whistles.   SAS Job Code (first pass)   This job is very similar to the one called HelloBigWorldFormatted. Starting with that job, you need to add a few extra lines in the initialization portion of the code to account for connecting to CAS and setting the CASUSER libname:   Picture 1- Setting up CAS session and library   Also, in the main processing portion of the code you need to replace the PROC PRINT statement that generates the ODS output with the code that loads the output table in memory:   Picture 2- Saving output table to CAS   The CASUSER output table name is fixed in this implementation, but obviously it would be nice to parameterize this table name, so that you could reuse this same job with multiple VA reports if needed, each one of them writing to its own table. This parameter could be set in the DDC code, but them you would have the same problem (the DDC could only be used in one VA report) or you could set this parameter in the VA report and pass it to the DDC, which would then be passed to the job. This is exactly what you will see in the enhanced implementation later in this article.   Finally, you need to terminate the CAS session created in the initialization phase:   Picture 3- Terminating CAS session   Data-Driven Content Code (first pass)   The changes necessary in the DDC code are also minimal. Considering that the job no longer sends information back to the DDC, you basically need to adjust the actions taken once the function callJob() finishes execution. Because this DDC does not have any job’s output to display, it displays some job status messages during its execution and clears the message when the execution ends:   Picture 4- Modified pre and post callJob() execution to inform job status   SAS Visual Analytics Report (first pass)   The report in this example looks like the following:   Picture 5- VA report (first pass)   The List Table at the top was added just for your reference. It is based on the input CARS data source, loaded in memory from the SASHELP library and displays what’s expected as output from the DDC/job. The object in the middle is the DDC. It doesn’t contain any visible output, except for a few status messages while the DDC/job are being executed. The List Table at the bottom uses the table loaded in the CASUSER library that the job created. The Button Bar at the very top of the report has actions to filter the first List Table and the DDC objects, so you can filter the data and see the job in action.   There are a few additional things about the VA report that should be highlighted:   When designing the VA report, you must remember that the job’s output table in the CASUSER library doesn’t exist until you execute the job successfully at least once. Only after the table is available in the CASUSER library, you can add it into the report and use it as the source of other VA report objects, like the List Table at the bottom. Tables from CASUSER library that are added to VA reports keep reference to the report author. This reference must be removed before the report is shared with a broader audience. As the report designer, you do that by editing the report BIRD XML: a. Save the VA report b. Hit Ctrl+Alt+B to open the SAS Visual Analytics Diagnostics window c. Make sure the BIRD tab across the top and the XML button are selected (these are the defaults) d. Click on the Data icon on the left e. Scroll down searching for the reference to the CASUSER library f. Remove (<userid>) that is appended to the CASUSER library   Picture 6- Removing userid from CASUSER g. Click the Load button on the top left h. Save the VA report   If you have the right privileges for exporting and importing content in SAS Environment Manager (menu option Manage Environment), you can replace all the steps above by exporting the report and them reimporting it. You just need to replace the target CASUSER(<userid>) library with just CASUSER in the GUI during the import process. You must set automatic refresh in the objects that depend on the table loaded in the CASUSER library, otherwise they will not refresh as the data changes with the job execution. You do that in the object’s Option tab by turning on the option labeled either “Automatically refresh object” or “Periodically reload object data” depending on the VA release. In this example, the object that needs automatic refresh is the List Table at the bottom of the report. Remember that automatic refresh only works when the report is in view mode. Picture 7- Setting up auto-refresh in all objects that depend on the table created by the job   SAS Job Code (with enhancements)   Although the previous implementation works just fine, it lacks some basic capabilities to make it more robust and flexible.   First, what if there is an issue with the job’s logic and it fails? The HTTP call to the job would be successful, but the results produced by the job would be incomplete or incorrect. In order to better respond to this, you would have to make the job a bit more intelligent by treating errors on every step of the process and inform the DDC if something happens.   Second, as discussed previously, what if you need to replicate the job functionality in another VA report? If you simply reuse the same job, you will end up with two reports writing to the same CASUSER table. A simple solution would be making a copy of the job just to replace the name of the output table that is hard coded, but that could create future maintenance overhead. The best solution is to parameterize the name of the output table that the job creates. Working towards a more robust job code, you could test for the existence of this parameter (macro variable CASTAB in this example) and issue an error in case the parameter doesn’t exist.   You will do those things by defining two macro functions. One called stopOnError that sends back a JSON message to the DDC in case of an error, and one called checkParams that validates the existence of expected parameters:   Picture 8- Macro functions to check job execution status and validate input parameters   Below is an example of calling the stopOnError macro function after each step in the process:   Picture 9- Checking job execution status after each step   The JSON message produced by the PROC JSON in this example has three key-value pairs: success: set to true or false depending on the job executed successfully or not retcode: SAS’s pre-defined macro variable SYSERR in case of error or 0 (zero) if successful message: text message containing additional information about the status of the execution If the job execution reaches the end, you should send a JSON message back to the DDC saying the job executed successfully:   Picture 10- Sending a JSON message back to the DDC informing the job executed successfully   With the CAS table being parameterized, the main processing steps would need to be re-written to replace the fixed table name with the macro variable &CASTAB:   Picture 11- Adapting the SAS code to name the output CAS table according to the parameter   Data-Driven Content Code (with enhancements)   In the enhanced DDC code you need do the following: Retrieve the parameter containing the name of the job’s output table that came in the message received from VA and pass it to the job. That will become the macro variable CASTAB you have just seen. Retrieve additional parameters from the VA message such as the name of the job to be executed and the text message to be displayed while the DDC waits for the job to execute and replace them where appropriate. This is to work towards making the DDC code configurable and reusable for multiple use cases. Check for possible error messages coming from the job by checking the job’s returned JSON message {success: …, retcode: …, message: …} For the first two items above, you can leverage the function getVAParameters() that is available in the GitHub utility library called util/contentUtil.js.   Picture 12- Adding contentUtil.js utility script file   You need to modify the onDataReceived() callback function to retrieve the parameters from VA, validate them, and send a message back if something is missing. That’s implemented with the code highlighted in yellow at the beginning and the bigger block of code in the middle, as indicated below. You also need to modify the code that verifies the response received from the job and sends the proper messages back to VA, done in the highlighted block at the end. The line that calls the va.contentUtil.initializeSelections() function is just a best practice to remove eventual columns that indicate brushing, which doesn’t make sense in a DDC that produces an in-memory table as the output:   Picture 13- DDC JavaScript code with enhancements   Observe in the last highlighted block above that when the job finishes and execution resumes to the callJob() function, you are informing the status of the job execution. If the execution succeeds, a temporary message “Done!” that lasts only 2 seconds is displayed, before it’s replaced with an empty string.   Considering that the job now needs the name of the output CASUSER table to be passed as a parameter called CASTAB (not case sensitive) and the job returns JSON back to the DDC, the callJob() function also needs to be slightly modified to account for those:   Picture 14- Modifications in callJob() function to pass the name of the output table as parameter and receive execution status information   Observe that the job parameter _output_type is set to JSON, and the “Accept” field in the header of the HTTP call is set to application/json, matching with what the job is returning to the DDC. With those modifications, the DDC is generic enough to work as a proxy for launching jobs that produce non-visual output.   Just to recap, the DDC receives from VA the data (aggregated table) and three parameters: _job_name – the name of the job to be executed _job_executing_message – the message to be displayed while the job is being executed _job_output_cas_table – the name of the table to be created in CASUSER library (this is the only parameter passed down to the job) The data is uploaded to the server and the name of the table to be created is passed to job. The job reads the uploaded table, processes it, and saves the output table with the desired name. The job returns a JSON object with the execution status back to the DDC that interprets it and displays the proper message.   SAS Visual Analytics Report (with enhancements)   The report in this example looks exactly as the previous one and the same observations made about that previous report apply to this one, but in addition, this enhanced version of the report must have the following:   You must define at least the parameters _job_name and _job_output_cas_table in the VA report and assign a value to them. The parameter _job_executing_message is optional. All of them are character parameters and their names are case sensitive. For this example, the following values were assigned: _job_output_cas_table: "CARS_COPY" _job_executing_message: "Waiting for job to finish..." _job_name: "/Public/Jobs/SAS Communities/HelloCASWorld"   Picture 15- Example of VA parameter indicating the name of the output table to be created  2.  VA parameters are only passed in the JSON message to the DDC if the parameters affect the data that is being passed to the DDC. This is further explained in the article Using parameters with Data-Driven Content in SAS Visual Analytics, and in this example you guarantee that all three parameters are being passed to the DDC by setting a dummy advanced filter in the DDC object that looks like this: Picture 16- Dummy advanced filter applied to the DDC object to guarantee the parameters are being passed in the VA message     Deploying This Example   All files used in this example are available for downloading from the GitHub project sas-visualanalytics-thirdpartyvisualizations, under folder called samples/IntegrationWithSASJobs.   It requires the CARS table to be loaded into memory in the Public caslib. The table is originally available in the SASHELP library.   Deployment steps: Download the GitHub project Unzip it into your Web Server document folder. These instructions assume you have unzipped the GitHub project in a folder called github in the Web Server document root folder. You will only need the content of folders github/utils and github/samples/IntegrationWithSASJobs. Logged into SAS Job Execution Web application, do the following: a. Create a new job in a folder of your preference (e.g. /Public/Jobs/SAS Communities) and name it HelloCASWorld (see first article for more details if needed) b. Open the job for edition, copy & paste the content of file github/samples/IntegrationWithSASJobs/3.HelloCASWorld/HelloCASWorld.sas c. Save the job d. Add job parameter _action=FORM as discussed in the first article e. Create a new job form either as a separate content or attached to the job (SAS Visual Analytics 8.5 or above only), as explained in the first article. If it’s a separate content, give it the same name as the job f. Open the job form for edition, copy & paste the content of file github/samples/IntegrationWithSASJobs/3.HelloCASWorld/HelloCASWorld.html  (1) g. Make changes to the host name (search for your.host.name and replace it accordingly) and path of src on lines 20, 21, and 22 (1) :   Picture 17- Modify src appropriately h. Save the job form   Logged into SAS Visual Analytics, do the following: a. Create a new empty report b. With the report opened, hit Ctrl+Alt+B to bring the SAS Visual Analytics Diagnostics window. c. With the BIRD tab selected across the top (that’s the default), replace the BIRD XML content with the content of the file github/samples/IntegrationWithSASJobs/3.HelloCASWorld/VA-DDC-Job Hello CAS World.xml d. Hit Load (this will close the diagnostics window) e. With the DDC object at the bottom of the report selected, go to the Options pane on the right and fix the URL entry: replace your.host.name accordingly and make sure the path to the job is correct (same value entered in #3a) f. Make changes to the values assigned to the parameter _job_name if necessary, so it matches with your environment (same value entered in #3a) g. Save the report   Note: (1) Starting with release 2023.06, the examples that used to inherit jQuery from their parent (the SAS Visual Analytics web application) no longer work, so we have provided replacement codes. These replacement files have the same names as their original, but they end with .v4. Because of that, some references to line numbers may not exactly match on those .v4 files.   We could have exported all the content as a package, but this would require special privileges in order to import it. Sharing the example as standalone files will give you the opportunity to better explore the SAS Job Execution Web application, familiarize yourself with the content, and understand how they are connected.   Note: The first time a user opens this report it fails because the CASUSER table doesn’t exist yet. Reopening the report will work, as well as any subsequent access to the report. This will happen to any other report that depends on a table that is being dynamically loaded in memory when a user opens the report for the first time.     Next Steps   This example showed how jobs can be used to load tables in memory, but in fact, the output table can be stored anywhere, including external files, relational databases or any other storage type your SAS environment has access to. You can also update and delete rows from existing tables, and you can do so using SAS Visual Analytics report as the front-end. There is an example in GitHub that updates tables, but it uses the SAS Viya REST API’s to do so directly from the DDC JavaScript code. Doing something similar using jobs is going to be left to you as an exercise 🙂   In the next article you will be applying the integration between SAS Visual Analytics, Data-Driven Content object, and SAS jobs in a business use case: create a Pareto chart by allowing dynamic filtering on the cumulative total percentage. You will be looking at GDP by country and year, and the goal is to allow you to report on the short list of countries that are responsible for let’s say 80% of the global GDP.     References   SAS Job Execution Web Application 2.2: User’s Guide documentation JSON Libname Engine documentation Reading data with the SAS JSON libname engine documentation Data-Driven Content: leveraging third-party visualizations in SAS Visual Analytics (part 1 of 2) by SAS’s Renato Luppi Data-Driven Content: leveraging third-party visualizations in SAS Visual Analytics (part 2 of 2) by SAS’s Renato Luppi Using parameters with Data-Driven Content in SAS Visual Analytics by SAS’s Renato Luppi sas-visualanalytics-thirdpartyvisualizations GitHub project and examples     Learn More…   Programming Considerations for Data-Driven Visualizations documentation Customize data visualizations in SAS Visual Analytics with #D3Thursday by SAS’ Ryan West  Leverage a Google 3D-PieChart within VA using the Data-Driven Content Object by SAS’ Meera Venkataramani Custom Visualizations in SAS Visual Analytics - A Strategy for Using the Data-Driven Content Object by SAS’ Robby Powell Use Custom Visualizations to Extend SAS® Visual Analytics by SAS’ Robby Powell Reading data with the SAS JSON libname engine by SAS’s Chris Hemedinger     ... View more

In this article you are going to expand on the solution provided in the previous article. Just to refresh your mind, you have used HTTP GET request to execute a SAS job that produced an HTML ODS output table containing the same data passed as input. The data was passed as a stringified JSON directly in the SASJobExecution web application URL. Even though you were able to display the HTML output in a SAS Visual Analytics report, the content needed some work, such as removing columns that didn’t exist in the input data, replace generic column names with their correct names, apply column labels, format values, etc. This is what this article is about. Besides fixing all those things, you will also see how to make the HTTP call using a POST request, which is recommended for handling bigger tables.     HelloBigWorld Example   In contrast with the example called HelloSmallWorld from the previous article, this HelloBigWorld example can handle larger tables as input because it uses HTTP POST request to upload the table onto the server.   SAS Job Code   The SAS job code is very similar to the one used before:   Picture 1- SAS job code for HelloBigWorld example   The basic difference compared to the initial example is that you no longer need to save the stringified JSON content passed as parameter in the SAS job URL into a file. As said before, the data has already been uploaded to the server, and it already exists as a temporary file that can be accessed through the pre-defined macro variable called _WEBIN_FILEURI. Because of that, the job code is slightly simpler.   Data-Driven Content Code   This code is also very similar to the one used in the HelloSmallWorld example. The only difference resides on the JavaScript function that calls the job:   GET request (previous example):   Picture 2- callJob function for HelloSmallWorld example   POST request (this example):   Picture 3- callJob function for HelloBigWorld example   The callJob() function code used in this example uses the FormData object to set the job input parameters, which is passed to the job in the HTTP POST request call. Unlike the HelloSmallWorld example that uses a GET request, the parameters aren’t appended to the URL, but instead, they are sent in the HTML payload as the data element. All parameters are the same as the previous example, except for the table data that is not only stringified, but also transformed into a Blob. A blob in the payload is what triggers the file to be uploaded to the server. The ajax() call has a few additional parameters such as contentType, ProcessData, and headers. The header contains the X-CRF-TOKEN set to pre-defined variable $CSRF$, which must be specified exactly as shown and it’s important to ensure that the request is considered non-malicious, as explained in the previous article.     HelloBigWorldFormatted Example   The modification performed in the previous HelloBigWorld example didn’t really change the output generated by the job and displayed in the report. In this example you will treat the data received from SAS Visual Analytics before it is sent to the job and then leverage that information in the job itself to produce cleaner and formatted results.   Data-Driven Content Code   The difference compared with the HelloBigWorld example is minimal:   Picture 4- Data-Driven Content (form) code for HelloBigWorldFormatted example   First, you will need to add a reference to the jobUtil.js module, available in GitHub. This module provides the function PrepareVADataForSASJobs(), which is responsible for making some transformations in the data received from SAS Visual Analytics and providing additional and useful information to the job, so it can better treat missing values and formats.   SAS Job Code   While the changes in the DDC code were minimal, the SAS job code needs additional steps to leverage the new information received in the JSON file. The new steps are highlighted in those three blocks of code.   Picture 5- SAS job code for HelloBigWorldFormatted example   The first block is responsible for retrieving enhanced column metadata that was added by the DDC via function PrepareVADataForSASJobs(). That information is stored in a dataset and will be used to create a JSON map file. This step also replaces blank spaces in column names with underscores.   The second block of code uses the dataset created in the first block and creates a JSON map file in a temporary location referenced by jmap.   The third block re-issues the JSON libname with the JSON map attribute. This allows for the JSON file to be read more intelligently and formatted.   JSON map files are explained in the JSON libname engine documentation.   The end result produced by the job before and after this new example is seen below.   Before:   Picture 6- Job ODS HTML output without column and format enhancements   After:   Picture 7- Job ODS HTML output with column and format enhancements     Deploying These Examples   All files used in this example are available for downloading from the GitHub project sas-visualanalytics-thirdpartyvisualizations, under folder called samples/IntegrationWithSASJobs.   It requires the CARS table to be loaded into memory in the Public caslib. The table is originally available in the SASHELP library.   Deployment steps: Download the GitHub project Unzip it into your Web Server document folder. These instructions assume you have unzipped the GitHub project in a folder called github in the Web Server document root folder. You will only need the content of folders github/utils and github/samples/IntegrationWithSASJobs. Logged into SAS Job Execution Web application, do the following: a. Create two new jobs in a folder of your preference (e.g. /Public/Jobs/SAS Communities) and name them HelloBigWorld and HelloBigWorldFormatted (see first article for more details if needed) b. Open the jobs for edition, copy & paste the content of files github/samples/IntegrationWithSASJobs/2.HelloBigWorld/HelloBigWorld.sas and github/samples/IntegrationWithSASJobs/2.HelloBigWorld/HelloBigWorldFormatted.sas to their respective jobs c. Save the jobs d. Add job parameter _action=FORM as discussed in the beginning of the first article to each of the jobs e. For each job, create a new job form either as a separate content or attached to the job (SAS Visual Analytics 8.5 or above only), as explained in the beginning of the first article. If they are separate content, give them the same name as their jobs f. Open the first job form for edition, copy & paste the content of file github/samples/IntegrationWithSASJobs/2.HelloBigWorld/HelloBigWorld.html (1) . Repeat the process for the second job using the file github/samples/IntegrationWithSASJobs/2.HelloBigWorld/HelloBigWorldFormatted.html (1) g. For each one of the forms, make changes to the host name (search for your.host.name and replace it accordingly) and the path of src on line 20 of HelloBigWorld, and lines 20 and 21 of HelloBigWorldFormatted  (1) : Picture 8- Modify src appropriately h. Make changes to the job path on line 71 of HelloBigWorld and 73 of HelloBigWorldFormatted if necessary (tip: right click on the code and turn on “Show line numbers”), so they match with your environment (same as #3a) (1) : Picture 12- Modify job path and name appropriately i. Save the job forms Logged into SAS Visual Analytics, do the following: Create a new empty report With the report opened, hit Ctrl+Alt+B to bring the SAS Visual Analytics Diagnostics window. With the BIRD tab selected across the top (that’s the default), replace the BIRD XML content with the content of the file github/samples/IntegrationWithSASJobs/2.HelloBigWorld/VA-DDC-Job Hello Big Worlds.xml Hit Load (this will close the diagnostics window) For each page on the report, select the DDC object at the bottom of the page, go to the Options pane on the right and fix the URL entries: replace your.host.name accordingly and make sure the path to the job is correct (same value entered in #3a) Save the report Note: (1) Starting with release 2023.06, the examples that used to inherit jQuery from their parent (the SAS Visual Analytics web application) no longer work, so we have provided replacement codes. These replacement files have the same names as their original, but they end with .v4. Because of that, some references to line numbers may not exactly match on those .v4 files.   We could have exported all the content as a package, but this would require special privileges in order to import it. Sharing the examples as standalone files will give you the opportunity to better explore the SAS Job Execution Web application, familiarize yourself with the content, and understand how they are connected.     Next Steps   In the next article you will be introduced to a new way of leveraging SAS jobs from SAS Visual Analytics. You still be using the Data-Driven Content as the bridge to execute SAS jobs, but jobs will no longer be generating the visual output, like the ODS HTML table you saw in the previous examples. After processing the data, those jobs will be loading the output table in memory, so that SAS Visual Analytics or another application can consume the information and display it anyway wanted.     References   SAS Job Execution Web Application 2.2: User’s Guide documentation JSON Libname Engine documentation Reading data with the SAS JSON libname engine documentation Data-Driven Content: leveraging third-party visualizations in SAS Visual Analytics (part 1 of 2) by SAS’s Renato Luppi Data-Driven Content: leveraging third-party visualizations in SAS Visual Analytics (part 2 of 2) by SAS’s Renato Luppi Using parameters with Data-Driven Content in SAS Visual Analytics by SAS’s Renato Luppi sas-visualanalytics-thirdpartyvisualizations GitHub project and examples   Learn More…   Programming Considerations for Data-Driven Visualizations documentation Customize data visualizations in SAS Visual Analytics with #D3Thursday by SAS’ Ryan West  Leverage a Google 3D-PieChart within VA using the Data-Driven Content Object by SAS’ Meera Venkataramani Custom Visualizations in SAS Visual Analytics - A Strategy for Using the Data-Driven Content Object by SAS’ Robby Powell Use Custom Visualizations to Extend SAS® Visual Analytics by SAS’ Robby Powell Reading data with the SAS JSON libname engine by SAS’s Chris Hemedinger ... View more

When Data-Driven Content (DDC) object was first added to SAS Visual Analytics (version 8.2), I wrote a two-part article to explain the concept and help introduce its basics. At that time, we explored the key elements that are provided in the json message that the DDC receives from the SAS Visual Analytics report: resultName, data, and columns. Intentionally, I didn’t include parameters, but now that people have adventured through Data-Driven Content, I started to get questions about accessing parameters and their values from the DDC Java Script code.   Information about parameters can be found in the json message and its structure is very similar to columns, but the parameters element isn’t always present in the message.   01-Structure of the message received from VA with parameters The parameters element is optional, and it only shows up in the message if there is a parameter used by the Data-Driven Content object, for example: A calculated item or aggregated measure that uses a parameter in its expression (e.g. pDiscountPct) is assigned to the DDC Roles pane 02-Calculated item expression with parameter The DDC has an advanced filter which expression is based on a parameter (e.g. pTypeMultiple) 03-Filter expression with parameter The DDC has a rank and the rank definition depends on a parameter (e.g. pTopN) 04-Rank definition with parameter For each parameter added to the message, you will get at least its name (internal id), label, datatype, and value. The value can be a single value or an array, if the parameter accepts multiple values. This is an example of a parameter called “pTypeMultiple” that has three string values assigned to it: 05-Example of multi-value parameterIf a parameter is formatted, the format information is included. The example below shows a parameter “pDiscountPct” with a value of 18%:   06-Example of parameter with format You saw previously that parameters must be used by the DDC object (in calculation, filter, or rank) for them to be added to the json message. What if you still want to have access to parameters and their values, but the parameter is not used in any calculation, filter, or rank? The answer is simple: you will have to add one of those elements, even if you don’t really need them. For example, you can add a dummy advanced filter that returns true always, so it won’t affect the results, such as: 07-Dummy advanced filter using a parameter If you want to play around with parameters in DDC, I highly recommend you try the sample DDC available in GitHub called jsonDataViewer.html. This sample DDC displays the json message and it makes it easier to visualize what’s being passed from VA to the DDC object. The easiest way to use the sample is to add the URL below to the Options pane of your DDC object: https://sassoftware.github.io/sas-visualanalytics-thirdpartyvisualizations/samples/jsonDataViewer.html   The video below contains an example of a DDC with calculated item, rank, and filter.     If you want to explore this example in your environment, I've attached the CARS dataset that should be loaded in the Public CAS library and the report as a json file that you can import from SAS Environment Manager (menu option Manage Environment). ... View more

This is the third part of a series of articles about advanced aggregations and calculations. In the previous one we covered the AggregateTable operator, and in this one you will explore aggregated data sources and data source joins.   Both aggregated data sources and data source joins take tables as input and generate temporary tables as output. Those temporary output tables can be used as source tables for visualizations and/or input for subsequent aggregated data sources and data source joins.   Aggregated data source   Aggregated data source was introduced in SAS Visual Analytics 8.3 and the concept behind it is simple: dynamically create an aggregated table that can be used as a data source in the current report. Because the aggregated data source is created on the fly and it is visible only by the user that is consuming the report, it works as a calculation.   Creating an aggregated data source   To create an aggregated data source, you select the input source table from the Data pane on the left, click on the Actions icon, and select New aggregated data source: 01-Aggregated data source menu The New Aggregated Data Source window that pops up is straight forward. Just select the columns (categories and measures) that you want in the resulting table by moving them from the Available items on the left to the Selected items on the right and hit OK: 02-Aggregated data source window The output table produced is equivalent to what the following SQL statement would produce (this is just for reference – there is no SQL query executed):   select <categories and aggreg(measures)> from <input table> where <filter> group by <categories>   The aggregations used with the selected measures are the aggregations assigned to the measures on the Data pane. Calculated items and aggregated measures can also be selected and if they use parameters, the expressions are evaluated on the fly. Calculated items and aggregated measures become regular measures in the output table and can be used in new aggregated items and aggregated measures expressions. This is another way to allow “aggregated measure of an aggregated measure”, discussed in the previous article. Custom categories are also accepted.   Observe that on the top right corner of the New Aggregated Data Source window there is the + New filter option that allows you to add data source filters. If you choose to add an Advanced filter, you can use complex filter expressions that includes parameters.   Using parameters in calculated items, aggregated measures, and data source filters is what makes the aggregated data source dynamic, flexible, interesting, and powerful, differentiating itself from a table that is aggregated upfront, as a data prep process.   For more information on aggregated data sources, please refer to the documentation.   Data source join   Data source join was also introduced in SAS Visual Analytics 8.3 and as you may be thinking, it allows for source tables to be joined, creating an output table that can be used in the report. Like the aggregated data source, the data source join is also performed on the fly and the output table is visible only by the user that is consuming the report, similarly to a calculation.   Like in the aggregated data source, calculated items and aggregated measures become regular measures in the output table and can be used in new aggregated items and aggregated measures expressions. Calculated items and aggregated values cannot be used as join keys, but if you derive aggregated source tables from then, they can be used as join keys. The best way to understand data source join is through an example (see examples 2, 3, and 4).   For additional information on data source joins, please refer to the documentation.   Examples   Example 1 – Aggregated data source to improve performance   Those two crosstabs below are identical, but one of them is based on data coming from the original data source, and the other is built from the aggregated data source that has only selected columns Date by Month, Product Brand, Expenses, and Order Quantity. 03-Example 1: Aggregated data source to improve performance The time required to query the data can be analyzed in the SAS Visual Analytics Diagnostics window (via Ctrl+Alt+P). In this example, the time dropped from almost 8 seconds to just a bit over 1 second. 04-Example 1: Aggregated data source to improve performance The difference in performance varies from report to report, each case is different, and it’s not guaranteed that the use of aggregated data sources will always improve performance. For example, if the previous crosstabs only had Expenses as the measure (which is not an aggregated measure like the others), there wouldn’t be any significant performance gain: 05-Example 1: Aggregated data source to improve performance Usually, reports that have multiple objects in a single page may experiment performance boosts if the objects can leverage the same aggregated data source that is a lot smaller than the original table. The performance improvement comes from the fact that the original data source table is filtered and aggregated once, instead of multiple times (for each object).   Example 2 – Data source join to bring data from multiple tables into one report object   Even though reports can have multiple data source tables, a report object can have its data coming from one source table only. The ability to join tables directly in SAS Visual Analytics helps overcome this limitation. For example, the following data source table has actual sales numbers and the one below it has sales target numbers: 06-Example 2: Sales table 07-Example 2: Sales target table To combine actual sales and target sales in one single report object you must join those two tables. You can do that upfront when preparing the data for reporting, or you can do that in SAS Visual Analytics, by creating a data source join:   08-Example 2: Data source join menu In this example a right join was selected to make sure the target sales values are present even if there were no actual sales. Those two tables were joined by Product type, Quarter, and Year:   09-Example 2: Data source join definition Clicking on the button Choose Columns… at the bottom left (picture above) allows you to select the columns you want in the joined output table:   10-Example 2: Data source join columns selection The joined output table is now available on the Data pane and as any other data source table, you can apply data source filters (parameterized or not), join it with other tables, use it as the input of an aggregated data source, etc., and of course use both actual sales and target sales in the same report object:   11-Example 2: Output data source join   12-Example 2: Output data source join If you have tables in different aggregation levels, for example: one table has daily transactional sales information at the product level and the other table has quarter sales goals at the product family level, you might want to aggregate the transactional sales table by creating an aggregated data source first, before joining it with the quarterly goals. There are other use cases that require a combination of aggregated data source and data source join, and this is what example #3 is about.   Example 3 – Aggregated data source and data source join to implement nested “select from”   Suppose you have a table with historic information about people, or products, etc. and certain conditions that those people or products might have been at a certain moment. Given one or more conditions, you want to retrieve all historic information from those people or products that are or were at that condition. That’s a very broad use case that could fit some similar use cases: Patients and diagnosis: given a diagnosis, list all patients and their diagnosis that occurred together with the selected diagnosis. Products and defects: given a defect, list all products and their defects that occurred together with the selected defect. Accounts and transactions: given a type of transaction, list all accounts and their types of transaction that occurred with the selected type of transaction. Managers and employees: given an employee, list all other employees that share the same manager of the selected employee. etc. If you had to write a SQL statement to resolve this query, it would be something like this:   Select * from <historic_table> where id in (select id from <historic_table> where condition in <selected conditions>)   In this example you have a table called TINYTABLE with three columns: ID (to identify the person or product), Condition, and Version (to indicate different moments). If condition B is selected, the goal is to return all records from ID’s 1 and 2 because they are the ID’s that contain condition B at some point:   13-Example 3: Input table The first thing you need to do is resolve the inner select statement to identify the ID’s that meet the selection criteria. This is easily accomplished by creating an aggregated data source where only the column ID is included in the output:   14-Example 3: Aggregated data source to select IDs Observe that the aggregated data source above has an advanced filter (top right) associated with it that filters for selected conditions only. In the filter expression, Selected Conditions is a multi-value parameter that could have been assigned to a List control object, for example. This is how the filter expression is defined:   15-Example 3: Advanced filter for aggregated data source The next step is to join the list of affected ID’s (the aggregated data source) with the original source table by ID, to create a table called Data_Source_Join:   16-Example 3: Data source join with aggregated data source The Data_Source_Join table contains the records that you are looking for and could be used in the report object (example when condition B is selected):   17-Example 3: Final output table There is only one caveat in this solution: to guarantee that the data source join is executed every time a new condition is selected, we need to add a data source filter to the Data_Source_Join table. This filter is just to establish a direct dependency between the parameter called Selected Conditions and the final joined table, so that changes on the parameter triggers the join to be re-evaluated. This filter could be anything that uses the parameter and always resolves to true, such as this:   18-Example 3: Dummy filter for final output table Example 4 – Cartesian joins   Cartesian joins can be dangerous, especially if you don’t know what you’re doing, but there are some cases where you might want or need to do that. Out of the box, cartesian join is not an option for the join type, but you can create a calculated item in SAS Visual Analytics with a constant value assigned to it and use that calculated item as the join key in an inner join. You will need to create that calculated item in both input tables.   But then there is a catch: calculated items cannot be used as join keys. You can go around this by creating aggregated data sources first and then joining those aggregated tables. If you remember from the beginning of this article, calculated items and aggregated measures behave as regular measures in the output table of an aggregated data source, but they are still dynamically calculated.       In the next and last article, you will work with an example that leverages most of the advanced aggregations and operations explained in this series and solve a complex calculation problem.     Additional resources on advanced calculations and aggregations: Documentation: Working with Aggregated Data Sources Documentation: Working with Data Source Joins in Reports Community article: SAS Visual Analytics Advanced Calculations (part 1 of 4): AggregateCells by SAS' Renato Luppi  Community article: SAS Visual Analytics Advanced Calculations (part 2 of 4): AggregateTable by SAS' Renato Luppi  Community article: SAS Visual Analytics Advanced Calculations (part 4 of 4): PMPM Example by SAS' Renato Luppi  ... View more