"is missing" command no longer working with Costpoint 4.0

[iguanadon]

For those of you using Impromptu in conjunction with Costpoint databases, have you had this issue come up. We just upgraded from Costpoint 3.0c to v4.0. All has been fine overall, however had an issue come up yesterday where the following data def that has worked fine for 3 years (prior to the upgrade to 4.0) suddenly isn't working. The 'is missing' portion of the if/then/else is being ignored if you will. I can change the else to say &quot;123&quot; and that's what appears in the report. Also, if I change 'is missing' to '< 0' it works fine, but that's not an acceptable fix. I have this command in dozens of places that are all screwing up now. Anybody run into this? Dave Griffin, I figured you'd be the one to possibly have seen or heard about this.

What's most bizarre is the substring is on a prompt, it's not even a Costpoint field that is being substringed, so why should the upgrade to 4.0 even be the cause? And yes, I've run the same .imr against a copy of our 3.0c database we kept and it's still working fine there.

if substring (Project ID,6,1) is missing) then (L1 Project Name) else if substring (Project ID 10,1) is missing) then (L2 Project Name) else if substring (Project 14,1)is missing) then (L3 Project Name) else (Proj Name)

Scott

 

[griffindm]

Scott,

Is the 'Is Missing' portion of the filter being ignored, or is it just being processed locally (i.e. not in the SQL statement, but seen in the result set)?

Are you using the same version of Impromptu that you used successfully before the Costpoint upgrade? What is your database? If using Oracle, is Auto Decode turned off?

Dave Griffin
The Decision Support Group
Reporting Consulting with Cognos BI Tools
&quot;Magic with Data&quot;
Want good answers? Read FAQ20-2863 first!

http://www.decisionsupportgroup.com

 

[iguanadon]

Hi Dave, thanks for the response. In the iqd, a samplie piece of it shows like this in both the working and non-working output files:

((substring('08550.001' from 10 for 1)) IS NULL)) THEN (T4.&quot;L2_proj_name&quot

So, it's treated it as a NULL in both cases, I thought maybe we were on to something, but no luck.

I'm on SQL server platform and yes I ran the exact same imr using the same version of Impromptu, from the same workstation, just pointed to the two different databases.

Again, if I change &quot;is missing&quot; to &quot;< 0&quot; it works properly. Completely bizarre to me, but I guess I shouldn't be surprised.

By the way, this is Scott with RTI in NC so you realize I'm the lucky person who maintains over 1,600 reports here.

I got the approval to attend the Cognos conference in June, I look forward to seeing you there.

If you have any other thoughts or ideas, let me know

 

[griffindm]

Scott,

I seem to remember some differences in how SQL Server treats true nulls as opposed to derived null strings from the substring function. Try using <> '' in the filter rathter than &quot;is missing&quot; and see if this works. Not the optimal solution as you would still have a lot of reports to touch in order to straighten them out.

Did you try a look to the project level in PROJ rather than a substring as another alternative on which project name to use?

Say hi to John P for me.

Regards,

Dave Griffin
The Decision Support Group
Reporting Consulting with Cognos BI Tools
&quot;Magic with Data&quot;
Want good answers? Read FAQ20-2863 first!

http://www.decisionsupportgroup.com