September 29, 2011 at 5:51 pm
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:
[font="Courier New"]
.................... 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 $
[/font]
Thanks in advance
Tito
October 11, 2011 at 8:51 pm
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]
October 12, 2011 at 6:58 am
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
April 18, 2012 at 11:50 pm
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.
October 24, 2013 at 3:23 pm
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]
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply