i have a simple hierarchy (continent, state...) and 2 measures (revenues, items number)
i needs to see (in a cross table, or other objects) just the items number if the user drill to continent, but i need to see the revenue measure if the user drill down to state category
I tried to do "Count distinct" on level2.
When on level 1 I got a value greater than 1 (Level 2 need to have more than one member thou').
*When on level2 I got the value 1 for each row.
I then created a calculated aggregated meassure that did an "If-then-else" and picked measure 1 or 2 depending on the "count distinct" variable.
Maybe you can do some more calculations to be sure that you are on the correct level?
Why I suggested the two list tables is that I don't really like showing different measures in the same column/table, I think it will confuse the users. I have done a lot of this using OLAP cubes and MDX, but it always got complicated and hard for my (especially new) users to understand.
I would try to separate it and create filters/interactions to get the same outcome 🙂
//Fredrik
Hi!
It's not working out of the box!
You can somehow determine which level you are at by counting distinct level members, but it failes if the sublevel only contains 1 item, i.e 1-1. And from that create a calculated aggregated meassure that uses either item or revenue depending on the flag.
Why not use two list tables one for items number and one for revenue and link them together?
//Fredrik
thanks FredriK,
the workaround with 2 table list object , i'm sure that it work, with interation between the 2 object
but i'm interested to understand if there is a way to know the drill level where the user lands....
your first suggestions its interesting but i'm not sure that i understand it, what U compare ?
eg.: hierarchy categories ---> lev1, lev2 and lev3
distinct(lev1) vs. .... ?
I tried to do "Count distinct" on level2.
When on level 1 I got a value greater than 1 (Level 2 need to have more than one member thou').
*When on level2 I got the value 1 for each row.
I then created a calculated aggregated meassure that did an "If-then-else" and picked measure 1 or 2 depending on the "count distinct" variable.
Maybe you can do some more calculations to be sure that you are on the correct level?
Why I suggested the two list tables is that I don't really like showing different measures in the same column/table, I think it will confuse the users. I have done a lot of this using OLAP cubes and MDX, but it always got complicated and hard for my (especially new) users to understand.
I would try to separate it and create filters/interactions to get the same outcome 🙂
//Fredrik
I tried,
great! it works
in that way U change column contents, but not column header... so U need to insert any dynamic disclaimer to suggest the user about the really column content (drill by drill...), but its a good way!!!!
now in the week end i try to have a dynamic column number in the drill...
and in this case i try using measure not stored in column of a source table , but stored in rows of a sourece table
thanks a lot
bye
Mauro
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
See how to use one filter for multiple data sources by mapping your data from SAS’ Alexandria McCall.
Find more tutorials on the SAS Users YouTube channel.