Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

The sub-total and total indicators must be retrieved by MDX in the cube. Expand / Collapse
Author
Message
Posted Friday, July 19, 2013 5:45 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, September 8, 2014 7:28 AM
Points: 55, Visits: 236
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.



  Post Attachments 
Example Figure.JPG (0 views, 58.06 KB)
Post #1475397
Posted Friday, July 19, 2013 6:05 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, September 8, 2014 7:28 AM
Points: 55, Visits: 236
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"
Post #1475413
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse