locked
Connection formula RRS feed

  • Question

  • I have a filter that gathers its data from [Fiscal Year].[Fiscal Year].  Display values are FY 2009, FY 2010, etc.  Actual values are in the format of [Fiscal Year].[Fiscal Year].&[2009].

    We are using this filter for two different reports on the dashboard.  Report1 uses the [Fiscal Year] dimension, but Report2 uses another dimension for the same type of values:  [Assessment Date].[Assessment Fiscal Year].&[2009]  What does the connection formula for Report2 need to look like so that I can use the same filter for both reports?

    Monday, March 12, 2012 7:12 PM

Answers

  • Maybe there is an easier approach that I can't think of. But this should do fine:

    strtoset('[Assessment Date].[Assessment Fiscal Year].&[' + RIGHT(<<SourceValue>>.Uniquename,5))

    So what it does is takes in the Fiscal Year dimension's selected member, gets the the last part which is always going to be 4 digit year number. Appends and converts to [Assessment Date] set.


    http://dailyitsolutions.blogspot.com/

    • Marked as answer by dbwrangler Tuesday, March 13, 2012 4:45 PM
    Monday, March 12, 2012 8:04 PM

All replies

  • Maybe there is an easier approach that I can't think of. But this should do fine:

    strtoset('[Assessment Date].[Assessment Fiscal Year].&[' + RIGHT(<<SourceValue>>.Uniquename,5))

    So what it does is takes in the Fiscal Year dimension's selected member, gets the the last part which is always going to be 4 digit year number. Appends and converts to [Assessment Date] set.


    http://dailyitsolutions.blogspot.com/

    • Marked as answer by dbwrangler Tuesday, March 13, 2012 4:45 PM
    Monday, March 12, 2012 8:04 PM
  • That was perfect!!  I had the strtoset('[Assessment Date].[Assessment Fiscal Year].&[' + RIGHT(<<SourceValue>>, 5)), but didn't have the Uniquename in there.  Thank you soooo much.

    Any suggestions on books for PPS?  There isn't much in way of BOL or help for filter connection formulas.  I'd like to get a more in depth knowledge of working with these things.

    Thanks again!!!

    Monday, March 12, 2012 8:53 PM