How to filter your report based on latest year/month/day in the data?
How would you like to be able to filter your report by the latest month or latest year without user interaction?
This article will explain how you can achieve this using the Data-Driven Content object available from SAS Visual Analytics 8.2.
Having prior experience with VA report design is assumed. This article will use JavaScript code. So, a basic knowledge about JavaScript and web development is required.
If you have not already heard about the Data-Driven Content object and the benefits it brings to Visual Analytics, you can read the following articles and websites:
To develop Data-Driven Content objects, you need:
- a running web server where you can store the files
- internet access to download needed files from GitHub
- a text editor (Notepad++, TextPad, Notepad) or IDE (I personally use Visual Studio Code but any other is fine)
You can use the web server included in the SAS Viya environment but it means you will have to upload your files each time to the SAS Viya environment. To keep the file transfer process to the minimum, you can run a web server on your machine during the development phase and then upload the code to a production web server as soon as you are happy with your code. If you want to install Apache web server on your machine, you have multiple options XAMPP being one of them.
Now that we are all on the same line regarding usage of Data-Driven Content object, I will explain the steps to create a Data-Driven Content object to dynamically filter your data.
Step 1 - Create VA report
First, we will create a report in VA. The report will contain two objects: a List table and a Data-Driven Content object. The report will be based on MegaCorp table but any other data source with a date variable having MMDDYY8 format will work. I will explain later why it should be in that format and how to workaround if you have dates formatted differently.
Here is a screenshot of my two object report and how I have assigned the roles from my MegaCorp data source. Please notice that the Data-Driven Content object displays a default view. This is useful to look at the data available to the object. I have also defined an action from the Data-Driven Content object to the List Table. The report is now fully functional and selecting values in the Data-Driven Content object will filter the List Table.
Select any image to see a larger version.
Step 2 - Write an html page to support the Data-Driven Content object
Now that we have a report designed for our purpose, we need to work on the html and JavaScript part. We need therefore to download a few files from GitHub.
Those two files should be stored in a folder named util on the web server that will be used to render the html page. You can also download the following 2 files and store them at the same level as the util folder created previously:
The folder structure should look like this:
The css file contains the styles to be applied on the html elements and make them look nicer.
The content of the util folder has been written by R&D to ease the development of data-driven content objects and we will use those files as-is. If you want to better understand those JavaScript files, please have a look at the Readme.md on GitHub. If you have questions/remarks or if you want to improve the code, please don't hesitate to reply to this post. The code is open source and should benefit from anyone's improvements.
The main file we will be altering is the filter.html. This HTML file is the one that will be rendered inside the Data-Driven Content object. You might want to split the pure HTML code from the JavaScript. In this example, I decided to have HTML and JavaScript in a single place. I will explain each section of the file. My objective is not teach JavaScript but to provide a working example that can be used as basis for your own developments.
The code can be used out of the box if your source data contains a date variable with a format that can be understood by the JavaScript Date object or in DDMMYY8, JULIAN7 or DATE9 formats. Those formats will be converted by the convertDateColumns function from contentUtil.js. Other formats will require some pre-processing . If you want to check if the SAS date format is valid for the JavaScript Date object, you can validate on www.w3schools.com.
Let's start with the header of the html page:
Line 7 references the css file.
Lines 8-10 points to the jQuery source code. jQuery is a JavaScript framework designed to simplify HTML DOM tree traversal and manipulation, as well as event handling, CSS animation and Ajax. It will facilitate the cross-browser support. In this example, it will be used the update of specific elements of the web page. The file is stored externally and therefore requires internet access. If you prefer, the file can be stored on your web server to avoid any internet connection while using the web page.
Lines 11 and 12 are loading the util scripts.
In the body, we have a div. The visual elements will be defined in that div.
The page contains:
- A drop-down list with three values of my choosing:
- Latest date
- Latest month
- Latest year
- A checkbox
Note the checkbox switch will appear as a toggle in the browser.
This div and the head section combined with the css file are enough to render the page in the browser:
The remainder of the file is the JavaScript that will define the logic of our application.
Function selectDate
The selectDate function defines a list of row IDs to be selected. Depending on the choice of the user, the function will return the single row ID for the "Latest date" selection or a list of row IDs for the "Latest month" and "Latest year" selections. You should always keep in mind that the VA report expects an array of row IDs to filter the data. The selection list should not contain more than the selected row IDs.
This function is also in charge of defining the information to be displayed in the html based on the selection.
On line 57, there is a little trick. The getMonth function returns a value between 0 and 11. This means that December as a value of 11 and not 12 as in real life. As you might guess, adding 1 to the returned value is enough to display the usual month numbers.
Function manageEvent
The manageEvent function is called each time there is an interaction in the report:
- loading data
- changing the selection in the drop-down list
- switching the checkbox on or off
If the switch is ON (line 77-78), the selectDate function is called to define which dates should be included in the filter.
If the switch is OFF (line 80-82), the selection is empty and there is no filter applied.
When the selection is defined, the function will take care of displaying the correct information (lines 84-85) and to filter the report data (line 86).
Function filter
This is the function that is called as soon as the VA report receives the data (line 108). The code checks if there is a variable with type "date" (line 92).
If there is no date, it will display a message to the user that a "date" variable is required.
If there is a "date" variable, the SAS returned date will be converted to JavaScript date by the convertDateColumn function if the SAS format as a parser defined. If not, JavaScript Date object will be used. Which might give unexpected results.
As mentioned earlier, you should validate your date format. If the SAS format is recognized by the JavaScript Date object, you are fine as the date will be handled properly. If the SAS format is not recognized then you should check the format used in VA. At the time I'm writing this article, only MMDDYY8, JULIAN7 or DATE9 are converted by the convertDateColumn function. Other SAS formats require a special attention as they will be passed directly to the JavaScript Date object.
You have two solutions:
- change the format in the report to something that can be handled without parsing
- write a code snippet to parse the date variable and create a Date object
If you need more information about the Date object in JavaScript, please refer to www.w3schools.com.
If you have created your own snippet to convert a SAS format to a JavaScript date, please share it on GitHub or in reply to this post. Your development can make the difference!
Function SetOnDataReceivedCallback
This call is most probably the most important. Without this line, nothing would happen. It makes the web page wait for the data from VA and then triggers the filter function as soon as the data are available.
Attach events
These few lines are there to handle the user interface logic. They define what should be done when the selection changes or when the switch is turned on or off.
Finally
Now, that you better understand what the html page contains, you should adapt the Data-Driven Content object in the VA report and make it point to the URL of the page.
Your report can now be used like this:
Of course, this example still requires some user interaction if you want to switch off the filter or change between Latest month or Latest year but there is no need to have those components in your web page. You can easily customize the code and the user interface to your needs.
If you need help, please don't hesitate to contact me.
