sorting months in calculated field 01 instead of 1

[lemmens.mattias]

I have been trying to achieve a Month Number calculated field in domain on jasperserver. But whenever i think it's going to finally work jasperserver throws "An error occurred while performing the previous request" error at me when testing in Ad Hoc View just as i try to add the calculated field to a row of column. 

I have been trying the following as well as a whole bunsh of varieties on this line. 

if(Month(dbo_ShowTex_Belgie_NV_Sales_Invoice_Header.Posting_Date)<='9',concat('M0',Month(dbo_ShowTex_Belgie_NV_Sales_Invoice_Header.Posting_Date)),concat('M',Month(dbo_ShowTex_Belgie_NV_Sales_Invoice_Header.Posting_Date)))

When it does work all i get is the Month Numbers and then they sort in the wrong order. (1,10,11,12,2,3,4,5,6,7,8,9)

It doesnt help that the designers of our navision database thought it would be hilarious to have all our companies in one Database; so theres thousands of tables in there now. So it takes a small eternity just to test the syntax of a calculated field. (please insert emoticon of man blowing own brains out with magnum here). I am new to this software. I have managed to concatenate calculated fields before and an if then statement also works. But when i try to combine them it all seems to go down the toilet. Ive also tried concatenate a prefix and suffix calculated field but that didnt work either. I'll keep trying things. But i really don't want to waste another whole dat on this. 

[lemmens.mattias]

Other failed attempts: Please note that these were tested in Ad Hoc View, I've taken to testing things out there first as it is less time consuming. To Load and submit the changes. 

IF(MonthNumber("Posting Date") <= 9, Concatenate('M', 0, MonthNumber("Posting Date")), Concatenate('M', MonthNumber("Posting Date")))

the following calculated fields worked. But when i tried to concatenate those calculated fields it failed again. 

IF(MonthNumber("Posting Date") <= 9, 'M0', 'M')

MonthNumber("Posting Date")

This is really doing my head in. 

[lemmens.mattias]

Another Strange one. This one is intended to find the Fiscal Year and then Concatenate the beginning year number with the ending year number. (our fiscal years run from the 7th month of the year till the 6th month of the year) Instead of concatenating the years it just adds them together. So my i get numbers like this. 4017, 4019, 4021, 4023, 4025, 4027, 4029

IF(MonthNumber("Posting Date") > 6, Concatenate(Year("Posting Date"), ' - ', Year("Posting Date")+1), Concatenate(Year("Posting Date") - 1, ' - ', Year("Posting Date")))

[lemmens.mattias]

Is this a possible answer?

I read somewhere that Concatenate only works with sql 2012 and up. 

Our sql server is 2008. 

Might that be preventing concat to work properly in my calculated fields?

[lemmens.mattias]

I beleive mylast comment regarding Concatenate was Valid

 

I have abandoned use of Calculated Field and have used a Derived Table instead. 

 

Below an example of how to make a Derived Table with Concatenated Fiscal Year(the old way)

 

SELECT gle.[Posting Date], CASE WHEN MONTH([Posting Date]) > 6 THEN CAST('y' AS VARCHAR) + CAST(YEAR([Posting Date]) AS VARCHAR) + CAST('--' as VARCHAR) + CAST((YEAR([Posting Date])+1) AS VARCHAR) 

WHEN MONTH([Posting Date]) < 7 THEN CAST('y' AS VARCHAR) + CAST((YEAR([Posting Date]) - 1) AS VARCHAR) + CAST('--' as VARCHAR) +CAST(YEAR([Posting Date]) AS VARCHAR) 

END AS FiscalYear 

FROM [showTex Belgie NV$G_L Entry] AS gle

 

I beleive you can INNER JOIN these as well using SQL in the derived table but i havent tested this. I did it manually in the Join Tab