Create a scrollable chart linked to Power Pivot data
Creating a Chart with many point can sometimes give some pretty irrelevant charts.
So I tried to see if it was possible to create a chart where I could scroll through the products based on their, and by using the old Forms Scroll bar control (you need to show the developer tab in the ribbon) it was quite easy.
I can now scroll through the records and as Excel 2013 has nice animations it looks fantastic.
So – How to
I created a connection to the sample tabular Adventure Works model on my local machine and named the connection AdventureWorks.
And then I created the SET of Products that I wanted to display.
=CUBESET(Connection_name;”FILTER([Product].[Product Name].Children, [Measures].[Total Sales])”;”Products”;2;”[Measures].[Total Sales]”)
The Filter is used to filter out products with no Sales and the last two arguments specifies that I want the set sorted after the Total Sales in Descending order.
Then I created the data that I wanted to plot in chart.
The first rank cell is linked to the scrollbar control (will show you later how that is done) and product and Sales Value is calculated via
The other rank values is calculated by adding 1 to the previous cell.
Now you can create the chart on the area
And then to make it scrollable you add a Scollbar from the Form Contols.
Right click the scrollbar and choose format control – and set the cell link to the first Rank in the table and specify max value and minimum value to 0
The max is set to the number of items in the set minus 10 – this could be automated with VBA.
You can download the example from here – (Will only work if you modify the connection to your own AdventureWorks model)