Flatten members from different hierarchies in an analytic grid RRS feed

  • Question

  • I have this query that gets the current day and the current month,



    StrToMember("[Date].[Calendar].[Date].&[" + Format(vba!dateadd("yyyy", -4,now()), "yyyyMMdd")+ "]")
    ,StrToMember("[Date].[Calendar].[Date].&[" + Format(vba!dateadd("yyyy", -4,now()), "yyyyMMdd")+ "].parent")


     on 0,

    [Measures].[Internet Freight Cost] on 1
    from [Adventure Works]


    But I want the two dates to appear like this:


    Measure                              April 22, 2007            April 2007

    Internet Freight Cost            211.34                      12,659.99


    As opposed to this as it currently does:

                                                                              April 2007

    Measures                             April 22, 2007

    Internet Freight Cost             $211.34                   $12,659.99


    I don't want the dates to appear on multiple lines in the column header.  Is this possible?

    Tim Toennies
    Saturday, April 23, 2011 4:50 AM


All replies

  • You can get the results if you place the date members on the rows and you can select the flattened fiew layout as well.  You could look at using reporting services if you want it to display like that on the columns. 
    Dan English's BI Blog
    Sunday, April 24, 2011 1:24 AM
  • That is exactly what I want to see but unfortunately the requirement is that the dates have to appear on the columns.  No possible way to make that happen in PPS?

    Tim Toennies
    Sunday, April 24, 2011 3:40 AM
  • The way that I would do this would be to setup what is known as a shell dimension to so the data for the date selected in column 1 (current) and then in column 2 it would show the month value (aggregate value for current month).  The thing that you would be missing would be the actualy member label names on the columns.

    If you are not familiar with shell dimensions take a look at this information here http://www.obs3.com/pdf/A%20Different%20Approach%20to%20Time%20Calculations%20in%20SSAS.pdf and you can also check out this example here as well http://www.sqlbi.eu/DateTool.aspx.

    Dan English's BI Blog
    Tuesday, April 26, 2011 10:30 AM