MDX CrossJoin Rows and Columns

This Sample will help you to understand How to applay Cross Join for Dimension meber( Rows and Columns)

Measures:
Payment Amt, Curr Payment

Rows : Vendor Name, Vendor Group are Rows
Columns : Month Name.

To Find Grand Total for Each Group :

[Vw Payment Details].[Vend Details].[Vend Details] .Members
[Vw Payment Details].[VENDGROUP].[VENDGROUP].Members
This Will give the value for each VendorName ,Vendor Group and Grand Total of Each Vendor Group.
Note ** But It will not give the [Grand Total] All the Vendor Groups

To Find Grand Total for ALL Group

1. Create Named Members
MEMBER [Vw Payment Details].[Vend Details].[Grand Total]ASAGGREGATE([Vw Payment Details].[Vend Details].[Vend Details] )MEMBER [Vw Payment Details].[VENDGROUP].[Grand Total]ASAGGREGATE([Vw Payment Details].[VENDGROUP].[VENDGROUP] )

2. Call the Member Inside the Query

({[Vw Payment Details].[Vend Details].[Grand Total]}
,{[Vw Payment Details].[VENDGROUP].[Grand Total]})









WITH
MEMBER [Vw Payment Details].[Vend Details].[Grand Total]
AS
AGGREGATE([Vw Payment Details].[Vend Details].[Vend Details] )
MEMBER [Vw Payment Details].[VENDGROUP].[Grand Total]
AS
AGGREGATE([Vw Payment Details].[VENDGROUP].[VENDGROUP] )
SELECT
CROSSJOIN(
{ [Time].[Fiscal Period].[Year].&[2009]&[2009 - 2010].&[2]&[May]
, [Time].[Fiscal Period].[Year].&[2009]&[2009 - 2010].&[1]&[April] }
, { [Measures].[Payment Amt], [Measures].[Cur Payment Amount] }
)
ON COLUMNS
,
{
CROSSJOIN({[Vw Payment Details].[Vend Details].[Vend Details] AS [Vendor Name] }
,{[Vw Payment Details].[VENDGROUP].[VENDGROUP] AS [Vendor Group]}
)
,(
{[Vw Payment Details].[Vend Details].[Grand Total]}
,{[Vw Payment Details].[VENDGROUP].[Grand Total]}
)
}
ON ROWS
FROM [Axapta Payment Details]