Hi,

I have the following calculated member called [Days Over 30 Days P1]

IIf([Measures].[Stock Days Left P1] >= 30, [Measures].[Stock Days Left
P1]-30,0)

When looking at the All Level for any dimension the cell value is Zero.

To try and get around the problem I created another calculated member
that uses the top calculated member to force the aggregation to the All
Level as follows:

Sum(crossjoin({[Periodicity].members},
{[Measures].[Days Over 30 Days P1]}), [Measures].[Days Over 30 Days
P1]).

This however does not give me the All level total.

Changing the second calculated member to the following gives me a All
Level total.

Sum(crossjoin(Descendants([Product].currentmember,product.product),
{[Measures].[Days Over 30 Days P1]}), [Measures].[Days Over 30 Days
P1])

Problem I have now is to make the formula generic so it works for all
dimensions and not just Product.

Please help.

Could you explain, with some sample data, what the calculated member is
supposed to compute? Maybe there's an easier way, like by adding a new
base measure to the cube.

Also, are you using AS 2000 or AS 2005?


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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

Hi Deepak,

I am using AS2000.
Here is some sample data. Free Stock Qty is amount of stock held and
is a base measure set to MAX in aggregation Type.
Movement Qty Period 1 is amount of stock sold or gone out of from
depot.
Movement P1 days' is hard coded as 30 days in table and is set to Min
as aggregation type.



Free Stock Qty, Movement Qty Period 1, Movement P1 Days
Product 1: 5, 10000, 30
Product 2: 5, 10000, 30
Product 3: 5, 15000, 30

The calculated member Stock Days Left P1 calculates average days stock
left in period 1 (p1)

IIf([Measures].[Free Stock Qty] = 0, Null,
IIf([Measures].[Movement Qty Period 1] = 0, Null,
(([Measures].[Free Stock Qty] / ([Measures].[Movement Qty Period 1]
/[Measures].[Movt P1 Days])))))

The results I get here based on the sample data above is

Stock Days Left p1
All Product = 232
Product 1=66
Product 2=66
Product 3=100

However at the All Product level the value is Zero.

I reference Stock Days Left P1in another calculated member below which
is called Days Over 30 Days P1.

IIf([Measures].[Stock Days Left P1] >= 30, [Measures].[Stock Days Left
P1]-30,0)

Result should be as follows

Days Over 30 Days P1
All Product =133
Product 1=33
Product 2=33
Product 3=67

Again the problem here is that I am not getting All Level as Zero
rather than 133.
I tried doing the Stock Days Left P1 calculation in the table and
using a base measure in the cube rather tahn calculated member.
Problem with this is that when I look at it by product which the lowest
level of granularity then the data looks OK. Putting another dimension
in the rows, i.e Bin Number (Bin number has many products) I get the
sum rather than the Average across the cube.

Using a calculated member will allow be to work out the average no
matter which dimension I put in the rows/columns. I just need it to
roll-up to the All level of any dimension.

Thanks

Since this sounds like an inventory scenario, the use of MAX aggregation
is unusual - more typical would be a semi-additive aggregation.

Anyway, based on the sample data (I think that Free Stock Qty and
Movement Qty Period 1 are reversed) you wish to sum the values from the
lower product levels, maybe like:

IIf([Measures].[Free Stock Qty] = 0, Null,
IIf([Measures].[Movement Qty Period 1] = 0, Null,
[Measures].[Free Stock Qty] / ([Measures].[Movement Qty Period 1]
/[Measures].[Movt P1 Days]))))


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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

Thanks Deepak. That did the trick.