3 Replies Latest reply: Jun 24, 2016 6:19 PM by Stefan Wühl RSS

    Create an Expandable Table

    Roy Morales

      Hello everyone,


      This is how my pivot table looks like:


                                   John Doe      John Smith     Jane Doe








      However, is there a possible way to make it look like something this:









      And then under employees, there will be that + sign to expand and see what's inside Employees


      Thank you

        • Re: Create a
          Stefan Wühl

          Create a field in your data model with a constant 'Employees', like




               'Employees' as EmployeeDim,



          Then use EmployeeDim and your existing Employee field as dimensions in your pivot table chart (plus the Month field).

          • Re: Create an Expandable Table
            John Witherspoon

            I'd probably do it just as swuehl says.


            But just to toss out a different idea that isn't exactly what you asked for but is similar in flavor, sometimes I'll create a value of 'Total' in a field [Total] disconnected from my data set, then create cyclic group(s) with [Total] and whatever field(s) I might want to break that total down by in the chart(s). In this case, something like:


            [Employees]: LOAD 'Employees' as [Employees] AUTOGENERATE 1;


            And then create an EmployeeGroup with [Employees] and [Employee] and use the group as the dimension.


            But it's a pivot table, so people will probably be more used to looking for the + button, and will know what it does where they won't know until they try it what clicking on the little cycle icon will do. So probably best to stick with just putting both fields in the pivot table. You could still leave [Employees] disconnected from the rest of your data model, though. A little more script, a tiny bit less memory used. I doubt speed would be any different either way, but I don't know. The 'Total' approach is really more for when you have more than one way you might want to break the data down by, or you might just want to look at the total.