Author Topic: Substring function in Cognos Report Studio 8.4  (Read 45188 times)

Offline JaiCognos

  • Full Member
  • ***
  • Join Date: Aug 2009
  • Posts: 16
  • Forum Citizenship: +0/-0
Substring function in Cognos Report Studio 8.4
« on: 05 Jan 2010 07:45:33 am »
Hi All,

I have problem with substr function in Report Studio Tool. My Requirement is below

I need to extract the last three digits from a number string

for Eg: ORBER_NUMBER=89909

I want to check the records whose order number ends with 909 and this values will be passed by user PROMPT.

I first applied cast function to convert NUMBER into VARCHAR and then applied SUBSTR(ORDER_NUMBER,-3,3) then cognos doest not read the last three strings it returns empty.

In Oracle i know this will work at any cost but in Cognos its not working can any one help me or suggest any alternative solution.

Thanks in Advance.

Offline MFGF

  • Never knowingly correct
  • Super Moderator
  • Statesman
  • ******
  • Join Date: Jul 2005
  • Posts: 11,038
  • Forum Citizenship: +655/-10
  • Cognos Software Muppet
Re: Substring function in Cognos Report Studio 8.4
« Reply #1 on: 05 Jan 2010 09:09:30 am »
You're almost there.

Your substr function at the moment specifies the to start extracting at character position -3.

Try SUBSTR([ORDER_NUMBER],3,3)  - extract three characters starting with the character at position 3.

MF.
Meep!

Offline JaiCognos

  • Full Member
  • ***
  • Join Date: Aug 2009
  • Posts: 16
  • Forum Citizenship: +0/-0
Re: Substring function in Cognos Report Studio 8.4
« Reply #2 on: 06 Jan 2010 06:44:12 am »
Hi,

Thanks for your response.

I forgot to mentioned the number lenght varies over here so substr(order_number,3,3) do not work.

Can you suggest any other workaround for this issue.


Thanks in Advance

Offline MFGF

  • Never knowingly correct
  • Super Moderator
  • Statesman
  • ******
  • Join Date: Jul 2005
  • Posts: 11,038
  • Forum Citizenship: +655/-10
  • Cognos Software Muppet
Re: Substring function in Cognos Report Studio 8.4
« Reply #3 on: 06 Jan 2010 06:59:35 am »
In that case, use the Char_Length function too:

SUBSTR([ORDER_NUMBER],CHAR_LENGTH([ORDER_NUMBER])-2,3)

MF.
Meep!

Offline JaiCognos

  • Full Member
  • ***
  • Join Date: Aug 2009
  • Posts: 16
  • Forum Citizenship: +0/-0
Re: Substring function in Cognos Report Studio 8.4
« Reply #4 on: 06 Jan 2010 07:48:11 am »
Hi MF,

Thanks a lot for your good logic.

I applied the logic but the cognos return null column.I am investigating the problem and let you know.



Thanks a lot once again.


Offline MFGF

  • Never knowingly correct
  • Super Moderator
  • Statesman
  • ******
  • Join Date: Jul 2005
  • Posts: 11,038
  • Forum Citizenship: +655/-10
  • Cognos Software Muppet
Re: Substring function in Cognos Report Studio 8.4
« Reply #5 on: 06 Jan 2010 08:37:35 am »
If you just use the Char_Length function on the ORDER_NUMBER, what results do you get?  Is the length being returned correctly?

MF
Meep!

Offline JaiCognos

  • Full Member
  • ***
  • Join Date: Aug 2009
  • Posts: 16
  • Forum Citizenship: +0/-0
Re: Substring function in Cognos Report Studio 8.4
« Reply #6 on: 07 Jan 2010 01:31:30 am »
Hi MF,

Thanks a Lot Cognos is returning the values perfectly.


Thanks Once Again. Its was Data base problem yesterday where it was returning the values null.

So you logic worked. Hope i could also think like you one day.


Thanks
Jai

Offline MFGF

  • Never knowingly correct
  • Super Moderator
  • Statesman
  • ******
  • Join Date: Jul 2005
  • Posts: 11,038
  • Forum Citizenship: +655/-10
  • Cognos Software Muppet
Re: Substring function in Cognos Report Studio 8.4
« Reply #7 on: 07 Jan 2010 04:14:14 am »
Great news!  ;D

Hope i could also think like you one day.

One day you too may become a grumpy old man like me! :)
Meep!

Offline ReportNet Addict

  • Global Moderator
  • Statesman
  • *****
  • Join Date: Sep 2005
  • Posts: 959
  • Forum Citizenship: +65/-41
  • Creator of the Impact Suite
    • The Impact Suite
Re: Substring function in Cognos Report Studio 8.4
« Reply #8 on: 08 Jan 2010 02:16:29 pm »
And he's grumpy...  Here's his picture http://truersourcesofsound.com/wp-content/uploads/2009/04/grumpy-old-men.jpg MFGF is the one on the right... :D

Offline MFGF

  • Never knowingly correct
  • Super Moderator
  • Statesman
  • ******
  • Join Date: Jul 2005
  • Posts: 11,038
  • Forum Citizenship: +655/-10
  • Cognos Software Muppet
Re: Substring function in Cognos Report Studio 8.4
« Reply #9 on: 11 Jan 2010 03:33:38 am »
And he's grumpy...  Here's his picture http://truersourcesofsound.com/wp-content/uploads/2009/04/grumpy-old-men.jpg MFGF is the one on the right... :D

Lol!  Yep - that's me and my best mate ReportNet Addict! :D
Meep!

Offline JaiCognos

  • Full Member
  • ***
  • Join Date: Aug 2009
  • Posts: 16
  • Forum Citizenship: +0/-0
Re: Substring function in Cognos Report Studio 8.4
« Reply #10 on: 20 Jan 2010 12:47:37 am »
Thanks a lot for sharing your picture.



Offline MFGF

  • Never knowingly correct
  • Super Moderator
  • Statesman
  • ******
  • Join Date: Jul 2005
  • Posts: 11,038
  • Forum Citizenship: +655/-10
  • Cognos Software Muppet
Re: Substring function in Cognos Report Studio 8.4
« Reply #11 on: 20 Jan 2010 05:24:34 am »
It's not really me - ReportNet Addict was just having a joke :)

I'm far older than the chap in the picture!  ;D
Meep!

Offline Priyanka_Jharaaik

  • Associate
  • **
  • Join Date: Jun 2019
  • Posts: 1
  • Forum Citizenship: +0/-0
Re: Substring function in Cognos Report Studio 8.4
« Reply #12 on: 26 Jun 2019 01:49:44 am »
Hello,

I am new on Cognoise and I need your help regarding substr,cast and _add_months() function.
I have a string which is like " 6 days or 14 days or 60 days". I need to extract the number from this string and I am using a substr function for that and it works fine.
Now I have to add these days to a particular date for which I tried using _add_days(date,days) and _add_days() requires the second argument to be an integer.

I tried to use cast and convert the string to integer but it is not working.

Can you please help me with this.

PS
Regards
PS

Offline BigChris

  • Statesman
  • ******
  • Join Date: Apr 2013
  • Posts: 1,213
  • Forum Citizenship: +89/-0
Re: Substring function in Cognos Report Studio 8.4
« Reply #13 on: 26 Jun 2019 02:26:45 am »
An old school method that might work is to multiply days by 1. There's a chance that that would have the effect of turning it into an integer.

 



       
Twittear