In-Cell Charts in Grids

Revision as of 12:58, 15 October 2010 by Antonio.remedios (talk | contribs) (Created page with 'MicroCharts in Grids MicroCharts can be used in 2 ways within XLCubed Grids. Firstly, they can be selected in the measures dialog. This is termed a Visual Grid. In this scenari…')
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)

MicroCharts in Grids

MicroCharts can be used in 2 ways within XLCubed Grids.

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

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

1. Visual Grids for Time Series Analysis

Visual Grids add visualization capability to the dynamic XLCubed Grid. In-cell MicroCharts 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 MicroCharts, or indeed both as below:

InCellChartsInGrids1.png

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:

InCellChartsInGrids2.png

On selecting MicroChart, 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. MicroChart formatting options are covered in depth in the MicroCharts help file.

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:

InCellChartsInGrids3.png

2. 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:

  1. Open the Hierarchy Editor for Measures by double clicking on the Measures hierarchy label.
  2. Click on the Gross Profit dropdown, and select MicroChart:
    InCellChartsInGrids4.png
  3. Select MicroBar as the chart type and choose the one with a value axis as the subtype:
    InCellChartsInGrids5.png
  4. Select just Current Member for the Hierarchy members to chart.

To create the Gross Profit Margin alert indicator:

  1. Select the Gross Profit Margin dropdown, and choose MicroChart.
  2. Select 3-State Alert Icons (Red Shade) as the chart type:
    InCellChartsInGrids6.png
  3. 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:
    InCellChartsInGrids7.png
  4. On the Label tab, set the chart label to Alert, then click OK
  5. Select just Current Member for the Hierarchy members to chart.
  6. Click OK again to accept the MicroChart.
  7. 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:
    InCellChartsInGrids8.png