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


MDX Query: Every end of each month on rows


MDX Query: Every end of each month on rows

Author
Message
Tito Luyo Murata
Tito Luyo Murata
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 53
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
davoscollective
davoscollective
SSC Eights!
SSC Eights! (939 reputation)SSC Eights! (939 reputation)SSC Eights! (939 reputation)SSC Eights! (939 reputation)SSC Eights! (939 reputation)SSC Eights! (939 reputation)SSC Eights! (939 reputation)SSC Eights! (939 reputation)

Group: General Forum Members
Points: 939 Visits: 1004
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]
Chris Umbaugh
Chris Umbaugh
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: 241
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
davoscollective
davoscollective
SSC Eights!
SSC Eights! (939 reputation)SSC Eights! (939 reputation)SSC Eights! (939 reputation)SSC Eights! (939 reputation)SSC Eights! (939 reputation)SSC Eights! (939 reputation)SSC Eights! (939 reputation)SSC Eights! (939 reputation)

Group: General Forum Members
Points: 939 Visits: 1004
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.
paya 98899
paya 98899
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 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]
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