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]

SSAS MDX Query Last Transaction Date , Count Payment Due

This Post will help you to Find the Payment Due of Vendors.
Here the Idea is Find the Last Transaction date and Last Date of Seleted Period Then subtract between this.
This is done using -[Measure].[Day Count] Row Count of DimDate.Date column
And i explained some different behaviour of this.

1. Find LastDate Over Period(Selected Time Period)
This Measure is Derived from Date Dimension Table.
Ex. Select Year 2009 in filter and select some Months in column then
Result is :
January : February :March
31/1/2009 ;28/2/2009 ;31/3/2009

TAIL(EXISTING [Date].[Date].[Date].MEMBERS).ITEM(0).Name

2. Last Transaction Date Over Period

Ex: Selected Month is Jan then
(Date >= Jan - 1 - 09 and Date <= Jan - 31 -09)

TAIL(NonEmpty(EXISTING [Date].[Date].MEMBERS, [Measures].[Amount])).ITEM(0).NAME

3. Last Transaction Date From Current Period to Previous Periods
Ex: Selected Month is Jan then (Date <= Jan - 31 -09)
Find the Transaction Date Between
1. All the Previous Periods : NULL
2. Tail Member : TAIL(EXISTING [Date].[Date].[Date].MEMBERS).ITEM(0)
3. Look up Measure Between this Date and get the Tail Member

TAIL(
FILTER(
(NULL : TAIL(EXISTING [Date].[Date].[Date].MEMBERS).ITEM(0))
, NOT ISEMPTY([Measures].[Amount])
)
).ITEM(0).Name

4. Count Payment Due
Count Date Between Last day of Each Period to Last Transaction Date of Previous Period to the Current Period
Step 1:
First Find the [Measures].[DayCount]
[Measures].[DayCount] :- Count Rowl Measure from DimDate.Date Column
Step2:

I).From : Last Date of Period (Jan - 31 -09)
II). To: LastTransactionDate(NULL : (all previous Member) and Date <= Jan - 31 -09)

III). Sum of DayCount Between the Period

SUM(
{ TAIL(EXISTING [Date].[Date].[Date].MEMBERS).ITEM(0)
:
TAIL(
FILTER(
(NULL : TAIL(EXISTING [Date].[Date].[Date].MEMBERS).ITEM(0))
, NOT ISEMPTY([Measures].[Amount])
)
).ITEM(0)
}
,[Measures].[DayCount]
)

5. Count Payment Due from Last Transaction Date To Last Date of current Period
Ex:
1.From : Last Date of Period (Jan - 31 -09)
2. To: LastTransactionDate(Date >= Jan - 1 - 09 and Date <= Jan - 31 -09)
3. Sum of DayCount Between the Period


SUM(
{ TAIL(EXISTING [Date].[Date].[Date].MEMBERS).ITEM(0)
:
TAIL(NonEmpty(EXISTING [Date].[Date].MEMBERS
, [Measures].[Amount])).ITEM(0)
}
,[Measures].[DayCount]
)


****************
Sample Query
****************

WITH
MEMBER [Measures].[PaymentDue Previous Period] AS
SUM(
{ TAIL(EXISTING [Date].[Date].[Date].MEMBERS).ITEM(0)
:
TAIL(
FILTER(
(NULL : TAIL(EXISTING [Date].[Date].[Date].MEMBERS).ITEM(0))
, NOT ISEMPTY([Measures].[Amount])
)
).ITEM(0)
}
,[Measures].[DayCount]
)
MEMBER [Measures].[PaymentDue Over Period] AS
SUM(
{ TAIL(EXISTING [Date].[Date].[Date].MEMBERS).ITEM(0)
:
TAIL(NonEmpty(EXISTING [Date].[Date].MEMBERS
, [Measures].[Amount])).ITEM(0)
}
,[Measures].[DayCount]
)

SELECT {[Measures].[PaymentDue Previous Period]
,[Measures].[PaymentDue Over Period]
}
ON ROWS,
[Date].[Month Name].MEMBERS
ON COLUMNS
FROM [Vendor Ledger]
WHERE ([Date].[Year].&[2008],[Vendor].[Vendor].&[10006])

In this example [Measures].[PaymentDue Over Period] will fit in to real time

** Any Feeback about this Post are welcome **

Create Dynamic Time Dimension Named Set in SSAS

This post will explain that How to create the Time Dimension Dynamic named set using CurrentDate .

Most of the people might have know about this. This post is for self reference and sharing the knowledge.

In My cube I have Calandar Time Hierarchy
My Hierarchy is
[Year] -> [Quarter] -> [Month] -> [Day]
Here i explained some Named Set.

1. [Last years]
2. [Last Quarter]
3. [Last Months]
4. [Last Days]

Step1: First Find the Time Dimension Hierarchy Member from your cube, and select the Hierarchy member inside your Query Window.
MemberName of Year,Quarter,Month,Day :
[Date].[Time].[Year].&[2009]
[Date].[Time].[Year].&[2009].&[2]
[Date].[Time].[Year].&[2009].&[2].&[5]
[Date].[Time].[Year].&[2009].&[2].&[5].&[15]

Now you will get this query.

Step2:
Frame the string member as it is in the step1. then create the Name set by using [New Named Set] in the cube Calculation Tab.

The following link will help you to derive DateParts from Current Date.
http://www.ssas-info.com/analysis-services-faq/27-mdx/78-how-write-mdx-query-that-uses-execution-datetime-now-as-parameter

1. [Last Years]

LASTPERIODS(3, StrToMember("[Date].[Time].[Year].&["
+ Format(Now(), "yyyy")
+ "]"))

2.[Last Quarter]

LASTPERIODS(4, StrToMember("[Date].[Time].[Year].&["
+ Format(Now(), "yyyy")
+ "].&["
+ cstr(datepart( "q", Now()))
+ "]"))

3. [Last Month]

LASTPERIODS(6, StrToMember("[Date].[Time].[Year].&["
+ Format(Now(), "yyyy")
+ "].&["
+ cstr(datepart( "q", Now()))
+ "].&["
+ Format(Now(), "MM")
+ "]"))

4.[Last Day]


LASTPERIODS(30, StrToMember("[Date].[Time].[Year].&["
+ Format(Now(), "yyyy")
+ "].&["
+ cstr(datepart( "q", Now()))
+ "].&["
+ Format(Now(), "MM")
+ "].&["
+ Format(Now(), "dd")
+ "]"))

here you can define the lastperiod as your wish.

** Any feedback about this Article are Welcome **