Month/Year Expression 1

[IanWaterman]

From my query I have summary data grouped by two numeric fields Month and year.

I want to produce a chart by month and would like to concatenate the Month and year together and pad the single digit months with a zero so that they sort correctly.

ie

01/2011, 02/2011,..... 10/2011 etc

What is the syntax to do this please, when I look at the field properties for Month I see

=Fields!Mth.Value

I could do this in query
cast(Year as varchar(4))&'/'&cast(Mth as varchar(2))
But I do not know syntax for padding Mth with leading zeros

Thank you

Ian

 

[RiverGuy]

To pad with a leading zero, you do something like this. Note that depending on the database you are using, you might use + instead of &.

cast(Year as varchar(4)) + '/' + RIGHT('0' + cast(Mth as varchar(2)), 2)

 

[IanWaterman]

Thanks.

I am using SQL server but also use Oracle so got my syntax mixed up, had to use + as you suggested.

Ian