In-Cell Charts in Grids

In-Cell Charts can be used in two ways within XLCubed Grids.

Firstly, they can be selected in the measures dialog. This is termed a Visual Grid. In this scenario, the In-Cell Charts behave in the same way as a standard measure, and hierarchies can be dragged and repositioned during analysis with the In-Cell Charts moving in sequence.

Secondly, in an environment where the hierarchies on rows and columns will not change, In-Cell Charts can be inserted as Grid Calculations. This allows for the use of sparkbars as, for example, deviation analysis, or bullet charts. These can be configured to cater for row dynamic reporting as outlined below.

Visual Grids for Time Series Analysis

Visual Grids add visualization capability to the dynamic XLCubed Grid. In-Cell Charts add historical context to the headline numbers and allow fast, effective parallel comparisons of trends and patterns, while fully retaining the slice and dice environment. So in any XLCubed Grid you can now choose to display just the numbers, just the historical trend through In-Cell Charts, or indeed both as below:

In this case we can see a revenue fall across all products for Europe in the same time frame, which should be investigated further and understood. There are a large number of formatting options around the charts, for example it's possible to use a common chart scale across the whole Grid, which can be used to good effect with column or area charts.

The process of adding a chart into a Grid is initiated through the measure selection. In the Hierarchy Editor for Measures, a drop down menu is available which gives the option to display the value (default), or to chart the trend:

On selecting InCell Chart, a dialog appears to allow you to configure the chart. The chart types most relevant for time series analysis are the Line, Area, Column and Win Lose types. Further configuration options are available depending on the chart type.

Once the chart type is chosen, the time context must be specified. The dialog defaults to the time hierarchy (or the first time hierarchy if there are several). Choose the required hierarchy, then select the members you wish to chart:

Visual Grids for Performance Analysis

We will use Visual Grid charts to show a chart for Gross Profit and when its margin falls below a specified value.

Start by following the MDX Calculations tutorial to create the initial report.

To create the Gross Profit bar chart:

Open the Hierarchy Editor for Measures by double clicking on the Measures hierarchy label.
1. Click on the Gross Profit dropdown, and select InCell Chart:

2. Select Bar as the chart type and choose the one with a value axis as the subtype:

3. Select just Current Member for the Hierarchy members to chart.

To create the Gross Profit Margin alert indicator:

4. Select the Gross Profit Margin dropdown, and choose InCell Chart.
5. Select 3-State Alert Icons (Red Shade) as the chart type:

6. Click the Chart Format button to change the format, and change the first colour's condition to > 0.6 and the second colour's to > 0.55:

7. On the Label tab, set the chart label to Alert, then click OK
8. Select just Current Member for the Hierarchy members to chart.
9. Click OK again to accept the chart.
10. When we run the report, we see all of the Road product group is in a warning state, and Road S8000 is in the alert state:

Switch Members

New in v8.1 you can easily switch the member that the chart is based on.

Simply click the ellipsis highlighted and you will be presented with the window where you can change the hierarchy member - this can also be an Excel range: