SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


The sub-total and total indicators must be retrieved by MDX in the cube.


The sub-total and total indicators must be retrieved by MDX in the cube.

Author
Message
shyamhr
shyamhr
SSC Veteran
SSC Veteran (290 reputation)SSC Veteran (290 reputation)SSC Veteran (290 reputation)SSC Veteran (290 reputation)SSC Veteran (290 reputation)SSC Veteran (290 reputation)SSC Veteran (290 reputation)SSC Veteran (290 reputation)

Group: General Forum Members
Points: 290 Visits: 323
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.
Attachments
Example Figure.JPG (7 views, 58.00 KB)
shyamhr
shyamhr
SSC Veteran
SSC Veteran (290 reputation)SSC Veteran (290 reputation)SSC Veteran (290 reputation)SSC Veteran (290 reputation)SSC Veteran (290 reputation)SSC Veteran (290 reputation)SSC Veteran (290 reputation)SSC Veteran (290 reputation)

Group: General Forum Members
Points: 290 Visits: 323
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"
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search