MDX Question

All,

I have a simple MDX query to return results based on user i/p in search screen. User can type in trade code and the results should give all the source of it. The dimension TRade contains couple of million members that are not unique. (Only source + trade is unique)

The below works when there is a single member corresponsing to input (xxxx). If there are multiple members, it returns nothing.

I can't use filter function here as the trade dimension is large and it takes time to filter. I can't search using member key as the key is made of Trade code + source where source is not avaiable during search,

any solution?

SELECT Measures.[Value] on 0,

NON EMPTY ([Source].[Source].Members) on 1

from MyCube

WHere [Trade].[Trade Code].[xxxx]

Cheers,

Arun

dear Arun,

Create a view with all distinct values that you pretend to aggredate. And in the datasourceview of AS, import this view and use it as a dimension.

regards!!

|||

Hi Pedro,

I dont really want to do that. The dimension design is right. I have another hierarchy in the Trade dimension with levels as Source & Trade Code. this is exactly what I need.

But my problem is filtering using the member name when it appears more than once in the dimension.

Cheers,

Arun

|||

Arun,

Do you have something like this:

Source Trade 1 T11 1 T12 2 T21 3 T31 3 T32 3 T33

correct?!

|||

Arun,

As PedroCGD has mentioned, make a Distinct Trade Value for Filter Criteria, and donot think about Single trade as you example

SELECT Measures.[Value] on 0,

NON EMPTY ([Source].[Source].Members) on 1

from MyCube

WHere [Trade].[Trade Code].[xxxx]

But think about Ranges.

Make a selection criteria for Start Range and End Range. I hope you will understand.

Bhudev

|||

Saw the rply only now.

I had manageed to solve the problem myself. Below is the query.

Source System dimension: (this dimension will have <20 members)

Key - name

1 - ss1

2 - ss2

3 - ss3

4 - ss4

trade dimension : (this can have millions of members) - uniquekey : SourceID + tradecode

SourceID TradeCode Key 1 TC11 Trade.TradeCode.&[1]&[TC11] 1 TC12 Trade.TradeCode.&[1]&[TC12] 2 TC11 Trade.TradeCode.&[2]&[TC11] 2 TC22 Trade.TradeCode.&[2]&[TC22] 3 TC31 Trade.TradeCode.&[3]&[TC31]

Code Snippet

SELECT [Measures].[Value] ON 0,

NON EMPTY (

STRTOSET(" {" +

GENERATE ( [Source System].[Source].[Source]. Members,

"[Trade].[Trade Code].&[" + CSTR([Source System].[Source].CurrentMember.Member_Key ) + "]&[xxxxxxx]" ,

"," )

+ "}")

)

ON 1

FROM [MyCube]