Concatenating string to measures in MDX query

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Data Warehousing » Analysis Services » Concatenating string to measures in MDX query...

Concatenating string to measures in MDX query

Rate Topic

Display Mode

Topic Options

Author

Message

sqlbi

Posted Tuesday, October 23, 2012 7:22 PM

SSC Rookie

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 3:03 PM
Points: 31, Visits: 159

Hi,

I have MDX Query which is

select non empty { ORDER( TOPCOUNT(NonEmpty({ [Measures].[Date] },( [Measures].[Date] )),1,( [Measures].[Date] )), ( [Measures].[Date] ), BDESC ) } on columns
from cube

The output of query is 20121023

I want output as

[Date].[Date hierarchy].[Date].&[20121023]

Is there any way to concatenate string to the date.

Post #1376293

Mackers

Posted Wednesday, October 24, 2012 2:39 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 4:19 AM
Points: 117, Visits: 460

Try...

member [Measures].[Date String] As

"[Date].[Date hierarchy].[Date].&[" + CSTR([Measures].[Date].Value) + "]"

,non_empty_behavior = [Measures].[Date]

Mack

Post #1376358

Mackers

Posted Wednesday, October 24, 2012 2:40 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 4:19 AM
Points: 117, Visits: 460

I also do something similar with this sort of MDX

TAIL(NONEMPTY({[Date].[Month].[All].Children},{[Measures].[Net Revenue]}),1).Item(0).UniqueName

Mack

Post #1376359

sqlbi

Posted Wednesday, October 24, 2012 7:44 AM

SSC Rookie

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 3:03 PM
Points: 31, Visits: 159

Thanks, I got error as

Query (1, 36) Parser: The syntax for '"[Date].[Date hierarchy].[Date].&["' is incorrect.

Post #1376481

Mackers

Posted Wednesday, October 24, 2012 8:04 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 4:19 AM
Points: 117, Visits: 460

Can you provide he entire query as the syntax looks okay (have tested it on my cube)? Or have you added this to the cube itself?

Mack

Post #1376499

sqlbi

Posted Wednesday, October 24, 2012 9:25 AM

SSC Rookie

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 3:03 PM
Points: 31, Visits: 159

it worked..sorry I made a mistake in my syntax.. Thanks!!

Post #1376549

« Prev Topic | Next Topic »

Permissions