Post reply

Creating Reports in MDX Query

  • December 7, 2009 at 6:40 am

    #217407

    Hi to Everybody,

    I'm creating a report via CUBE i created the mdx query but i'm bit struggling in how to use the COUNT function in MDX query. whether it is necessary to use GROUP BY function when using aggregate functions .for your reference i pasted the MDX query below.

    i have used the count function in the query but it shows som error message. please anybody help me to resolve this issues.

    SELECT NON EMPTY { [Measures].[Quantity On Hand], [Measures].[Value On Hand] } ON COLUMNS, NON EMPTY { ([L Department].[L Department Name].[L Department Name].ALLMEMBERS * COUNT([Dim Product Hierarchy].[Current Product Code].[Current Product Code].ALLMEMBERS )) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( { [L Chain].[L Chain Code].&[Barnetts] } ) ON COLUMNS FROM ( SELECT ( { [Dim Fact Stock On Hand].[Is SOH More Than Zero].&[True] } ) ON COLUMNS FROM [CUBE_JDGJEDataMart])) WHERE ( [Dim Fact Stock On Hand].[Is SOH More Than Zero].&[True], [L Chain].[L Chain Code].&[Barnetts] ) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

    Thanks in Advance,

    Sabarinathan.C

  • December 7, 2009 at 12:14 pm

    #1088801

    Hi

    MDX equivalent of a filtered GROUP BY in SQL. see the link below:

    http://geekswithblogs.net/darrengosbell/archive/2009/01/28/mdx-equivalent-of-a-filtered-group-by-in-sql.aspx

  • December 7, 2009 at 12:33 pm

    #1088821

    Hi

    Here it comes, a quick intro into the MDX language

    /*

    LETS DEFINE THE MEMBER simple count so default INCLUDEEMTPY. I do presume you need the count for every row.. (Context) then we use EXISTING.. Otherwise the count result in the all members of the set for every row.

    */

    WITH MEMBER [Measures].[ProductCode count] AS

    ' COUNT(

    EXISTING [Dim Product Hierarchy].[Current Product Code].[Current Product Code].MEMBERS

    )

    '

    /* FORMAL MDX SYNTAX MEMBER definition is enclosed in '' */

    SELECT NON EMPTY { [Measures].[ProductCode count], [Measures].[Quantity On Hand], [Measures].[Value On Hand] } ON COLUMNS, NON EMPTY { ([L Department].[L Department Name].[L Department Name].ALLMEMBERS } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( { [L Chain].[L Chain Code].&[Barnetts] } ) ON COLUMNS FROM ( SELECT ( { [Dim Fact Stock On Hand].[Is SOH More Than Zero].&[True] } ) ON COLUMNS FROM [CUBE_JDGJEDataMart])) WHERE ( [Dim Fact Stock On Hand].[Is SOH More Than Zero].&[True], [L Chain].[L Chain Code].&[Barnetts] ) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

    /* Regards Kees */

  • December 8, 2009 at 12:01 am

    #1089041

    Hi Kees,

    First come First i want to thank u for ur quick response. i copied ur coding and n run in the cube but it shows some error when i'm clicking the layout tab.

    because i never written MDX query.so please help me to run that query.

    i need a o/p like a group by function not for single row values.

    Thanks,

    Sabarinathan.C

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

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