Post reply

MDX Query SQL SERVER

  • July 21, 2012 at 10:45 pm

    #264127

    Hi Friends,

    I am new in the MDX Query. I am working on the below table structure to get the output but it is not showing what I want. Query describe below.

    (Table)

    Emp NameQtyAmt

    A 2100

    B 3200

    C 4100

    D 5200

    A 1300

    B 3400

    I am writing the query to get the out put

    with member [Measures].[Total] as

    [Measures].[Amt] * [Measures].[Qty]

    select

    {[Measures].[Total]}

    on columns,

    nonempty([Employee Master].[Empid].members) on rows

    from

    [Sales_Cube]

    The output is

    Emp NameAmt

    A 1200

    B 3600

    C 400

    D 1000

    However I want to calculate the each cell and then want to add the amount.

    I want to get the output as

    EmpAmt

    A300

    B1200

    C100

    D200

    Please help me out.

    Thanks.

    Please suggest how to write MDX query to calculate each cell and then sum the value

  • July 23, 2012 at 8:55 am

    #1516287

    I think the 'DrillThrough' statement will give you the results you are looking for. It returns all rows from the data set and does not apply any aggregation.

    Here is a link to the Statement in the Tech Net library.

    http://technet.microsoft.com/en-us/library/ms145964

  • July 23, 2012 at 11:51 am

    #1516387

    Hi,

    Thanks for ur reply.

    I have tried to use this but didn't get how to use it. Can u pls explain by modifying my query....

  • July 23, 2012 at 1:46 pm

    #1516451

    with member [Measures].[Total] as

    [Measures].[Amt] * [Measures].[Qty]

    DRILLTHROUGH MAXROWS 1000 SELECT

    {[Measures].[Total]}

    on columns,

    nonempty([Employee Master].[Empid].members) on rows

    from

    [Sales_Cube]

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

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