Getting in Control of Your Excel Dashboards: Combining Cube Functions and Form Controls

This is a story about control, form control. Control of what Excel says, control of what Excel does. And this time Excel is gonna do it my way. I hope you enjoy this as much as I do. Are we ready? Excel is, ’cause it’s all about control and Excel’s got lots of it.

I may be the only one to relate Excel Form Controls to a Janet song but they really are a powerful tool. They are also helpful when designing dashboards in Excel. Space is always a premium in dashboards and leveraging form controls with cube functions can alleviate those issues. In this post, I will describe how you can use a scroll bar to create a space-efficient list of sorted values and a combo box to change how the list is sorted.

(I used an AX cube and data for this example, but it applies to all SSAS cubes, including those that come with GP.)

My Scenario:

• Create a list of customers sorted on either Sales Amount or Units Sold
• Ability to change the sort value
• Display both the Sales Amount and Units sold for each customer
• Display only 5 customers at a time with the ability to view all customers
• Add a visualization to the lists in order to compare values at the bottom of the list to those at the top

How I Did It:

The first action was to create a good starting point pivot table and then convert it to formulas. To save a little typing, here is what my pivot table looked like (it should give you an idea of where I’m headed):

Once that was built, converting the pivot table to formulas gave a good starting point for the dashboard.

The next step was to create two CUBESET’s; one sorted for each of the two measures in my list. To refresh your memory on the CUBESET function, its syntax is as follows:

=CUBESET(connection, set expression, caption, sort order, sort by)

=CUBESET(“AX Sales Cube”,”([Date].[Month].[All].[“&B2&”],[Customer].[Customers – Name].[All].children)”,2,D4)

I created two sets, both for all of my customers (that’s what the “children” suffix does), with one sorted by the Invoice Line Amount and the other sorted by the Units Sold. I also made sure to include the date selected by my report filter as part of my set expression.

With my CUBESETs in place, I added a combo box form control to my sheet. In its format control dialog box, I set the Input Range to the cells where I had defined my CUBESETs and set the Cell Link to a cell off the viewable area of the dashboard. The Input Range is the list of values displayed in the combo box, so it is helpful to give your CUBESETs good captions. The Cell Link is the cell in your worksheet that will hold the value of the form control. If the first option of the combo box is selected, then that cell will have a value of 1, incrementing by 1 for each option. This combo box and its cell link will be used in selecting the sort order later.

After the combo box was in place, I added a scroll bar to my worksheet. In its format control dialog box, I selected a Cell Link to an appropriate cell in the worksheet (somewhere where it won’t distract from the dashboard).

With those two form controls in place, no the really fun Excel stuff could begin. The main star of this segment is the CUBERANKEDMEMBER function with support from IF, ROW, and OFFSET. Here are their respective syntaxes and how I eventually combined them to return the member I wanted:

=CUBERANKEDMEMBER(connection, set expression, rank, <optional: caption>)

=IF(logical test, value if true, value if false)

=ROW(reference)

=OFFSET(reference, rows, columns, <optional: height, width>)

=CUBERANKEDMEMBER(“AX Sales Cube”,IF($J$2=2,$I$6,$I$5),ROW(OFFSET(AB1,$J$1,0)))

In my formula, I choose between my two sets based on the value of the combo box (J2 is its cell link) and then get the rank based on the value of the scroll bar (J1 is its cell link). When the scroll bar is at the top, J1 is 0, and the rank is equal to the row of cell AB1, which is 1. For each click down, the rank will increment by 1.

With that knowledge of the CUBERANKEDMEMBER function and some formatting, the first four bullets of my scenario can be marked as complete.

What about the last bullet?

In order to visualize the data within the list, I used Data Bars from Conditional Formatting to add a fill to each cell with values. By default, the bars will adjust to max value displayed, which was an issue when scrolling through the list. To correct that, I made sure that I got the first rank value for each set and changed the maximum value of the conditional formatting rules to that value.