Today’s blog is going to show you how to use XLCubed’s custom calculation functionality to create column breaks in a grid. Imagine that you have a report that shows you Reseller Sales across Product Model Categories over a 12-month time period.
There’s nothing wrong with this report but don’t you think it would be nicer if there was a way to separate out each quarter block ie put in a divider column between March and April, June and July, September and October. That would make it much easier to read and show clearly where each quarter period started and ended.
So let’s start by creating a custom calculation. Click the highlighted icon and give your custom calculation a name – let’s call it ColBreak. It’s connected to the Date.Calendar hierarchy.
Now in the Expression area enter a blank string starting and ending with ” (double-quote). Click OK.
To insert this into our report we now go to the Hierarchy Editor for Calendar Date – expand the All member and you will see ColBreak.
Drag this across and insert it into the report. We will insert it after March, June and September and click OK.
The report now looks like this:
Now let’s format this column break so that the we don’t see ColBreak appearing as a column heading. You need to right-click to get XLCubed’s right-click menu and then choose Format This Member.
We will choose white for the Font colour before clicking OK.
The report now looks like this with clear demarcations between each quarter:
Hi,
This is a nice trick. But you are running in trouble with the zero supression on rows. If a row element has no values, the zero elements are not supressed, because of the calculated member in the column. Is there a good workaround for that issue?
Hi Christian,
Thanks for your comment.
You could change the calculation to something like:
iif(IsEmpty([Date].DefaultMember), null, ‘ ‘)
You could change [Date].DefaultMember to a specific year to check for the presence of data.
Thanks!
To avoid issues when printing in black and white one could create a calculation named ” ” (one or more spaces) or use a number format like “;;;” to make the name of the empty row or column invisible!
Thanks to Craig Salter from xlCubed for this!
arno