How to create a drop-down list with overlapping values?

[lucydog]

I'm new to SAS VA (using v. 7.5) so I may not be using the right terminology. But I want the person viewing my report to be able to filter data to display from a list of values that overlap (are not exclusive). In my example, I want to have a list of age categories such as: 12+ years, 12-18 years, 18+ years, 18-64 years, 65+ years.

So, instead of the drop-down list shown below (with exclusive values), I want to be able to create a list of choices with overlapping values. I have both category and numeric age variables to help me with this task.

[Sam_SAS · Accepted solution]

Hello,

This should be possible using a parameter and an advanced filter.

For the drop-down list, you need to have a column that contains the selector values such as "15-19 years". If you have access to the source data table, adding these values to the table would be the simplest approach. If you cannot update the source table, you can create the values within VA by creating either a custom category or a calculated item. For example, I did this as a custom category:

It is important to remember that these mappings don't really matter. All we are doing is making a column that has the values "11-14" and "13-16".

Now, create a drop-down list object in the body of your page. It must NOT be placed in the page prompt area or report prompt area, because we do not want the control to directly filter anything. The data item that has your selector labels should be assigned to the Category role, and you will create a new character parameter to assign to the Parameter role.

Now, on the object that you want to filter, create an advanced filter. In my example, I am using this expression:

IF ( 'Age Group'p NotMissing )
RETURN (
IF ( 'Age Group'p = '11-14' )
RETURN ( 'Age'n BetweenInclusive(11, 14) )
ELSE ( 'Age'n BetweenInclusive(13, 16) ) )
ELSE ( 1 = 1 )

I will put the same code with comments below:

IF ( 'Age Group'p NotMissing )  * Check if the parameter is set (is anything selected in the drop-down list)
RETURN (
   IF ( 'Age Group'p = '11-14' )  * If the selection is '11-14', then...
   RETURN ( 'Age'n BetweenInclusive(11, 14) )  * Select rows where Age is between 11 and 14
   ELSE ( 'Age'n BetweenInclusive(13, 16) ) ) * Select rows where Age is between 13 and 16
ELSE ( 1 = 1 ) * Nothing is selected in the list, so return TRUE and show all rows.

By adding additional IF/ELSE statements, you should be able to handle as many selections as needed.

Let us know if you get it working or if you have additional questions.

Thanks,

Sam

[lucydog]

Thank you very much, Sam. This worked and I would not have been able to figure it out on my own, ever.

Ideally, though, I wanted a page, or possibly a report, drop-down list with overlapping values. But it seems that these "advanced filters with parameters" only work at the object level? When I moved the filter to "page control" -- which is an option -- it behaved strangely and on objects where I had not created a filter. [You had said, "It must NOT be placed in the page prompt area or report prompt area", and that appears to be good advice.]

So the filter worked perfectly for filtering my one object. However, I would like this same filter to work on lots of objects/pages. So, I wondered, do I need to create the same filter for each object, or is there a way 1) to copy this filter to other objects; or 2) to use this filter as a page or report control?

[Sam_SAS]

Hi Lucy,

Great to hear it worked for you.

Controls in the report prompt area or the page prompt area automatically filter all objects in the report (or page, for a page prompt) so that is why your drop-down list must be in the body of a page. This is a common issue for users (as a user myself I find it hugely frustrating!) and the dev team is looking into a way to improve that situation. I can't promise this will be fixed, but it is a known issue. 

Depending on the version of VA that you have, you might be able to convert the filter to a "common" filter that can be assigned to multiple objects. Using Common Filters 

Alternatively, if you edit the filter in "Text mode", you can copy-paste the expression to facilitate making additional copies of it.

Sam