gosi75
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]
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],[D IM 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?
SUM([Measures].[Amount],[D
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?
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
[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]
)
)
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.
ASKER
This one worked, thanks a lot for the assist.
([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)?