Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello everyone,
This is how my pivot table looks like:
John Doe John Smith Jane Doe
June
July
August
However, is there a possible way to make it look like something this:
Employees
June
July
August
And then under employees, there will be that + sign to expand and see what's inside Employees
Thank you
Create a field in your data model with a constant 'Employees', like
LOAD
Employee,
'Employees' as EmployeeDim,
...
Then use EmployeeDim and your existing Employee field as dimensions in your pivot table chart (plus the Month field).
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.
Just as a side note, John, I think a constant value won't occupy any memory (in the bit stuffed pointer) in the record table (that's something I learned recently in a discussion following one of Henric's blog posts). You can see this when looking at the field bit information when storing this table to a QVD.
Symbol tables should be mostly the same in both cases.