Hi all,



I need someone to help me solve this problem...



I have a measure in my cube named "Stock" that represents a number of given
product in store. Generally it expresses a last state of store in chosen
time period (e.g. "Stock" measure for May 2002 has value equal to
31.5.2002 - 'cos this is a last state of store in this time period - and for
second quarter 2002 has value equal to 30.6.2002 etc.). But in All levels
(totals) I want to calculate a SUM of (selected) store states to get a total
stock (e.g. calculate a total stock of all stores in given time period). And
this is my problem! Could somebody help me with this PLEASE? Btw, there are
also "Purchase price" and "Sale price" measures in this cube. These should
be aggregate always as average.



... I don't know if it's possible to do something like this direct in AS
(???).



Thanks a lot for any tip in advance!





Vlado

Hi Vladimir,

One approach is to use a custom level formula on all levels in your time
dimension except the lowest level. Something like:

time.currentmember.lastchild

Another approach is to create a calculated cell on top of each inventory
type member with a similar calcualtion (except now you have to test whether
or not you;re at the bottom level). You need Enterprise edition for
calculated cells feature.

And another approach is to create a calculated member for each stock-type
member and do the same thing.

Hope this helps,
Richard



--
This posting is provided 'AS IS' with no warranties, and confers no rights.

"Vladimir Turenic" <vladot (AT) hotmail (DOT) com> wrote

Hello Richard!

Firstly I’d like to thank you for your quick response! Please excuse my
late but I was “a bit” busy.

I used a custom rollup formula on all levels in my time dimension expect
“Day” level to find a last state of store in a given time period. Like
you’ve said. The MDX that I used was:

Tail(Filter(Descendants([TimeDim].CurrentMember, [TimeDim].[Day]),
[Measures].[Stock] <> NULL), 1).Item(0)

Since I can’t exactly say that a last day of given period has a required
stock value for many reasons (e.g. the store didn’t send a report for
the day ‘cos it was a holiday or something like that).

But since there are “price measures”, purchase and sale price, in
my stock cube (I've mentioned them in my first message) I can't use a
custom rollup formulas 'cos they're applied to all measures and these
price measures must be aggregate as average . That's why I use a
calculated member now. The stock calculated measure expression luck
like this:

(Tail(Filter(Descendants([TimeDim].CurrentMember, [TimeDim].[Day]),
[Measures].[Stock] <> NULL), 1).Item(0), [Measures].[Stock])

....and the purchase and sale price calculated measure expressions like
this:

[Measures].[Purchase <Sale> Price] /
Count(Descendants([Products].CurrentMember, [Products].[Product Name]))

Well, I'll try to use a “Calculated Cells” to calculate a grand totals
for “All Level” on specified dimensions in a cube that should express
a SUM of all dimesnion's members (not average or a last value). Howeve, I
don't know exactly how I should do it. I'll be not able to try it until
monday or tuesday. I'll let you know how I came off. Thanks a lot for now!


Have a nice weekend!

Vladimir T.

On Thu, 02 Oct 2003 10:23:28 -0700, Richard Tkachuk [MS] wrote:

Hi Richard,

I've instaled on tuesday a SQL AS Ent Edition and I've tied to use a
caluclated cells. It seems it has solved my problem.
Thx a lot for help.

Have a nice day!


V. Turenic


"Richard Tkachuk [MS]" <richtk (AT) microsoft (DOT) com> wrote