Link to home
Start Free TrialLog in
Avatar of gosi75
gosi75Flag for Iceland

asked on

Writing calclulated member with where clause

Is it possible to rewrite this MDX query to a simple member including all conditions:
WITH MEMBER test as
SUM([Measures].[Amount],[DIM Bus Post Group].[Code].&[A])
Select {test} on 0
from [OLAP]
WHERE ([DIM Customer].[Cust Num].children - [DIM Customer].[Cust Num].&[0000])

to somehing like this(is not working though)
WITH MEMBER test as
SUM([Measures].[Amount],([DIM Bus Post Group].[Tegund].&[A],[DIM Customer].[Cust Num].children - [DIM Customer].[Cust Num].&[0000]))
Select {test} on 0
from [OLAP]

Open in new window

Avatar of Megan Brooks
Megan Brooks
Flag of United States of America image

I am not an MDX expert by any means but placing
([DIM Bus Post Group].[Tegund].&[A],[DIM Customer].[Cust Num].children - [DIM Customer].[Cust Num].&[0000])
in a tuple as if it were an attribute hierarchy doesn't look right to me. What do you want to do with that expression (the one I just quoted)?
Avatar of gosi75

ASKER

I wanted to add this as a calculated member in my OLAP
So the expression above is a calculation, then? I see what looks like two calculations:
SUM([Measures].[Amount],[DIM Bus Post Group].[Code].&[A])
and
[DIM Bus Post Group].[Tegund].&[A],[DIM Customer].[Cust Num].children - [DIM Customer].[Cust Num].&[0000]
The second one, however, appears to be subtracting a member from a set. Did you want the difference between each member of the set and the member with key &[0000]? What did you want to do with those differences?
You want these returned as a single calculated member? Do you want to filter the first expression using some relationship defined by the second one? I am still trying to understand the original question. Could you describe the basic business problem in words?
Avatar of gosi75

ASKER

I'll try to clarify the problem. I need to create a calculated member with the following criteria:

The [DIM Bus Post Group] should be only with code A and as there are so many customer numbers that are empty(and is therefore marked 0000 in the OLAP), I wanted to make the calculated member exclude the customer with 0000. Thats why I was trying to take all of the customer and subtract it with the one marked 0000.

Hope this helps understanding my problem
ASKER CERTIFIED SOLUTION
Avatar of agandau
agandau

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I created a similar example (see snippet below) using one of my cubes. I used sub-selects and didn't need a calculated member. The "Southern California" member corresponds to your "A" member -- only data from that region--and is used to restrict the inner sub-select. The "Consortium" set, corresponding to your
[DIM Bus Post Group], is filtered (using Except()) to exclude key "0,". The zero key value represents "None" in the dimension table.
Since everything is filtered in the sub-selects all I have to do is place the measure of interest on columns and the hierarchy of interest on rows. I intentionally omitted the All member in the rows set.
Because the two sub-selects refer to different dimensions, I also need the Exists in the row query to limit the Consortium members to those rows to those that relate to the measure group data. Otherwise the query returns rows with null measure values.

select [Measures].[QI Survey Count] on columns,
       Exists([QI Info].[Consortium].[Consortium], [Measures].[QI Survey Count], "QI Measures") on rows
from (
    select [QI Info].[Consortium].[Consortium] - [QI Info].[Consortium].&[0] on columns from (
       select [Geography].[Region].&[1] on columns from [QI Data]
    )
)

Open in new window

Oops, region 1 is Southern California. The orginal version of my query used a different dimension, in which the region key was a string and you could actually read what it meant.
Avatar of gosi75

ASKER

This one worked, thanks a lot for the assist.