Small Multiple Charts
- 1 Introduction
- 2 Video demonstration
- 3 Adding A Small Multiples Chart
- 4 Designing a Small Multiples Chart
- 5 Customising the Display
- 6 Drill Options
- 7 Drilling with Advanced selections
- 8 Relationships between Measures
- 9 Tooltips
- 10 Properties
- 11 Bump charts
- 12 Trendlines
- 13 Animated Charts
- 14 Interactive Charting
- 15 Using Percentages
- 16 Reference Lines
- 17 Data Table
- 18 SQL and Excel sourced Charts
- 19 See Also
Small multiple charts is a term popularised by Edward Tufte. They allow comparing data series as they are repeated for different members.
In XLCubed, they are useful for visualising large amounts of data, as hundreds of data points can be plotted and compared easily.
This allows users to find trends and outliers in the data more easily than by trying to study a large table of data.
For a tutorial, try watching our Small Multiple Videos.
Adding A Small Multiples Chart
Insert a chart by using the Dynamic charts option on the ribbon. The dropdown option allows you to build SQL and Excel sourced Charts.
This will insert a new chart and open the task pane. After closing the task pane, it can be reopened by selecting Show Task Pane in the chart's right-click menu.
To move, size or close the task pane, drag it using the window bar or use the drop down menu on the task pane header.
To delete the Small Multiple chart use the right mouse menu Delete option.
You can move and resize the chart using the controls indicated below:
The data within the chart can be refreshed (like a grid) by using the right mouse menu Refresh option
Designing a Small Multiples Chart
The task pane on the right can be used to define which series and members will be displayed.
Categories defines the x-axis within each individual Chart. Series defines the of data series to be charted.
The data can be split into separate charts at any level by dragging and dropping the desired hierarchies into Columns and Rows.
Clicking on a hierarchy takes you to the member tab to select members/levels.
Moving a hierarchy to Filters will apply a filter to the data. By moving the Measures to the Filters you can add to and or change the currently charted measure.
Customising the Display
If nothing is selected on the rows, a better way to display the information can be to turn on chart wrapping. The data still has a common scale and is more easily compared.
Move the Legend
Use the right-click menu to select the position for the legend. Integrated is only available for stacked chart types: it displays the legend text directly next to the series in the chart.
Highlight a Series
Selecting a series in the legend, or directly clicking on a series or category in the chart, will highlight that selection. This can be useful when series overlap to make them easier to read. Multiple series can be selected by using Ctrl. These selections can be outputted to a cell for even more interactivity with the rest of your report.
New in Version 9.1 is the ability to customise a row of charts so they are displayed differently.
Different rows of charts can be plotted on their own scale and chart type (available from Version 9.1).
For example, you might have a row of charts for different measures. In this case you might want a different y-axis scale for each row, and a different chart type. Before making any changes the "Discount Amount" is not easy to see as it shares the same scale as the "Reseller Sales Amount".
In the chart Properties -> Axis Scales tab, change "Y Axis Common Scale" from "All Charts" to "Row of Charts". Right click on a row of the chart to see Row Chart Type and Edit row... options which allow further customisation of the display.
Note this option only applies to charts with a selection on Rows, this feature is not triggered by wrapping charts.
Define the Number of Rows/Columns Displayed
The maximum number of rows and columns displayed can be set in Properties.
From v10.0.79, all the chart's formatting and display settings have been moved to the Format tab of the chart task pane. Prior to this version, these can be found in the Properties dialog.
You can set a number format for each measure if you do not want to use the cube formats. To do this give the chart a name and add a row to the format sheet for each measure you wish to specify. This will be applied to the axis, tooltips and data labels.
You can customise the look of a series by right clicking it in the Legend and selecting "Edit Series..."
From Version 9 you can specify the series colour in the format sheet.
To do this you set the "Item Name" for either the name of the Chart (specified in the options form) or SmallMultiples to apply to all small multiples.
The Hierarchy and Member should be specified, and the colour will be taken from the Data Format cell in the row.
From v9.2.47, a ribbon for formatting a small multiple chart is available. This ribbon will be visible whenever the small multiple task pane is open and will edit the currently selected small multiple.
Click on the area of the chart you want to format, or use the selection drop down in the ribbon, and then make any available formatting changes. Any changes made in the ribbon are also available through the chart's properties, however the ribbon can be useful for quick changes where you can immediately see the results. You can use the 'Select All' option in the dropdown to update all elements of the chart.
See Dynamic Chart Ribbon for more details.
For any of the drill options below, use the navigation buttons in the top left of the chart to go back to the previous view. From Version 9.2, you can Clear Drills which will remove all drills and return the chart to the state as defined in the task pane.
Drill Into: for a breakdown on the chart or axis
Double clicking on a chart column, row, category or area, or using the right-click menu, will drill into the selected member to display its children.
From Version 8.1, you can limit the level to which users can drill. This can be especially useful for hierarchies which have many members at a low level when charting all of these doesn't give a usable chart. This is set in the Lockdown tab of Properties.
Keep Only: for a closer look at a single chart
Click on the chart you wish to keep and use the Keep Only menu option to remove the other charts.
Keep Except: for a closer comparison
Use the right-click menu to remove the selected chart in order to get a better comparison of the remaining charts. The scales will automatically adjust to reflect only the data displayed.
Drilling with Advanced selections
When you drill into a hierarchy with Advanced selections configured XLCubed will automatically remove the Advanced sets.
This is so that the drilled results show all the children, and add up to the parent total. For example these setups would remove children that are under the drilled member.
- Member search, e.g. show accounts begining with "AC0001"
- Ranking, e.g. show Top 5 and there are more children than that
- Filter, e.g. show "Sales > 1,000,000", could result in no data visible if none of the children reach that threshold
However it is sometimes useful to keep the set functions, e.g. if Sorting Categories.
You can do this by adding the Advanced set to the whole Hierarchy rather than the first Member Set. To do this:
- Add a temporary second member set (no selection required)
- Click the toolbar button to add your required Advance function
- Remove the temporary set.
Result - sorting is maintaned after drilling
Relationships between Measures
Colour Coding can be applied to the data points in order to highlight differences.
Drag the hierarchy to plot, normally the Measures, to the Values box and select the Measure to be plotted ( Y Axis).
Tick the Colour box in the Options area of the Task Pane; this makes the colour gradient active.
Select the value to apply the gradient to. This can either be the same Measure that is being charted or another measure altogether.
The colours used for charting can be changed by using the button to the right of the colour swatch.
In the image below, the Reseller Sales Amount is plotted and the chart is coloured based on the Discount Amount. Here we can see that although there were large volumes of Sales in the USA they were also the most heavily discounted.
Colour and Gradient Picker
The colour gradient picker is used to determine both the colours and the rate at which the colour changes.This can have a dramatic effect on the impact on the data being displayed and is a valuable tool for making sure that data becomes information.
Here the only change was made to the centre of the colour bar.
Plotting a Second Series Using a Second Y Axis
It would be useful to view the Order Numbers vs. the Reseller Sales Amounts; if they are both plotted on the same scale it is almost impossible to see the Order Count values as they are so small by comparison. Using the Right Mouse Menu, whilst positioned on the Legend Entry for the series you wish to move, click Move to other Axis; this allows you to see two Y Axis scales, one each for Amount and Count.
Scatter Charts: Plotting a Correlation
Select scatter graph for the chart type and define the Y axis and X axis against which it is to be plotted.
Here we can see how the Sales Amount and Order Count are related - closely in Australia, but spread out in the United States.
Colour can be applied to the chart using a third measure, e.g. Discount Amount.
The chart type can be changed to a Bubble. Using the Discount Amount to determine the size of the bubble it can be seen that the greatest discounts occur in the United States.
Drilling through the individual charts can then provide more details as to where the actual sales occur. eg. in Australia, most Sales with high discounts occur in New South Wales in the Lane Cove area.
Hovering over a data point on a small multiple displays a tooltip. This will, by default, show the category and value for that point. This can be customised, however, in the small multiple properties (see below).
From Version 9.2, it is possible to add more values to be shown in the tooltip. To do this, set a values hierarchy in the task pane. The tooltips button will then be enabled at the top of the task pane - this opens a member selector from which you can define additional members.
In the example below, the measures hierarchy has been placed in values and is plotting Reseller Sales Amount. The tooltip has been customised to also show the value for Reseller Order Quantity and Reseller Tax Amount.
The Small Multiple Chart Properties can be accessed either from the right mouse button pop-up menu -> Properties or from the tool bar at the top of the Small Multiple Charts Task Pane
Axes Controls the minima and maxima of the vertical axes
- Note that it isn't possible to set the minimum to a value between -1 and 0 or 0 and 1, the chart will round to 1. If you need a percentage smaller than 1 then you can create a measure of Value * 100 and set the format string of the axis to 0.00"%" (jump to Using Percentages).
Match to: Set the chart to have the same scale as another chart on the same sheet. The blank option means it is not sharing a scale. Available from Version 10
Max charts on Columns: the maximum number of Charts displayed in each Column.
Max charts on Rows: the maximum number of Charts displayed in each Row.
Note: if the total number of charts exceeds the Number of Rows times the Number of Columns, then some charts will not be displayed. To display them increase either the number or Rows Columns or both.
When Wrapping charts:
- Auto Arrange: XLCubed overrides the Column and Row Counts to give a balanced view of the data.
Note: The Column and Row counts still apply as maximums, so if there are not enough spaces the warning message will appear, i.e. if there are 3 columns and 1 row only 3 charts will be displayed, and a warning message given if there are more that number of charts, however the charts will be displayed as a 2 by 2 block.
- Fix Column Count: Uses the Column count first to determine the number of Columns displayed, then display as many charts as possible given the row count.
Space chart areas: When ticked creates a space between each chart:
Top Padding: Sets the top padding of the Small Multiples chart
Bottom Padding: Sets the bottom padding of the Small Multiples chart
Title Height: Sets the height of the chart title
XAxis Text Height: Sets the height of the xAxis text
YAxis Text Width: Sets the width of the yAxis text
Y2Axis Text Width: Sets the width of the y2Axis text
Legend Size: Sets the size of the legend (From Version 8.1)
Colour Palette: Select the Colour Palette for the Chart
Colour by Category: Make each category have its own colour
From Version 7.6:
Chart background: the colour used to fill in the area behind the charts
Chart area background: the colour used to fill the individual charts
Axis colour: the colour used to draw the vertical and horizontal axes
Gridline colour: the colour used to draw any gridlines on each chart
Chart Type Options
Show columns and bars as cylinders: when ticked, column and bar charts are filled with a 3D effect
Missing values: Defines how the null source values are handled
- Plot missing values as zero: Plots the null values as if they were zeroes
- Don't plot missing values: Leaves a space where the null values are
- Interpolate missing values: Creates a value between neighbouring values to plot
Line chart line width: From Version 7.5, the thickness of the lines for line charts.
Marker style: for chart types that use them (e.g. scatter charts), the shape of the markers:
Marker size: for chart types that use them (e.g. scatter charts, dot charts), the size of the markers
Legend, Labels & Tooltips
Legend: The font used to display the legend items.
Show Tooltip From Version 7.5, select the tooltip to display when hovering over a point in the chart. Placeholders may be used to put data into the tooltip text:
- %value%: The numeric vertical value at the point
- %valuex%: The numeric x (horizontal) value at the point, if applicable
- %category%: The category member at the point
- %series%: The series the point belongs to
- %column%: The column member at the point
- %row%: The row member at the point
- %color%: The numeric value that controls the colour of the point, if applicable
- %size%: The numeric value that controls the size of the point, if applicable
- %percent%: The percentage contribution of a 100% stacked chart (available from Version 10)
Numeric values can be formatted using Custom number formats.
Custom formatted tooltip: From Version 9.2, this option allows you to customise the font and colours of the tooltip.
Output Cells From Version 7.5, XLCubed places the selected member for the selected point into the chosen cell. This value can then be used to drive other charts, formulae and/or Grid selections.
In this example, the Column member Geography – France is placed in $A$26, and Product Categories –  Bikes in $A$27. This value can then be used to drive other charts, formulas and or grid selections:
Link To From Version 7.5, when a point on the chart is clicked, this range is selected.
Clear cells where no selection made From Version 8.1, the cell is cleared if no row/column is selected
- Unique Name: Displays the unique identifier for the selected hierarchy in the selected cell
- Caption: Displays a label of caption associated with the hierarchy in the selected cell
Titles and Axes
From Version 7.5
Chart titles: The font used to display the column and row members
Apply Cube formatting: Uses the measure's number format to format labels
- Label font The font used to display the labels on the axis
- Label angle The angle to show the label text at
- Allow label truncation Allows label text to be shortened in order to display tidily
- Stagger labels Allows alternate labels to be shifted in order to improve readability
- End label visible Shows or hides the last label
- Primary axis number format The number format to use for the primary vertical axis
- Secondary axis number format The number format to use for the secondary vertical axis
From Version 8.1
- Automatic Title Allows entering the axis titles for the x and y labels
- Category Labels: Determines the display of the Category Labels on the Chart.
- Automatic: XLCubed will decide how many labels to display, based on the space available.
- Every Category: each category will be displayed regardless of the space available.
- None: No labels will be displayed.
- Custom: The labels will be shown based on a number you enter.
Chart Name the Name of the Chart for easy reference.
Placement Controls the behaviour of the chart when surrounding cells are moved and resized:
- Free floating: The chart ignores all cell size changes
- Move: The chart is moved together with the surrounding cells, but is not resized by them
- Move and Size: The chart is moved and resized together with the surrounding cells
Show Back/Forward controls Controls whether back and forward controls are displayed on the chart to undo and redo changes made to it
Enable high performance charts If ticked, high performance charts are used
From Version 8.1
Limit drilling to: Limit the chart to drill down to a specified level only
These options refer to the animated chart style introduced in Version 9.2.
- Animate new data: when this option is enabled, the chart will animated when refreshed/drilled into etc.
- Animation duration: this controls the speed that the animated hierarchy will play.
- Zoom mode: setting a zoom mode adds a control to the bottom of the chart area from which you can make a selection to zoom in on the hierarchy on categories. This can either be None, Slider or Mini-chart (see below).
- Zoom area height: sets the height of the zoom control.
- Zoom on slide: if a slider has been set as the zoom mode, this option will enable the chart to zoom as soon as the slider has been dragged. With the option turned off, the chart will zoom once you have finished dragging and released the mouse.
- Zoom on charts: when this option is enabled, you can highlight an area directly on the chart to zoom in on it. This does not require a zoom mode to be set but can be used in conjunction with one.
- Sync points by category
These are new in Version 8. Bump Charts plot the rank position rather than the actual value. For example if I sell a product in a marketplace with 10 other competitors I may like to see how the rank position of my product and the competition changes over time to see if we are gaining or losing market position. You must have more than one series so that the rankings can be compared. Usually you will want dates on the category axis so the trends are shown across time.
Bump charts are only available on small multiples based on cube data.
New in Version 7.5 - you can right-click on a Series (or the chart for all series) to add a trendline:
You can then select the type of trendline and formatting in the Trendline Options
From Version 9.2, small multiple charts now animate when displaying new data. This can greatly improve the appearance of your reports and dashboards, giving a smoother user experience as well as helping to better visualise changes in data.
Adding a hierarchy to the Animate charts by area of the taskpane adds a play control to the bottom of the small multiple. Use the task pane to select which members to play through. Then, by using the controls, you can play through all the members, forward/rewind through one at a time, or jump to the first/last.
The speed that the charts plays through the members is set in the chart's properties.
In the example below, Fiscal Date is set as the animated hierarchy and the selected members are descendants of 2018 at month level.
From Version 7.5, it is possible to track any selected element of a Small Multiple Chart. This includes an axis element, an individual chart, or any data point in the chart. This information can be outputted to a range of Excel cells, and then other parts of the overall report, including grids and formulae, can be based on the content of these cells. The chart will also highlight which chart or datapoint is selected.
These output properties are configured in the properties for the chart control. To track an element, select the relevant checkbox and chose the desired output cell.
'Link to' is also available as an option, where the focus can be moved to another area of the report in the context of the selected chart or chart element.
From Version 9.2, charts can be zoomed in on to highlight areas of interest. This is set in the chart's properties. The area to zoom in on is selected either on a slider or mini-chart below the main chart, or directly on the chart area itself.
If a zoom mode has been set (i.e. a slider or mini-chart) the zoom can be reset (zoom out completely) by using the button to the right of the zoom control. If "zoom on charts" is enabled, the zoom can also be reset by single clicking on the chart area.
The minimum value for an axis must be a whole number, which can be an issue when using percentages as you may want the scale to go from 0.5 (50%) to 0.9 (90%) for example.
You can do this by plotting the numbers multiplied by 100, and then setting the number format of the axis to display the % sign after the number using the format 0"%".
This means the scale starts at a whole number and the axis labelling is also correct.
If you need more precision you can also use a format like 0.00"%".
As the axis is scaled by 100 you must also scale the values being plotted. If you are creating a small multiple against a cube the easiest way to do this is to create a Custom Calculation with the expression [Measures].[My Measure] * 100 and plot that.
New in v8.1 Small Multiples is the ability to draw a reference line behind your data.
You can have one or two lines, and optionally shade the area in between them.
The reference values come from an Excel range. You get a line for each row of data, and the shape of the line is determined from the values in the columns.
Reference lines are not available for Bar charts.
Also new in Version 9.1 is the ability to show a data table of the numbers behind the chart, and to copy these into Excel for further analysis if required. Simply select "View Data" from the right click menu to pop up the numbers. You can click on a chart area to get the data for that individual chart, or on the category labels to get the data for all charts at once.
SQL and Excel sourced Charts
7.5 introduces the capability to build small multiple charts from non-cube data. This can be any Excel range, or directly from a SQL query or stored procedure. When inserting a small multiple chart from the XLCubed ribbon, you can now specify which data source this is for.