Post reply

Unable to filter the data for multiple time selections by dimensions

  • March 19, 2014 at 12:29 am

    #305431

    Hi to all,

    I am new in MDX, i have a problem with my MDX query.

    Calculated Member Logic:

    SUM((OPENINGPERIOD([Date].[YQMD].[Year],[Date].[YQMD].[Month].&[2010-12-01T00:00:00]):[Date].[YQMD].Currentmember),[Measures].[Paid Amt])

    Mdx Logic EX:

    With Member [MEASURES].[Received_Amount]

    AS

    SUM((OPENINGPERIOD([Date].[YQMD].[Year],[Date].[YQMD].[Month].&[2010-12-01T00:00:00]):[Date].[YQMD].Currentmember)

    ,[Measures].[Paid Amt])

    SELECT {[MEASURES].[Received_Amount]} On Columns

    ,[Date].[YQMD].[Year].members On Rows

    From [Financial]

    If i select multiple time periods in Rows, the query working fine.

    but if select multiple periods in where clause it is not responding.

    With Member [MEASURES].[Received_Amount]

    AS

    SUM((OPENINGPERIOD([Date].[YQMD].[Year],[Date].[YQMD].[Month].&[2010-12-01T00:00:00]):[Date].[YQMD].Currentmember)

    ,[Measures].[Paid Amt])

    SELECT {[MEASURES].[Received_Amount]} On Columns

    ,[Speciality].[Specialty Name].[Specialty Name].members On Rows

    From [Financial]

    Where {[Date].[YQMD].[Year].&[2012-01-01T00:00:00],[Date].[YQMD].[Year].&[2013-01-01T00:00:00]}

    Note:

    Each of them is considered from the minimum date in the database to the selected time.

    And also the data has to be filtered with respect to each drill down dimension.

    If select multiple time periods the same formula has to be applied with respect to the dimensions.

    Kindly help me to get out of this problem

    Best Regards,

    Nagendra

  • March 20, 2014 at 9:59 am

    #1698766

    My laptop is not showing the screen print so I am taking a blind stab here, but consider this:

    1. The WHERE clause in MDX is evlauted before the axes.

    Furthermore, the contents of a WHERE clause are evaluted as one unit (SUBCUBE)...not as two separate

    members. Whereas, if you put that same set on an AXIS those members will display separately.

    2. A set or tuple in a where clause is the equivalent of having the EXISTS function on both axes.

    3. The currentmember function returns a member. So, in the case where your WHERE clause contains more than one member from an attribute that the hierarchy corresponds to, the CurrentMember function will return an error.

    EDIT: Basically what I am saying is, "Don't use the CurrentMember function if your where clause contains more than one member from the same dimension. Otherwise, you will get an error.":-)

    In business terms, what are you trying to accomplish?

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply