Include Null Only?

[rachelm920]

Hello, I am a newbie to this forum, but I've been using Cognos Impromptu for several years. I have a question about a report I created. What the report does is list all of the orders placed in the system with various prompt options. It works good for all the prompts except one. There is a column on the report that is the "FRC Code", and I want to be able to run the report with the option to include Null FRC Codes only. If I put a "Y" in the prompt for "Yes" the report comes up with everything including populated FRC Codes. All of the FRC Codes start with the letters "HT". This is what the reason code flag states:

Code Select Expand

if (upper(ReasonCode) = 'Y' and ReasonCode not starts with 'HT' then (1) else (0) This is what the main flag states, all of the other flags listed here do work.

Code Select Expand

(Infact Flag + NonInfact Flag) * (Envelope Flag + Insert Flag + Paper Flag + All Others Flag + Complete Flag) * Obsolete Flag * (Completed Order Flag + P&R Orders Flag) + Reason Flag The Filter states:

Code Select Expand

Main Flag >=1among many other things, but that is the only filter detail pertaining to this problem... I am running Cognos Impromptu - Administrator Version 7.1.339.0

Any help would be greatly appreciated. The reason I have many prompts on the report is so it can be loaded to Upfront and used for multiple purposes.
Thank you in advance,
Rachel  ;D

[almeids]

I don't understand the logic you've included or how it relates to your problem since FRC Code doesn't appear anywhere in it...is FRC code the same as reason code?  The first piece of code is a bit odd, if ReasonCode is Y it will never start with HT, so as far as I can tell that simply translates a Y to a 1 and the mention of the HT prefix is irrelevant.
At any rate, to give you a generic answer in case it helps: you can't compare anything to a null value, nulls are by definition undefined (!).  Where you want to select or identify something that is null you need to explicitly use 'is null' or 'is missing' syntax.
So, I'm guessing (assuming that for whatever prompt it is we're talking about, a Y value means 'show nulls only') that you want something like:

if (upper(ReasonCode) = 'Y' and FRC_code is missing) then (1) else (0)

[rachelm920]

I don't understand the logic you've included or how it relates to your problem since FRC Code doesn't appear anywhere in it...is FRC code the same as reason code? 

Yes it is I'm sorry about that FRC stands for "Financial Reason Code"

The first piece of code is a bit odd, if ReasonCode is Y it will never start with HT, so as far as I can tell that simply translates a Y to a 1 and the mention of the HT prefix is irrelevant.

The "Y" is translated into a 1 in order to make only certain data appear. This makes it easier for "non Cognos savvy" people understand the prompt on Upfront. The prompt states "Include Null FRC Codes Only? (Y/N)"

if (upper(ReasonCode) = 'Y' and FRC_code is missing) then (1) else (0)
returns the same result as the first  ???

Could it be something in the "Main Flag"? All of the other Flags listed in the Main Flag have a similar code as the Reason Code flag. If I use any one of those prompts as "Y" or "N" they all work.

if(upper(flag name) = 'Y'  and data information ) then (1) else (0)

[almeids]

There must be a little more to it than that, or the main flag calculation would simply add all the flags and wouldn't involve multiplication...for example, if obsolete flag is 0 it will override all other flags and not return the record

add the reason flag to your report layout so you can determine its value and make sure it's a 1 on the null-reason-code records and 0 on the others

assuming that's the case, the problem is somewhere either in one of the other components of main flag (put them on the report to check the values), or in the logic of its expression (maybe reason flag belongs inside that last set of parentheses? maybe the entire expression prior to adding reason flag needs to be parenthesized so reason flag overrides the other settings?)

I think the logic of main_flag is where you'll find your problem

[rachelm920]

if (upper(ReasonCode) = 'Y' and ReasonCode is missing) then (1) else (0)
All Zeros - Apparently Cognos didn't like the "is missing" option.

if (upper(ReasonCode) = 'Y' and ReasonCode not starts with 'HT' then (1) else (0)
Is working

Added Reason Flag to the last set of parentheses:
Didn't work

Then I moved Reason Flag to before the last sent of parentheses and changed the "+" to a "*". It now reads (Infact Flag + NonInfact Flag) * (Envelope Flag + Insert Flag + Paper Flag + All Others Flag + Complete Flag) * Obsolete Flag * Reason Flag * (Completed Order Flag + P&R Orders Flag)  TADA!!! It is working!
Thank you so much for your help!!!
;D ;D ;D ;D ;D ;D ;D ;D

[almeids]

Hmmmm...well, OK then.  I don't understand how the filter can be working if the flag calculation isn't, but all's well that ends well!