Dynamic filters in Web Report Studio are awesome – no predefining static values and also in sync with the data. But…. they can be a bit on the slow side. Clicking on the ‘Get Values’ button runs a select distinct query which will return the filter list. How long this takes is dependant on a number of things, mainly the number of records that will be returned and whether you are using a single datatable or multiple tables with a ‘Required table’ specified.
Compare this now with static list…boom…instant gratification. Of course the downside of static lists is someone needs to predefine all the values and your list may eventually become out of sync with your data. All true. But there is a way we can have the benefits of dynamic lists and the turbo boost of static lists. Read on ….
At a recent project the customer was dissatisfied with the 10-15 second wait for his dynamic filters to appear. We decided the only way forward was to introduce dynamic static filters, that is static filters that are filled with the most recent data values. The solution turned out to be a lot easier than I expected. PROC INFOMAPS to the rescue.
The UPDATE DATAITEM statement allows the value generation method to be changed and also specify a static list.
The syntax looks like this -
|UPDATE DATAITEM ”data-item-ID”
<VALUE_GENERATION=NONE | DYNAMIC | ADD (custom-value-list) | REPLACE (custom-value-list) | REMOVE (unformatted-value-list)>;
See the following link for the full syntax explanation-
And heres a quick example -
update infomap “REMAnalysis” MAPPATH=”/metadata data path”;
update dataitem “Column 1″ value_generation=add((“1″ “Static value1″) (“2″ “Static value2″) (“3″ “Static value3″));
The above code snippet shows a custom value list being added to dataitem Column1.
The concept is simple. Although for the customer project there is still some work to do. Of course we need to replace the hardcoded custom list with dynamic values read from the data. I feel some macro coding coming on! And since the customer has many information maps, we also need some code to determine which items need to be changed to static lists and feed this into the macro. I am hoping we can get this information from the Metadata. That should be enough to fill the next blog post