I am trying to setup hierarchy and drill down on a report. The table has 30 columns and each cloumn represents a level from 1 - 30. I want to be able to drill down from one level to another without having to put all 30 columns in the report.
eg
Column name \ value
L1 \ 0005
L2 \ 0006
L3 \ 0007
L4 \ 0008
etc
So when the user runs the report, they can enter any of the values. I currently want only one column “Levels” to be used for L1,L2,L3,L4 so if the user enters 0005 I am able to drill down from L1 to L4 and other levels, but if the user enters 0008, since there is no column with the L4 object, I am not able to drill down. I have tried to dynamically replace the values in the column “Levels” which works but when I enable the drill option, nothing happens.
Any thought other than putting all 30 objects in the report?
A hierarchy is defined by its list of dimension objects. As such, I think your only solution is going to be to include all 30 “L” objects in your query.
I have no problem listing the objects in the query, I just don’t want to list all 30 in the report. I would like to have one column used to represent all 30 which I am able to do using IF statement, but using that does not allow me to drill down.
But that’s how drill-down works - you drill down from dimension object to dimension object. And you don’t need to display all objects to drill-down. In the original, high-level table, you only need to display the top-most object in the hierarchy. When you begin drilling down, this column will change to the next dimension object in the hierarchy.
I have a report that requires only 4 columns. I would like to use column 1 to represent and hold the values for 30 objects L1,L2,L3,…L30. each object will contain only the values that pertains to the level they belong from 1-30.
L1 - 20,
L1 - 21
L2 - 30
L3 - 40
L4 - 50
So if I select 20 or 21 for the prompt, I want column 1 to show the values for the object L1. If I any other value, I want the corresponding object for that level to be displayed in column1. I am able to put the object L1 in column 1 as the default and be able to drill, I am able to change the object in column 1 to another Level object but I do not get the option to drill.
If all you are trying to do is to show the values, then this should work, you create an object and place this in the object “=if ([Level Number] = 1; [L1]; if ([Level Number] = 2; [L2]; if ([Level Number] = 3; [L3]; if ([Level Number] = 4; [L4]))))”
This will check for the respective level and replace the value of the column with the value