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.