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

MDX Query: Every end of each month on rows Expand / Collapse
Author
Message
Posted Thursday, September 29, 2011 5:51 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, June 20, 2014 8:55 AM
Points: 5, Visits: 43
Hi all, I'm new to MDX,
I want to create a query to get on Rows every end of each month.

The basic query on AW gets all days,

SELECT
NON EMPTY { [Measures].[Sales Amount] } ON COLUMNS,
NON EMPTY { [Date].[Calendar].[Date] } ON ROWS
FROM [Adventure Works]


But I want to get only month's ends:


.................... Sales Amount
July 31, 2005....... 15,012.18 $
August 31, 2005..... 20,859.78 $
September 30, 2005.. 35,782.70 $
October 31, 2005.... 6,749.98 $
November 30, 2005... 18,590.45 $
December 31, 2005... 22,168.72 $


Thanks in advance
Tito
Post #1183489
Posted Tuesday, October 11, 2011 8:51 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 2:34 PM
Points: 471, Visits: 876
Hi,
I can't really think of another way other than this:



WITH Member [Measures].[Sales Last Day of Month] AS
(
[Date].[Calendar].CurrentMember.LastChild
,[Measures].[Sales Amount]
) ,FORMAT_STRING = "Currency"


SELECT
NON EMPTY
{
[Measures].[Sales Last Day of Month]
}

ON COLUMNS,

NON EMPTY
(
[Date].[Calendar].[Month]

)
ON ROWS

FROM [Adventure Works]
Post #1188852
Posted Wednesday, October 12, 2011 6:58 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, June 3, 2014 8:57 AM
Points: 228, Visits: 236
Are you looking for the sales ON the last day of each month or the sales for each month AS OF the last day of each month (including all of the days in that month)?


Chris Umbaugh
Data Warehouse / Business Intelligence Consultant
twitter @ToledoSQL
Post #1189090
Posted Wednesday, April 18, 2012 11:50 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 2:34 PM
Points: 471, Visits: 876
Chris Umbaugh (10/12/2011)
Are you looking for the sales ON the last day of each month or the sales for each month AS OF the last day of each month (including all of the days in that month)?


I know its a few months later, just revisiting this.

Thanks for pointing that out Chris.

If you want the sales ON the last day of the month then that is the query I've shown in the previous post.
If you want the sales for the whole month it's pretty much the most basic looking MDX query you could imagine.

SELECT
NON EMPTY
(
[Measures].[Sales Amount]
)
ON COLUMNS,

NON EMPTY
(
[Date].[Calendar].[Month]
)
ON ROWS

FROM [Adventure Works]

It's not going to give you end of month dates, it will just show the month. This will be the total for the month although if the cube includes the current month it will show the partial total to date for that current month.

If you want to display the date of the last day of the month and the total for the month in the other column I'm not sure how to do that. It doesn't really make sense from a cube point of view to do that anyway given how slicing & hierarchies work. A dataset like that is ambigous. It would imply that it's the sales ON the last day of the month.

If you really want month totals next to last day of month it's really just a matter of formatting and display which you could achieve in the reporting solution.
Post #1286193
Posted Thursday, October 24, 2013 3:23 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, October 24, 2013 4:05 PM
Points: 1, Visits: 1
If hierarchy is ...-Months-Days

SELECT
NON EMPTY { [Measures].[Sales Amount] } ON COLUMNS,
NON EMPTY { filter([Date].[Calendar].[Date], [Date].[Calendar].currentmember is [Date].[Calendar].currentmember.parent.lastchild) } ON ROWS
FROM [Adventure Works]

If hierarchy is ...-Months-...-Days

SELECT
NON EMPTY { [Measures].[Sales Amount] } ON COLUMNS,
NON EMPTY { filter([Date].[Calendar].[Date], [Date].[Calendar].currentmember is closingperiod([Date].[Calendar].[Date], [Date].[Calendar].currentmember.parent.parent....)) } ON ROWS
FROM [Adventure Works]
Post #1508287
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse