A common scenario for Analysis Services reporting is to want to present different measures for different members, particularly in budgeting and planning. So I want a grid that shows actuals for previous month, budget for this month and forecasts for future months.
This could be achieved in the cube, by using a “Phasing” measure to switch to the different values but quite often our customers are not in control of the cube structure.
We will look at a way to achieve this from within XLCubed itself by working through an example.
So this is our initial grid – it is currently set to report both Revenue and Discount values across all quarters in 2002 – 2004.
Let’s create a couple of slicers – one for Revenue:
and a similar one for Discounts:
The settings tab for Discount slicer is:
You can see that this is a multi-select slicer allows which updates an Excel range with the slicer choices.
The entries in the Excel range are referred to by their ‘Unique Name’ eg for Quarter 2 2004 equates to April 2004.
The settings tab for Revenue is similar except it outputs to different cell locations:
In this example our Discount slicer choices are Quarters 2 & 3 in 2004 and our Revenue slicer choices are Quarter 4, 2004.
Now let’s set up the excluded data – remember that we when reporting Revenue rows we want to exclude the Discount slicer choices and vice versa.
Right-click on the Discount header row and then select Exclude from Display
You will now see a red triangle appearing in the corner for the first member of the hierarchy which has excluded data. If you hover over this cell it displays an additional message that the rows are being restricted by members and that you should right-click to edit axis (it’s the menu option just after grid charts).
In the Axis Designer window pick the Excluded Slicers tab and click in the lower-half of the window (highlighted) – this is where we are going to define the quarters that are to be excluded on the Time hierarchy.
Select the time hierarchy and then click the box to its right (highlighted in screenshot below) and click the box next to the drop-down so that you can pick the Excel range – in our example it is cells I23 through to I28 (the Revenue slicer choices that we do not want reported as Discounts). Clicking OK will refresh the report and only show Discounts rows for the quarters selected.
We now need to do the same for Revenue so right-click on the row containing the red triangle and set up the Revenue excluded slices in a similar way. Click the icon highlighted to add a new exclusion. This will be cells A23 through to A28 (the Discount slicer choices that we do not want reported as Revenue).
Click on the new exclusion row and then in lower half of screen build up the Revenue exclusion in the same way but remembering to point to the Excel range to cell locations A23 to A28.
You should end up with an Axis Designer window something like this – for Discounts exclude slices in cell locations I23 – I28; for Revenue exclude slices in cell locations A23 –A28.
OK to apply these changes and the report now looks like:
As you can see the report shows Discounts for Quarters 2 & 3 in 2004 but only shows Revenue for Quarter 4 in 2004 and because everything has been linked to ranges driven by slicers, the user of the report can easily control the switch in measures.