SAS Viya aggregation and display rule

[vietlinh12hoa]

Hi everyone,

I have table below, ID is unique:

ID	Group A	Group B	Group C	Revenue
0	Y	N	Y	100
1	Y	Y	Y	200
2	Y	N	Y	300
3	Y	Y	N	400
4	N	N	N	500

In dashboard, I want to aggregate filter by Group, display rule is that:

• If Group A is selected, ID 0-3 will be aggregated
• Similarly, if group B is selected, ID 1 and 3 will be aggregated
• If no selection, all id will be aggregated

My challenge is overall aggregation where ID can be in 2 groups. I need to customize display rule for "no selection" option. How we can customize the rule? 

[Sam_SAS · Accepted solution]

I have an ugly and messy solution to this, but it seems to work.

Results first:

If you remove the "Group" variables and ID from the table, then you will get your aggregated Revenue value.

I don't think it is possible to do this by using a display rule. I am using a parameter and an advanced filter.

To make the button bar work, you need a variable that has the values "A", "B", and "C". If you can edit your source table directly, the easy solution is just to make a new column that has those values. But otherwise you can make a calculated item using this expression:

IF ( 'Group A'n = 'Y' )
RETURN (
IF ( 'Group B'n = 'Y' )
RETURN 'A'
ELSE 'B' )
ELSE 'C'

The actual rows these correspond to don't matter. We just need to get the values A, B, C into the table.

Once that is available, we can create a button bar. This must be placed in the main part of the report page and not the prompt area. We don't want the button bar to filter. Instead, it will set the value of a parameter.

Make a character parameter, and assign it to the Parameter role of the button bar.

Now, select your table and make a filter on the Filters pane. Click New Filter and then Advanced Filter to edit the expression manually.

Our expression will be:

IF ( 'Parameter 1'p NotIn ('A', 'B', 'C') )
RETURN ( 1 = 1 )
ELSE (
IF ( 'Parameter 1'p = 'A' )
RETURN ( 'Group A'n = 'Y' )
ELSE (
IF ( 'Parameter 1'p = 'B' )
RETURN ( 'Group B'n = 'Y' )
ELSE (
IF ( 'Parameter 1'p = 'C' )
RETURN ( 'Group C'n = 'Y' )
ELSE ( 1 = 1 ) ) ) )

Wherever we return TRUE, the filter selects the data row and it is displayed in the table.

This should work for you, although you may need to change the variable names in the expression above to match data items in your report.

This is not a very elegant solution and perhaps someone else can offer a better one. It is a bit difficult to understand, so please feel free to ask if there is something you don't get or if it is not working for you.

Thanks,

Sam

[vietlinh12hoa]

OHHH Thank you very much. I got the advanced filter with table object by changing the source. Any chance to link this advanced filter to button bar or drop down objects?

[Sam_SAS]

I'm not sure I understand the question. The value of the parameter that is used in the advanced filter is set by the button bar (or whatever other control you prefer.)

[vietlinh12hoa]

I mean with your help, I have the Group Filter but it only display when I click on the table

How can I move this object as button bar in the report? Do I have to convert to common filters?

[vietlinh12hoa]

AHhhh ok, I got it. I have to create filter object first. Then link with desired table, and from filter object creating advanced filtering rule.
What I did is creating advanced filtering rule in the table object

[Sam_SAS]

Is your button bar filtering the table now, in the way that you wanted?

[vietlinh12hoa]

Yes it's working. But can you elaborate more about the expression logic. If an ID is both in group A and B, it will return A?
IF ( 'Group A'n = 'Y' )
RETURN (
IF ( 'Group B'n = 'Y' )
RETURN 'A'
ELSE 'B' )
ELSE 'C'

[Sam_SAS]

The filter logic is choosing whether each row is included in the table. It only returns TRUE (include the row) or FALSE (remove the row).

So if you select A in the button bar, the rows with Group A = Y are included. It doesn't matter if any other groups are also Y.

If no button is selected, then the parameter is not in [A,B,C] and we return TRUE (1=1) so that all rows are included in the table. Probably this could have been coded in a better way, but "1=1" is a shortcut if you want the filter to return TRUE.

I wanted to put comments in the expression, but I am not sure if this is possible in the VA expression editor.

I hope this helps a little, and please let me know if there is more that you'd like to know.

[vietlinh12hoa]

Thanks a lot for the feedback. I've tried to follow the same principle with 5 different Levels (Table contains column flg_lev indicate boolean value, (Level column is just for drop down selection, Rows these correspond to don't matter)).

If I didn't add any advanced filter dropdown selection is ok

But after I add the advanced filter, selecting based on flag level from drop down. The code is below:

IF ( 'Level'n NotIn ('Level 0', 'Level 1', 'Level 2', 'Level 3', 'Level 4') ) RETURN ( 1 = 1 ) #ALL
ELSE (IF ( 'Level'n = 'Level 0' ) RETURN ( 'flg_lev0'n = 1 )
      ELSE (IF ( 'Level'n = 'Level 1' ) RETURN ( 'flg_lev1'n = 1 )
            ELSE (IF ( 'Level'n = 'Level 2' ) RETURN ( ( 'flg_lev2a'n = 1 ) OR ( 'flg_lev2b'n = 1 ) )
                  ELSE (IF ( 'Level'n = 'Level 3' ) RETURN ( ( 'flg_lev3a'n = 1 ) OR ( 'flg_lev3b'n = 1 ) )
                        ELSE (IF ( 'Level'n = 'Level 4' ) RETURN ( 'flg_lev4'n = 1 )
                              ELSE ( 1 = 1 ) ) ) ) ) )

Now, all level except for Level 4 disappear in the drop down. I don't understand why it's happening.

[Sam_SAS]

Hello,

There are two issues I see.

1. The advanced filter is assigned to the drop-down list control instead of the table.

2.  Everywhere in your filter expression that you have Level'n, you should instead be using the parameter that is assigned to the drop-down list control. (Did you create a character parameter and assign it to the Parameter role of the drop-down control?)

Returning 1=1 at the bottom is not strictly needed because you are already handling the no selection case at the beginning. I don't think it is doing any harm, however.

Try making those changes and let us know how it goes.

Sam

[vietlinh12hoa]

Thanks a lot. I made some correction, and it's working perfectly.

[Sam_SAS]

That is great to hear!! Thanks for letting us know

Sam