Axis Ranking

XLCubed provides two modes of Ranking: Hierarchy Ranking and Axis Ranking.

The first is ‘Hierarchy mode’, and is accessed through the Advanced tab on the member selector for the relevant hierarchy. In this model, the ranking is contained within the hierarchy itself, so that if you move the hierarchy from rows to columns, the ranking is then applied on columns. The screenshot below shows a grid displaying the top 5 Products for CY2004. On the right, Geography has been added to rows, so that the Product hierarchy is nested within Geography.

As the top 5 ranking has been added to the Product Model Categories hierarchy, we now get the top 5 products for each split of the Geography hierarchy.

For further information on ‘Hierarchy’ Ranking, Filtering and Sorting go to: Ranking,_Sorting_and_Filtering

Axis Ranking, Filtering and Sorting

Axis Mode is a different approach to Ranking, Filtering and Sorting, and can be thought of as result set ranking. Here the Ranking is applied to an entire axis. So for example if you apply a top 5 ranking while Geography is on rows you get the top 5 Geographies, but if you switch Geography with Product, you will get the top 5 Products without having to apply the ranking. Where you have two hierarchies on the axis, the result will be the top 5 intersections between the two hierarchies, as shown below.

Axis ranking is applied through XLCubed’s right-click menu, and is based from the column header, not from within a specific hierarchy. Right-click on the column header that you wish to rank (in our example you would right-click on CY 2004) and then select XLCubed, Apply, ranking, Top x Rows. You can see that Top 10 and Bottom 10 appear as options – if you wish to rank any other count you select Top x Rows/ Bottom x Rows and you will be prompted to enter a figure.

You will notice that the first member on the right-hand hierarchy has a little red indicator in the corner – see screenshot below:

If you hover over this cell it displays an informational message that the rows are being ranked and that you can right-click to edit axis or clear the axis ranking.

Clear All Use this option to clear all axis ranking options.

Edit axis You will be presented with the window below showing the current ranking setting. You can click the drop-down box to amend this if necessary.

The excluded slices tab allows you to remove certain members from your report.

To add an exclusion list click

To Clear All current exclusions click

We will add France to our excluded slices list. We select the Geography hierarchy and then in the Selection area choose France. When we click OK France appears in the top half of the window as a member to exclude.

The report is refreshed and you can see that France has been removed from the report.