ettedo
11-27-2017, 05:12 PM
Hi everyone :hi:
This is my first post on a tech support forum, so please bear with me :)
I've been tasked with automating my organisation's financial reports using VBA to manipulate OLAP pivot tables in Excel (2016, 64bit).
My design is based around userforms for manual selection of which report to run. The OLAP pivot table is then filtered based on the selected parameters in the userform.
The part that I'm having troubles with is filtering my table using a variable number of variable values...
The report I am currently trying to produce requires a filter on YTD (Year to Date), so there could be a possible 1-12 number of variable filters (e.g. 2 filters for the August report, 12 filters for the June report etc)
I've found some code on an old post in this forum which had the base logic for what I'm trying to do, but for some reason it produces an error for me [Run-time error '1004': Query (1, 39) Parser: The syntax for ',' is incorrect]. There appears to be absolutely no information on Google for this particular error in an Excel environment.
Below is my script that is run when the user clicks 'Submit' on the userform for selecting a report;
For Each PivotTable In Sheets("REPORT").PivotTables
PivotTable.RefreshTable
PivotTable.ClearTable
PivotTable.AddDataField PivotTable.CubeFields("[Measures].[Amount In Lc]")
PivotTable.AllowMultipleFilters = True
If ComboBox_Report.Value = "Income Statement" Then
With PivotTable.CubeFields("[Time Hierarchy].[Period]")
.Orientation = xlRowField
.Position = 1
End With
'----THIS IS THE SECTION THAT APPLIES THE VARIABLE FILTERS----
Dim PivotStr As String, cMth As Long, fMth As Long, vPer As Long, fVar As Long
cMth = Month(Now) - 1
fMth = Application.WorksheetFunction.VLookup(cMth, Sheets("REFERENCE").range("A2:G13"), 2, False)
fVar = Application.WorksheetFunction.VLookup(cMth, Sheets("REFERENCE").range("A2:G13"), 2, False)
vPer = Application.WorksheetFunction.VLookup(fVar, Sheets("REFERENCE").range("B2:E13"), 4, False)
PivotStr = """[Time Hierarchy].[Period].&[" & (vPer) & "]"""
Do While fVar <= fMth And fVar > 1
fVar = fVar - 1
vPer = Application.WorksheetFunction.VLookup(fVar, Sheets("REFERENCE").range("B2:E13"), 4, False)
PivotStr = PivotStr & ", " & """[Time Hierarchy].[Period].&[" & (vPer) & "]"""
Loop
'----THIS PRODUCES THE BELOW VALUE FOR VARIABLE 'PivotStr', WHICH BTW WORKS WHEN I RUN IT MANUALLY AS TEXT
'---- "[Time Hierarchy].[Period].&[201707]","[Time Hierarchy].[Period].&[201708]","[Time Hierarchy].[Period].&[201709]","[Time Hierarchy].[Period].&[201710]")
msgbox1 = MsgBox(PivotStr)
'----THIS IS THE LINE THAT GIVES ME ERROR: (The syntax for ',' is incorrect)
PivotTable.PivotFields("[Time Hierarchy].[Period].[Period]").VisibleItemsList = Array(PivotStr)
End If
Next PivotTable
When I record a macro to perform the above filters it produces the below code which is identical to what I'm entering using the PivotStr variable :banghead:
ActiveSheet.PivotTables("PivotTable5").PivotFields( _
"[Time Hierarchy].[Period].[Period]").VisibleItemsList = Array( _
"[Time Hierarchy].[Period].&[201707]", "[Time Hierarchy].[Period].&[201708]", _
"[Time Hierarchy].[Period].&[201709]", "[Time Hierarchy].[Period].&[201710]")
I know this could probably be achieved using a 'Select Case' and typing out every possible scenario, but this is clunky and inefficient
Hope someone can point me in the right direction :)
Thanks in advance for anyone's assistance!
This is my first post on a tech support forum, so please bear with me :)
I've been tasked with automating my organisation's financial reports using VBA to manipulate OLAP pivot tables in Excel (2016, 64bit).
My design is based around userforms for manual selection of which report to run. The OLAP pivot table is then filtered based on the selected parameters in the userform.
The part that I'm having troubles with is filtering my table using a variable number of variable values...
The report I am currently trying to produce requires a filter on YTD (Year to Date), so there could be a possible 1-12 number of variable filters (e.g. 2 filters for the August report, 12 filters for the June report etc)
I've found some code on an old post in this forum which had the base logic for what I'm trying to do, but for some reason it produces an error for me [Run-time error '1004': Query (1, 39) Parser: The syntax for ',' is incorrect]. There appears to be absolutely no information on Google for this particular error in an Excel environment.
Below is my script that is run when the user clicks 'Submit' on the userform for selecting a report;
For Each PivotTable In Sheets("REPORT").PivotTables
PivotTable.RefreshTable
PivotTable.ClearTable
PivotTable.AddDataField PivotTable.CubeFields("[Measures].[Amount In Lc]")
PivotTable.AllowMultipleFilters = True
If ComboBox_Report.Value = "Income Statement" Then
With PivotTable.CubeFields("[Time Hierarchy].[Period]")
.Orientation = xlRowField
.Position = 1
End With
'----THIS IS THE SECTION THAT APPLIES THE VARIABLE FILTERS----
Dim PivotStr As String, cMth As Long, fMth As Long, vPer As Long, fVar As Long
cMth = Month(Now) - 1
fMth = Application.WorksheetFunction.VLookup(cMth, Sheets("REFERENCE").range("A2:G13"), 2, False)
fVar = Application.WorksheetFunction.VLookup(cMth, Sheets("REFERENCE").range("A2:G13"), 2, False)
vPer = Application.WorksheetFunction.VLookup(fVar, Sheets("REFERENCE").range("B2:E13"), 4, False)
PivotStr = """[Time Hierarchy].[Period].&[" & (vPer) & "]"""
Do While fVar <= fMth And fVar > 1
fVar = fVar - 1
vPer = Application.WorksheetFunction.VLookup(fVar, Sheets("REFERENCE").range("B2:E13"), 4, False)
PivotStr = PivotStr & ", " & """[Time Hierarchy].[Period].&[" & (vPer) & "]"""
Loop
'----THIS PRODUCES THE BELOW VALUE FOR VARIABLE 'PivotStr', WHICH BTW WORKS WHEN I RUN IT MANUALLY AS TEXT
'---- "[Time Hierarchy].[Period].&[201707]","[Time Hierarchy].[Period].&[201708]","[Time Hierarchy].[Period].&[201709]","[Time Hierarchy].[Period].&[201710]")
msgbox1 = MsgBox(PivotStr)
'----THIS IS THE LINE THAT GIVES ME ERROR: (The syntax for ',' is incorrect)
PivotTable.PivotFields("[Time Hierarchy].[Period].[Period]").VisibleItemsList = Array(PivotStr)
End If
Next PivotTable
When I record a macro to perform the above filters it produces the below code which is identical to what I'm entering using the PivotStr variable :banghead:
ActiveSheet.PivotTables("PivotTable5").PivotFields( _
"[Time Hierarchy].[Period].[Period]").VisibleItemsList = Array( _
"[Time Hierarchy].[Period].&[201707]", "[Time Hierarchy].[Period].&[201708]", _
"[Time Hierarchy].[Period].&[201709]", "[Time Hierarchy].[Period].&[201710]")
I know this could probably be achieved using a 'Select Case' and typing out every possible scenario, but this is clunky and inefficient
Hope someone can point me in the right direction :)
Thanks in advance for anyone's assistance!