Pivot Table Programming
 

Hi,

I have a pivot table and I'm tryng to change the page data dynamically. The
page data is a period dimension and based on the quarter, and month, it
should filter the page area...here is my code below and I will explain where
the
problem is below...

This is a custom function that gets the quarter...nothing wrong here
Function QTRNum(ENTER_DATE)
QTRNum = DatePart("q", ENTER_DATE)
End Function
-----------------------------------------------------------
This is the function that does the work.

Function currMonth()
'Declare some stuff
Dim D As String
Dim Y As String
Dim m As String
Dim Q As String

'Set values. I'm palcing the format of the data for the for the page field

Q = "[Quarter " & Module5.QTRNum(Date) & "]"
Y = "[" & Year(Date) & "]"
m = "[" & MonthName(Month(Date)) & "]"

'This simply assigns the .addpageitem to the currMonth variable. I know the
value is correct, because I will debug.pring CurrMonth and then paste the
results in the procedure and it executes fine. It's like it won't take a
string for this method.

It produces this...AddPageItem "[PERIOD].[All PERIOD].[2004].[Quarter
3].[October]"

currMonth = ".AddPageItem " & Chr(34) & "[PERIOD].[All PERIOD]" & "." & Y &
"." & Q & "." & m & Chr(34)
End Function
---Now, this is what should set the filter

With ActiveSheet.PivotTables("YTD").PivotFields("[PERIOD]")
.AddPageItem "[PERIOD].[All PERIOD].[2004].[Quarter 1]", True
.AddPageItem "[PERIOD].[All PERIOD].[2004].[Quarter 2]"
.AddPageItem "[PERIOD].[All PERIOD].[2004].[Quarter 3]"
Module5.currMonth
End With

HELP PLEASE!!!!!!!!!!!!



Expand AllCollapse All

Manage Your Profile

Pivot Table Programming
 

If I'm understanding this correctly, it looks like you're trying to use the
results of the curMonth function (which is a string) and wanting it to
execute like it was code. There may be some way to do that, but why don't
you just have curMonth return just the string:
currMonth = "[PERIOD].[All PERIOD]" & "." & Y &
"." & Q & "." & m & Chr(34)
End Function

And then down in the rest of your code call the .AddPageItem method with the
string returned from curMonth:

With ActiveSheet.PivotTables("YTD").PivotFields("[PERIOD]")
.AddPageItem "[PERIOD].[All PERIOD].[2004].[Quarter 1]", True
.AddPageItem "[PERIOD].[All PERIOD].[2004].[Quarter 2]"
.AddPageItem "[PERIOD].[All PERIOD].[2004].[Quarter 3]"
.AddPageItem Module5.currMonth
End With

-Erik

"OrrLyfe" wrote in message
...
Hi,

I have a pivot table and I'm tryng to change the page data dynamically.
The
page data is a period dimension and based on the quarter, and month, it
should filter the page area...here is my code below and I will explain
where
the
problem is below...

This is a custom function that gets the quarter...nothing wrong here
Function QTRNum(ENTER_DATE)
QTRNum = DatePart("q", ENTER_DATE)
End Function
-----------------------------------------------------------
This is the function that does the work.

Function currMonth()
'Declare some stuff
Dim D As String
Dim Y As String
Dim m As String
Dim Q As String

'Set values. I'm palcing the format of the data for the for the page
field

Q = "[Quarter " & Module5.QTRNum(Date) & "]"
Y = "[" & Year(Date) & "]"
m = "[" & MonthName(Month(Date)) & "]"

'This simply assigns the .addpageitem to the currMonth variable. I know
the
value is correct, because I will debug.pring CurrMonth and then paste the
results in the procedure and it executes fine. It's like it won't take a
string for this method.

It produces this...AddPageItem "[PERIOD].[All PERIOD].[2004].[Quarter
3].[October]"

currMonth = ".AddPageItem " & Chr(34) & "[PERIOD].[All PERIOD]" & "." & Y
&
"." & Q & "." & m & Chr(34)
End Function
---Now, this is what should set the filter

With ActiveSheet.PivotTables("YTD").PivotFields("[PERIOD]")
.AddPageItem "[PERIOD].[All PERIOD].[2004].[Quarter 1]", True
.AddPageItem "[PERIOD].[All PERIOD].[2004].[Quarter 2]"
.AddPageItem "[PERIOD].[All PERIOD].[2004].[Quarter 3]"
Module5.currMonth
End With

HELP PLEASE!!!!!!!!!!!!



Expand AllCollapse All

Manage Your Profile

Pivot Table Programming
 

Hi Eirk, Thanks for the reply, but now I'm getting message "Item could not be
found in OLAP cube". If I debug.print currMonth, copy and paste the results
directly after .Addpageitem, it works. So that tells me that the value is
correct.
Any other ideas?

"E Oveson" wrote:

If I'm understanding this correctly, it looks like you're trying to use the
results of the curMonth function (which is a string) and wanting it to
execute like it was code. There may be some way to do that, but why don't
you just have curMonth return just the string:
currMonth = "[PERIOD].[All PERIOD]" & "." & Y &
"." & Q & "." & m & Chr(34)
End Function

And then down in the rest of your code call the .AddPageItem method with the
string returned from curMonth:

With ActiveSheet.PivotTables("YTD").PivotFields("[PERIOD]")
.AddPageItem "[PERIOD].[All PERIOD].[2004].[Quarter 1]", True
.AddPageItem "[PERIOD].[All PERIOD].[2004].[Quarter 2]"
.AddPageItem "[PERIOD].[All PERIOD].[2004].[Quarter 3]"
.AddPageItem Module5.currMonth
End With

-Erik

"OrrLyfe" wrote in message
...
Hi,

I have a pivot table and I'm tryng to change the page data dynamically.
The
page data is a period dimension and based on the quarter, and month, it
should filter the page area...here is my code below and I will explain
where
the
problem is below...

This is a custom function that gets the quarter...nothing wrong here
Function QTRNum(ENTER_DATE)
QTRNum = DatePart("q", ENTER_DATE)
End Function
-----------------------------------------------------------
This is the function that does the work.

Function currMonth()
'Declare some stuff
Dim D As String
Dim Y As String
Dim m As String
Dim Q As String

'Set values. I'm palcing the format of the data for the for the page
field

Q = "[Quarter " & Module5.QTRNum(Date) & "]"
Y = "[" & Year(Date) & "]"
m = "[" & MonthName(Month(Date)) & "]"

'This simply assigns the .addpageitem to the currMonth variable. I know
the
value is correct, because I will debug.pring CurrMonth and then paste the
results in the procedure and it executes fine. It's like it won't take a
string for this method.

It produces this...AddPageItem "[PERIOD].[All PERIOD].[2004].[Quarter
3].[October]"

currMonth = ".AddPageItem " & Chr(34) & "[PERIOD].[All PERIOD]" & "." & Y
&
"." & Q & "." & m & Chr(34)
End Function
---Now, this is what should set the filter

With ActiveSheet.PivotTables("YTD").PivotFields("[PERIOD]")
.AddPageItem "[PERIOD].[All PERIOD].[2004].[Quarter 1]", True
.AddPageItem "[PERIOD].[All PERIOD].[2004].[Quarter 2]"
.AddPageItem "[PERIOD].[All PERIOD].[2004].[Quarter 3]"
Module5.currMonth
End With

HELP PLEASE!!!!!!!!!!!!



Expand AllCollapse All

Manage Your Profile

Pivot Table Programming
 

Hi,
Looking at your code more closely, it looks like you have chr(34)'s wrapping
your string, which would end up with a string that has quotations inside the
string. Then when you debug.print'd it returned the string with quotations
which you could just paste into the code and it would run as expected. But
when you ran the code, it had an extra set of quotations and so it could not
find that item. This is my guess at what is going wrong...

currMonth = "[PERIOD].[All PERIOD]" & "." & Y & "." & Q & "." & m 'strip
away extra quotes

That is probably the problem. If not, whatever's the problem, it must be
that there is some small difference between what you have pasted in
manually, and what currMonth is returning.

-Erik

"OrrLyfe" wrote in message
...
Hi Eirk, Thanks for the reply, but now I'm getting message "Item could not
be
found in OLAP cube". If I debug.print currMonth, copy and paste the
results
directly after .Addpageitem, it works. So that tells me that the value is
correct.
Any other ideas?

"E Oveson" wrote:

If I'm understanding this correctly, it looks like you're trying to use
the
results of the curMonth function (which is a string) and wanting it to
execute like it was code. There may be some way to do that, but why
don't
you just have curMonth return just the string:
currMonth = "[PERIOD].[All PERIOD]" & "." & Y &
"." & Q & "." & m & Chr(34)
End Function

And then down in the rest of your code call the .AddPageItem method with
the
string returned from curMonth:

With ActiveSheet.PivotTables("YTD").PivotFields("[PERIOD]")
.AddPageItem "[PERIOD].[All PERIOD].[2004].[Quarter 1]", True
.AddPageItem "[PERIOD].[All PERIOD].[2004].[Quarter 2]"
.AddPageItem "[PERIOD].[All PERIOD].[2004].[Quarter 3]"
.AddPageItem Module5.currMonth
End With

-Erik

"OrrLyfe" wrote in message
...
Hi,

I have a pivot table and I'm tryng to change the page data dynamically.
The
page data is a period dimension and based on the quarter, and month, it
should filter the page area...here is my code below and I will explain
where
the
problem is below...

This is a custom function that gets the quarter...nothing wrong here
Function QTRNum(ENTER_DATE)
QTRNum = DatePart("q", ENTER_DATE)
End Function
-----------------------------------------------------------
This is the function that does the work.

Function currMonth()
'Declare some stuff
Dim D As String
Dim Y As String
Dim m As String
Dim Q As String

'Set values. I'm palcing the format of the data for the for the page
field

Q = "[Quarter " & Module5.QTRNum(Date) & "]"
Y = "[" & Year(Date) & "]"
m = "[" & MonthName(Month(Date)) & "]"

'This simply assigns the .addpageitem to the currMonth variable. I
know
the
value is correct, because I will debug.pring CurrMonth and then paste
the
results in the procedure and it executes fine. It's like it won't take
a
string for this method.

It produces this...AddPageItem "[PERIOD].[All PERIOD].[2004].[Quarter
3].[October]"

currMonth = ".AddPageItem " & Chr(34) & "[PERIOD].[All PERIOD]" & "."
& Y
&
"." & Q & "." & m & Chr(34)
End Function
---Now, this is what should set the filter

With ActiveSheet.PivotTables("YTD").PivotFields("[PERIOD]")
.AddPageItem "[PERIOD].[All PERIOD].[2004].[Quarter 1]", True
.AddPageItem "[PERIOD].[All PERIOD].[2004].[Quarter 2]"
.AddPageItem "[PERIOD].[All PERIOD].[2004].[Quarter 3]"
Module5.currMonth
End With

HELP PLEASE!!!!!!!!!!!!



Expand AllCollapse All

Manage Your Profile