Database Reference
In-Depth Information
This is of course not a scale break, which you may be familiar with from
Reporting Services (which has it as a built-in option).
There are some (relatively tricky) ways of getting scale breaks to work in Excel:
The one you are going to work through now is reliant on being able to create
custom measures. This is a feature specific to Excel 2013—in Excel 2007 and
2010 you need to download a tool called OLAPPivotTableExtensions from
http://olappivottableextend.codeplex.com/
. You will be continuing to
use the same pivot table you used in the previous example.
oTher sCAle BreAK TrICKs
Jon Peltier has some great alternative tricks to building a scale break, which
are based on the data residing within Excel and not a cube. He calls them
Panel Charts, and you can read more about them at
http://peltiertech.
com/Excel/ChartsHowTo/PanelUnevenScales.html
.
Start by removing your current measures from the pivot
table. Alternatively, if you are creating a new chart, make
sure to add the YMD hierarchy to the filters section, filter
for 2011, add the Regions hierarchy to the Axis category,
and filter for Switzerland and Estonia.
Next, choose the appropriate scale break values. Given how
low the value for Estonia is, you can set the upper scale
break to 2000. You will do this by going to OLAP Tools on
the Ribbon's Analyze tab and choosing MDX Calculated
Measure, as shown Figure 12-30.
F I g u R e 12 -30
Adding an MDX Calculated Measure
As shown in Figure 12-31, call your new measure LowerReserve and put it in
the FactOECDNationalReserve measure group. Use the following code—all it
does is set any value higher than 2000 to 2000.
CASE WHEN [Measures].[LatestReserveValue] > 2000 THEN 2000 ELSE
[Measures].[LatestReserveValue] END
Add this measure to your pivot table by dragging the field name from the
right-hand pane onto your pivot table. Now right-click the axis on the right
side and choose format Axis. Set the number format to currency, and set the
maximum to 4000. Because you are splitting the axis in two, this setting is
always double the maximum value for the lower reserve.
