18 Replies Latest reply: May 2, 2011 3:08 PM by Toni Kautto RSS

    Set analysis depending of a selected field in a tree view list box


      on a my report I have created a tree view list box to show year and month dimensions. For it, I have created a specific year_month field by joining year and month. In this way, an user can select year = 2010 + month = 9 and year = 2011 + month = 3 and so on.

      Then I have created two pivot chart, the first one with some measures with yeartomonth data for each month, and the second one with monthly measures. In other terms, in the firts pivot chart if I show measures related to year = 2011 and month = 3 it is as I show the measure values between january and march 2001, while measures in the second pivot are really monthly amounts. My source db is made in this manner.

      So, when I make one or more selections on the tree view list box in the second pivot chart I must show the measures respect to the selected year and with month minus or equals to the selected month. I need to find the right set analysis expression. It must be similar to this:

      sum({$<Month = {"<=$(varSelectedMonth)"}>} MeasureAmount)

      but I add the part related to the year.

      This expression must function when I select year = 2010 + month = 10 and year = 2011 + month = 3. For these selections, I must show 2010-01, 2010-02, 2010-03, 2010-04, 2010-05, 2010-06, 2010-07, 2010-08, 2010-09, 2010-10 and 2011-01, 2011-02, 2011-03.

      Moreover, I have the problem to extract years and months selected from the tree view list box and put the right values on a variable to pass to the set analysis expression. I can select year = 2010 + month = 8 and year = 2011 + month = 3 and year = 2011 + month = 5 and so on; in other terms, I can made a multiple year + month selection.

      Any suggests to me, please? Thanks