Skip to content
March 20, 2013 / Erik Svensen

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

=CUBERANKEDMEMBER(Connection_name;$I$3;G7)

And

=CUBEVALUE(Connection_name;H7;”[Measures].[Total Sales]”)

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)

 

 

 

 

 

 

 

 

 

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: