Dropdown Filters: How to display names different from the underlying keys that connect to visuals.

[es_edu]

Good morning,

I'm trying to build a report that will allow users to select a school district & school that will update visuals according to the selection.  My visuals and data sources are mapped via a "district_id" and "school_id" which is a numeric code to dimension tables that include district ID's and names, and school ID's and names.

In the report I would like to display the appropriate district names (Jones County, Brown County, etc.) and school names (Harris Elementary, Thomas Elementary, etc.).  As district and school names are subject to change, I don't want to link data via names but based on the underlying ID keys that are time invariant.  The name to be displayed will be the ones in the school and district dimension tables.

I was wondering if there were any resources or known solutions that address displaying text different to the underlying filter values?  Thanks!

[Madelyn_SAS]

Which version of Visual Analytics are you using? 

[es_edu]

Version: Long-Term Support 2024.03

Release: 20240701

[Madelyn_SAS]

I just did a quick test and you should be able to do this by creating custom categories based on District and School. So, for example, create a new custom category called District Names and use the district_id data item as the source. Then use the district_id in the dropdown, and use the District Names custom category in the object. Link them manually, or from the page or report level. 

This is what it looks like with no selection in the drop down. 

Select a value, and the table should show the corresponding name. 

[es_edu]

Thank you, one difficulty is that I have about 150 districts and 1000+ schools, which would be a lot of value groups to manually create.  I do think that this solution would work, but it would be time consuming.  

[Sam_SAS]

Hello,

Did you try creating custom categories to map the district and school IDs to names? That would work for basic filtering.

If you want to store the value in a parameter and use that in advanced filters/calculations, then it would be more complicated but it should still be possible.

Sam

[KeithM · Accepted solution]

I have a solution for you.  I will display the sample report and then explain how it works.

 1. I have two lookup tables:   District_Lookup, School_Lookup

You would make these part of your Data process.  If a district or school name changes then you update these tables.

 2. My report data is called "District_School_Enrollment".  It only contains id's.

 

3.  All 3 tables are imported into CAS.   You will need to create a Join between District_School_Enrollment to the other two lookup tables so you can get District Name and School Name.

First Join to get the District Name

 

2nd join to get the School Name.  The resulting table is called "Data_Join(1).   That will be the table we create the report off of.

 

4.  After you have this setup, you can now build your entire report off of Data_Join (1).  If you want to add List prompts going against the lookup tables are most efficient.  If you do that, you simply need to MAP the tables.    District Lookup --> Data_Join(1).     School_Lookup --> Data_Join(1).

: