Filtering OLAP Pivots by Date Variables/Ranges Please help

  • Hi All,


    I have an OLAP pivot table that I'm having trouble with. Basically I want to filter the pivot by a particular date by a defined range or variable.


    I recorded a macro and changed the code to simplify it. So far this works.


    Code
    pf.VisibleItemsList = Array("[Transaction Date].[By Transaction Date].[Transaction Month].&[2016]&[3]")


    This filters the pivot by 2016 and month 3 (March).


    My attempt to put my variables have failed as per below:


    Code
    pf.VisibleItemsList = Array("[Transaction Date].[By Transaction Date].[Transaction Month].&[theyear]&[themonth]")


    I get the error "The item could not be found in the OLAP cube"


    My entire code as follows:




    I know I'm not far with the syntex. Can anyone help please?

  • Re: Filtering OLAP Pivots by Date Variables/Ranges Please help


    I figured it out thanks to some googling. The syntex here is complicated and annoying. But this now works


    Code
    pf.VisibleItemsList = Array("[Transaction Date].[By Transaction Date].[Transaction Month].&[" & theyear & "]&[" & themonth & "]")

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!