Post reply

Using MDX Filter() to limit number of results

  • April 28, 2010 at 8:40 pm

    #218340

    Hi all,

    What i want to do is look at sales of only certain transaction types in my cube. the trans codes are '001', '002', '144' etc

    I was thinking i could use Filter() as a way to limit the sales information for the products.

    eg (a very cut down table which the cube is based on)

    ID TransCode TransValue

    1 001 100

    2 002 200

    3 001 300

    4 004 500

    5 144 250

    I have been trying to create a named set under calculations tab in my project to do this:

    FILTER( [Measures].[Trans Value], [PVH TransCodes].[Trans Code] = {'001', '002', '144'})

    Im clearly going about this the wrong way, im wondering if i am even close to being on track.

    I've tried to read various sources on how to use filters, but not seeing how i can do what i want to do.

  • April 29, 2010 at 1:35 am

    #1159468

    Hi adzymcfadzy,

    MDX differs from TransactSql. Instead of using Filter I suggest you create your set by assigning specific members to the set. Using AdventureWorks something like this:

    with

    set [x] as

    {[Customer].[Customer Geography].[Country].&[Australia],

    [Customer].[Customer Geography].[Country].&[Canada],

    [Customer].[Customer Geography].[Country].&[France]}

    select [x] on 0,

    [Measures].[Reseller Order Count] on 1

    from [Adventure Works]

    result is:

    Australia Canada France

    Reseller Order Count 5,584 5,584 5,584

    Assuming that 001, 002 and 144 are the key of the dimensionmember keys, the set you need will be like this:

    {[PVH TransCodes].[Trans Code],&[001],

    [PVH TransCodes].[Trans Code],&[002],

    [PVH TransCodes].[Trans Code],&[144]}

    Hope that helps you out,

    Cees

  • April 29, 2010 at 3:55 pm

    #1160089

    Thanks for the reply,

    MDX is certainly a different beast to TSQL 🙂 I have been doing more readng on it, thanks for putting me on the right track.

    Regards

    Adam

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

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