Graceful Exception Handling in Expressions

[mgwalter]

Somewhat new to Report Studio, and I could use assistance with gracefully handling exceptions in expressions. I only have access to report studio; our Cognos install and the data source (SQL Server) are managed by a provider, so as much as I'd like to correct the garbage before it is stored in the DB, I can't. Currently, I'm writing reports which do math based on values which end-users input in an application (which I also do not maintain). The end users are supposed to enter integers, but the data can be garbage - null, empty strings, characters, fractions, expletives, insults, etc..

In my expressions, I've no issue casting a value then doing math on the value with another value and outputting correct data. However, this blows up when I try, for example, to divide by vales which aren't numbers.

I can do a series of if statements to be sure the data I'm working with indeed casts correctly and output "N/A" in the end report, but it's ugly code. Being a long-time Java developer, I know there are very simple ways, like a try/catch block to solve for this. However, I want to know if there's a 'correct' way to do this without all the if statements testing for validity in my Cognos Expressions.

So, would love some help of how you all would handle outputting something like (A+B)/C knowing A,B and C begin life as characters, and if one of them fails to cast to an integer, I'd just output "N/A" for the value of the expression. Thanks!

[Lynn]

Somewhat new to Report Studio, and I could use assistance with gracefully handling exceptions in expressions. I only have access to report studio; our Cognos install and the data source (SQL Server) are managed by a provider, so as much as I'd like to correct the garbage before it is stored in the DB, I can't. Currently, I'm writing reports which do math based on values which end-users input in an application (which I also do not maintain). The end users are supposed to enter integers, but the data can be garbage - null, empty strings, characters, fractions, expletives, insults, etc..

In my expressions, I've no issue casting a value then doing math on the value with another value and outputting correct data. However, this blows up when I try, for example, to divide by vales which aren't numbers.

I can do a series of if statements to be sure the data I'm working with indeed casts correctly and output "N/A" in the end report, but it's ugly code. Being a long-time Java developer, I know there are very simple ways, like a try/catch block to solve for this. However, I want to know if there's a 'correct' way to do this without all the if statements testing for validity in my Cognos Expressions.

So, would love some help of how you all would handle outputting something like (A+B)/C knowing A,B and C begin life as characters, and if one of them fails to cast to an integer, I'd just output "N/A" for the value of the expression. Thanks!

How about something like this:

case
  when isnumeric( [A] ) = 1 and isnumeric( [B ] ) = 1 and isnumeric( [C] ) = 1
  then #/* do your casting and computation here */#
  else null
end

Set the missing values property in your layout to 'N/A'

[BigChris]

I had a look for an ISNUMERIC type function this morning when I saw this pop up, but I couldn't find one...whereabouts would I find that, or do you just need to know to type it in?

[Lynn]

I had a look for an ISNUMERIC type function this morning when I saw this pop up, but I couldn't find one...whereabouts would I find that, or do you just need to know to type it in?

The function list within RS doesn't always list every possible thing out there. In general I'd suggest people use Cognos functions rather than vendor-specific functions because things are then more durable should a decision be made to change to another DBMS. But when you need something specific you tend to find yourself relying on database vendor functions available in your environment. Usually googling helps find what options there are within a particular DBMS. Mainly I'm just old and have run across a lot of different requirements in a lot of different databases over the years.

[BigChris]

That's handy to know - I'd always assumed the list of functions listed were the ones that Cognos could use...makes sense that if it exists in the DB then you ought to be able to use it, since it'll just get passed to the DB anyway. Thanks for the pointer  :D

[bdbits]

I would add that I try to push as much of this kind of thing down into Framework Manager as possible. The reason is that if you put the expressions into each report, and your vendor changes say a datatype, you will have to go to every report. If you put it in FM, you will only have to change the model and republish.