Create date prompts in SAS BI Dashboard, Part 1

Don't give up on the dating game. Just because there is no prompt type specifically for dates, it can still be accomplished with some stored process matchmaking. BI Dashboard offers a dynamic prompt indicator which provides the ultimate flexibility in developing prompt based reports and dashboards.

The dynamic prompt indicator allows you to generate a drop down box of values dynamically from a specified indicator data element. In this post (part 1), I will explain how to create a drop down box of the last 12 months with no required monthly maintenance (adding months, running ETL scripts to populate a source table, etc). Next week, in Part 2 I will describe how to define interactions between multiple date indicators so your dates are smarter.

Step 1. Develop a stored process to create a data source for the indicator data

Included is the code to create a temporary data table (work.test) which publishes the data to a package 'DynamicDateList' for the BI Dashboard to pick up.
Note that this code dynamically creates a list of the last twelve months (including the current month) in descending order. Some customer require that a default selection is setup, reducing the amount of clicks to get the most often requested report. In BI Dashboard, the dynamic prompt displays by default the first record in the table. For this example, the code is written to create months in descending order so the current month will appear as the selection.

%macro dt;
data work.test;
format date monyy5.;
%do i=0 %to 11;
date=intnx('month', today(), -&i);
output;
%end;
run;
%mend;
%dt
%let temploc = %sysfunc(pathname(work));
data null;
length path $32767;
rc = 0;
pid = 0 ;
description = '0';
name = '';
call package_begin( pid, description, name, rc);
call insert_dataset( pid, "WORK", "test", "dynamic date list", '', rc);
call package_publish( pid, "TO_ARCHIVE", rc, "archive_path, 
            archive_name, archive_fullpath","&temploc", "DynamicDateList", path );
call symput( '_ARCHIVE_FULLPATH', path);
call package_end( pid, rc);
run;

Important Note: The stored process must be registered to generate a package result.

Step 2. Setup the indicator data

Choose 'Stored Process' as the data source and choose the stored process created in Step 1. If the published data set name ('test' in this example) doesn't appear there is an error in either the stored process or within the metadata registration. To troubleshoot, I would recommend moving to the SAS Stored Process web application (http://server:port/SASStoredProcess) to run the same stored process and review the log message there.

Step 3. Create a dynamic prompt indicator leveraging the indicator data

After saving the indicator data from Step 2, create a dynamic prompt indicator using this new indicator data. As you can see the default selection is October (Oct12), the first record in the test data table.

Check back next week on how to make the date prompts smarter in part 2!

And for another example of using a stored process to dynamically generate a data result for a dashboard indicator, check out my 2nd book The 50 Keys to Learning SAS Stored Processes.