I am having an issue while trying to filter on entity within my company's Essbase database. The issue is that our Entity dimension that includes region, country, cost center, etc. is not standard according to level or generation. Region A could be generation/level 2 while Region B is generation/level 4. Likewise, Country A could be generation/level 9 and Country B generation/level 3. When adding a filter on a particular level or generation, it provides all data for that level or generation within the filter. What I want to have is a filter that simply shows all the regions, and then another filter that shows all the country's with only relevant values according to the region filter. Below are screenshots of the Entity dimension:
I've tried going the set route, but when I add both the region set and country set, only the values that are the same (US and Canada) appear. Is there a way to get around my issue, or does the database need to be structured differently?
The regions I want are US, Canada, Latin America, Europe, Asia, Africa.