Re: MDX Grouping Problem

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

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




.

Relevant Pages