Hi all

I am using SQL AS for building intranet based information
systems within our org.

I was trying to generate a calculated member that is the
running total of one of the measures. I tried searching
the net and MSDN articles for some info on this. I was
able to get info on creating a calc member for calculating
cumulative totals using dates (time dimensions)
(PeriodsToDate,YTD,MTD).

My requirement is I need to build a cumulative total based
on a non-date dimension.

My Dimension is plain MOLAP, Regular, STAR schema
dimension with 22 members.
I got Usage count as one of the measures. Using this i
created a calculated member (% Total) as
(usage count of that member/total count) *100

Now i need a running sum for the %Total.

This will be something like this

Dimension UsageCount %Total CumTotal
ASC 100 13.004 13.004
GHT 150 19.506 32.510
HYU 102 13.264 45.774
HJK 125 16.255 62.029
HJU 145 18.856 80.884
MNH 147 19.116 100

Added to the above.. I was able to do the calculation for
performing the cumulative total. But the issue is I donot
know the sort order of the axis members(dimension
members), so by default the members are sorted
alphabetically.

Is there any way to do the cumulative calculation as per
the current view??

here is what the MDX looks like

with member [measures].[cumulative] AS
'Sum( [classification].currentmember.firstchild :
[classification].currentmember, [Measures].[PcntTotal])'

Select {[measures].[pcnttotal],[measures].[cumulative],
[measures].[weighted]} on columns,
{ ORDER({[classification].[Id].members},
([Measures].[pcnttotal]) , BDESC)} ON ROWS
from [Weights]

Now the issue is I was able to sort the data but the
cumulative must be calculated at runtime based on the
sorted data of [measures].[pcnttotal] not by the default!!
It is sorting the data but the cumulative total is still
as per the alphabetical sort of the members in the
dimension on row axis..

Please help!!

Thanks in advance
__________________
Regards
Guru Shyam CV

If I understood your problem correctly, this Foodmart
query may help you create a solution:

with set MySet as 'order(product.[product
department].members, [unit sales], bdesc)'
member [measures].[cumulative] AS 'Sum( { head
(myset, rank(product.currentmember,myset) ) } ,
[unit sales])'
select
{[unit sales] , cumulative } on columns,
myset on rows
from sales

HTH,
Brian
www.geocities.com/brianaltmann/olap.html

Hi Brian

Thats a boogie!! It really helped me in coming up with a
solution with some minor modifications to my Calculated
member expression / MDX..
Thanks a lot for the information.

One small issue faced, I was unable to use NamedSets in
Analysis cube editor for solving this. Hence I need to
replace the NamedSet with the actual expression of the
NamedSet. Anyway, the expression in my calculated member
of the Analysis cube editor looks a bit cumbersome but
manageable.

Can you please suggest me how to use a NamedSet in the
calc member expression of Analysis cube editor?

Thanks and Regards

Guru Shyam CV

Hi all
I was able to get a solution for this.. I was able to do
this using UDF's and calling the same in the expression of
the calc member.
i used Subset and Order functions to achieve this.

If interested, I'll be pleased to send the expression
syntax.

Thanks and regards

Guru Shyam CV

I'm afraid I cant't help you with that.
I never had any problems using Named Sets in Calc Member
Editor.
Regards,
Brian