CUBESET(Connection, Set Expression,[Caption],[Sort Order], [Sort By]) - Slicer Info as Chart Title

[M1donne]

Hi all,

I have several pivot tables and pivot charts that use slicers to dissect the data, I would like to have the chart title reflect the selections made via the slicers and read an interesting article that made use of the following formulae

I'm not sure if I can show the site below but apologies if this breaks any rules...

http://everydayexcel123.blogspot.co.uk/2014/01/slicer-selection-in-chart-title-without.html


CUBESET(Connection, Set Expression,[Caption],[Sort Order], [Sort By])
CUBERANKEDMEMBER(connection, set_expression, rank, [caption])
IFERROR(Value, Value if Error)

Anyway I have tried to follow the instructions but I'm confused whether this will work without having Powerpivot installed - I'm on Excel 2013 Pro if that helps, my understanding was all the functionality of Power Pivot was then deployed into the latest versions of Excel (2013 onwards) - although I could be wrong.

Does anyone have experience of this that would be willing to share the solution - I'm failing when I paste the CUBESET formula into Cell A1.

As always any help is massively appreciated.

 

[Jerry Sullivan]

Hi M1donne,

What are your pivottables using as a datasource? Are they using a range, or a network connection?

You won't be able to use CUBESET and CUBERANKEDMEMBER unless the data source is an OLAP type source.

If you don't have an OLAP source, you could use VBA to make the list of PivotItems selected by the slicer, then display that in your chart.

 

[M1donne]

Hi M1donne,

What are your pivottables using as a datasource? Are they using a range, or a network connection?

You won't be able to use CUBESET and CUBERANKEDMEMBER unless the data source is an OLAP type source.

If you don't have an OLAP source, you could use VBA to make the list of PivotItems selected by the slicer, then display that in your chart.

Thanks Jerry,

At the moment I'm simply using a table of data within the excel worksheet, is there a way to convert this to be an OLAP data source? Would that be an external data source?

regards

 

[Jerry Sullivan]

You can use PowerPivot to add an Excel Table to the Data Model, then use that Data Model as the data source for your PivotTables.

If you don't have a Power Pivot tab displayed on your ribbon, you'll need to install the Power Pivot Add-In.

 

[M1donne]

You can use PowerPivot to add an Excel Table to the Data Model, then use that Data Model as the data source for your PivotTables.

If you don't have a Power Pivot tab displayed on your ribbon, you'll need to install the Power Pivot Add-In.

Thanks Jerry, do you have a link to 2013 Power Pivot - I can't seem to find one?

Thanks again for your help.

 

[Jerry Sullivan]

First, check to see if you already have it...

File > Options > Add-Ins

At the bottom of the Add-Ins window, in the dropdown next to Manage: select COM Add-Ins.

In the COM Add-Ins dialog, check the box next to "Microsoft Office PowerPivot for Excel 2013" (if that is listed).

 

[M1donne]

First, check to see if you already have it...

File > Options > Add-Ins

At the bottom of the Add-Ins window, in the dropdown next to Manage: select COM Add-Ins.

In the COM Add-Ins dialog, check the box next to "Microsoft Office PowerPivot for Excel 2013" (if that is listed).

Thanks Jerry,

I forgot its not a exe anymore.

Much appreciated

Md