I had a couple of emails referring to a particular new features guide here. It has been said in this new features guide that one can achieve highly formatted financial layouts and also an example is given in the form of screenshots. A couple of users wanted to know how to go about achieving the same. Now lets start with exactly the same pivot table as shown in the guide above and try to achieve the same.
In the final layout, we only need the BalanceGroup1, AccountDesc and Amount columns. So lets hide the remaining 2 i.e BalanceGroup2 and BalanceGroup3. We are not removing these columns since we need sub group sums.
Once this is done, if you look at the final layout required, there is a line break between BalanceGroup1 and Account Desc. So, we would have to move the BalanceGroup1 column to the Sections area.
If you notice, we still have to position the Assets to the left and then remove the tabular headings. In order to achieve this, go to the Section Properties and insert Page Break on the BalanceGroup1 column. After that go to the Measure Labels section and hide the headers.
Now, we need to include to the sub-totals for both BalanceGroup2 and BalanceGroup3.
Next is to left Align the Assets value. Go to the Section Properties and change the Horizontal Cell Alignment to Left.
Next we need to change the label names to something Total Assets etc. In order to do this we shall use “Total @”. @ will bring the value of the column.
Now the next step is to add indentation to all the Totals and subtotals. Go to the hidden BalanceGroup2 total Label properties and add a left indentation of 25. Similarly add the left indentation for BalanceGroup2 to 50 and AccountDesc values to 75.
Our result so far will be like this.
Now the next step is to remove all the border lines. In order to do this go to all the Columns and Section properties and change the Border Position to None. For example go to AccountDesc Format Values and change the position to None as shown below.
The following pic shows all the places that you need to change the Border Position to None.
The next step is to change the background color of the subtotals and the AccountDesc to #FFFF (that will give a white background). And lastly if you want total values to have 2 horizontal bars on the top just go to border position of the sub total and include the top two borders.
And this should give you the desired Financial Template Layout.
I know some of the above is pretty trivial. But this should give you an idea of how to achieve very good formatting with pivot tables.
Posts
