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

Help in combining 4 different MDX queries Expand / Collapse
Author
Message
Posted Thursday, September 27, 2012 11:29 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 8:12 PM
Points: 46, Visits: 129
Hi guys,

I am new to complex MDX queries
I have managed to write these queries but now i need to combine them all together so that I get 4 columns of result set instead of one columns in every result set.

Can you please help me with this request

Can you please use the comment heading for the column names

//=======================================================================================
// Latest Bill
//=======================================================================================

WITH SET [Row Axis] AS
NONEMPTY(
{[Dim Customer].[Party Name].[Party Name].MEMBERS} *
{[Insert Datetime].[Date].[Date].MEMBERS}
,([Measures].[New Charge Amount])
)
MEMBER [Measures].[RowNumber] AS
RANK (
(
[Dim Customer].[Party Name].CurrentMember,
[Insert Datetime].[Date].CurrentMember
),
[Row Axis]
)
SELECT
{
[Measures].[New Charge Amount]
} ON COLUMNS,
TOPCOUNT
(
[Row Axis],
1,
([Measures].[RowNumber])
) ON ROWS
FROM [ACCOUNT_LEVEL]

//=======================================================================================
// Oldest Bill
//=======================================================================================

WITH SET [Row Axis] AS
NONEMPTY(
{[Dim Customer].[Party Name].[Party Name].MEMBERS} *
{[Insert Datetime].[Date].[Date].MEMBERS}
,([Measures].[New Charge Amount])
)
MEMBER [Measures].[RowNumber] AS
RANK (
(
[Dim Customer].[Party Name].CurrentMember,
[Insert Datetime].[Date].CurrentMember
),
[Row Axis]
)
SELECT
{
[Measures].[New Charge Amount]
} ON COLUMNS,
BottomCOUNT
(
[Row Axis],
1,
([Measures].[RowNumber])
) ON ROWS
FROM [ACCOUNT_LEVEL]

//=======================================================================================
// Biggest Invoices
//=======================================================================================


SELECT
{[Measures].[New Charge Amount]} ON COLUMNS,

TOPCOUNT(
{[Dim Customer].[Party Name].CHILDREN}*
{([Insert Datetime].[Date].CHILDREN)},
1,
([Measures].[New Charge Amount])
) ON ROWS
FROM [ACCOUNT_LEVEL]

//=======================================================================================
// Lowest Invoices
//=======================================================================================


WITH
MEMBER [Measures].[Invoice Rank] AS
RANK(
[Insert Datetime].[Date].CurrentMember,
[Insert Datetime].[Date].CurrentMember.Siblings,
([Measures].[New Charge Amount])
)
SELECT
{
([Measures].[New Charge Amount])
} ON COLUMNS,
TOPCOUNT
(
FILTER
(
{[Dim Customer].[Party Name].CHILDREN}*
{[Insert Datetime].[Date].CHILDREN},
([Measures].[New Charge Amount])>0
),
1,
([Measures].[Invoice Rank])
)
ON ROWS
FROM [ACCOUNT_LEVEL]

Thank you in advance
Post #1365614
Posted Tuesday, October 2, 2012 8:04 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 8:12 PM
Points: 46, Visits: 129
Hi Guys,

any thoughts about this post!

can help would be appreciated

Thank you
Post #1367376
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse