none
Creating Pivot Table Filters dynamically RRS feed

  • Question

  • Good Day,There is a workbook with numerous sheets all with the same pivot table filter structure. The filter that is required is for the periods (we have 13 periods in a year, 4 trimesters in a year (evidently) and the fourth trimester has 4 periods.)

    The Workbook is connected to an LDAP cube.

    My code for generating the array string is as follows:

                Tris = Replace(MaChaine, " ", "")         'remove spaces
                If InStr(Len(Tris), Tris, ",", vbTextCompare) > 0 Then      'remove last comma if is the last character
                    Tris = Mid(Tris, 1, Len(Tris) - 1)
                End If
                Buffer = Split(Tris, ",")
                Tris = Buffer


                'Trimestre
                For i = 0 To UBound(Tris)
                    MyString = MyString & Chr(34) & "[Fiscal Time].[Fiscal Time].[Fiscal Year].&[" & MyYear & "].&[" & Tris(i) & "]" & Chr(34) & Chr(44) & Chr(32) 'double quotes & comma & space
                Next

                MyString = Mid(MyString, 1, Len(MyString) - 2)      'remove last comma & space
                Debug.Print MyString

                For Each Feuille In ActiveWorkbook.Sheets
                    Feuille.Select
                    If ActiveSheet.PivotTables.Count > 0 Then

                        ActiveSheet.PivotTables(1).PivotFields( _
                            "[Fiscal Time].[Fiscal Time].[Fiscal Year]").VisibleItemsList = Array("")
                        ActiveSheet.PivotTables(1).PivotFields( _
                            "[Fiscal Time].[Fiscal Time].[Fiscal Quarter]").VisibleItemsList = Array(MyString)
                        ActiveSheet.PivotTables(1).PivotFields( _
                            "[Fiscal Time].[Fiscal Time].[Fiscal Period]").VisibleItemsList = Array("")
                        ActiveSheet.PivotTables(1).PivotFields( _
                            "[Fiscal Time].[Fiscal Time].[Fiscal Day]").VisibleItemsList = Array("")

                    End If

                Next

    '**********end of clippet***************

    The code bugs on the line with the Array(MyString) giving the error "Execution error '1004': error defined by the application or the object.

    However for just the year My code is :

            For Each Feuille In ActiveWorkbook.Sheets
                Feuille.Select
                If ActiveSheet.PivotTables.Count > 0 Then

                    'Année
                    ActiveSheet.PivotTables(1).PivotFields( _
                        "[Fiscal Time].[Fiscal Time].[Fiscal Year]").VisibleItemsList = Array( _
                        "[Fiscal Time].[Fiscal Time].[Fiscal Year].&[" & MyYear & "]")      'This line does not bug with the variable MyYear
                    ActiveSheet.PivotTables(1).PivotFields( _
                        "[Fiscal Time].[Fiscal Time].[Fiscal Quarter]").VisibleItemsList = Array("")
                    ActiveSheet.PivotTables(1).PivotFields( _
                        "[Fiscal Time].[Fiscal Time].[Fiscal Period]").VisibleItemsList = Array("")
                    ActiveSheet.PivotTables(1).PivotFields( _
                        "[Fiscal Time].[Fiscal Time].[Fiscal Day]").VisibleItemsList = Array("")
                End If
            Next

    The value for the variable MyString is :"[Fiscal Time].[Fiscal Time].[Fiscal Year].&[2016].&[1]", "[Fiscal Time].[Fiscal Time].[Fiscal Year].&[2016].&[2]"

    If I paste the string value the code works but it does not seem to accept the variable.

    Does anyone have any ideas  why and how to work around the problem. (I have the same problem with  the periods)

    Many thanks in advance

    Thursday, July 21, 2016 3:09 PM

Answers

  • Hi JayBrew,

    Do you want to set MyString for “Fiscal Year” or “Fiscal Quarter”? Based on your worked code, it seems you want to set for “Fiscal Year”, but in no-worked code, you set for “Fiscal Quarter”. Is it a mistake or what you want?

    If your issue is not related with this, I would suggest you try to create an Array, and pass it to VisibleItemsList instead of passing a variable string to Array().

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    • Marked as answer by JayBrew Tuesday, July 26, 2016 10:11 AM
    Friday, July 22, 2016 11:34 AM

All replies

  • Hi JayBrew,

    Do you want to set MyString for “Fiscal Year” or “Fiscal Quarter”? Based on your worked code, it seems you want to set for “Fiscal Year”, but in no-worked code, you set for “Fiscal Quarter”. Is it a mistake or what you want?

    If your issue is not related with this, I would suggest you try to create an Array, and pass it to VisibleItemsList instead of passing a variable string to Array().

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    • Marked as answer by JayBrew Tuesday, July 26, 2016 10:11 AM
    Friday, July 22, 2016 11:34 AM
  • Hello Edward,

    Fiscal year is always just one year and is set in the variable myyear but this is not relevant.

    I modified the code adding a few loops to count the iterations for redimensionning the arrays and then affecting the values in another loop.

    Works great. Many thanks Edward, your insight was precious.

    Monday, July 25, 2016 11:56 AM