What are the 2 differences in the tables below ?
Well there are a few but the 2 ones I want to focus are
1. The object on the left is a TABLE object and the object on the right is a PIVOT TABLE object
2. The object on the left has column headers aligned with the details, and the object on the right does not .
So if you are looking for a solution to display an expanded pivot table with headers that align above their rows then you can use the attached and described solution to sort and conditionally render the higher level dimensions so that the table looks like a pivot table.
In this solution the following alterations have been made to the TABLE:
1. The dimensions are NOT dimensions , they are measures. The exception is the lowest level granularity field , in this case Customer which is a dimension.
2. The measures are conditional. They check the value directly above them using the chart inter-record function above() to see if the current value is the same as the previous value. If it is, display a blank or '' value. Since they are measures, we need to aggregate the values by their own dimension to do this comparison.
= if( aggr([countries_world.Name], [countries_world.Name])= aggr( above( total [countries_world.Name] ,1), [countries_world.Name]),' ', aggr([countries_world.Name], [countries_world.Name]))
3. We are doing some funky sorting based on some additional fields added to the data model
a. the leading sort field is 'Customer' which has a 'sort by expression' defined as: CountryCityCustomerID
b. CountryCityCustomerID is a compound key with a dual() value that has a text + a numeric value
Below you can see that I am sorting the Customer table by Country, then City, and then Customer.
I also create a new dual value for Customer where the text of the dual is just Customer, but the number is a sequence number (1,2,3...) that is unique for each combination of Country,City and Customer.
The country that comes first alphabetically and the city within that country that comes first alphabetically and the customer that comes first in that City alphabetically will have a value of '1'.
If there are more customers in that same city and same country, they will be numbered 2,3,4 in their alphabetical order.
Once all the customers have been numbered in thet first country and city, it moves on to the 2nd City and enumerates the next customers (5,6,7 ..) and on and on through each other City and each other country and their cities all in nest alphabetical order.
So when we rended customer in a table, we can sort by the numeric value .
dual( [Customer],AutoNumber([countries_world.Name]&City&Customer,'CountryCityID')) as CountryCityCustomerID,
order by [countries_world.Name],City,Customer;
drop table Customers1;