Expert Needed!!!!!!!!!! Pivot problem

[Guest]

hi im trying to automate a selection for date on all my pvot tables. i have
these pivots that are joined by an olap cube to my workbook. what i have done
is create diffrent tables for diffrent measures but i have one constant and
this is Date. im trying to create a user form which selects from a list the
months required for viewing.
eg March april May, 2006

these selections are fed into arrays as strings like
[Time].[Months].[All Time].[2006].[Quarter 3 2006].[July 2006]
i understand that with the Date field it requires a hide method for the
months not selected.

this is my code so far


ActiveSheet.PivotTables("PivotTable1").CubeFields(44).TreeviewControl.Drilled
_
= Array(Array(""), array(showyear)), showquart)

ActiveSheet.PivotTables("PivotTable1").PivotFields("[Time].[Months].[Year]").
_
HiddenItemsList = Array(notyear)

ActiveSheet.PivotTables("PivotTable1").PivotFields("[Time].[Months].[Quarter]") _
.HiddenItemsList = notquart()

ActiveSheet.PivotTables("PivotTable1").PivotFields("[Time].[Months].[Month]"). _
HiddenItemsList = array(notmonth)

all the Arrays are previously set into strings on the workbook but the pivot
will not read my arrays as normal it comes up with errors and will not change
anything.

hope this reads alright

if anyones got a sample code that would be excellant!!!!!
i just want to be able to auto change 30 pivot tables to the date i chose in
a user format the start! sounds simple but as i found out, not so!

please help

 

[Debra Dalgleish]

You could create an array from the list, instead of a string. Then, make
the HiddenItemsList equal to the array.

hi im trying to automate a selection for date on all my pvot tables. i have
these pivots that are joined by an olap cube to my workbook. what i have done
is create diffrent tables for diffrent measures but i have one constant and
this is Date. im trying to create a user form which selects from a list the
months required for viewing.
eg March april May, 2006

these selections are fed into arrays as strings like
[Time].[Months].[All Time].[2006].[Quarter 3 2006].[July 2006]
i understand that with the Date field it requires a hide method for the
months not selected.

this is my code so far


ActiveSheet.PivotTables("PivotTable1").CubeFields(44).TreeviewControl.Drilled
_
= Array(Array(""), array(showyear)), showquart)

ActiveSheet.PivotTables("PivotTable1").PivotFields("[Time].[Months].[Year]").
_
HiddenItemsList = Array(notyear)

ActiveSheet.PivotTables("PivotTable1").PivotFields("[Time].[Months].[Quarter]") _
.HiddenItemsList = notquart()

ActiveSheet.PivotTables("PivotTable1").PivotFields("[Time].[Months].[Month]"). _
HiddenItemsList = array(notmonth)

all the Arrays are previously set into strings on the workbook but the pivot
will not read my arrays as normal it comes up with errors and will not change
anything.

hope this reads alright

if anyones got a sample code that would be excellant!!!!!
i just want to be able to auto change 30 pivot tables to the date i chose in
a user format the start! sounds simple but as i found out, not so!

please help

 

[Guest]

hi debra
I had already tried hiddenitemlist = showquart()
but this caused an error saying type miss match
and then tried
hiddenitemlist = Array(showquart)
which then gave a ore encouraging error
items were not found on OLAP cube


any idea?

You could create an array from the list, instead of a string. Then, make
the HiddenItemsList equal to the array.

hi im trying to automate a selection for date on all my pvot tables. i have
these pivots that are joined by an olap cube to my workbook. what i have done
is create diffrent tables for diffrent measures but i have one constant and
this is Date. im trying to create a user form which selects from a list the
months required for viewing.
eg March april May, 2006

these selections are fed into arrays as strings like
[Time].[Months].[All Time].[2006].[Quarter 3 2006].[July 2006]
i understand that with the Date field it requires a hide method for the
months not selected.

this is my code so far


ActiveSheet.PivotTables("PivotTable1").CubeFields(44).TreeviewControl.Drilled
_
= Array(Array(""), array(showyear)), showquart)

ActiveSheet.PivotTables("PivotTable1").PivotFields("[Time].[Months].[Year]").
_
HiddenItemsList = Array(notyear)

ActiveSheet.PivotTables("PivotTable1").PivotFields("[Time].[Months].[Quarter]") _
.HiddenItemsList = notquart()

ActiveSheet.PivotTables("PivotTable1").PivotFields("[Time].[Months].[Month]"). _
HiddenItemsList = array(notmonth)

all the Arrays are previously set into strings on the workbook but the pivot
will not read my arrays as normal it comes up with errors and will not change
anything.

hope this reads alright

if anyones got a sample code that would be excellant!!!!!
i just want to be able to auto change 30 pivot tables to the date i chose in
a user format the start! sounds simple but as i found out, not so!

please help

 

[Debra Dalgleish]

You said that "these selections are fed into arrays as strings". I
suggested creating an array from the list on the user form, instead of a
string. Did you try that?

Then, .hiddenitemlist=showquart should work.

hi debra
I had already tried hiddenitemlist = showquart()
but this caused an error saying type miss match
and then tried
hiddenitemlist = Array(showquart)
which then gave a ore encouraging error
items were not found on OLAP cube


any idea?

:

You could create an array from the list, instead of a string. Then, make
the HiddenItemsList equal to the array.

hi im trying to automate a selection for date on all my pvot tables. i have
these pivots that are joined by an olap cube to my workbook. what i have done
is create diffrent tables for diffrent measures but i have one constant and
this is Date. im trying to create a user form which selects from a list the
months required for viewing.
eg March april May, 2006

these selections are fed into arrays as strings like
[Time].[Months].[All Time].[2006].[Quarter 3 2006].[July 2006]
i understand that with the Date field it requires a hide method for the
months not selected.

this is my code so far


ActiveSheet.PivotTables("PivotTable1").CubeFields(44).TreeviewControl.Drilled
_
= Array(Array(""), array(showyear)), showquart)

ActiveSheet.PivotTables("PivotTable1").PivotFields("[Time].[Months].[Year]").
_
HiddenItemsList = Array(notyear)

ActiveSheet.PivotTables("PivotTable1").PivotFields("[Time].[Months].[Quarter]") _
.HiddenItemsList = notquart()

ActiveSheet.PivotTables("PivotTable1").PivotFields("[Time].[Months].[Month]"). _
HiddenItemsList = array(notmonth)

all the Arrays are previously set into strings on the workbook but the pivot
will not read my arrays as normal it comes up with errors and will not change
anything.

hope this reads alright

if anyones got a sample code that would be excellant!!!!!
i just want to be able to auto change 30 pivot tables to the date i chose in
a user format the start! sounds simple but as i found out, not so!

please help

 

[Guest]

i have a data source which links to my sheet via a pivot table. on this pivot
table is the Date range the date starts at january 2005-present what i
require if its possible to minipulate the pivot tables to automatically
select may 2006 -July 2006 from list box.

can you send me some sample code if its posssible please because im not an
expert in pivot tables and dont no really how to start.

(my previouse code was created from a macro but the string of selections was
changed to strings gained from arrays (variants) which were linked to a
worksheet that worked them out from the selection made in the list)

You said that "these selections are fed into arrays as strings". I
suggested creating an array from the list on the user form, instead of a
string. Did you try that?

Then, .hiddenitemlist=showquart should work.

hi debra
I had already tried hiddenitemlist = showquart()
but this caused an error saying type miss match
and then tried
hiddenitemlist = Array(showquart)
which then gave a ore encouraging error
items were not found on OLAP cube


any idea?

:

You could create an array from the list, instead of a string. Then, make
the HiddenItemsList equal to the array.

rivers wrote:

hi im trying to automate a selection for date on all my pvot tables. i have
these pivots that are joined by an olap cube to my workbook. what i have done
is create diffrent tables for diffrent measures but i have one constant and
this is Date. im trying to create a user form which selects from a list the
months required for viewing.
eg March april May, 2006

these selections are fed into arrays as strings like
[Time].[Months].[All Time].[2006].[Quarter 3 2006].[July 2006]
i understand that with the Date field it requires a hide method for the
months not selected.

this is my code so far


ActiveSheet.PivotTables("PivotTable1").CubeFields(44).TreeviewControl.Drilled
_
= Array(Array(""), array(showyear)), showquart)

ActiveSheet.PivotTables("PivotTable1").PivotFields("[Time].[Months].[Year]").
_
HiddenItemsList = Array(notyear)

ActiveSheet.PivotTables("PivotTable1").PivotFields("[Time].[Months].[Quarter]") _
.HiddenItemsList = notquart()

ActiveSheet.PivotTables("PivotTable1").PivotFields("[Time].[Months].[Month]"). _
HiddenItemsList = array(notmonth)

all the Arrays are previously set into strings on the workbook but the pivot
will not read my arrays as normal it comes up with errors and will not change
anything.

hope this reads alright

if anyones got a sample code that would be excellant!!!!!
i just want to be able to auto change 30 pivot tables to the date i chose in
a user format the start! sounds simple but as i found out, not so!

please help

 

[Debra Dalgleish]

You'd have to adapt this to your data and userform, but with a list box
(named lstQtr) that shows quarters, e.g.:

[2003].[1]
[2003].[2]
[2003].[3]

The following code would hide the unselected items in each pivot table
in the workbook:

'========================
Private Sub cmdOK_Click()
Dim lQtr As Long
Dim myArray() As Variant
Dim ws As Worksheet
Dim lItems As Long
Dim lCount As Long
Dim pt As PivotTable
Dim lSel As Long

Me.Hide

lItems = 0

'count the unselected items -- these will be hidden
With Me.lstQtr
For lQtr = 0 To .ListCount - 1
If .Selected(lQtr) = False Then
lItems = lItems + 1
End If
Next lQtr
End With

ReDim myArray(0 To lItems - 1)

With Me.lstQtr
lSel = 0
For lCount = 0 To .ListCount - 1
If .Selected(lCount) = False Then
myArray(lSel) = "[Period].[All]." & .List(lCount)
lSel = lSel + 1
End If
Next lCount
End With

For Each ws In ActiveWorkbook.Worksheets
For Each pt In ws.PivotTables
With pt
.PivotFields("[Period].[Year]").HiddenItemsList = Array("")
.PivotFields("[Period].[Quarter]").HiddenItemsList = Array("")
.PivotFields("[Period].[Quarter]").HiddenItemsList = myArray
End With
Next pt
Next ws

Unload frmHideItems

End Sub
'=====================

i have a data source which links to my sheet via a pivot table. on this pivot
table is the Date range the date starts at january 2005-present what i
require if its possible to minipulate the pivot tables to automatically
select may 2006 -July 2006 from list box.

can you send me some sample code if its posssible please because im not an
expert in pivot tables and dont no really how to start.

(my previouse code was created from a macro but the string of selections was
changed to strings gained from arrays (variants) which were linked to a
worksheet that worked them out from the selection made in the list)

:

You said that "these selections are fed into arrays as strings". I
suggested creating an array from the list on the user form, instead of a
string. Did you try that?

Then, .hiddenitemlist=showquart should work.

hi debra
I had already tried hiddenitemlist = showquart()
but this caused an error saying type miss match
and then tried
hiddenitemlist = Array(showquart)
which then gave a ore encouraging error
items were not found on OLAP cube


any idea?

:



You could create an array from the list, instead of a string. Then, make
the HiddenItemsList equal to the array.

rivers wrote:


hi im trying to automate a selection for date on all my pvot tables. i have
these pivots that are joined by an olap cube to my workbook. what i have done
is create diffrent tables for diffrent measures but i have one constant and
this is Date. im trying to create a user form which selects from a list the
months required for viewing.
eg March april May, 2006

these selections are fed into arrays as strings like
[Time].[Months].[All Time].[2006].[Quarter 3 2006].[July 2006]
i understand that with the Date field it requires a hide method for the
months not selected.

this is my code so far


ActiveSheet.PivotTables("PivotTable1").CubeFields(44).TreeviewControl.Drilled
_
= Array(Array(""), array(showyear)), showquart)

ActiveSheet.PivotTables("PivotTable1").PivotFields("[Time].[Months].[Year]").
_
HiddenItemsList = Array(notyear)

ActiveSheet.PivotTables("PivotTable1").PivotFields("[Time].[Months].[Quarter]") _
.HiddenItemsList = notquart()

ActiveSheet.PivotTables("PivotTable1").PivotFields("[Time].[Months].[Month]"). _
HiddenItemsList = array(notmonth)

all the Arrays are previously set into strings on the workbook but the pivot
will not read my arrays as normal it comes up with errors and will not change
anything.

hope this reads alright

if anyones got a sample code that would be excellant!!!!!
i just want to be able to auto change 30 pivot tables to the date i chose in
a user format the start! sounds simple but as i found out, not so!

please help