Paging in Xcelsius

Often, there will be a lot of data that your users would like to see on a page, and there's never quite enough space. In this post, I'll create two page navigation buttons that will allow users to move through many 'pages' in a dashboard.
I'm Using:

• Dashboard Design 2011 (the steps are the same in Xcelsius 2008)

I'm Assuming:

• That you know your way around Xcelsius and some basic Excel formulas

 The Finished Product


For this example, I want to show a list of stores, ranked by a number of metrics. Each page will only fit five stores, so I will use page buttons to let the user move throughout the list. The overall flow for this project is this:

1. Arrange your source data. This will include all stores.
2. Define a region that the scorecard will read from. This will include only five stores.
3. Create the page number logic that will allow the user to move through the pages.

The Source Data

My source data contains one row for each store, and a static number of columns for the metrics that I want to report on.

The Scorecard Data

Note that for the scorecard, the stores become columns, and the metrics are now rows. This entire range is dynamic, meaning that the data will change based on a selection that the user makes (in this case, click forwards or backwards a page).
Notice across the top and down the left of this range are numbers. I'm using an index function that fetches data from the table based on these coordinates.



For example, the formula in cell F6 looks at the raw data ('RawData' is a named range), and returns the cell in the 2nd row (Sydney) and the 5th column (YTD Revenue).
That little cell F1 is quite important. As you can imagine, if I change that '2' to a '7', then I'll get data for the store in row 7 (Berlin). Here's the range again with the 2 changed to a 7. See that the range is now showing stores from rows 7,8,9,etc. (the cells to the right of the seven simply add 1 to their neighbour).



Getting the picture? Excellent.

The Scorecard

This is fairly simple stuff. Add a scorecard to your canvas and map it to the calculated range of cells. With a few graphical tweaks to make the scorecard look pretty, my workspace now looks like this.

The Page Buttons

Now, I can manually change cell F1 from 2 to 7 or any other number and thus change the stores that are shown in the scorecard, I just need to allow the end user to do the same. For this, I need three cells set up. One will show the current page number (defaulting to 1, naturally), one will hold the next page number (current + 1) and one will hold the previous page number (current - 1).



Now I'll add two Push Buttons to the canvas. One will copy cell B3 to B2, the other will copy B4 to B2. These will be my page forward and backward buttons. As the user clicks these, cell B2 will go either up or down by one.
Next I'll add a control so that the user can only navigate within the available number of pages.



I know that I have five stores per page, so I can get the total number of pages by counting my stores, dividing by five, and rounding up. I'll use min and max functions in B3 and B4 so that the previous page can't be less than 1, and next page can't be more than 5. Next, I'll add a 'Base Record' cell. This tells me which store is the first store to show on the page. Since the above screenshot shows me on page 2, the first store to show is number 6 (the first five stores are on page one). When I'm on page 3, this will be 11.

So, cell F1 looks at B5 to see which row of data the table should be reading from (store six is on row seven, because I have a row of headers). All other formulas are based on this.




Push buttons aren't sexy, it's true. So I'll reach out to the internet and get some nice page navigation buttons.
Keep in mind that some versions of flash don't like transparent backgrounds in png files (it will display as black) so I recommend sticking to solid colours.
These were found on www.iconarchive.com. Place the images on your canvas and put each push button in front of the appropriate arrow, then deselect its Show Button Background property. I've shown the button semi-transparent here so you can see what's happening.



That's it!

Testing and Troubleshooting

So, hopefully everything went nicely for you. But if you're having trouble, always remember that your troubleshooting friend is the spreadsheet component. Add the component to the canvas and map it to the range of cells you want to monitor. Think of it as a watch window. Then preview the dashboard and click the page buttons. Can you see what's going wrong? Which cells are changing, which cells aren't?

Lastly, and slightly off topic, I'll add some spark lines. The sparkline component is great, but I prefer tiny line charts because they give me tool-tips and point markers. So I've left one row blank in my scorecard and will place five small line charts on top, linked to the same data block as the scorecard. Pardon the messy screen, but you can see below that the line chart maps to cells F16:F20, which show revenue for the past five years. Of course these numbers will update like the others when the page is changed.




From here, it would be easy to add first-page and last-page buttons, and dynamically hide and show buttons based on the current page.