02.03 Layout - Pivoting Across
As covered in the first reporting session, every report has a down axis and an across axis.
For many transactional reports the across axis simply contains the Measures dimension, the set of things that you want to add up and everything else is in the down axis.
However, it is common, particularly in GL, to have more than one dimension in the Across Axis.
Here is a video, or read below - don't forget the exercises at the bottom!
So to start off with we looked at this example from AR.
Let’s look at another simple report, in this case a GL balances report:
Again, those textual columns to the left form what is called the “Down Axis” and the column headings to the right form the “Across Axis”. Ie:
In this example, the down axis is made up of the Company, Division, Department and Account dimensions.
And in the across axis: Year, Period and Measures.
By default, every combination of selected members in each of the across dimensions has its own column.
Hence, in the across axis, if we select
the members “2020” and “2019” from the “Fiscal Year” dimension, and
the members “Period 1”, “Period 2” and “Period 3” from the “Fiscal Period” dimension, and
the members “Actual” and “Budget” from the “Measures” dimension
we will get the following (2 x 3 x 2 = 12) columns:
2020, Period 1, Actual
2020, Period 1, Budget
2020, Period 2, Actual
2020, Period 2, Budget
2020, Period 3, Actual
2020, Period 3, Budget
2019, Period 1, Actual
2019, Period 1, Budget
2019, Period 2, Actual
2019, Period 2, Budget
2019, Period 3, Actual
2019, Period 3, Budget
Dimensions can be pivoted to the Across Axis using the Rows and Columns dialog
There is also a degree of pivoting available in the right click menu on the grid.
In the down axis, you can pivot the dimension to the across:
And in the across axis, back to down:
Combined with the removal of detail columns, this is very effective at producing summarised reports from detailed ones.
For example transforming that early AR Detail Report
into a summarised report built from exactly the same data
Hiding Repeating Labels
One issue that occurs when pivoting multiple dimensions into the across axis, is that visually it can be quite hard to interpret what you're looking at, for example:
is quite cluttered.
Clicking the "Hide Repeating Labels" button on the toolbar:
removes this visual clutter making the across axis much easier to read:
Now try the downloadable exercises below:
