MS SQL Server OLAP - Percent of a child vs Parent (Cummulative)

Author

Percent of a child vs Parent (Cummulative)

Andrew Alfaro via SQLMonster.com

2006-01-26, 4:58 pm

Hello,

I have TIME, ARTICLES, STORES, COLOR as Dimensions.
When I´m at the leaf level of ARTICLES, I do MDX that calcs the cummulative
participation of this value, against the total of his parent.

UnitSell CALC
A1 55 - 0.55
A2 30 - 0.30
A3 15 - 0.15
Sum A 100

Doing:

iif(
[articles].CurrentMember is [articles].[all articles],
1,
[Measures].[Unit sell]/Sum([articles].CurrentMember.Parent.children,
[Measures].[Unit sell])
)

As I use MS Excel, when I choose a few artilces:
UnitSell CALC
A1 55 - 0.55
A2 30 - 0.30

In stead of 55 of 85.
This is beacuse I choose all children of the parent. And non selected items
are included in this sum.

Is there a way to not include them?

Thanks

--
If sun burns, sand too!

Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Fo...r-olap/200601/1

Deepak Puri

2006-01-26, 8:23 pm

Hi Andrew,

Not sure exactly what you're looking for, but maybe this past post
helps:

http://groups.google.com/group/micr....olap/msg/164be
08ec911233b[color=da
rkred]
Newsgroups: microsoft.public.sqlserver.olap
From: Deepak Puri
Date: Thu, 15 Dec 2005 09:27:49 -0800

Subject: Re: Calculate Measure/Percentage of Total

Hi Fernando,

What you tried is fine - pasting the query into MDX Sample app, after
selecting Foodmart 2000 database. I think that the process of posting
the sample is adding unwanted characters or breaking lines, resulting in
an error. I've edited the text to make lines shorter - see if it works:

WITH
MEMBER [MEASURES].[RollupFraction] AS '2'
CELL CALCULATION [LowestDim] FOR '({[MEASURES].& #91;RollupFraction]}
)' AS
'IIF(IsEmpty(StrToSe
t("Axis(0)").Item(0).Item(StrToSet("Axis(0)")
.Item(0).Count-1)), NULL,
iif(CALCULATIONPASSV
ALUE([MEASURES].& #91;RollupFraction],
-1, RELATIVE) < 2,
CALCULATIONPASSVALUE
([MEASURES].& #91;RollupFraction],
-1, RELATIVE),
iif(CALCULATIONCURRE
NTPASS() = 129, 1,
iif(Intersect(Extrac
t(StrToSet("Axis(1)"),
StrToSet("Axis(1)").Item(0).Item(128- CALCULATIONCURRENTPA
SS())
.Dimension),
{StrToSet("Axis(1)").Item(0).Item(128- CALCULATIONCURRENTPA
SS())
.Dimension.Parent}).Count= 0, 2,
StrToSet("Axis(0)").Item(0).Item(StrToSet("Axis(0)").Item(0).Count-1)/
(StrToSet("Axis(0)").Item(0).Item(StrToSet("Axis(0)").Item(0).Count-1),
StrToSet("Axis(1)").Item(0).Item(128- CALCULATIONCURRENTPA
SS())
.Dimension.Parent)))))',
CALCULATION_PASS_NUM
BER=129, CALCULATION_PASS_DEP
TH=128,
SOLVE_ORDER=2, FORMAT_STRING = 'Percent',
CONDITION='CALCULATI
ONCURRENTPASS()>(128-StrToSet("Axis(1)")
.Item(0).COUNT)'

SELECT {[Measures].[Unit Sales],
[MEASURES].& #91;RollupFraction]}
ON columns,
Non Empty CrossJoin([Store Type].Members,
CrossJoin([Education Level].Members,
[Yearly Income].Members)) on rows
FROM SALES
...[color=darkred]

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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

Sponsored Links