3 Replies Latest reply on Jun 6, 2016 6:48 AM by john.stjohn

    Create dynamic Table row headers based on automatic filtering




      I have attached a sample workbook that's pretty simple.  I've got multiple dashboards of large HR data.   There is a hierarchy that is intuitive to the columns and or plots or other visualizations.


      I'm familiar with hierarchy and using parameters to select displays but recently someone asked about an automatic system and I've not been able to get it to work.


      We have a hierarch of:


      Business unit --->   Segment ----->   Level 4 Leader ----->   at Dell


      Right now we can either use the hierarchy opening and closing


      Or use a parameter and axis choice to choose to display any of the three.



      Someone asked a logical question:


      If I select my business unit or the row level security automatically picks my business unit, why doesn't it automatically show the segment.  Alternatively, if I select segment then why doesn't it automatically display Level 4 leader.


      I tried the following logic but the 'All' statement appears to be only in the filters and not in the actual data set:


      //Select Axis automatically Filter


      If [Business Unit] <> 'All' then [Segment]

      Elseif [Business Unit] <> 'All' and [Segment] <> 'All'

      then [Level 4 Name]

      else [Business Unit]



      again, the all statement appears to be added after the filter is created and isn't a standard part of the list of fields which I completely understand but I was reaching.


      This isn't a MUST DO, it's just a question to see if the results are even possible.  It makes sense in a "Steve Jobs, why does the iPod have a power button?" kind of way.  Why do we have to select the next level down of a hierarchy if the choice has been made of the first category?


      Thanks for any insight.  A sample workbook is attached.

        • 1. Re: Create dynamic Table row headers based on automatic filtering
          Mahfooj Khan

          You've to just edit your parameter to get the desired output. As per my understanding what you want is when you select "Business Unit" then "Segment" should be displayed and when "Segment" then "Level 4 leader" should be displayed. For this just edit your existing parameter like this.

          Just create a calc field to show the hierarchy like this and drag it before axis choice in row shelf.

          Let me know If this help.



          • 2. Re: Create dynamic Table row headers based on automatic filtering

            Hi, actually, I'm wanting it so that if the user selects anything other than viewing all of the business units, the axis automatically changes to show just the segment.  Normally I would want the list of 8 business units shown when the user lands on the page.


            The ask is, If and only if I choose to view less than all business units, all segments are displayed.


            Then if and only if I choose less than all business units and less than all segments then level 4 leaders are displayed.  But my curiosity is if I can do this automatically.  The instant that a user selects to see just his business unit (Say paper clip manufacturing) then the axis automatically switches from showing a list of business units to the various segments of paper clip manufacturing.


            I gave the first two examples to show that there are already things I can do.  My curiosity is if I can get rid of a step for my users. 


            Thanks for thinking about it!



            • 3. Re: Create dynamic Table row headers based on automatic filtering

              I tried this statement which makes us of if then and logic but its still not grouping the 'all' even if you try to trick it with every field in the dimension business unit.



              [Business Unit]='Finance'

              and [Business Unit] = 'Operations'

              and [Business Unit] = 'Services' then [Business Unit]

              elseif [Business Unit] ='Finance' then [New Segment]

                  elseif [Business Unit]=  'Operations' then [New Segment] 

                 Elseif [Business Unit]=  'Services' then [New Segment]





              I know I'm close, I just have to capture an 'All' function when it doesn't exist in the field.  I am going to see if I can load all in the parameter