Posts by unwonted

    Re: OLAP Cube Pivot Table - Get Current Page Items Selected

    Thanks for all the help : D , I got it working eventually. I used the "Worksheet_SelectionChange" function to detect when the pivottable is selected to run the update. rather than the "Worksheet_PivotTableUpdate" function because when it runs it causes the code to loop.
    It may not be the best but it's working, and if you have any suggestions for improvement I'd be more than happy to use them .

    Here's the code in the worksheet:

    And here's the code I modified from your suggestion:

    Re: OLAP Cube Pivot Table - Get Current Page Items Selected

    Thanks for the code, I'm rewriting it now, for the resons below.
    I didn't get a chance to clear things up last week, this should make things easier.
    Firstly I have two pivot tables that I am comparing, "PivotTable1" & "PivotTable2".

    In "PivotTable1" I have the following Page Items:
    [Customer], [Skillset Name] and [Call Date]

    In "PivotTable2" I have the following Page Items:
    [Employee Work Group Originator] and [Logged Date]

    The only items/fields that have the same structure are [Call Date] and [Logged Date], and these are the only ones I'm concerned with.
    Actually [Call Date] has the following structure:
    [Call Date].[All Call Date].[Year].[Month].[Day] and
    [Logged Date] has the following structure
    [Logged Date].[All Logged Date].[Year].[Month].[Day]
    so I'll have to edit the string a bit to work

    I only want to automatically match the selections made in either [Call Date] or [Logged Date] when they are changed and no other pivot fields.

    Re: OLAP Cube Pivot Table - Get Current Page Items Selected

    I will definitely try your suggestion when I get a chance on monday. This can wait until then.

    I have two PT's from two different cubes. When somebody selects a specific date/period from one of the PT's then I want to update the other PT to show the same date/period. The specific code for changing the selection I know how to do, but getting the currently selected items I don't.

    Two things:
    1. The page item [Call Date] does contain a hierarchy, i.e.

    All Call Date
    - Year
    - Month
    - Date
    also see attachment

    2. I don't know what way the cube was created. It was created by somebody offsite and I don't know the contact, is it possible to tell somehow?

    Re: Pivot Table - Get Current Page Item Selected

    I have been away for a week and haven't had access to test your suggestion until today:

    I tested it as you suggested and taking out the pf.CurrentPageName gave me the following 4 results:
    [Skillset Name]
    [Year Week]
    [Call Date]

    I am using "mulitple items" so i tested the original code you gave me and unticked the "multiple items" option and ran the code and it works properly and gave me the pagenames, but i need multiple items enabled.
    Any ideas on how to return the array of values selected?

    Sorry, I know very little, if anything about the cube.

    Re: Pivot Table - Get Current Page Item Selected

    I'm using an OLAP Cube.

    I have 4 page items:
    Skillset Name
    Year Week
    Call Date

    when I run the code it's giving me an immediate error:
    Run-time error '1004'
    Application-defined or object-defined error

    and when I debug it
    the line:

    MsgBox ("Field name = " & & " Page = " & pf.CurrentPageName)

    shows as "[Customer]" but pf.CurrentPageName as 'Application-defined or object-defined error'

    Re: Pivot Table - Get Current Page Item Selected

    I tried using the CurrentPage function before and I get the following error:

    Run-time error '1004':
    Unable to get the PivotFields property of the PivotTable class

    this is the code I used this time:

    Dim CPage As String
    CPage = Worksheets("CitA TSA Data").PivotTables("PivotTable1").PivotFields("Call Date").CurrentPage

    still the same

    Maybe I should mention that this is connected to a Cube

    Re: Listing Pivottable Field List Fields

    I made a few changes to extract the list of fields to a new sheet:

    Re: Listing Pivottable Field List Fields

    I think this code displays the fields that are "selected" or being used by the pivot table.

    Is there a way to print/list all the fields that can potentially be added to the pivottable, i.e. all the entries from the "PivotTable Field List"?

    I need to create a pivot table with a custom calculated field from an external data source.
    I cannot alter the data from the external data source.

    My pivotTable is called "pivotTable2"

    I have three fields for example, field a, b and c. I want to include a field which equals a custom calculation such as (a*b/c)*6

    Is this possible and if so how do i do it?

    Re: PivotTable Total cell Field reference

    It's alright, I eventually found it in Microsoft Help


    ActiveCell.PivotTable.GetPivotData([DataField], [Field1], [Item1])

    the code I needed to use was

    ActiveCell.PivotTable.GetPivotData("Calls Offered")

    where "Calls Offered" is the Title of the data Field I needed the total of that column

    Thanks anyway