I'm building an application which builds dynamic MDX according to user
choices. This is done incrementally. So for example if there are 3
dimensions A, B and C. The first piece of MDX will use the A criteria
as the rows dimension. In the 2nd B is the focus but including the
criteria of A. in the 3rd query C is the focus but including the
criteria for A and B. In an SQL query this would result in query 3
being Select C criteria WHERE A = and B= and C =

Pretending all are ranges of ID's from 1 to 10 then this may be (in SQL
like form)

SELECT C
FROM CUBE
WHERE
A IN (1, 2, 3, 4, 5)
AND
B IN (6, 7, 8, 9)
AND
C IN (2, 4, 6, 8)

However I don't seem to be able to do this in MDX. So far I have done
this using crossjoins and compiled the data into the desired format in
code. Does anyone know if there is a way to move my crossjoins to a
WHERE clause (as long as the selection criteria is trhe same I'm happy
to trim the volume of Cells)? I'm assuming that even asking this
question is to misunderstand the nature of Cubes but I thought I'd
check!

Sample

SELECT CROSSJOIN ({[ProfileQtr].Children}, {[Measures].Children}) on
columns,
CROSSJOIN(CROSSJOIN(CROSSJOIN({[A].[1], [A].[2], [A].[3]}, {[b].[1],
[b].[2], [b].[3]}), {[C].[1], [C].[2], [C].[3]})) on rows
FROM Cube

Would like something like

SELECT CROSSJOIN ({[ProfileQtr].Children}, {[Measures].Children}) on
columns,
{[C].[1], [C].[2], [C].[3]} on rows
FROM Cube
WHERE
([A].[1], [A].[2], [A].[3], [b].[1], [b].[2], [b].[3])


Thanks,

Ben.

Hi,

How large is your set [ProfileQtr].Children?
Nestet crossjoin ist not good idea. Try to use NonEmptyCrossjoin. It has
essentially better performance.
You could rewrite your query in following form.

WITH
MEMBER [A].[MY_MEMBER] as SUM({[A].[1], [A].[2], [A].[3]},
Measures.CURRENTMEMBER)
MEMBER [b].[MY_MEMBER] as SUM({[b].[1], [b].[2], [b].[3]},
Measures.CURRENTMEMBER)
SELECT
CROSSJOIN({[ProfileQtr].Children}, {[Measures].Children}) on columns,
{[C].[1], [C].[2], [C].[3]} on rows
FROM Cube
WHERE ([A].[MY_MEMBER], [b].[MY_MEMBER])

Is it what you need?

Thanks,
Vladimir Chtepa

"Ben" <gringogordo (AT) hotmail (DOT) com> schrieb im Newsbeitrag
news:1136548687.595719.263410 (AT) g47g2000cwa (DOT) googlegroups.com...

Sorry to display my ignorance - is it possible to use WITH MEMBER in
the MDX sample app. I'm getting an invalid Axis Definition. Should I
play with my syntax or is it not something the sample app does.

Thanks.

This appears to be exactly what I needed. Many thanks.

If you use AS2000, calculated member schould be defined in ''.

WITH
MEMBER [A].[MY_MEMBER] as 'SUM({[A].[1], [A].[2], [A].[3]},
Measures.CURRENTMEMBER)'
MEMBER [b].[MY_MEMBER] as 'SUM({[b].[1], [b].[2], [b].[3]},
Measures.CURRENTMEMBER)'
SELECT
CROSSJOIN({[ProfileQtr].Children}, {[Measures].Children}) on columns,
{[C].[1], [C].[2], [C].[3]} on rows
FROM Cube
WHERE ([A].[MY_MEMBER], [b].[MY_MEMBER])


"Ben" <gringogordo (AT) hotmail (DOT) com> wrote