How to achieve this in BI answer Column Editor.

Question

Hi All,

I have a Month Range Prompt having Presentation Variable 'M'.

suppose user gives Month between: APR-2014 to DEC2015, I am passing this prompt as  descriptor ID of another column which will be : 201404,201512

so the PV M will have '201404,201512',

Now I want to fetch only 2014 from that whole string i.e first 4 digit, then I want to add 1 into it, so now i will have 2015, then i want to concatenate with '03',

so my final value should be '201503'. This will represent as MAR-2015, which i ll pass to BI server to fetch the data only for MAR-2015.

I tried with the below scripts, but both are giving me a space while concatenating....

1. cast(cast(substring('@{M}' from 1 for 4) as int)+1 as char)||'03'

2. CAST(CAST(LEFT('@{M}' ,4) AS INT)+1 AS CHAR)||'03'

Both the script output: 2015 03, Which is not valid.

Please suggest.

Regards,

Abinash

AbinashBehera · Answer

Thanks, Its working fine!!

Gianni Ceresa · Answer

Hi,

Simply fix the size of your CHAR in the CAST function:

CAST(...AS CHAR(4))

By doing that you don't have the space anymore between 2015 and 03.

Tested with both your formulas, same result: