VBA: OLAP Issue with multiple selections

L

laxcat73

Board Regular
Joined
Aug 5, 2011
Messages
143
Hi - bit of background.

I'm trying to set up an OLAP cube via VBA to update 2 tables - one is for the current month, and the other is for all history UP THROUGH last month. For example, if the selection is March 2015, I want one to pull everything in March, and the other to pull everything through February. The code works fine to update the current month, but since there are no multiple selections, it works fine. Something in generating the multiple selection string is causing the issue.

Warning: there are multiple named ranges in a worksheet to populate these variables.

Code: 
Sub UpdateStatusMonth()


Dim i As Integer
Dim rngYear As String
Dim rngQtr As String
Dim rngMonth As String
Dim rngLastMonth As String
Dim rngYearStart As String
Dim intLastMonth As String
Dim intLastMonthEnd As String
Dim intLastMonthStart As String
Dim aryYear As String
Dim aryQtr As String
Dim aryMonth As String
Dim aryTest As String


rngYear = Application.VLookup(Range("rngCubeFilterYear"), Range("rngCubeFilter"), 4, False)
rngQtr = Application.VLookup(Range("rngCubeFilterMonth"), Range("rngCubeFilter"), 3, False)
rngMonth = Range("rngCubeFilterMonth")


aryTest = "[Status].[Status Date - Hierarchy].[Status Date - Mon].&[" & rngYear & "]&[" & rngQtr & "]&[" & rngMonth & "]"


With Worksheets("StatusCurrent").PivotTables("pvtStatus")
    .PivotFields("[Status].[Status Date - Hierarchy].[Status Date - Year]").VisibleItemsList = Array("")
    .PivotFields("[Status].[Status Date - Hierarchy].[Status Date - Qtr]").VisibleItemsList = Array("")
    .PivotFields("[Status].[Status Date - Hierarchy].[Status Date - Day]").VisibleItemsList = Array("")
    .PivotFields("[Status].[Status Date - Hierarchy].[Status Date - Mon]").VisibleItemsList = _
        Array(aryTest)
        '"[Status].[Status Date - Hierarchy].[Status Date - Mon].&[" & rngYear & "]&[" & rngQtr & "]&[" & rngMonth & "]"
End With


Worksheets("Annotation").Range("R1").Value = aryTest


rngYear = Application.VLookup(Range("rngCubeFilterYear"), Range("rngCubeFilter"), 4, False)
rngQtr = Application.VLookup(Range("rngCubeFilterMonth"), Range("rngCubeFilter"), 3, False)
rngMonth = Application.VLookup(Range("rngCubeFilterMonth"), Range("rngCubeFilter"), 2, False)


rngLastMonth = rngYear & rngQtr & rngMonth
intLastMonthEnd = Application.Index(Range("rngMonthID"), Application.Match(rngLastMonth, Range("rngMonthIDLong"), 0)) - 1


'Create Year array
rngYear = ""
rngYear = Application.VLookup(intLastMonthEnd / 1, Range("rngMonthIndex"), 2, False)
For i = 0 To rngYear - 2012 - 1
    If aryYear = "" Then
        aryYear = "[Status].[Status Date - Hierarchy].[Status Date - Year].&[" & 2012 + i & "]"
    Else
        aryYear = aryYear & ", " & "[Status].[Status Date - Hierarchy].[Status Date - Year].&[" & 2012 + i & "]"
    End If
Next i


'Create Month array - also fills quarter


intLastMonthStart = Application.Index(Range("rngMonthID"), Application.Match(rngYear / 1, Range("rngFullYear"), 0))
aryMonth = ""


For i = intLastMonthStart To intLastMonthEnd
    If Range("rngCubeFilterMonth").Value = 1 Then GoTo Skip_i
        rngMonth = Application.VLookup(i, Range("rngMonthIndex"), 4, False)
        rngQtr = Application.VLookup(rngMonth / 1, Range("rngMonthIndex"), 3, False)
        If aryMonth = "" Then
            aryMonth = "[Status].[Status Date - Hierarchy].[Status Date - Mon].&[" & rngYear & "]&[" & rngQtr & "]&[" & rngMonth & "]"
        Else
            aryMonth = aryMonth & ", " & "[Status].[Status Date - Hierarchy].[Status Date - Mon].&[" & rngYear & "]&[" & rngQtr & "]&[" & rngMonth & "]"
        End If
    'End If
Next i


Skip_i:


Worksheets("Annotation").Range("R4").Value = aryYear
Worksheets("Annotation").Range("R3").Value = aryMonth


With Worksheets("StatusHistory").PivotTables("pvtStatus")
[B][COLOR=#ff0000]    .PivotFields("[Status].[Status Date - Hierarchy].[Status Date - Year]").VisibleItemsList = _[/COLOR][/B]
[B][COLOR=#ff0000]        Array(aryYear)[/COLOR][/B]
    .PivotFields("[Status].[Status Date - Hierarchy].[Status Date - Qtr]").VisibleItemsList = _
        Array("")
        If aryMonth = "" Then
            .PivotFields("[Status].[Status Date - Hierarchy].[Status Date - Mon]").VisibleItemsList _
            = Array("")
        Else: .PivotFields("[Status].[Status Date - Hierarchy].[Status Date - Mon]").VisibleItemsList _
            = Array(aryMonth)
        End If


    .PivotFields("[Status].[Status Date - Hierarchy].[Status Date - Day]").VisibleItemsList = Array("")
End With


End Sub

The issue is here: .PivotFields("[Status].[Status Date - Hierarchy].[Status Date - Year]").VisibleItemsList = Array(aryYear)
The error is Run-time 1004 - Application-defined or object-defined error.

Not sure how to fix this. Help is appreciated.
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Sort by date Sort by votes
Hi laxcat73, With an OLAP source, you can pass multiple items through the VisibleItemsList property. Your code isn't working because of the syntax you are using in trying to build an array through the use of a comma-delimited string.

This recent code example shows how to size a dynamic array using redim, populate its elements, then assign that array to the VisibleItemsList property.

http://www.mrexcel.com/forum/excel-questions/851622-i-need-urgent-excel-help.html#post4143870

Just ask if you want some help adapting that.
 
Upvote 0
Hi Jerry -

Thank you for the info. I tried to implement myself but was unable to get it to work properly. With the modifications, it now runs, but sets the filter to "All" instead of the array items.

Code: 
Sub UpdateStatusMonth()

Dim lNdx As Long
Dim aryMonthLong As Variant
Dim i As Integer


Dim rngYear As String
Dim rngQtr As String
Dim rngMonth As String
Dim rngLastMonth As String
Dim rngYearStart As String


Dim intLastMonth As String
Dim intLastMonthEnd As String
Dim intLastMonthStart As String


Dim aryYear As String
Dim aryQtr As String
Dim aryMonth As String
Dim aryCurr As String


rngYear = Application.VLookup(Range("rngCubeFilterYear"), Range("rngCubeFilter"), 4, False)
rngQtr = Application.VLookup(Range("rngCubeFilterMonth"), Range("rngCubeFilter"), 3, False)
rngMonth = Range("rngCubeFilterMonth")


aryCurr = "[Status].[Status Date - Hierarchy].[Status Date - Mon].&[" & rngYear & "]&[" & rngQtr & "]&[" & rngMonth & "]"


With Worksheets("StatusCurrent").PivotTables("pvtStatus")
    .PivotFields("[Status].[Status Date - Hierarchy].[Status Date - Year]").VisibleItemsList = Array("")
    .PivotFields("[Status].[Status Date - Hierarchy].[Status Date - Qtr]").VisibleItemsList = Array("")
    .PivotFields("[Status].[Status Date - Hierarchy].[Status Date - Day]").VisibleItemsList = Array("")
    .PivotFields("[Status].[Status Date - Hierarchy].[Status Date - Mon]").VisibleItemsList = _
        Array(aryCurr)
End With


Worksheets("Annotation").Range("R1").Value = aryCurr


rngYear = Application.VLookup(Range("rngCubeFilterYear"), Range("rngCubeFilter"), 4, False)
rngQtr = Application.VLookup(Range("rngCubeFilterMonth"), Range("rngCubeFilter"), 3, False)
rngMonth = Application.VLookup(Range("rngCubeFilterMonth"), Range("rngCubeFilter"), 2, False)


rngLastMonth = rngYear & rngQtr & rngMonth
intLastMonthEnd = Application.Index(Range("rngMonthID"), Application.Match(rngLastMonth, Range("rngMonthIDLong"), 0)) - 1


intLastMonthStart = Application.Index(Range("rngMonthID"), Application.Match(rngYear / 1, Range("rngFullYear"), 0))
aryMonth = ""


ReDim aryMonthLong(intLastMonthStart To intLastMonthEnd)
lNdx = intLastMonthStart / 1 - 1
    If Range("rngCubeFilterMonth").Value = 1 Then GoTo Skip_i
        Do While lNdx = intLastMonthEnd - 1
            lNdx = lNdx + 1
            rngMonth = Application.VLookup(lNdx, Range("rngMonthIndex"), 4, False)
            rngQtr = Application.VLookup(rngMonth / 1, Range("rngMonthIndex"), 3, False)
            aryMonthLong(lNdx) = "[Status].[Status Date - Hierarchy].[Status Date - Mon].&[" & rngYear & "]&[" & rngQtr & "]&[" & rngMonth & "]"
        Loop


Skip_i:


Worksheets("Annotation").Range("R4").Value = aryYear
Worksheets("Annotation").Range("R3").Value = aryMonthLong


With Worksheets("StatusHistory").PivotTables("pvtStatus")
        If aryMonth = "" Then
              .PivotFields("[Status].[Status Date - Hierarchy].[Status Date - Mon]").VisibleItemsList = Array("")
        Else: .PivotFields("[Status].[Status Date - Hierarchy].[Status Date - Mon]").VisibleItemsList = aryMonthLong
        End If
End With
End Sub
 
Last edited:
Upvote 0
Ah got it!! Was an issue that the lNdx wasn't working with the other number which was stored as a string.. thank you for the help!
 
Upvote 0
Hi Jerry -

Might need your help after all. Here is where I got to, but it is not only applying the year to my filters, and is excluding the months.

EDIT: I'm an idiot. I forgot I had reused the "rngYear" to populate the year array. Works perfectly now, thank you again!!

Below is final code for future reference.

Code: 
Sub UpdateStatusMonth()

Dim lStartYear As Long
lStartYear = 2012




Dim lNdx As Long
Dim lintLastMonthEnd As Long
Dim lintYear As Long
Dim aryMonthLong As Variant
Dim aryYearLong As Variant
Dim i As Integer


Dim rngYear As String
Dim rngQtr As String
Dim rngMonth As String
Dim rngLastMonth As String
Dim rngYearStart As String


Dim intLastMonth As String
Dim intLastMonthEnd As String
Dim intLastMonthStart As String


Dim aryYear As String
Dim aryQtr As String
Dim aryMonth As String
Dim aryCurr As String


rngYear = Application.VLookup(Range("rngCubeFilterYear"), Range("rngCubeFilter"), 4, False)
rngQtr = Application.VLookup(Range("rngCubeFilterMonth"), Range("rngCubeFilter"), 3, False)
rngMonth = Range("rngCubeFilterMonth")


aryCurr = "[Status].[Status Date - Hierarchy].[Status Date - Mon].&[" & rngYear & "]&[" & rngQtr & "]&[" & rngMonth & "]"


With Worksheets("StatusCurrent").PivotTables("pvtStatus")
    .PivotFields("[Status].[Status Date - Hierarchy].[Status Date - Mon]").VisibleItemsList = _
        Array(aryCurr)
End With


rngYear = Application.VLookup(Range("rngCubeFilterYear"), Range("rngCubeFilter"), 4, False)
rngQtr = Application.VLookup(Range("rngCubeFilterMonth"), Range("rngCubeFilter"), 3, False)
rngMonth = Application.VLookup(Range("rngCubeFilterMonth"), Range("rngCubeFilter"), 2, False)


rngLastMonth = rngYear & rngQtr & rngMonth
intLastMonthEnd = Application.Index(Range("rngMonthID"), Application.Match(rngLastMonth, Range("rngMonthIDLong"), 0)) - 1


intLastMonthStart = Application.Index(Range("rngMonthID"), Application.Match(rngYear / 1, Range("rngFullYear"), 0))
aryMonth = ""
lintLastMonthEnd = intLastMonthEnd / 1
lintLastMonthStart = intLastMonthStart / 1
lintYear = (rngYear / 1) - 1


ReDim aryYearLong(lStartYear To lintYear)
lNdx = lStartYear
        Do While lNdx <= lintYear
            rngYear = lNdx
            aryYearLong(lNdx) = "[Status].[Status Date - Hierarchy].[Status Date - Year].&[" & rngYear & "]"
            lNdx = lNdx + 1
        Loop


rngYear = Application.VLookup(Range("rngCubeFilterYear"), Range("rngCubeFilter"), 4, False)


ReDim aryMonthLong(intLastMonthStart To intLastMonthEnd)
lNdx = lintLastMonthStart - 1
    If Range("rngCubeFilterMonth").Value = 1 Then GoTo Skip_i
        Do While lNdx <= lintLastMonthEnd - 1
            lNdx = lNdx + 1
            rngMonth = Application.VLookup(lNdx, Range("rngMonthIndex"), 4, False)
            rngQtr = Application.VLookup(rngMonth / 1, Range("rngMonthIndex"), 3, False)
            aryMonthLong(lNdx) = "[Status].[Status Date - Hierarchy].[Status Date - Mon].&[" & rngYear & "]&[" & rngQtr & "]&[" & rngMonth & "]"
        Loop


Skip_i:


With Worksheets("StatusHistory").PivotTables("pvtStatus")
        .PivotFields("[Status].[Status Date - Hierarchy].[Status Date - Year]").VisibleItemsList = aryYearLong
        .PivotFields("[Status].[Status Date - Hierarchy].[Status Date - Mon]").VisibleItemsList = aryMonthLong
End With
End Sub
 
Last edited:
Upvote 0
Here is final code... can't edit previous post.

Code: 
Sub UpdateStatusMonth()

Dim lStartYear As Long
lStartYear = 2012


Dim lNdx As Long
Dim lintLastMonthEnd As Long
Dim lintYear As Long
Dim aryMonthLong As Variant
Dim aryYearLong As Variant


Dim rngYear As String
Dim rngQtr As String
Dim rngMonth As String
Dim rngLastMonth As String
Dim rngYearStart As String


Dim intLastMonth As String
Dim intLastMonthEnd As String
Dim intLastMonthStart As String


Dim aryYear As String
Dim aryQtr As String
Dim aryMonth As String
Dim aryCurr As String


rngYear = Application.VLookup(Range("rngCubeFilterYear"), Range("rngCubeFilter"), 4, False)
rngQtr = Application.VLookup(Range("rngCubeFilterMonth"), Range("rngCubeFilter"), 3, False)
rngMonth = Range("rngCubeFilterMonth")


aryCurr = "[Status].[Status Date - Hierarchy].[Status Date - Mon].&[" & rngYear & "]&[" & rngQtr & "]&[" & rngMonth & "]"


With Worksheets("StatusCurrent").PivotTables("pvtStatus")
    .PivotFields("[Status].[Status Date - Hierarchy].[Status Date - Mon]").VisibleItemsList = _
        Array(aryCurr)
End With


rngYear = Application.VLookup(Range("rngCubeFilterYear"), Range("rngCubeFilter"), 4, False)
rngQtr = Application.VLookup(Range("rngCubeFilterMonth"), Range("rngCubeFilter"), 3, False)
rngMonth = Application.VLookup(Range("rngCubeFilterMonth"), Range("rngCubeFilter"), 2, False)


rngLastMonth = rngYear & rngQtr & rngMonth
intLastMonthEnd = Application.Index(Range("rngMonthID"), Application.Match(rngLastMonth, Range("rngMonthIDLong"), 0)) - 1


intLastMonthStart = Application.Index(Range("rngMonthID"), Application.Match(rngYear / 1, Range("rngFullYear"), 0))
aryMonth = ""
lintLastMonthEnd = intLastMonthEnd / 1
lintLastMonthStart = intLastMonthStart / 1
lintYear = (rngYear / 1) - 1


ReDim aryYearLong(lStartYear To lintYear)
lNdx = lStartYear
        Do While lNdx <= lintYear
            rngYear = lNdx
            aryYearLong(lNdx) = "[Status].[Status Date - Hierarchy].[Status Date - Year].&[" & rngYear & "]"
            lNdx = lNdx + 1
        Loop


rngYear = Application.VLookup(Range("rngCubeFilterYear"), Range("rngCubeFilter"), 4, False)


If lintLastMonthStart > lintLastMonthEnd Then GoTo Skip_Month
ReDim aryMonthLong(intLastMonthStart To intLastMonthEnd)
lNdx = lintLastMonthStart - 1
        Do While lNdx <= lintLastMonthEnd - 1
            lNdx = lNdx + 1
            rngMonth = Application.VLookup(lNdx, Range("rngMonthIndex"), 4, False)
            rngQtr = Application.VLookup(rngMonth / 1, Range("rngMonthIndex"), 3, False)
            aryMonthLong(lNdx) = "[Status].[Status Date - Hierarchy].[Status Date - Mon].&[" & rngYear & "]&[" & rngQtr & "]&[" & rngMonth & "]"
        Loop


With Worksheets("StatusHistory").PivotTables("pvtStatus")
        .PivotFields("[Status].[Status Date - Hierarchy].[Status Date - Year]").VisibleItemsList = aryYearLong
        .PivotFields("[Status].[Status Date - Hierarchy].[Status Date - Mon]").VisibleItemsList = aryMonthLong
End With


GoTo End_Sub


Skip_Month:


With Worksheets("StatusHistory").PivotTables("pvtStatus")
        .PivotFields("[Status].[Status Date - Hierarchy].[Status Date - Year]").VisibleItemsList = aryYearLong
End With
End_Sub:


Run "SortStatus"
Run "HeatmapFormat"


End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

G
  • Question Question
(VBA) OLAP query with multiple selection
Replies
0
Views
744
Goan
G
N
  • Question Question
VBA Help for PivotTable Filter Update
Replies
0
Views
483
nsfekeci
N
E
  • Question Question
Olap filter on dates using VBA and custom Date filter
Replies
0
Views
3K
EhhMikey
E
J
  • Question Question
VBA & OLAP Pivot: VBA Code to set report filter pivot field with multiple items from another pivot table report filter
Replies
0
Views
2K
juliemozz
J
J
  • Question Question
Selecting String/Value range in an OLAP slicer using macro
Replies
3
Views
2K
Jerry Sullivan
Jerry Sullivan

Forum statistics

Threads
1,223,234
Messages
6,170,891
Members
452,366
Latest member
TePunaBloke

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top