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:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim PT As PivotTable
    
    cell_column = ActiveCell.Column
    cell_row = ActiveCell.Row
    cell_id = ColumnLetter(cell_column) & cell_row
On Error GoTo ErrorHndl
    Set PT = Range(cell_id).PivotTable
    If PT <> "" Then
        Call SyncOLAPBasedPivotTablesWithMultiDimsAndMultiSelect(PT)
    End If
ErrorHndl:
End Sub


Function ColumnLetter(ByVal colNum As Long) As String
    Dim s As Long, x As Long
    For s = Int(Log(CDbl(25 * (CDbl(colNum) + 1))) / Log(26)) - 1 To 0 Step -1
        x = (26 ^ (s + 1) - 1) / 25 - 1
        If colNum > x Then
            ColumnLetter = ColumnLetter & Chr(((colNum - x - 1) \ 26 ^ s) Mod 26 + 65)
        End If
    Next s
End Function

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

Function SyncOLAPBasedPivotTablesWithMultiDimsAndMultiSelect(active_pivottable)
    Dim PT As PivotTable
    Dim PT2 As PivotTable
    Dim PF As PivotField
    Dim PF2 As PivotField
    Dim CPICounter As Integer
    Dim PF_Check As PivotField


    Set PT = ActiveSheet.PivotTables("PivotTable1")
    Set PT2 = ActiveSheet.PivotTables("PivotTable2")
    Set PF = PT.PivotFields("[Call Date]")
    Set PF2 = PT2.PivotFields("[Logged Date]")
    
    PF.CubeField.EnableMultiplePageItems = True
    PF2.CubeField.EnableMultiplePageItems = True
    
    If active_pivottable = "PivotTable1" Then
        Set PF_Check = ActiveSheet.PivotTables("PivotTable1").PivotFields("[Call Date]")
        For CPICounter = 1 To PageCount(PF_Check)
            If CPICounter = 1 Then
                PF2.AddPageItem "[Logged Date].[All Logged Date]" & Mid(PF.CurrentPageList(CPICounter), 28), True
            Else
                PF2.AddPageItem "[Logged Date].[All Logged Date]" & Mid(PF.CurrentPageList(CPICounter), 28), False
            End If
        Next CPICounter
    ElseIf active_pivottable = "PivotTable2" Then
        Set PF_Check = ActiveSheet.PivotTables("PivotTable2").PivotFields("[Logged Date]")
        For CPICounter = 1 To PageCount(PF_Check)
            If CPICounter = 1 Then
                PF.AddPageItem "[Call Date].[All Call Date]" & Mid(PF2.CurrentPageList(CPICounter), 32), True
            Else
                PF.AddPageItem "[Call Date].[All Call Date]" & Mid(PF2.CurrentPageList(CPICounter), 32), False
            End If
        Next CPICounter
    End If
End Sub
 
Function PageCount(PF_Check As PivotField) As Integer
On Error GoTo ErrOut
    For PageCount = 1 To 200
        Debug.Print PF_Check.CurrentPageList(PageCount)
    Next PageCount
ErrOut:
    PageCount = PageCount - 1
End Function

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]
[Customer]
[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
Customer
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 = " & pf.name & " Page = " & pf.CurrentPageName)

shows pf.name 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

Is it possible to get the current Page Item Selected for a pivot table

I've tried the following functions in VBA, but have had no success
CurrentPageName
CurrentPage
CurrentPageItem
ParentItems

Re: Listing Pivottable Field List Fields

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

Sub list_cube_fields()


    Set objNewSheet = Worksheets.Add
    objNewSheet.Activate
    intRow = 1
    For Each objCubeFld In Worksheets("PivotTable Data").PivotTables(1).CubeFields
            objNewSheet.Cells(intRow, 1).Value = objCubeFld.Name
            intRow = intRow + 1
    Next objCubeFld
    
End Sub

Re: Listing Pivottable Field List Fields

That's it, it's working fine. Perfect.

Thanks!

Re: Listing Pivottable Field List Fields

You're right. I ran it with data from within a spreadsheet and it works but like you say it's different and doesn't work when I use a cube.

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"?

Is there any code or way to generate the compete list of fields that are selectable from the "PivotTable Field list"?

I have various cubes I need to check and compile the list of fields for each.

Re: Add Horizontal Line to Chart Without Creating New Series

Ok it's working now.

Thanks, Much appreciated.

Re: Add Horizontal Line to Chart Without Creating New Series

Sorry, I've had problems with my home network.

I have the secondary xaxis visible but I don't understand how you inserted this line to be part of the chart.

Re: Adding Horizontal Line to chart without creating new Series

That's what I'm looking for
Did you just draw a line on the plot area?
I can't believe I didn't think to do that already. Thanks Andy Pope

Re: Adding Horizontal Line to chart without creating new Series

It's not going to be that static, I shouldn't have used the word static.
The chart will be subject to different data sets, resizing moving etc.

I have percentages on the secondary axis and want to draw a horizontal line at 80%.

I have a line Chart and want to create a static horizontal/indicator line.

Any suggestions?

Note: I cannot create a new series to plot this horizontal line as I'm using an external data source (OLAP cube) as source data for the chart.

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 is the GETPIVOTDATA formula, but you can't record it in a macro and use it the same way, so no.
The formatting is quite different.
You got it half right, thanks all the same

Re: PivotTable Total cell Field reference

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

FYI

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