VA 7.5: Conflicts between parameter defaults and dropdown selections

[markpevey]

I have a report that shows monthly expenditures. I have a dropdown box to select fiscal year and a second dropdown box to select the report month. I have a parameter associated with each of these dropdown boxes. I use the parameters in calculations. I want the report to always open with the current month. I could normally handle that by setting the dropdown box to initially select the first value in the list. However, that option is not available if there is a parameter associated with the dropdown. As it is now, the report will always open with the parameter value that was selected when the report was saved, which will not necessarily be the current month (the one I want to have everything default to when the report is opened). Any thoughts on how to accomplish this? I'm running VA 7.5. Thanks

[acordes]

create an item that calculates the difference between the current month something like:

abs(month(yourdate)-month(datepart(now())))

this will resolve to zero for the month matching the current month.

use this data item in the filter and apply a sort to the filter by this item.

I've just tested it. Tweaking the today's month to march by adding 2 an re-open of the report auto-selects march as month in the filter.

[Stu_SAS]

Hey markpevey! Thanks for posting. The good news: this is fully resolved in Viya 4. Also good news: there is a workaround for VA 7.5/8.x

Back in early versions of Visual Analytics that did not have the ability to select the max value for drop-down lists, I would use a parameter trick with object filters to return the most recent data using drop-down menus when they are cleared. To do this, you'll need to add an indicator for the most recent month in your data. For example:

Date	flag_max_date
OCT2021	0
NOV2021	0
DEC2021	0
JAN2022	1

What we want to do is make sure that it only shows the most recent month if both values are not selected. To do this:

1. Uncheck the "Required" option for both drop-down lists
2. Add the following filter to your crosstab:
   

   if(missing('Fiscal Year'p) OR missing('Month'p) ) return 'flag_max_date'n = 1
       else 1=1

This filter is saying "If any of the required drop-down lists are missing, only show the most recent data. Otherwise, do not filter anything." "else 1=1" is a trick to get around the required "else" statement so that it does not do anything. Think of this as dynamically enabling the object filter. When all of the right controls are selected, the object filter no longer does anything and the page controls will filter the object. Note that this may result in a missing data error while the user initially selects values, but that will immediately be resolved once they select both a year and a month.

Here is an example of this for a forecasting report that shows historical forecasts. Users can select between forecasts using the drop-down menu. When they clear it, the most recent forecast is always shown.

If you'd like to know more about this trick, check out pages 13-17 of Mastering Parameters in Visual Analytics where it's explained in detail.