Re: MDX Grouping Problem
- From: "Ohjoo Kwon" <ojkwon@xxxxxxxxxx>
- Date: Fri, 2 Mar 2007 15:16:46 +0900
Why don't you use NonEmpty or NonEmptyCrossJoin before applying Filter?
Anyway, if the total cells crossjoined by several dimensions are too many,
it will cause bad impact on performance.
Ohjoo
"Trevor Howe" <TrevorHowe@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:50AEAC80-15A4-4C55-9087-5089DA7561FE@xxxxxxxxxxxxxxxx
Hi Ohjoo
Thanks for your reply. Maybe I was a bit unclear in my original mail. I
will
work out the percentage value in the report using a formula of Count /
Total
Count for a Region. I want to know how to get the total count for a region
for every line returned by the MDX. So far I have defined a calculated
member
(Region Site Count) as below and it seems to work but the query
performance
has taken a bit of a turn for the worse:
WITH MEMBER [Measures].[Region Site Count] AS
(
[Sales Stream].[Region].CurrentMember,
[Site].[Site No].DefaultMember,
[Site].[Site Name].DefaultMember,
[Sales Stream].[Network Manager].DefaultMember,
[Profile].[Profile Hierarchy].DefaultMember,
[Measures].[Profile Site Count]
)
SELECT
NON EMPTY
{[Measures].[Profile Site Count],
[Measures].[Region Site Count],
[Measures].[Vol Pot Y-2 (Avg KL)],
[Measures].[Vol Pot Y-1 (Avg KL)],
[Measures].[Vol Pot (Avg KL)],
[Measures].[Vol Pot Gas (Avg KL)],
[Measures].[Vol Pot ADO Y-1 (Avg KL)],
[Measures].[Vol Pot ADO (Avg KL)],
[Measures].[Vol Pot GAS Y-1 (Avg KL)]
} ON COLUMNS,
NON EMPTY
FILTER(
[Sales Stream].[Region].[Region].ALLMEMBERS *
[Sales Stream].[Network Manager].[Network Manager].ALLMEMBERS *
[Site].[Site No].[Site No].ALLMEMBERS *
[Site].[Site Name].[Site Name].ALLMEMBERS *
[Profile].[Profile Hierarchy].[Profile Type].&[Fuel Potential].Children,
[Measures].[Profile Site Count] = 1) ON ROWS
FROM [Sales]
WHERE
(
[Month].[Month].&[200603],
[Sales Stream].[Sales Stream Hierarchy].[All Sales Stream]
)
I have to create 5 such calculated members in a particular query. Is the
above calculat
"Ohjoo Kwon" wrote:
Try below.
100 * Count / (Count, [All Profiles])
Ohjoo
"Trevor Howe" <TrevorHowe@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:6D5CFA99-CABA-4BD3-98C0-A1230BD2BF5F@xxxxxxxxxxxxxxxx
Hi All
I have a baffling MDX problem:
In a report in Reporting Services (using cube as source), I must
produce
the
following Layout:
Profile A Count %
Region A 36 78
Region B 124 29
Region C 148 31
Total 308
Profile B Count %
Region A 9 20
Region B 289 68
Region C 299 62
Total 614
Profile C Count %
Region A 1 2
Region B 11 3
Region C 34 7
Total 308
All Profiles
Region A 46
Region B 424
Region C 481
Total 1230
Profile and Region are Dimensions, and Count is a measure. As you can
see
the % column is a calculation using the count in a profile (e.g. 36 for
Region A, Profile A) divided by the Total for the region for all
profiles
(e.g. 46 for Region A, All Profiles) - 36/46 = 78%. How can I define a
MDX
calculated member to give me the regional total for each line returned
by
the
MDX. I know the answer is easy but it eludes me at present.
Much appreciated
.
- References:
- Re: MDX Grouping Problem
- From: Ohjoo Kwon
- Re: MDX Grouping Problem
- From: Trevor Howe
- Re: MDX Grouping Problem
- Prev by Date: RE: -Excel 2007: Pivot Table to SSAS why menu item "Options"->"Formulas" is disabled?
- Next by Date: Optimization with OLAP possible?
- Previous by thread: Re: MDX Grouping Problem
- Next by thread: Re: More than the maximum of 64,000 dimension member children
- Index(es):
Relevant Pages
|
