July 17, 2014 at 3:10 am
Hi, could someone please assist a MDX novice with formatting the column values to "yyyy-MM" for the following MDX that runs and returns the Month Start Date as datetime i.e. 2008-01-01 00:00:00.000
Thanks in advance
Stuart
SELECT
{
[Carrier Local Date].[Month Start Date].[Month Start Date]
}
ON COLUMNS,
{ ORDER( { [Measures].[New Users] }, ( [Measures].[# Processed Messages] ), BDESC ) }
ON ROWS
FROM [ReportingCube]
July 17, 2014 at 8:04 am
stub28 (7/17/2014)
Hi, could someone please assist a MDX novice with formatting the column values to "yyyy-MM" for the following MDX that runs and returns the Month Start Date as datetime i.e. 2008-01-01 00:00:00.000Thanks in advance
Stuart
SELECT
{
[Carrier Local Date].[Month Start Date].[Month Start Date]
}
ON COLUMNS,
{ ORDER( { [Measures].[New Users] }, ( [Measures].[# Processed Messages] ), BDESC ) }
ON ROWS
FROM [ReportingCube]
Either way, much cleaner to just have a dimension attribute in that format.
Generally this is done by actually building the desired format into the dimension, I believe.
The problem is you think you have a date, but what you really have is a preformatted string representing the date. Most of the formatting you can do in an mdx query is on measures, not dimensions.
To get it into the other format, afaik you need to create a measure for it using WITH Member, do a string manipulation, and then select the measure in your query. and then your query will show both formats.
I may be messing this up due to the order, but something like
WITH MEMBER [Measures].[Formatted Date] AS
LEFT([Carrier Local Date].[Month Start Date].Currentmember.name,7)
SELECT
{
[Carrier Local Date].[Month Start Date].[Month Start Date]
}
ON COLUMNS,
{ ORDER( { [Measures].[Formatted Date],[Measures].[New Users] }, ( [Measures].[# Processed Messages] ), BDESC ) }
ON ROWS
FROM [ReportingCube]
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply