July 19, 2013 at 5:45 am
I have a MDX Query like this :
WITH MEMBER [Number of incoming mails] AS
(
[Measures].[Ind_01_S - Number of incoming mails in Stock mode]
)
MEMBER [Number of coupons received from mailing channel] AS
(
[Measures].[Ind_03_S - Number of coupons received from mailing channel in Stock mode]
)
MEMBER [Number of coupons received from another channel (fax, email)] AS
(
[Measures].[Ind_06_S - Number of coupons received from another channel (fax, email) in Stock mode]
)
MEMBER [Number of coupons in Backlog AT DATE] AS
(
[Measures].[Ind_07_S - Number of coupons in Backlog AT DATE in Stock mode]
)
MEMBER [Number of unusable coupons] AS
(
[Measures].[Ind_08_S - Number of unusable coupons in Stock mode]
)
MEMBER [Number of usable coupons] AS
(
[Measures].[Ind_09_S - Number of usable coupons in Stock mode]
)
MEMBER [Number of denied coupons] AS
(
[Measures].[Ind_10_S - Number of denied coupons in Stock mode]
)
MEMBER [Number of potential subscriptions] AS
(
[Measures].[Ind_11_S - Number of potential subscriptions in Stock mode]
)
MEMBER [Number of pending subscriptions AT DATE] AS
(
[Measures].[Ind_12_S - Number of pending subscriptions AT DATE in Stock mode]
)
MEMBER [Pending vs Potential ratio] AS
(
[Measures].[Ind_13_S - Pending vs Potential ratio in Stock mode]
)
SELECT
{
[Number of incoming mails]
,[Number of coupons received from mailing channel]
, [Number of coupons received from another channel (fax, email)]
,[Number of coupons in Backlog AT DATE]
,[Number of unusable coupons]
,[Number of usable coupons]
,[Number of denied coupons]
,[Number of potential subscriptions]
,[Number of pending subscriptions AT DATE]
,[Pending vs Potential ratio]
} ON COLUMNS,
NON EMPTY
(
(
[Dim Marketing Product].[Product Version].[Product Version]
,[Dim Marketing Campaign].[Campaigns].[Campaign Country]
,[Dim Marketing Campaign].[Campaign Market].[Campaign Market]
,[Dim Marketing Campaign].[Campaign Partner].[Campaign Partner]
,[Dim Marketing Campaign].[Campaign].[Campaign]
,[Dim Marketing Campaign].[Campaign Origin].[Campaign Origin]
)
)
DIMENSION PROPERTIES MEMBER_CAPTION,MEMBER_VALUE,LEVEL_NUMBER
ON ROWS
FROM
[ADP Mailing Management]
Now my aim is to Aggregate (Sum up) all the calculated measures based on the dimensions given below:
[Dim Marketing Product].[Product Version].[Product Version]
,[Dim Marketing Campaign].[Campaigns].[Campaign Country]
,[Dim Marketing Campaign].[Campaign Market].[Campaign Market]
,[Dim Marketing Campaign].[Campaign Partner].[Campaign Partner]
,[Dim Marketing Campaign].[Campaign].[Campaign]
,[Dim Marketing Campaign].[Campaign Origin].[Campaign Origin]
All the aggregation(sum) has to be done with MDX only. Considering the highest Hierarchy as [Dim Marketing Product].[Product Version].[Product Version] till the low level hierarchy
,[Dim Marketing Campaign].[Campaign Origin].[Campaign Origin]
But I need to sum up separate values from
[Dim Marketing Product].[Product Version].[Product Version],
[Dim Marketing Campaign].[Campaigns].[Campaign Country], ....till
[Dim Marketing Campaign].[Campaign Origin].[Campaign Origin]
Can you please help me out ? Thanks in advance.
July 19, 2013 at 6:05 am
For Example.
Refer the figure.
Considering First Hierarchy tree:
[Dim Marketing Product].[Product Version].[Product Version] as "France Term Life + Assistance"
[Dim Marketing Campaign].[Campaigns].[Campaign Country] as "France"
[Dim Marketing Campaign].[Campaign Market].[Campaign Market] as "DIGITAL"
[Dim Marketing Campaign].[Campaign Partner].[Campaign Partner] as "iQuitis"
[Dim Marketing Campaign].[Campaign].[Campaign] as "FRPDOF05PN"
[Dim Marketing Campaign].[Campaign Origin].[Campaign Origin] as "IQ01"
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply