PDA

View Full Version : [SOLVED] VBA code for pivot filtering



timor
03-09-2013, 06:46 AM
Hi, i'm having a problem with the VBA code for changing the report-filter in a pivot table.

ActiveSheet.PivotTables("Vrtilna tabela4").PivotFields( _
"[DATUM].[Day_Of_Month].[Day_Of_Month]").VisibleItemsList = Array( _
"[DATUM].[Day_Of_Month].&[1]", "[DATUM].[Day_Of_Month].&[2]", _
"[DATUM].[Day_Of_Month].&[3]", "[DATUM].[Day_Of_Month].&[4]")

Today I manualy typed "[DATUM].[Day_Of_Month].&[X]" from the first day of the month to that day that I want.

I would like to read the number in a cell G31. This number represent number of days that I want to filter from pivotTables


Range("g31").Select
BS = ActiveCell.Value
counter = 1
Do While counter < BS
ActiveSheet.PivotTables("Vrtilna tabela4").PivotFields( _
"[DATUM].[Day_Of_Month].[Day_Of_Month]").VisibleItemsList = Array( _
"[DATUM].[Day_Of_Month].&[" & counter & "]")
counter = counter + 1
Loop
End Sub


Unfortunately this doesn't work.
Thank you for you help.

Timor

sassora
03-09-2013, 10:41 AM
Hi Timor

I think I understand what you are trying to do...

Extending the array string from
Array( _
"[DATUM].[Day_Of_Month].&[1]", "[DATUM].[Day_Of_Month].&[2]", _
"[DATUM].[Day_Of_Month].&[3]", ... to the number of days required.

The following code generates the array string which then gets added on at the end:

Sub UpToDayOfMonth()

Dim PivotStr As String
Dim BS As Long
Dim counter As Long

BS = Range("G31").Value
PivotStr = "[DATUM].[Day_Of_Month].&[1]"

counter = 2
Do While counter < BS
PivotStr = PivotStr + ", [DATUM].[Day_Of_Month].&[" & counter & "]"
counter = counter + 1
Loop

ActiveSheet.PivotTables("Vrtilna tabela4").PivotFields( _
"[DATUM].[Day_Of_Month].[Day_Of_Month]").VisibleItemsList = Array(PivotStr)

End Sub


This could be tweaked slightly. This will always give you at least the first two days - this can be adapted if needed.

timor
03-09-2013, 02:09 PM
Sassora thank you

You showed me the way of thinking. I had to change a little bit your code and now is working perfectly.

Thank you


Sub UpToDayOfMonth()

Dim PivotStr(1 To 31) As String
Dim BS As Long
Dim counter As Long

BS = Range("G31").Value
PivotStr(1) = "[DATUM].[Day_Of_Month].&[1]"

counter = 2
Do While counter < BS
PivotStr(counter) = "[DATUM].[Day_Of_Month].&[" & counter & "]"
counter = counter + 1
Loop

ActiveSheet.PivotTables("Vrtilna tabela4").PivotFields( _
"[DATUM].[Day_Of_Month].[Day_Of_Month]").VisibleItemsList = Array(PivotStr)

End Sub

sassora
03-10-2013, 02:29 AM
How about ...

Sub UpToDayOfMonth()

Dim PivotStr(1 To 31) As String
Dim BS As Long
Dim counter As Long

BS = Range("G31").Value

counter = 1
Do While counter < BS
PivotStr(counter) = "[DATUM].[Day_Of_Month].&[" & counter & "]"
counter = counter + 1
Loop

ActiveSheet.PivotTables("Vrtilna tabela4").PivotFields( _
"[DATUM].[Day_Of_Month].[Day_Of_Month]").VisibleItemsList = Array(PivotStr)

End Sub


Would you send an example of the workbook? .. I'd like to see this in action.

chichato
07-06-2016, 08:55 AM
Thank you so much, I had the same problem, but now it works



You showed me the way of thinking. I had to change a little bit your code and now is working perfectly.

Thank you

Sub UpToDayOfMonth()

Dim PivotStr(1 To 31) As String
Dim BS As Long
Dim counter As Long

BS = Range("G31").Value
PivotStr(1) = "[DATUM].[Day_Of_Month].&[1]"

counter = 2
Do While counter < BS
PivotStr(counter) = "[DATUM].[Day_Of_Month].&[" & counter & "]"
counter = counter + 1
Loop

ActiveSheet.PivotTables("Vrtilna tabela4").PivotFields( _
"[DATUM].[Day_Of_Month].[Day_Of_Month]").VisibleItemsList = Array(PivotStr)

End Sub

Formatting tags added by mark007

Quarantain
09-20-2016, 07:37 AM
Hi,

Although the thread is rather old, I'm hoping I'll be able to find some help.

I have Pivottables in Excel which have an OLAP Cube behind them. I want to be able to change the values in the Period filter (e.g. 201607, 201606, 201603) through VBA.
I have recorded a macro while manually changing the filter and subsequently tried to replicate this with VBA code.
When I use the recorded code and change the values, the code works perfectly.
When I use the dynamic code I hit a the proverbial brick wall: Run-time error '1004': Application-Defined or object-defined error

Can anybody help me expose my blind-spot?


Function HelpWithPivotPlusOLAP() '(ByRef PivotTableID As String)
' Update filters that require the last month and the last months of the last three quarters
' PivotTable uses a Connection in Excel to het to the OLAP cube
'

Dim Reportingdate, EndOfLastQuarter1D, EndOfLastQuarter2D, EndOfLastQuarter3D As Date
Dim LastMonth, EndOfLastQuarter1, EndOfLastQuarter2, EndOfLastQuarter3, Arr_Support(3) As Long
Dim Rep_MY, PivotTableID, CodedVisibleItem, CodedVisibleItemsList As String
Dim Index, Rep_Month, Rep_Year, A, I As Integer

Dim Arr_LM3LQ(3) As Variant '0-3 is 4 positions; Array to hold values for the pivot table's VisibleItemsList

PivotTableID = Chr(34) & "PV_29_1_1" & Chr(34)

' Transform user provided info into workable variable
Rep_Month = ActiveWorkbook.Sheets("Uitvoering").Range("C2").Value '7
Rep_Year = ActiveWorkbook.Sheets("Uitvoering").Range("D2").Value '2016
Reportingdate = DateSerial(Rep_Year, Rep_Month + 1, 0) 'Last calendar day for the indicated month

'Determine necessary values
EndOfLastQuarter1D = DateSerial(Year(Reportingdate), (((Month(Reportingdate) - 1) \ 3) * 3) + 1, 0)
EndOfLastQuarter2D = DateAdd("Q", -1, EndOfLastQuarter1D)
EndOfLastQuarter3D = DateAdd("Q", -1, EndOfLastQuarter2D)

Arr_Support(0) = CLng(Rep_Year & Format(Rep_Month, "00")) 'LastMonth
Arr_Support(1) = CLng(Year(EndOfLastQuarter1D) & Format(Month(EndOfLastQuarter1D), "00"))
Arr_Support(2) = CLng(Year(EndOfLastQuarter2D) & Format(Month(EndOfLastQuarter2D), "00"))
Arr_Support(3) = CLng(Year(EndOfLastQuarter3D) & Format(Month(EndOfLastQuarter3D), "00"))

'Build up VisibleItems Array string and populate Array to control pivot table
For I = LBound(Arr_Support) To UBound(Arr_Support)
If I = UBound(Arr_Support) Then
Arr_LM3LQ(I) = Chr(34) & "[dim02_Tijd].[Alle Periode].[" & Arr_Support(I) & "]" & Chr(34)
ElseIf I < UBound(Arr_Support) Then
Arr_LM3LQ(I) = Chr(34) & "[dim02_Tijd].[Alle Periode].[" & Arr_Support(I) & "]" & Chr(34) & ", _"
End If
Next I

For A = LBound(Arr_LM3LQ) To UBound(Arr_LM3LQ)
CodedVisibleItemsList = CodedVisibleItemsList & Arr_LM3LQ(A) & vbCrLf
Next A

MsgBox CodedVisibleItemsList

ActiveSheet.PivotTables("PV_29_1_1").PivotFields("[dim02_Tijd].[PEIL_DT]"). _
VisibleItemsList = Array(Arr_LM3LQ())
' ========> Run-time error '1004': Application-Defined or object-defined error

' ActiveSheet.PivotTables(PivotTableID).PivotFields("[dim02_Tijd].[PEIL_DT]"). _
' VisibleItemsList = Array(Arr_LM3LQ())
' ========> Run-time error '1004': Application-Defined or object-defined error

' Recorded with macro and reformatted for presentation
' ActiveSheet.PivotTables("PV_29_1_1").PivotFields("[dim02_Tijd].[PEIL_DT]"). _
' VisibleItemsList = Array( _
' "[dim02_Tijd].[Alle Periode].[201512]", _
' "[dim02_Tijd].[Alle Periode].[201603]", _
' "[dim02_Tijd].[Alle Periode].[201606]", _
' "[dim02_Tijd].[Alle Periode].[201602]" _
' )
' ========> Works perfect!

On Error GoTo CheckError
MsgBox Err.Number

Exit Function
CheckError:
For Index = 1 To 500
Debug.Print Error$(Index)
Next Index

End Function

sassora
10-22-2016, 12:49 AM
It's been a while since I've been on this website. One comment I'd like to make is on the definitions as the start of the code.


Dim Reportingdate, EndOfLastQuarter1D, EndOfLastQuarter2D, EndOfLastQuarter3D As Date

Setting the variable type at the end of the list doesn't make each 'dimension' a Date type, only the final one. If you want each dimension to be a date to need to specify it each time. Otherwise the default is the Variant data type which is probably not what you want here.


Dim Reportingdate As Date, EndOfLastQuarter1D As Date, EndOfLastQuarter2D As Date, EndOfLastQuarter3D As Date

Similar approach needed for the next few lines of definitions in the code.

sassora
10-22-2016, 12:58 AM
I imagine the issue may have been causes by the Chr(34) which from the top of my head is a double quote. This shouldn't be needed in addition to the string you are creating. (for anyone with the same problem)

tripti1812
01-19-2017, 05:21 AM
I am facing similar issue. I am trying to use :

ActiveSheet.PivotTables("PivotTable1").PivotFields( _
"[Gross Sales].[Gross Sales].[Gross Sales]").VisibleItemsList = Array( _
filterListPro)

where : filterListPro is a string and has value : "[Gross Sales].[Gross Sales].&[2]", "[Gross Sales].[Gross Sales].&[3]"

But this throws the error 1004, however in recording it takes the same value and runs fine.




I imagine the issue may have been causes by the Chr(34) which from the top of my head is a double quote. This shouldn't be needed in addition to the string you are creating. (for anyone with the same problem)

sassora
01-21-2017, 04:03 AM
Can you post the pivot table with the recorded code?