Print Page - Reverse() + substring() function

Title: Reverse() + substring() function
Post by: DALSOM on 28 Mar 2012 10:01:56 AM

Hi,
I see an older post, in that is used the reverse() function to get last occurrence of a string into an other.

But it has to use an sql function that I have not available on report studio.

So I need to get last position of an string like this : "Jan,Feb,Mar,Apr,May,Jun"
in order concat with the first string before the comma, and my results must be a "Jan-Jun" string or in a large way "January-June" string.

How can i look for last comma of my string ("Jan,Feb,Mar,Apr,May,Jun") in order to get the last word of the string. Notice, that in a large way you do not have 3 word format for months, so I need the whole word.

Thanks in advance.
Dalsom. :o

The sql function to do is this > reverse(substring(reverse([data_item]),1,position(',',[data_item])))

The order post is > http://www.cognoise.com/community/index.php/topic,14343.msg45407.html#msg45407

Title: Re: Reverse() + substring() function
Post by: DALSOM on 28 Mar 2012 10:05:35 AM

Can this problem "last character position" be work around with some java code?

Thanks in advance,
Dalsom.

Title: Re: Reverse() + substring() function
Post by: blom0344 on 28 Mar 2012 03:23:11 PM

As I mentioned in the reply to the older post, the 'reverse' function is typically SQL server. AFAIK Java code is not about changing query definitions

Title: Re: Reverse() + substring() function
Post by: DALSOM on 30 Mar 2012 07:28:11 AM

I got it!! 8)

I solve my issue with this formula, it is a very flinstones like, but worked for me. And works for well known and finite arrays of strings, or comma separate values on a string.

Here is it :

substring (ParamDisplayValue('Mo'),1,(position (",",ParamDisplayValue('Mo'))-1)) +
CASE WHEN
   (substring (ParamDisplayValue('Mo'),1,(position (",",ParamDisplayValue('Mo'))-1)) <>
   (CASE
   WHEN ParamDisplayValue('Mo') ends with "Jan" Then "Jan"
   WHEN ParamDisplayValue('Mo') ends with "Feb" Then "Feb"
   WHEN ParamDisplayValue('Mo') ends with "Mar" Then "Mar"
   WHEN ParamDisplayValue('Mo') ends with "Apr" Then "Apr"
   WHEN ParamDisplayValue('Mo') ends with "May" Then "May"
   WHEN ParamDisplayValue('Mo') ends with "Jun" Then "Jun"
   WHEN ParamDisplayValue('Mo') ends with "Jul" Then "Jul"
   WHEN ParamDisplayValue('Mo') ends with "Aug" Then "Aug"
   WHEN ParamDisplayValue('Mo') ends with "Sep" Then "Sep"
   WHEN ParamDisplayValue('Mo') ends with "Nov" Then "Nov"
   WHEN ParamDisplayValue('Mo') ends with "Dic" Then "Dec"
   ELSE "" END))
THEN
   CASE
   WHEN ParamDisplayValue('Mo') ends with "Jan" Then "-Jan"
   WHEN ParamDisplayValue('Mo') ends with "Feb" Then "-Feb"
   WHEN ParamDisplayValue('Mo') ends with "Mar" Then "-Mar"
   WHEN ParamDisplayValue('Mo') ends with "Apr" Then "-Apr"
   WHEN ParamDisplayValue('Mo') ends with "May" Then "-May"
   WHEN ParamDisplayValue('Mo') ends with "Jun" Then "-Jun"
   WHEN ParamDisplayValue('Mo') ends with "Jul" Then "-Jul"
   WHEN ParamDisplayValue('Mo') ends with "Aug" Then "-Aug"
   WHEN ParamDisplayValue('Mo') ends with "Sep" Then "-Sep"
   WHEN ParamDisplayValue('Mo') ends with "Nov" Then "-Nov"
   WHEN ParamDisplayValue('Mo') ends with "Dec" Then "-Dec"
   ELSE "" END
ELSE "" END

Thank you all, for read my post! :)
Dalsom

COGNOiSe.com - The IBM Cognos Community

IBM Cognos 8 Platform => COGNOS 8 => Report Studio => Topic started by: DALSOM on 28 Mar 2012 10:01:56 AM