I have a calculated member which has the following formula

sum(descendants([GEOGRAPHY].currentmember,[CUSTOMERNAME]),
([Measures].[AMOUNT]))

Dimension GEOGRAPHY has the following levels:
..REGION
..SUBREGION
...WHOLESALER
....CUSTOMERNAME

This function is working properly when i select only one item as a
filter in the GEOGRAPHY dimension. But it is not working when i select
multiple items in the GEOGRAPHY dimension. Is there any workaround for
this problem?
By the way i am using OWC 10.0 as a front end tool.



*** Sent via Developersdex http://www.developersdex.com ***

Selecting multiple members will generate a calculated member, under the
same dimension (GEOGRAPHY), which aggregates the selected members. So
the hierarchy expression:
descendants([GEOGRAPHY].currentmember,[CUSTOMERNAME]) will not work as
with single selections.

What is the purpose of this calculated member - why won't a simple 'Sum'
measure work? If you give some data examples, maybe there is some other
way to solve this.


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

Hello Deepak,

I need a report that shows sales amounts by geography and time according
to the predefined amount intervals. Let me explain it with an example.
In geography dimension i have 3 members (Geo1, Geo2, Geo3). During year
2004 following sales transactions occured.(Geography-Date-SalesAmount)
Geo1-01/01/2004-1000
Geo1-02/01/2004-2000
Geo1-03/01/2004-1000

Geo2-10/01/2004-1500
Geo2-10/02/2004-1500

Geo3-01/01/2004-500
Geo3-05/01/2004-500

If i select whole year 2004 as a filter report would look like as
follows

0 <= SalesAmount < 5.000.....5.000 <= SalesAmount < 10.000

Geo1...............4000
Geo2...............3000
Geo3...............1000
Total..............8000

To achieve this i created calculated members for each amount interval.
These members have the parent dimension as AMOUNTINTERVAL which has
actually one real member(ALLAMOUNTINTERVAL). Other members will be
calculated members. For calculated member[0 <= SalesAmount < 5.000] mdx
is as follows:
IIF (
([Measures].[SALESAMOUNT],[ALLAMOUNTINTERVAL])>=0 and
([Measures].[SALESAMOUNT],[ALLAMOUNTINTERVAL])<5000,
([Measures].[SALESAMOUNT],[ALLAMOUNTINTERVAL]),null)

It works fine. But the problem is that for the Total row the value 8000
goes to next interval. It has to be under first interval. This was the
first problem i have faced.

To solve this problem i have created two temporary calculated members.
They are not visible. Firs i renamed the first one as 0 <= SalesAmount <
5.000Tmp. Its formula was the same. The second calculated member is for
sum of 0 <= SalesAmount < 5.000Tmp. The name was SumTmp. Its formula
was given in the first mail which is as follows :
sum(descendants([GEOGRAPHY].currentmember,[CUSTOMERNAME]),
([AMOUNTINTERVAL].[0 <= SalesAmount <
5.000Tmp],[Measures].[SALESAMOUNT]))
The last calculated member will be 0 <= SalesAmount < 5.000. And its
formula as follows:
IIF(
[GEOGRAPHY].CurrentMember.Level.Name="REGION",
[AMOUNTINTERVAL].[TmpSum],
[AMOUNTINTERVAL].[0 <= SalesAmount < 5.000])


As i mentioned before these are okey if i select only one member for the
geography dimension. But it is not working when i select multiple
filters.

Thank you for your reply. Best regards.

Nilgun


*** Sent via Developersdex http://www.developersdex.com ***

Hi Nilgun,

A couple of clarifications:

- Is the threshold (5000) for the [SalesAmount] of an individual
transaction, or for the sum of all transactions in a fixed time period?
What about the time period?

- Are the thresholds fixed (5000, etc) or can they change?

In case the thresholds apply at the transaction (ie. fact table row)
granularity, and are fixed, then they can be pre-aggregated into the
cube (either via an Interval dimension or additional measures). Then
calculated members, which use Geography dimension hierachy functions,
can be avoided.


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

Hi Deepak,

- The threshold is for the sum of all transaction in a selected time
period. Time period is not fixed. The user selects the interval he/she
wants in OWC as a filter.
- Now we have 12 intervals. They are fixed. (0-5000, 5000-10000,
10000-15000,.. and so on)

Your approach would work if the threshold is for individual transaction.
Unfortunately they want it for sum of all transactions.

Thanks again. Regards.

Nilgun



*** Sent via Developersdex http://www.developersdex.com ***

Hi Nilgun,

Here is an approach that seems to work with the Foodmart Sales cube -
can this be adapted to your case?

Assuming that [Unit Sales] needs to be banded along the [Store]
dimension, say that all [Unit Sales] < 5000 should be summed by store,
for any selected time period. So, for all of 1997, the only stores with
[Unit Sales] < 5000 were San Francisco (2117), Bellingham (2237) and
Walla Walla (2203). If there is a calculated measure: [Sales < 5K], it
should then be 2117 for CA, Null for OR and 4440 for WA. And for USA,
[Sales < 5K] should be 6557, not Null.

To do this, I duplicated the Store dimension with a new name: [StoreDup]
- it uses same dimension table: "store". Then I created a new cube:
[StoreMap], using "store" as the fact table, and adding both [Store] and
[StoreDup] dimensions. So [StoreMap] cube only has 1 table: "store" in
its schema. And only 1 "Count" measure: [StoreCount] is created, from
the "store_id" column.

Then [Sales] and [StoreMap] cubes are combined in a [SalesBands] virtual
cube, which has all dimensions and measures from [Sales]; plus
[StoreDup] dimension and [StoreCount] measure from [StoreMap], but both
these can be hidden. Finally, new [Sales < 5K] measure is defined as:

Sum(Generate({[Store].CurrentMember} as StoreSet,
Filter([Store].[Store Name].Members,
ValidMeasure(([Measures].[StoreCount], StoreSet.Item(0),
LinkMember([Store].CurrentMember, [StoreDup]))) > 0)),
iif([Measures].[Unit Sales] >= 0 And
[Measures].[Unit Sales] < 5000,
[Measures].[Unit Sales], Null))


When viewing [Sales < 5K] for 1997 in Excel 2003 Pivot Table, with
[Store] dimension in Multi-Select Filter, selecting both CA and WA
returns 6557, as required.



- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

Hi Deepak,

I wanted to show results in FoodMart200 first. I did all you explained.
But when i put the calculated member in OWC #VALUE returns. (Formula
error - cannot find linked dimension member for "Store 19" - in the
LinkMember(<object>,<object>) function).
What would be the reason for this error? And can you explain the logic
of your MDX formula?
Thanks in advance. Regards.

Nilgun

*** Sent via Developersdex http://www.developersdex.com ***

Hi Nilgun,

Not sure why you're getting the error, but LinkMember() can get finicky
at times - it uses Member Keys to look up equivalent members. Since
Store 19 is the first store in
the [Store].[Store Name].Members set, it looks like there is a basic
problem in evaluating LinkMember([Store].CurrentMember, [StoreDup])].
You can also try using the name, instead of the key, as a link:

Filter([Store].[Store Name].Members,
ValidMeasure(([Measures].[StoreCount], S.Item(0),
StrToMember("[StoreDup].["
+ [Store].CurrentMember.Name + "]"))) > 0)),
iif([Measures].[Unit Sales] >= 0 And
[Measures].[Unit Sales] < 5000, [Measures].[Unit Sales], Null))

The idea of the second [StoreMap] cube is that each individual leaf
Store can be tested against the Multi-Select Calculated Member, to see
whether it is included under the selected members or not. A copy of the
[Store] dimension is needed because the Multi-Select Member is selected
on [Store] itself, so the individual Store is selected along the
identical [StoreDup] dimension. The resultant [StoreCount] is only > 0
if the tested Store is a descendant of one of the multi-select [Store]
members. So only those leaf Stores lying under the selected members are
included in the [Sales < 5K] measure calculation.


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

Hello Deepak,

The formula you gave worked.I wanted to add another band for example
[5.000 <= Sales < 10.000]. I used the same formula and changed only
numbers.

Sum(Generate({[Store].CurrentMember} as S,
Filter([Store].[Store Name].Members,
ValidMeasure(([Measures].[StoreCount], S.Item(0),
StrToMember("[StoreDup].["
+ [Store].CurrentMember.Name + "]"))) > 0)),
iif([Measures].[Unit Sales] >= 5000 And
[Measures].[Unit Sales] < 10000, [Measures].[Unit Sales], Null))

But the following error message occured:
"Unable to update the calculated member. An unexpected internal error
has occured"

Do you have any idea about this error?

Thanks.

Nilgun

*** Sent via Developersdex http://www.developersdex.com ***

Hi Nilgun,

Glad that the second approach (linking by name) worked for you! Not sure
what's causing error in the 2nd calculated measure, but try different
alias set name (it might be possible to re-use "S" in 2nd measure, but
this will confirm the source of the error):

Filter([Store].[Store Name].Members,
ValidMeasure(([Measures].[StoreCount], S2.Item(0),
StrToMember("[StoreDup].["
+ [Store].CurrentMember.Name + "]"))) > 0)),
iif([Measures].[Unit Sales] >= 5000 And
[Measures].[Unit Sales] < 10000, [Measures].[Unit Sales], Null))

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***