Post reply

How to calculate 'ToDay' or 'CurrentDay' using MDX?

  • November 27, 2008 at 10:34 am

    #154959

    Hello there,

    How can we calculate the present day or today in MDX?

    I've created a report using analysis services cube,

    how i can set today's date as default parameter value.

    Or is there any other way to set the default parameter in the report?

    Regards,

    Ramu

    Ramu
    No Dream Is Too Big....!

  • November 27, 2008 at 11:58 am

    #904453

    You can set defaults in reporting services, but creating a "current date" calculated member and a named set is pretty useful.

    For our cubes, the "current date" needs to be yesterday's date. To accomplish this we have:

    [font="Courier New"]CREATE MEMBER CURRENTCUBE.[MEASURES].[Current As Of Day String]

    AS CStr( Month(vba![Date]())) + "/" + CStr( Day( vba![Date]())) + "/" + vba![Right]( CStr( Year ( vba![Date]())),2),

    VISIBLE = 0;

    CREATE SET CURRENTCUBE.[Current As Of Day Set]

    AS

    //Members("[As Of Date].[Date-Fiscal].[Dates].&[" + CStr( Year(vba![Date]())) + vba![Right]("0" + CStr( Month( vba![Date]())),2) + vba![Right]("0" + CStr( Day ( vba![Date]())),2) + "].PrevMember")

    Filter([As Of Date].[Date-Fiscal].[Dates],

    vba!Cstr([As Of Date].[Date-Fiscal].MemberValue) = Root([MEASURES].[Current As Of Day String] ) ).Item(0).PrevMember;

    CREATE MEMBER CURRENTCUBE.[As Of Date].[Date-Fiscal].[Current As Of Day]

    AS

    [Current As Of Day Set].Item(0)

    //CASE WHEN vba![DatePart]("w",vba![Date]()) = 2 THEN

    // StrToMember("[As Of Date].[Date-Fiscal].[Dates].&[" + CStr( Year(vba![Date]())) + vba![Right]("0" + CStr( Month( vba![Date]())),2) + vba![Right]("0" + CStr( Day ( vba![Date]())),2) + "].PrevMember.PrevMember")

    //ELSE

    // StrToMember("[As Of Date].[Date-Fiscal].[Dates].&[" + CStr( Year(vba![Date]())) + vba![Right]("0" + CStr( Month( vba![Date]())),2) + vba![Right]("0" + CStr( Day ( vba![Date]())),2) + "].PrevMember")

    //END

    ,

    VISIBLE = 1;[/font]

  • November 27, 2008 at 12:38 pm

    #904466

    Thanks a lot for your immediate response....!

    Can you explain me what the query do and which dimensions have you used( like .....[As Of Date].[Date-Fiscal].[Dates].&[.......)?

    Ramu
    No Dream Is Too Big....!

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

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