Excel PivotTables: finding origin of grouped field 1

[Chad1984]

I'm having trouble analyzing and replatforming some reports that are delivered in Excel, the data of which comes from a string of Access databases (oh, the horror!). This specific snag is about grouped fields in PivotTables. I've discovered the VBA code to find the connection string to a PivotTable, and then realized some fields were in the PivotTable that were not pulled from the database. In this specific example, a date field was grouped by month, resulting in a field called Months, and a date field was grouped by year, resulting in a field called Years. Now what I have is a PivotTable years old with a Months grouped field and a Years grouped field, and no idea which of the numerous date fields they came from. I can find nothing in the Excel interface to see where it originated from. Ungrouping the fields doesn't help me at all. I even read through the Excel VBA reference on

http://www.msdn.com

and have found nothing stating this.

The only thing I can think of is to ungroup the field and see what data in other fields exist in the same row, then compare that to the data cache that appears when I double-click a data are column... but even that is inconclusive, as some of the date fields hold identical values! Any ideas?

 

[SkipVought]

Hi Chad (fellow Texan),

Paste this little function into a MODULE.

Use as you would any spreadsheet function, on the sheet containing the PivotTable...

Function PivotFieldSouceName(PvtFieldName As String, Optional PvtTableNbr As Integer = 1)
'SkipVought 2008/11/12    
    PivotFieldSouceName = ActiveSheet.PivotTables(PvtTableNbr).PivotFields(PvtFieldName).SourceName
End Function

It returns the SourceName for the given field name. If there are multipls PTs on the sheet, use that number as the optional second argument.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

Here's an improvement...

Function PivotFieldSouceName(PvtFieldName As Range)
'SkipVought 2008/11/12
    Dim pvt As PivotTable, PvtTableNbr As Integer
'first find the PivotTable
    PvtTableNbr = 1
    For Each pvt In ActiveSheet.PivotTables
        If Not Intersect(pvt.TableRange1, PvtFieldName) Is Nothing Then Exit For
        PvtTableNbr = PvtTableNbr + 1
    Next
'return the value
    PivotFieldSouceName = ActiveSheet.PivotTables(PvtTableNbr).PivotFields(PvtFieldName.Value).SourceName
End Function

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[xlbo]

Skip - all kindsa awesome - that's one for the archive!

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Chad1984]

Wow, Skip, thanks for taking the time to write that super-fancy code! That seems to almost do the trick!

I say "almost" because, oddly enough, it worked for one field and not the other. I have two fields, "Months" and "Years," which may or may not be based on the same data field. The macro tells me which database field "Months" comes from, which is terrific! However, it just returns "Years" for the "Years" field, instead of a database field name.

But it doesn't end there. There's even more bizarre behavior (these are both in the same PivotTable, I should note).
If I ungroup Years, the Years field disappears
If I ungroup Months, the Years field disappears
Does this mean that I can be certain the Years field was grouped from Months, or how can I find out what database field Years was grouped from?

 

[SkipVought]

I have two fields, "Months" and "Years," which may or may not be based on the same data field. "

That's because the Group Feature in the PivotTable, can produce MULTIPLE headings, like Year & Month. You COULD just group by MONTHS, but what good does that do unless you want to see EVERYTHING for each month for ALL YEARS, aggregated to that level. But typically, you group a date field by Month & Year. Only ONE of those is linked to the source, it seems.

If you ungroup Year, you get aggregations to the MONTH and vis versa.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

BTW, I've posted this function and another PivotTable-related procedure called CleanMyPivots in this forum's FAQ, under PivotTable Tips. I invite other members to post related FAQ's there as well.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Chad1984]

The code works great for the months field... the years field displays some odd behavior, but ah well--I'll just assume that they are based on the same database field. Thanks again, Skip, you're a lifesaver!