Text only | Text with Images

COGNOiSe.com - The IBM Cognos Community

IBM Cognos 10 Platform => Cognos 10 BI => Report Studio => Topic started by: bihat on 17 Feb 2015 02:36:39 PM

Title: ifthen parse error
Post by: bihat on 17 Feb 2015 02:36:39 PM
First post.

I am new to Cognos and have only been through a TM1 training class. Be gentle. lol

Currently this is a crosstab report. Currently I've only added this element.

My Expression definition

Code Select

IF ( substring (cast([Stage].[Facilities].[Nbr],varchar(4)),4,1) = 0 ) THEN
    ( [Stage].[Facilities].[Nbr])


Based on my programming background I feel like this should work. When I remove the ifthen my substring/cast returns what I want and that is the 4th digit of the number. Yet when I run check the code, I get a CCLException parsing error.

I only want a result if it ends in 0.

Any advice is appreciated.
Title: Re: ifthen parse error
Post by: bdbits on 17 Feb 2015 05:36:42 PM
(Because you are new, you might want to read http://www.cognoise.com/index.php/topic,24105.msg75293.html#msg75293 (http://www.cognoise.com/index.php/topic,24105.msg75293.html#msg75293).)

First, is this a dimensional or a relational package? Did you type in the MUN or use the expression editor GUI? Can you copy the specific error message you see and paste it in here?
Title: Re: ifthen parse error
Post by: BigChris on 18 Feb 2015 02:05:18 AM
Is your original string [Facilities].[Nbr] always four characters long?

I don't think Cognos is generally too picky, but I'd tend to put your 0 in quotes, and I'd also tend to put an else clause into your statement so that you'd end up with something that looks like:
Code Select
IF ( substring (cast([Stage].[Facilities].[Nbr],varchar(4)),4,1) = '0' )
THEN ( [Stage].[Facilities].[Nbr])
ELSE ('Something else')
Title: Re: ifthen parse error
Post by: Robl on 18 Feb 2015 03:29:23 AM
IF ( substring (cast([Stage].[Facilities].[Nbr],varchar(4)),4,1) = '0' )
THEN ( [Stage].[Facilities].[Nbr])
ELSE ('Something else')

The problem I think is that your return values are different types.
[Stage].[Facilities].[Nbr] is a number
'Something else' is a string.

try
THEN (cast([Stage].[Facilities].[Nbr],varchar(4))
ELSE ('Something else')


Also
I agree that you'll need the '0' in quotes to compare a string with a string.
If you can have a chat to your development team that number should realy be changed to a string in the database/package.
Only measures should be numbers; otherwise you get a silly situation where code numbers can be accidentally aggregated on an auto group and summarise.
Text only | Text with Images