Hi,
I am using PivotTable in Excel to display the data in the cube. My
following dimension in the PivotTable are Year(Page Filter), Office(Page
Filter), Customer(Row), and Country(column), and my measure is Commission Amt.

I have other measures like US BROKER FEE AMT, US CLEARING FEE AMT, LOCAL
BROKER FEE AMT, LOCAL CLEARING FEE AMT, TAX AMT, and NET COMMISSION AMT in
this cube.

What I have done is create another spreadsheet with only these measure with
the same dimension and copied it over to the Excel spreadsheet below the
PivotTable; however, I would like to create the necessary code to retrieve
this data and display it under the PivotTable as a footheader.

I am not sure how to do this. I would appreciate any assistant or guidance
in achieving this result.

Regards,
Fernando Sanchez

I'm not sure if I completely understand what you are trying to do, so
let me know if I am heading down the wrong track.

If you want to see your extra measures just as totals (ie. not broken
down by customer) then have you considered creating a second pivot table
below the first? You can use the hide rows feature in excel to hide the
second copy of the page filters. I am pretty sure it is also possible to
write vba code to keep the page filters in synch.

--
Regards
Darren Gosbell [MCSD]
Blog: http://www.geekswithblogs.net/darrengosbell

In article <27908409-471E-46AF-ABAA-377349C9801B (AT) microsoft (DOT) com>,
fsanchez (AT) discussions (DOT) microsoft.com says...

Hi Darren,
First of all, I want to thank you for responding to a post/question in
October of this year regarding YTD. I was struggling, for some time, with a
calculate measure that retrieved the year-to-date for the corresponding
period for the previous year and your suggestion worked.

Prior YTD Commission Amt =
(ParallelPeriod([YEAR],1,[PERIOD].CurrentMember),Measures.[Ytd Commission
Amt])

Regarding this question, you are correct. I have created a second
pivotTable in another Excel spreadsheet and format it with the same dimension
except with Customer and displayed all the aggregated measure. The issue I
have is that the user might alter the selection and not modify the PivotTable
with aggregated measure. I want to be able to provide, perhaps, menu botton
in which the VBA code reference the dimension selection from the first
PivotTable and display the aggregated measures with the same dimension
selection and members below the first PivotTable. My problem is that I do
not know how to properly code the VBA to reference the OLAP cube and display
data.

Would you have a sample VBA code that I can reference as guidance?

Regards,
Fernando Sanchez

"Darren Gosbell" wrote:

This sample code will keep the page fields for multiple pivottables on
the one sheet synchronised. It hooks into the PivotTableUpdate event of
the worksheet object. You could extend it to look across all the sheets
in the current workbook. Or alternatively you can setup your own
dropdown controls and push values into the pagefields manually using
similar code.

'\\ ================================================== ===========
'\\ Author : Darren Gosbell
'\\ Date : 12 Dec 2005
'\\ Description: Keeps the page fields of multiple PivotTables
'\\ on the same sheet synchronised.
'\\ ================================================== ===========

'\\ This global flag stops event recursion
Dim bUpdating As Boolean

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
Dim pt As PivotTable
Dim pf As PivotField

If Not bUpdating Then
bUpdating = True
Me.Application.ScreenUpdating = False

'\\ For each pivot table on the current sheet
'\\ find any pagefields with the same name as those
'\\ in the sheet that was just changed and synchronise
'\\ them.
For Each pt In Me.PivotTables
If pt.Name <> Target.Name Then
For Each pf In Target.PageFields
If Not pt.PageFields(pf.Name) Is Nothing Then
pt.PageFields(pf.Name).CurrentPageName = _
pf.CurrentPageName
End If
Next pf

End If
Next pt

Columns("B:B").EntireColumn.AutoFit
Me.Application.ScreenUpdating = True

bUpdating = False
End If
End Sub

'\\ ================================================== ===========

--
Regards
Darren Gosbell [MCSD]
Blog: http://www.geekswithblogs.net/darrengosbell

In article <F42F447F-2003-4723-89DD-C98BF1348F5A (AT) microsoft (DOT) com>,
fsanchez (AT) discussions (DOT) microsoft.com says...

Hi Darren,
I really appreciate the time you took to respond to my post/question. I
will take a look at your suggestion. Once again, thanks a lot for your help
and guidance.

Regards,
Fernando Sanchez

"Darren Gosbell" wrote:

Hi Darren,

Thank you very much for the script. It works great !

I have one question.


If I have on 1 sheet 3 pivot tables and customer in all 3 page fields
than it is working ok. If one of the pivottable does have another
slicer as page filed there is a vb error.

Any idea how and what to change to have this also working.

Thank you

Regards, Marco




fsanchez schreef:

Hi Darren,

Thank you very much for the script. It works great !

I have one question.


If I have on 1 sheet 3 pivot tables and customer in all 3 page fields
than it is working ok. If one of the pivottable does have another
slicer as page filed there is a vb error.

Any idea how and what to change to have this also working.

Thank you

Regards, Marco




fsanchez schreef:

Hmm.. The for each loop seems to get really confused when there are a
different number of page fields. I have swapped to referencing by index
and altered the error trapping. The script below should be more robust.

Watch out for word wrapping some of the lines are longer that the 80
character limit for newsgroup postings.



'\\ This global flag stops event recursion
Dim bUpdating As Boolean

'\\ ================================================== ===========
'\\ Author : Darren Gosbell
'\\ Date : 12 Dec 2005
'\\ Description: Keeps the page fields of multiple PivotTables
'\\ on the same sheet synchronised.
'\\ ================================================== ===========
Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
Dim pt As PivotTable
Dim pf As PivotField
Dim I As Integer

If Not bUpdating Then
bUpdating = True
Me.Application.ScreenUpdating = False

'\\ For each pivot table on the current sheet
'\\ find any pagefields with the same name as those
'\\ in the sheet that was just changed and synchronise
'\\ them.
On Error Resume Next

For Each pt In Me.PivotTables
If pt.Name <> Target.Name Then
For I = 1 To Target.PageFields.Count
Set pf = Nothing

Set pf = pt.PageFields(Target.PageFields(I).Name)
If Not pf Is Nothing Then
pt.PageFields(Target.PageFields
(I).Name).CurrentPageName = Target.PageFields(I).CurrentPageName
End If
Next I

End If
Next pt

Columns("B:B").EntireColumn.AutoFit
Me.Application.ScreenUpdating = True

bUpdating = False
End If
End Sub



--
Regards
Darren Gosbell [MCSD]
Blog: http://www.geekswithblogs.net/darrengosbell

In article <1134471620.113008.306600 (AT) g44g2000cwa (DOT) googlegroups.com>,
olap (AT) gmsbv (DOT) nl says...

Good morning Darren,
If I understand the sample code, there is no need for me to create dropdown
control to refresh the PivotTables in the worksheet.

I am not sure if I did this correctly because I ran into an error. I
selected the menu option Visual Basic Editor under the Menu Tool for Macro.
I copied and pasted the code under the worksheet. Did I do this correctly?

Regards,
Fernando Sanchez

"Darren Gosbell" wrote:

I think I may have posted the wrong code sample previously. I have been
helping someone else that just wanted to keep 2 pivot tables on the one
sheet in synch and I think I got mixed up.

I have created a workbook that will update all pivot tables on any sheet
in the workbook, you can find a link to download it on my blog at:

http://geekswithblogs.net/darrengosb.../14/63085.aspx

--
Regards
Darren Gosbell [MCSD]
Blog: http://www.geekswithblogs.net/darrengosbell

In article <C3624E62-D022-43AD-92CC-58CE187EC88E (AT) microsoft (DOT) com>,
fsanchez (AT) discussions (DOT) microsoft.com says...