Pivot table help needed....

S

Sajith

HI,

I have a doubt in excel pivot tables. I have "Year"
dimension in Page Area. Month and Week date dimension in
Row area. There are some old data is displaying in week
date. I dont want to change the pivot table design for
this as data is huge. Want to know if a macro can help in
this matter.

Like If i select YEar dimension in the page area as "2004"
the weekdate should only have data of 2004.

Example :

Below is the current setup


YEAR 2004


MONTH WEEKDATE AMT

JUNE 01/06/2003 0
06/06/2004 56
11/06/2003 0
11/06/2004 78
JULY 01/07/2003 0
06/07/2004 34
11/07/2003 0
11/07/2004 86

I want to chage like the below...Depends on the
dimension..if i run a macro can we change like this.

YEAR 2004


MONTH WEEKDATE AMT

JUNE 06/02/2004 56
11/02/2004 78
JULY 06/07/2004 34
11/07/2004 86

Thanks in advance for any help regarding this..

Regards

Sajith
 
I

icestationzbra

when you say old data, does it mean that the data that is showing up i
the pivot table does not exist in the database on which this pivo
table is based upon? if so, then you can delete such items. go over t
http://www.contextures.com/xlPivot04.html.

if you mean something else, then perhaps i did not understand yo
question right. provide a little more detail. do you mean that you don
want items showing up that have amount as '0'
 
S

Sajith

hi
let me say thanks first for replying to my query...I will
try to explain my doubt by making more simpler.

Year is a dimension.
Month & week date is another dimension. Week date is the
child of Month.

using a macro can i unselect the all the weekdate whose
year is 2003 ?

below is the current set up

MONTH WEEKDATE AMT

JUNE 01/06/2003 0
06/06/2004 56
11/06/2003 0
11/06/2004 78
JULY 01/07/2003 0
06/07/2004 34
11/07/2003 0
11/07/2004 86

I want to chage like the below...after the macro runs

MONTH WEEKDATE AMT

JUNE 06/02/2004 56
11/02/2004 78
JULY 06/07/2004 34
11/07/2004 86

Thanks in advance for any help regarding this..

Regards

sajith
 
D

Dave Peterson

First, I think I'd just create one more field in my data table.

=year() to extract the year from the date.

Then make that Year field a page field.

I could hide/show whatever year I wanted and it would be easy to play what-if
games.

But Debra Dalgleish has lots of pivottable code at:
http://www.contextures.com/xlPivot03.html

I stole part of one and came up with this. You'll have to modify it match your
situation:

Option Explicit
Sub testme()
Dim pf As PivotField
Dim pi As PivotItem

Application.ScreenUpdating = False
Application.DisplayAlerts = False

Set pf = Worksheets("sheet2").PivotTables("PivotTable1") _
.PivotFields("weekdate")

On Error Resume Next
With pf
.AutoSort xlManual, .SourceName
For Each pi In pf.PivotItems
If IsDate(pi.Caption) Then
If Year(CDate(pi.Caption)) = 2004 Then
pi.Visible = False
End If
End If
Next pi
.AutoSort xlAscending, .SourceName
End With
On Error GoTo 0

Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub
 
S

Sajith

hi,
thanks for the help....i have changed my code
accordingly....But "pi.Visible" is always true. I am not
getting the results.

Tried to update "pi.Visible" = False when even year =
2003 . But it is not updating. Seems like visible
property of pivot items is read only. Always it is true.
When i checked microsoft site, its written visible type is
read/write boolean. where I am going wrong ?

Or Visible property of Pivot items will not work for OLAP
data source (my data source is poiting to analysis
Manager) ??

changed code is below...

Dim pf As PivotField
Dim pfmonth As PivotField
Dim pi As PivotItem
Dim Pimonth As PivotItem

Application.ScreenUpdating = False
Application.DisplayAlerts = False

Set pf = ActiveSheet.PivotTables("PivotTable1") _
.PivotFields("[Week Date].[Week Date]")

Set pfmonth = ActiveSheet.PivotTables("PivotTable1") _
.PivotFields("[Week Date].[Month]")
Worksheets("Sheet2").PivotTables("PivotTable1") _
.PivotFields("[Week Date].[Week Date]").PivotItems
("22/09/2003").Visible = False


'For Each Pimonth In pfmonth.ChildItems
' If Pimonth.ChildItems = "01/01/2004" Then
' End If

'Next Pimonth
Application.ScreenUpdating = True
Application.DisplayAlerts = True

On Error Resume Next
With pf
.AutoSort xlManual, .SourceName
For Each pi In pf.PivotItems
If IsDate(pi.Caption) Then
If Year(CDate(pi.Caption)) = 2004 Then
pi.Visible = True

Else
pi.Visible = False

End If
End If
Next pi
.AutoSort xlAscending, .SourceName
End With
On Error GoTo 0

Application.DisplayAlerts = True
Application.ScreenUpdating = True
ActiveSheet.PivotTables("PivotTable1").RefreshTable
 
D

Dave Peterson

Try just this little portion:

Worksheets("Sheet2").PivotTables("PivotTable1") _
.PivotFields("[Week Date].[Week Date]").PivotItems("22/09/2003").Visible _
= False

Did that work to hide 22/09/2003?

If it did, then comment out the "on error resume next" line. Maybe it's masking
a more basic error.

(I'd double check those names to make sure that they're correct, too.)


hi,
thanks for the help....i have changed my code
accordingly....But "pi.Visible" is always true. I am not
getting the results.

Tried to update "pi.Visible" = False when even year =
2003 . But it is not updating. Seems like visible
property of pivot items is read only. Always it is true.
When i checked microsoft site, its written visible type is
read/write boolean. where I am going wrong ?

Or Visible property of Pivot items will not work for OLAP
data source (my data source is poiting to analysis
Manager) ??

changed code is below...

Dim pf As PivotField
Dim pfmonth As PivotField
Dim pi As PivotItem
Dim Pimonth As PivotItem

Application.ScreenUpdating = False
Application.DisplayAlerts = False

Set pf = ActiveSheet.PivotTables("PivotTable1") _
.PivotFields("[Week Date].[Week Date]")

Set pfmonth = ActiveSheet.PivotTables("PivotTable1") _
.PivotFields("[Week Date].[Month]")
Worksheets("Sheet2").PivotTables("PivotTable1") _
.PivotFields("[Week Date].[Week Date]").PivotItems
("22/09/2003").Visible = False

'For Each Pimonth In pfmonth.ChildItems
' If Pimonth.ChildItems = "01/01/2004" Then
' End If

'Next Pimonth
Application.ScreenUpdating = True
Application.DisplayAlerts = True

On Error Resume Next
With pf
.AutoSort xlManual, .SourceName
For Each pi In pf.PivotItems
If IsDate(pi.Caption) Then
If Year(CDate(pi.Caption)) = 2004 Then
pi.Visible = True

Else
pi.Visible = False

End If
End If
Next pi
.AutoSort xlAscending, .SourceName
End With
On Error GoTo 0

Application.DisplayAlerts = True
Application.ScreenUpdating = True
ActiveSheet.PivotTables("PivotTable1").RefreshTable
-----Original Message-----
First, I think I'd just create one more field in my data table.

=year() to extract the year from the date.

Then make that Year field a page field.

I could hide/show whatever year I wanted and it would be easy to play what-if
games.

But Debra Dalgleish has lots of pivottable code at:
http://www.contextures.com/xlPivot03.html

I stole part of one and came up with this. You'll have to modify it match your
situation:

Option Explicit
Sub testme()
Dim pf As PivotField
Dim pi As PivotItem

Application.ScreenUpdating = False
Application.DisplayAlerts = False

Set pf = Worksheets("sheet2").PivotTables("PivotTable1") _
.PivotFields("weekdate")

On Error Resume Next
With pf
.AutoSort xlManual, .SourceName
For Each pi In pf.PivotItems
If IsDate(pi.Caption) Then
If Year(CDate(pi.Caption)) = 2004 Then
pi.Visible = False
End If
End If
Next pi
.AutoSort xlAscending, .SourceName
End With
On Error GoTo 0

Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub




--

Dave Peterson
(e-mail address removed)
.
Click to expand...
Click to expand...
 
S

Sajith

hi dave,
yes you are right. it is giving me error when i comment
out "on error resume next" line. This is the message I am
getting "Unable to set the visible property of the pivot
item class" . Is the visible property is read only ?

Roughly what time you will be online so that i can be
online here that time. I am in singapore (GMT + 8:00). Can
I send mails straight to "(e-mail address removed)" ?

Regards

-----Original Message-----
Try just this little portion:

Worksheets("Sheet2").PivotTables("PivotTable1") _
.PivotFields("[Week Date].[Week Date]").PivotItems ("22/09/2003").Visible _
= False

Did that work to hide 22/09/2003?

If it did, then comment out the "on error resume next" line. Maybe it's masking
a more basic error.

(I'd double check those names to make sure that they're correct, too.)


hi,
thanks for the help....i have changed my code
accordingly....But "pi.Visible" is always true. I am not
getting the results.

Tried to update "pi.Visible" = False when even year =
2003 . But it is not updating. Seems like visible
property of pivot items is read only. Always it is true.
When i checked microsoft site, its written visible type is
read/write boolean. where I am going wrong ?

Or Visible property of Pivot items will not work for OLAP
data source (my data source is poiting to analysis
Manager) ??

changed code is below...

Dim pf As PivotField
Dim pfmonth As PivotField
Dim pi As PivotItem
Dim Pimonth As PivotItem

Application.ScreenUpdating = False
Application.DisplayAlerts = False

Set pf = ActiveSheet.PivotTables("PivotTable1") _
.PivotFields("[Week Date].[Week Date]")

Set pfmonth = ActiveSheet.PivotTables("PivotTable1") _
.PivotFields("[Week Date].[Month]")
Worksheets("Sheet2").PivotTables("PivotTable1") _
.PivotFields("[Week Date].[Week Date]").PivotItems
("22/09/2003").Visible = False

'For Each Pimonth In pfmonth.ChildItems
' If Pimonth.ChildItems = "01/01/2004" Then
' End If

'Next Pimonth
Application.ScreenUpdating = True
Application.DisplayAlerts = True

On Error Resume Next
With pf
.AutoSort xlManual, .SourceName
For Each pi In pf.PivotItems
If IsDate(pi.Caption) Then
If Year(CDate(pi.Caption)) = 2004 Then
pi.Visible = True

Else
pi.Visible = False

End If
End If
Next pi
.AutoSort xlAscending, .SourceName
End With
On Error GoTo 0

Application.DisplayAlerts = True
Application.ScreenUpdating = True
ActiveSheet.PivotTables("PivotTable1").RefreshTable
-----Original Message-----
First, I think I'd just create one more field in my
Click to expand...
data
table.
=year() to extract the year from the date.

Then make that Year field a page field.

I could hide/show whatever year I wanted and it would
Click to expand...
be
easy to play what-if
games.

But Debra Dalgleish has lots of pivottable code at:
http://www.contextures.com/xlPivot03.html

I stole part of one and came up with this. You'll have to modify it match your
situation:

Option Explicit
Sub testme()
Dim pf As PivotField
Dim pi As PivotItem

Application.ScreenUpdating = False
Application.DisplayAlerts = False

Set pf = Worksheets("sheet2").PivotTables ("PivotTable1") _
.PivotFields("weekdate")

On Error Resume Next
With pf
.AutoSort xlManual, .SourceName
For Each pi In pf.PivotItems
If IsDate(pi.Caption) Then
If Year(CDate(pi.Caption)) = 2004 Then
pi.Visible = False
End If
End If
Next pi
.AutoSort xlAscending, .SourceName
End With
On Error GoTo 0

Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub



Sajith wrote:

hi
let me say thanks first for replying to my query...I will
try to explain my doubt by making more simpler.

Year is a dimension.
Month & week date is another dimension. Week date is the
child of Month.

using a macro can i unselect the all the weekdate whose
year is 2003 ?

below is the current set up

MONTH WEEKDATE AMT

JUNE 01/06/2003 0
06/06/2004 56
11/06/2003 0
11/06/2004 78
JULY 01/07/2003 0
06/07/2004 34
11/07/2003 0
11/07/2004 86

I want to chage like the below...after the macro runs

MONTH WEEKDATE AMT

JUNE 06/02/2004 56
11/02/2004 78
JULY 06/07/2004 34
11/07/2004 86

Thanks in advance for any help regarding this..

Regards

sajith
-----Original Message-----

when you say old data, does it mean that the data
Click to expand...
that
is
showing up in
the pivot table does not exist in the database on which
this pivot
table is based upon? if so, then you can delete such
items. go over to
http://www.contextures.com/xlPivot04.html.

if you mean something else, then perhaps i did not
understand you
question right. provide a little more detail. do you mean
that you dont
want items showing up that have amount as '0'.
Click to expand...
---
---
--------------
icestationzbra's Profile:
http://www.excelforum.com/member.php?
action=getinfo&userid=4580
View this thread:
http://www.excelforum.com/showthread.php? threadid=261540

.


--

Dave Peterson
(e-mail address removed)
.
Click to expand...
Click to expand...

--

Dave Peterson
(e-mail address removed)
.
Click to expand...
 
D

Dave Peterson

In a private email exchange, I suggested that Sajith record a macro when hiding
one date. Then post back to the newsgroup so that field names/captions can be
seen.
hi dave,
yes you are right. it is giving me error when i comment
out "on error resume next" line. This is the message I am
getting "Unable to set the visible property of the pivot
item class" . Is the visible property is read only ?

Roughly what time you will be online so that i can be
online here that time. I am in singapore (GMT + 8:00). Can
I send mails straight to "(e-mail address removed)" ?

Regards
-----Original Message-----
Try just this little portion:

Worksheets("Sheet2").PivotTables("PivotTable1") _
.PivotFields("[Week Date].[Week Date]").PivotItems ("22/09/2003").Visible _
= False

Did that work to hide 22/09/2003?

If it did, then comment out the "on error resume next" line. Maybe it's masking
a more basic error.

(I'd double check those names to make sure that they're correct, too.)


hi,
thanks for the help....i have changed my code
accordingly....But "pi.Visible" is always true. I am not
getting the results.

Tried to update "pi.Visible" = False when even year =
2003 . But it is not updating. Seems like visible
property of pivot items is read only. Always it is true.
When i checked microsoft site, its written visible type is
read/write boolean. where I am going wrong ?

Or Visible property of Pivot items will not work for OLAP
data source (my data source is poiting to analysis
Manager) ??

changed code is below...

Dim pf As PivotField
Dim pfmonth As PivotField
Dim pi As PivotItem
Dim Pimonth As PivotItem

Application.ScreenUpdating = False
Application.DisplayAlerts = False

Set pf = ActiveSheet.PivotTables("PivotTable1") _
.PivotFields("[Week Date].[Week Date]")

Set pfmonth = ActiveSheet.PivotTables("PivotTable1") _
.PivotFields("[Week Date].[Month]")
Worksheets("Sheet2").PivotTables("PivotTable1") _
.PivotFields("[Week Date].[Week Date]").PivotItems
("22/09/2003").Visible = False

'For Each Pimonth In pfmonth.ChildItems
' If Pimonth.ChildItems = "01/01/2004" Then
' End If

'Next Pimonth
Application.ScreenUpdating = True
Application.DisplayAlerts = True

On Error Resume Next
With pf
.AutoSort xlManual, .SourceName
For Each pi In pf.PivotItems
If IsDate(pi.Caption) Then
If Year(CDate(pi.Caption)) = 2004 Then
pi.Visible = True

Else
pi.Visible = False

End If
End If
Next pi
.AutoSort xlAscending, .SourceName
End With
On Error GoTo 0

Application.DisplayAlerts = True
Application.ScreenUpdating = True
ActiveSheet.PivotTables("PivotTable1").RefreshTable

-----Original Message-----
First, I think I'd just create one more field in my data
table.

=year() to extract the year from the date.

Then make that Year field a page field.

I could hide/show whatever year I wanted and it would be
easy to play what-if
games.

But Debra Dalgleish has lots of pivottable code at:
http://www.contextures.com/xlPivot03.html

I stole part of one and came up with this. You'll have
to modify it match your
situation:

Option Explicit
Sub testme()
Dim pf As PivotField
Dim pi As PivotItem

Application.ScreenUpdating = False
Application.DisplayAlerts = False

Set pf = Worksheets("sheet2").PivotTables ("PivotTable1") _
.PivotFields("weekdate")

On Error Resume Next
With pf
.AutoSort xlManual, .SourceName
For Each pi In pf.PivotItems
If IsDate(pi.Caption) Then
If Year(CDate(pi.Caption)) = 2004 Then
pi.Visible = False
End If
End If
Next pi
.AutoSort xlAscending, .SourceName
End With
On Error GoTo 0

Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub



Sajith wrote:

hi
let me say thanks first for replying to my query...I
will
try to explain my doubt by making more simpler.

Year is a dimension.
Month & week date is another dimension. Week date is the
child of Month.

using a macro can i unselect the all the weekdate whose
year is 2003 ?

below is the current set up

MONTH WEEKDATE AMT

JUNE 01/06/2003 0
06/06/2004 56
11/06/2003 0
11/06/2004 78
JULY 01/07/2003 0
06/07/2004 34
11/07/2003 0
11/07/2004 86

I want to chage like the below...after the macro runs

MONTH WEEKDATE AMT

JUNE 06/02/2004 56
11/02/2004 78
JULY 06/07/2004 34
11/07/2004 86

Thanks in advance for any help regarding this..

Regards

sajith
-----Original Message-----

when you say old data, does it mean that the data that
is
showing up in
the pivot table does not exist in the database on which
this pivot
table is based upon? if so, then you can delete such
items. go over to
http://www.contextures.com/xlPivot04.html.

if you mean something else, then perhaps i did not
understand you
question right. provide a little more detail. do you
mean
that you dont
want items showing up that have amount as '0'.


--
icestationzbra
---------------------------------------------------- ---
---
--------------
icestationzbra's Profile:
http://www.excelforum.com/member.php?
action=getinfo&userid=4580
View this thread:
http://www.excelforum.com/showthread.php? threadid=261540

.


--

Dave Peterson
(e-mail address removed)
.
Click to expand...

--

Dave Peterson
(e-mail address removed)
.
Click to expand...
Click to expand...
 
S

Sajith

Hi dave,

I have successfully recorded the macro. It is giving me
the activesheet....treeviewcontrol.hidden = array(""),
etc....a lengthy code and it is working also. But i was
not able to do implement it programattically.

In "excel help" itself is said, "hidden and drilled" is
used only for macro recording. Further i tried to explore
on the visible property of OLAP. I found that visible
property does work for the values in excel(data source is
excel). If the source is Analysis Manager(olap data
source), visible property will not work. Is there any
other option i can use ? Please help me. I wont be
implement a new level for the dimension in the analysis
server as the data size is so huge. If I process it will
take hours and it will affect the server performance. This
is the reason i am trying to write this query...

It will be very thankful if you can help me in this
matter...

Thanks and regards

Sajith
-----Original Message-----
In a private email exchange, I suggested that Sajith record a macro when hiding
one date. Then post back to the newsgroup so that field names/captions can be
seen.
hi dave,
yes you are right. it is giving me error when i comment
out "on error resume next" line. This is the message I am
getting "Unable to set the visible property of the pivot
item class" . Is the visible property is read only ?

Roughly what time you will be online so that i can be
online here that time. I am in singapore (GMT + 8:00). Can
I send mails straight to "(e-mail address removed)" ?

Regards
-----Original Message-----
Try just this little portion:

Worksheets("Sheet2").PivotTables("PivotTable1") _
.PivotFields("[Week Date].[Week Date]").PivotItems ("22/09/2003").Visible _
= False

Did that work to hide 22/09/2003?

If it did, then comment out the "on error resume next" line. Maybe it's masking
a more basic error.

(I'd double check those names to make sure that they're correct, too.)



Sajith wrote:

hi,
thanks for the help....i have changed my code
accordingly....But "pi.Visible" is always true. I am not
getting the results.

Tried to update "pi.Visible" = False when even year =
2003 . But it is not updating. Seems like visible
property of pivot items is read only. Always it is true.
When i checked microsoft site, its written visible
Click to expand...
type
is
read/write boolean. where I am going wrong ?

Or Visible property of Pivot items will not work for OLAP
data source (my data source is poiting to analysis
Manager) ??

changed code is below...

Dim pf As PivotField
Dim pfmonth As PivotField
Dim pi As PivotItem
Dim Pimonth As PivotItem

Application.ScreenUpdating = False
Application.DisplayAlerts = False

Set pf = ActiveSheet.PivotTables("PivotTable1") _
.PivotFields("[Week Date].[Week Date]")

Set pfmonth = ActiveSheet.PivotTables("PivotTable1") _
.PivotFields("[Week Date].[Month]")
Worksheets("Sheet2").PivotTables("PivotTable1") _
.PivotFields("[Week Date].[Week Date]").PivotItems
("22/09/2003").Visible = False

'For Each Pimonth In pfmonth.ChildItems
' If Pimonth.ChildItems = "01/01/2004" Then
' End If

'Next Pimonth
Application.ScreenUpdating = True
Application.DisplayAlerts = True

On Error Resume Next
With pf
.AutoSort xlManual, .SourceName
For Each pi In pf.PivotItems
If IsDate(pi.Caption) Then
If Year(CDate(pi.Caption)) = 2004 Then
pi.Visible = True

Else
pi.Visible = False

End If
End If
Next pi
.AutoSort xlAscending, .SourceName
End With
On Error GoTo 0

Application.DisplayAlerts = True
Application.ScreenUpdating = True
ActiveSheet.PivotTables("PivotTable1").RefreshTable

-----Original Message-----
First, I think I'd just create one more field in my data
table.

=year() to extract the year from the date.

Then make that Year field a page field.

I could hide/show whatever year I wanted and it
Click to expand...
would
be
easy to play what-if
games.

But Debra Dalgleish has lots of pivottable code at:
http://www.contextures.com/xlPivot03.html

I stole part of one and came up with this. You'll have
to modify it match your
situation:

Option Explicit
Sub testme()
Dim pf As PivotField
Dim pi As PivotItem

Application.ScreenUpdating = False
Application.DisplayAlerts = False

Set pf = Worksheets("sheet2").PivotTables ("PivotTable1") _
.PivotFields("weekdate")

On Error Resume Next
With pf
.AutoSort xlManual, .SourceName
For Each pi In pf.PivotItems
If IsDate(pi.Caption) Then
If Year(CDate(pi.Caption)) = 2004 Then
pi.Visible = False
End If
End If
Next pi
.AutoSort xlAscending, .SourceName
End With
On Error GoTo 0

Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub



Sajith wrote:

hi
let me say thanks first for replying to my query...I
will
try to explain my doubt by making more simpler.

Year is a dimension.
Month & week date is another dimension. Week date
Click to expand...
is
the
child of Month.

using a macro can i unselect the all the weekdate whose
year is 2003 ?

below is the current set up

MONTH WEEKDATE AMT

JUNE 01/06/2003 0
06/06/2004 56
11/06/2003 0
11/06/2004 78
JULY 01/07/2003 0
06/07/2004 34
11/07/2003 0
11/07/2004 86

I want to chage like the below...after the macro runs

MONTH WEEKDATE AMT

JUNE 06/02/2004 56
11/02/2004 78
JULY 06/07/2004 34
11/07/2004 86

Thanks in advance for any help regarding this..

Regards

sajith
-----Original Message-----

when you say old data, does it mean that the data that
is
showing up in
the pivot table does not exist in the database on which
this pivot
table is based upon? if so, then you can delete such
items. go over to
http://www.contextures.com/xlPivot04.html.

if you mean something else, then perhaps i did not
understand you
question right. provide a little more detail. do you
mean
that you dont
want items showing up that have amount as '0'.
Click to expand...
---
---
---
--------------
icestationzbra's Profile:
http://www.excelforum.com/member.php?
action=getinfo&userid=4580
View this thread:
http://www.excelforum.com/showthread.php? threadid=261540

.


--

Dave Peterson
(e-mail address removed)
.


--

Dave Peterson
(e-mail address removed)
.
Click to expand...
Click to expand...

--

Dave Peterson
(e-mail address removed)
.
Click to expand...
 
D

Dave Peterson

First, I've never used the treeview control and I've never used OLAP. So I
don't think I can offer any suggestions.
Hi dave,

I have successfully recorded the macro. It is giving me
the activesheet....treeviewcontrol.hidden = array(""),
etc....a lengthy code and it is working also. But i was
not able to do implement it programattically.

In "excel help" itself is said, "hidden and drilled" is
used only for macro recording. Further i tried to explore
on the visible property of OLAP. I found that visible
property does work for the values in excel(data source is
excel). If the source is Analysis Manager(olap data
source), visible property will not work. Is there any
other option i can use ? Please help me. I wont be
implement a new level for the dimension in the analysis
server as the data size is so huge. If I process it will
take hours and it will affect the server performance. This
is the reason i am trying to write this query...

It will be very thankful if you can help me in this
matter...

Thanks and regards

Sajith
-----Original Message-----
In a private email exchange, I suggested that Sajith record a macro when hiding
one date. Then post back to the newsgroup so that field names/captions can be
seen.
hi dave,
yes you are right. it is giving me error when i comment
out "on error resume next" line. This is the message I am
getting "Unable to set the visible property of the pivot
item class" . Is the visible property is read only ?

Roughly what time you will be online so that i can be
online here that time. I am in singapore (GMT + 8:00). Can
I send mails straight to "(e-mail address removed)" ?

Regards

-----Original Message-----
Try just this little portion:

Worksheets("Sheet2").PivotTables("PivotTable1") _
.PivotFields("[Week Date].[Week Date]").PivotItems
("22/09/2003").Visible _
= False

Did that work to hide 22/09/2003?

If it did, then comment out the "on error resume next"
line. Maybe it's masking
a more basic error.

(I'd double check those names to make sure that they're
correct, too.)



Sajith wrote:

hi,
thanks for the help....i have changed my code
accordingly....But "pi.Visible" is always true. I am
not
getting the results.

Tried to update "pi.Visible" = False when even year =
2003 . But it is not updating. Seems like visible
property of pivot items is read only. Always it is true.
When i checked microsoft site, its written visible type
is
read/write boolean. where I am going wrong ?

Or Visible property of Pivot items will not work for
OLAP
data source (my data source is poiting to analysis
Manager) ??

changed code is below...

Dim pf As PivotField
Dim pfmonth As PivotField
Dim pi As PivotItem
Dim Pimonth As PivotItem

Application.ScreenUpdating = False
Application.DisplayAlerts = False

Set pf = ActiveSheet.PivotTables("PivotTable1") _
.PivotFields("[Week Date].[Week Date]")

Set pfmonth = ActiveSheet.PivotTables("PivotTable1") _
.PivotFields("[Week Date].[Month]")
Worksheets("Sheet2").PivotTables("PivotTable1") _
.PivotFields("[Week Date].[Week Date]").PivotItems
("22/09/2003").Visible = False

'For Each Pimonth In pfmonth.ChildItems
' If Pimonth.ChildItems = "01/01/2004" Then
' End If

'Next Pimonth
Application.ScreenUpdating = True
Application.DisplayAlerts = True

On Error Resume Next
With pf
.AutoSort xlManual, .SourceName
For Each pi In pf.PivotItems
If IsDate(pi.Caption) Then
If Year(CDate(pi.Caption)) = 2004 Then
pi.Visible = True

Else
pi.Visible = False

End If
End If
Next pi
.AutoSort xlAscending, .SourceName
End With
On Error GoTo 0

Application.DisplayAlerts = True
Application.ScreenUpdating = True
ActiveSheet.PivotTables("PivotTable1").RefreshTable

-----Original Message-----
First, I think I'd just create one more field in my
data
table.

=year() to extract the year from the date.

Then make that Year field a page field.

I could hide/show whatever year I wanted and it would
be
easy to play what-if
games.

But Debra Dalgleish has lots of pivottable code at:
http://www.contextures.com/xlPivot03.html

I stole part of one and came up with this. You'll have
to modify it match your
situation:

Option Explicit
Sub testme()
Dim pf As PivotField
Dim pi As PivotItem

Application.ScreenUpdating = False
Application.DisplayAlerts = False

Set pf = Worksheets("sheet2").PivotTables
("PivotTable1") _
.PivotFields("weekdate")

On Error Resume Next
With pf
.AutoSort xlManual, .SourceName
For Each pi In pf.PivotItems
If IsDate(pi.Caption) Then
If Year(CDate(pi.Caption)) = 2004 Then
pi.Visible = False
End If
End If
Next pi
.AutoSort xlAscending, .SourceName
End With
On Error GoTo 0

Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub



Sajith wrote:

hi
let me say thanks first for replying to my query...I
will
try to explain my doubt by making more simpler.

Year is a dimension.
Month & week date is another dimension. Week date is
the
child of Month.

using a macro can i unselect the all the weekdate
whose
year is 2003 ?

below is the current set up

MONTH WEEKDATE AMT

JUNE 01/06/2003 0
06/06/2004 56
11/06/2003 0
11/06/2004 78
JULY 01/07/2003 0
06/07/2004 34
11/07/2003 0
11/07/2004 86

I want to chage like the below...after the macro runs

MONTH WEEKDATE AMT

JUNE 06/02/2004 56
11/02/2004 78
JULY 06/07/2004 34
11/07/2004 86

Thanks in advance for any help regarding this..

Regards

sajith
-----Original Message-----

when you say old data, does it mean that the data
that
is
showing up in
the pivot table does not exist in the database on
which
this pivot
table is based upon? if so, then you can delete such
items. go over to
http://www.contextures.com/xlPivot04.html.

if you mean something else, then perhaps i did not
understand you
question right. provide a little more detail. do you
mean
that you dont
want items showing up that have amount as '0'.


--
icestationzbra
------------------------------------------------- ---
---
---
--------------
icestationzbra's Profile:
http://www.excelforum.com/member.php?
action=getinfo&userid=4580
View this thread:
http://www.excelforum.com/showthread.php?
threadid=261540

.


--

Dave Peterson
(e-mail address removed)
.


--

Dave Peterson
(e-mail address removed)
.
Click to expand...

--

Dave Peterson
(e-mail address removed)
.
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Olap Report problem 1
Pivot table doubt 1
How to Round a Date to a Month 2
Calculations within summary cells, reflecting the visble cells only after filtering 5
Tricky Scenario finding days/months/years! 3
OT: Selective HP drivers (apologies for length) 7
Pivot Table Summarizing Quirk - Please Help 1
Conditional sum on an array based on another array 3

Top