• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

getpivotdata of multiple items from pivot table report filter

M

matt-gilbert

Member
I have a pivot table which shows the weather statistics for the past 50 years (rainfall & temperature). My pivot table has a Report Filter based on Year.

I start with a Worksheet called Weather Data where all the raw data is input.

From this I have created a Pivot Table and placed this on a new Worksheet called Weather Pivot. From this I have created a Chart and placed this on a new Worksheet called Weather Chart. I would like the Chart Title to reflect the information that it is being presented ie;

Paraburdoo, WA – Weather Observations for Year/s

I have entered the following formula into the Chart Title.

=CONCATENATE(“Paraburdoo, WA - Weather Observations for ",IF('Weather Pivot'!$B$1="(All)",CONCATENATE((MIN('Weather Data'!E:E)),"-",(MAX('Weather Data'!E:E))),'Weather Pivot'!$B$1))

Where:

'Weather Pivot'!$B$1 = Pivot Table Report Filter field (ie Year) and;

'Weather Data'!E:E = Source data for Year/s

When 2011 is selected from the Report Filter, the result is;

Paraburdoo, WA – Weather Observations for 2011

When (All) years are selected from the Report Filter, the result is;

Paraburdoo, WA – Weather Observations for 1962-2012

When only some years are selected eg 2007, 2008, 2009, 2010 and 2011, the result is;

Paraburdoo, WA – Weather Observations for (Mulitple Items)

How can I return the years as they have been selected in the Pivot Table Report Filter ie;

Paraburdoo, WA – Weather Observations for 2007-2011

Or, if these years are non-contiguous;

Paraburdoo, WA – Weather Observations for 2007, 2008, 2009, 2010, 2011

I have searched everywhere for an answer to no avail. Would be most appreciative for some help.

Thanks in advance

Matt
 
Hi Matt,


I understand what you are asking for, some how we have to list the selections of report filter.


If you are using 2003 version try this link:

http://www.excelbanter.com/showthread.php?t=217255

I could not check as I have 2010 version.


If above 2007 version then it may need some vba stuff:

http://www.mediafire.com/file/dndt1kqjyl4/01_21_09.xlsm

Again, i am not able to check that as well.


Do let us know if it works.


Regards,

Prasad DN
 
Hi guys,


Sorry about the delay - I thought I might receive an email notifying me of your responses.


Faseeh - I have uploaded sample file as per below


http://dl.dropbox.com/u/38326386/getpivotdata%20of%20multiple%20items%20from%20pivot%20table%20report%20filter.xlsx


prasaddn - I will review your link (I am using 2007 btw)


Thanks.

Matt
 
Hi Matt,


got the opportunity to check the file in the link :

http://www.mediafire.com/file/dndt1kqjyl4/01_21_09.xlsm


The chartP shows chart title as per what is selected under PivotTableP


Exactly what you were looking for, but it requires VBA function and not based on formula.


VBA Code:

Code: 
With Sheets("PivotTableP").PivotTables(1).PivotFields("DDate")

n = .PivotItems.Count

For i = 1 To n

If .PivotItems(i).Visible Then

s = s & " " & .PivotItems(i).Name

End If

Next i

End With

With Sheets("ChartP")

.SetElement (msoElementChartTitleAboveChart)

.ChartTitle.Text = s

End With


<code/>

This code needs to be called either manually or on worksheet change event.


Regards,

Prasad DN.

PS: Will still be looking for chances of getting this thru formulas..
 
Hi Parsaddn,


By far i am still struggling. The idea i was following was to create a pivot table that results in a chart thus controlling chart, then getting years out of it using getpivotdata and then joining them using formula. Where i am stuck are actually two points:


1. How should i connect a cell to the selection made for filtering table?

2. How to mark the start and end of a group if i some how gets years from the pivot table?

3. Some problems with form controls.


...while i keep trying, i think matt should go with your vba based solution that is accurate and smart.Thumbs up for you!!


Yours truly,

Faseeh
 
Hi Faseeh,


All credit should go to person who put up the code and file in the link.


You are right, workingout as formula is something I tried and gave up (particularly)after I fould solution in the links provided.


Regards,

Prasad DN
 
Prasaddn/Faseeh,


Thanks for your help. I too have given up on trying to workout using a formula. The sample that I posted is sufficient for my purposes (at this stage). Unfortunately I do not have the luxury of time to work this out!


Thanks again.


Matt
 
Hi,


I wrote the function version of the macro above

The argument of the function must be the range (cell) containing the Report Filter field..

Provided as is, not extensively tested... Enjoy!

[pre]
Code: 
<br />
Function ShowReportFilterItems(rng As Range)<br />
Application.Volatile<br />
myfield = rng.Value<br />
a = rng.PivotTable.Name<br />
With rng.PivotTable.PivotFields(myfield)<br />
n = .PivotItems.Count<br />
For i = 1 To n<br />
If .PivotItems(i).Visible Then<br />
s = s & " " & .PivotItems(i).Name<br />
End If<br />
Next i<br />
End With<br />
ShowReportFilterItems = s<br />
End Function</p>
<p>
[/pre]
 
You must log in or register to reply here.
Back
Top