Most Recent List in Xcelsius

Most Recent List in Xcelsius

Imagine a dashboard with a set of charts. Imagine this dashboard has a dropdown that allows you to filter the charts for a particular country. You select one country, check it out; then another, and so on. But switching between countries involves clicking a dropdown and finding a country in a list of 200. There must be an easier way, right?


I’m Using:

• Dashboards (Xcelsius) 2011 SP5, but the steps are the same from about 2008 onwards.

I’m Assuming:

• You should have Xcelsius installed, ready to go, and a basic understanding of what components are, how to bind to a range, set properties etc.

The idea of this trick is that you don’t need to sort through a list of objects in a long dropdown box each time you want to select another item. As you select, say, a country from the dropdown list, it will be added to a ‘most recent’ list (MRU), which will show the ten last selected countries. Clicking a country in this MRU list will have the same effect as selecting that country from the dropdown list.
The example I use throughout is a list of countries, but the logic is of course the same for cost centres, or products, serial numbers, etc.


The End Result


Here I have an example of a dashboard that shows the revenue for a selected country, by default showing data for Australia. If I select Canada from the list…


I will see a some objects show up below the dropdown list:


If I continue selecting different countries from the dropdown, this list will grow (to a maximum of ten). Clicking on any of these will do the same as selecting from this list.


Duplicates won’t show, and clicking one of the buttons will not change the order of the list.

The How To

1. Add a combo box component to your canvas. I'll use the terms combo box and dropdown list interchangably.
2. Bind the component to a range of cells for the labels and also the insert destination.
3. For now, set the insertion type to Label and put the insert destination at the top of the list, like so (I use yellow cells where a component writes back to the spreadsheet).
   
4. Add a History component to the canvas. This is in the Other category. This component ‘watches’ a cell and puts that value into some other range of cells, either when the value changes, or on some interval.
5. Bind the Data value to the cell that will contain the selected country name, set the destination to a range of cells 1x10 (the yellow cells in the screenshot below).
   
6. For testing, as always, add a spreadsheet component to the canvas and bind it all those cells so you can see what’s going on. Don’t be shy, bind it to a big range, have ten of them if you need, the spreadsheet component is your friend.
   
   
   When you preview, the first thing you’ll notice is that as you change the value in the dropdown, the range of ten cells that the history component writes to is filled from the bottom up. I’ve no idea why.
   
   We’re getting somewhere, we have a recently used list. But it’s upside down. And potentially contains duplicates. And is not buttons. So next, let’s flip the list right-way up.
7. Down the left of your recent list, type the numbers ten to one. In another range of cells, type the numbers one to ten. (I use green cells for typed, static text.) I’ve also typed in some fake data to the yellow cells. No formulas so far.
   
8. In another column (called ‘Sorted’ below, I use orange cells for formulas), use a vlookup formula to flip the list. E.g. the first row will look up the number 1 in the ‘raw’ list. Which is the bottom row. The second row down will look up the number 2, which is the second bottom row, and so on.
   
   
   Now we have a list up the right way, but it could potentially contain duplicates (if the user clicks Australia, then Canada, then Australia again).
9. Add a column to the left of the sorted column (it’s important that it’s on the left) with a formula that counts only unique values. The idea is that you want to be able to look up the first instance of each country name, and not count a country that is appearing for the second time.
   
   
   Notice how there is a 1, 2, 3, 4 next to the first four items, but there isn’t a 5 or 6 because Canada and Australia already appeared in the list. For each row, the function counts how many times that country has appeared in the list above. If it has already appeared, then the number isn’t incremented. If it hasn’t appeared, then the number is incremented. This is why there’s a zero at the top of the column. Below is the formula in cell L4.
   
10. The next and final step is to pick out the unique countries from the list. This is done by looking up 1, 2, 3, 4, etc in column L above. As you can see, there is no number 5, so your formula must recognise this and return a blank, rather than an error. The column and formula look like this:
    
    
    So that’s most of the formula trickery out of the way. Next up and easy bit, then a not so easy bit, then done!
    
11. Add a Label Based Menu to the page. Set it’s Orientation to Vertical, set (on the Behavior tab) it to Ignore Blank Cells.
12. Bind this to the range of cells from the No Duplicates column in the screenshot above.
13. Preview your dashboard and check that it works. If you’re like me, you don’t get anything right the first time; add a spreadsheet component so that you can see what’s going wrong in the spreadsheet.
    
    That was the easy bit.
    
    You’ll want to be able to select a country from the dropdown list, or the label based menu that you’ve just added, right? And whatever you select in one should overwrite the other selection, otherwise you wind up with this battle of two components both trying to write their value to the same destination.
    
    My favourite trick to work around this is to have your source data lists in columns next to each other, but starting and ending in non-overlapping rows. When you select something from one column, it will overwrite the selection from the other column. It does this by taking the whole row. Which will be the country selected, and a blank cell.
    
    
    Take the above screenshot. I have selected Australia from the dropdown list. If I click on one of the most recently used countries (the list in orange) then it will copy that country, and the blank cell to its left, up to the two yellow cells at the top. Thus selecting from either list will overwrite the selection from the other list. The cell in the top right is a simple concatenation of the two cells.
14. Update your dropdown menu to use the insertion type of Row, do the same for the label based menu and arrange the two columns as above. Then bind the Selected Item property of the dropdown to the cell in the top right from the screenshot above (“Australia”). This ensures that the dropdown value changes on the screen when the user clicks a country in the MRU list. The above setup also means that the MRU list won’t update when you click on it.

From here, you use the usual process to select whichever data you want to display in your charts or other components. If you want the most recent 50, or 5, just change the ranges for the history component and associated formulas. If you want to get fancy, you could use a scorecard component rather than a label based menu and show some additional data (e.g. average revenue or a traffic light).

So there you have it, a handy little most recently used list.


Happy clicking!

- See more at: http://blog.davidg.com.au/2012/11/most-recent-list-in-xcelsius.html#sthash.43DnccIz.dpuf