How to handle nulls as one of the values in a prompt's list 1

[jamesdb]

I have a report that has one prompt on a column with Yes/No values, and another prompt on a 2nd column where records have a range of numeric values 1 - 10 IF the first prompt is a Yes. But if the first prompt is a No, records of the column for the second prompt are null. Results display for the first case, but it looks like the report will not display any records when the second column has null values. Instead of pulling the values from the table I tried creating a picklist file for the second prompt that would have a null in it, but this doesn't seem to work - the null won't show in the list of prompts. I also tried using a report file for the null prompt. I only see as an option splitting this report into two reports, one for Yes prompts and the second report for No prompts & nulls, deleting the null prompt and column from the query since all records are null anyway. If anyone has another suggestion how to keep these together in 1 report I would appreciate it.

 

[thanask]

check the following :

Whether there is any filter with condition
"second_column is not missing" in the report

(or)

Try creating the second prompt like this

"if second_column is null then 'value is null' else second_column"

Run the report and check whether it shows the 'value is null' in the prompt.

 

[nagrajm]

Hey Guys

Try using ifnullnumeric() or the ifnullstring() function to get the prompt to show from a report picklist.

Hope this helps!!

 

[griffindm]

jamesdb,

Where nagrajm is going to is to have you change the picklist for the second column to a report picklist, and in that report return the column with the desired 1-10 values using the ifnull-numeric function to return a unique value, say 99, when the true value is null. This function is only visible if your database supports it, which most do. The prompt message for the second prompt would read something like "Pick Value for "Y" or 99 for "N".

Give this a try.

Good idea nagrajm - I'm giving you a star!

Regards,

Dave Griffin
The Decision Support Group
Reporting Consulting with Cognos BI Tools
"Magic with Data"

http://www.decisionsupportgroup.com

 

[nagrajm]

Hey Dave

That was mighty nice of you. Thanx. How have you been?

You are doing a great job on this forum. Keep it up.

Nagraj

 

[griffindm]

Hi Nagraj,

Been real busy with client year-end issues, but I still find time to check in here and try to give others a little help on their year-end headaches. Glad to see you and others doing the same.

I'd still be interested in some of the other regulars here taking up a FAQ or two for their relevent areas of expertise. I've been dissappointed in the amount of use mine have apparently received by users looking there first before posting, but I do use them to point users to rather than reprinting answers that are addressed there.

I'm on the round table forum here with site management and others, so if anyone here has a relevent idea that could improve the site's functionality or ease of use, please pass it along in a post.

Happy Holidays to all!

Regards,

Dave Griffin
The Decision Support Group
Reporting Consulting with Cognos BI Tools
"Magic with Data"

http://www.decisionsupportgroup.com

 

[sln007]

In the filter try.....
(((first_prompt = 'YES') and
(first_column = first_prompt) and
(second_column = second_prompt))
or
((first_prompt = 'NO') and (first_column = first_prompt))

This should allow for the correct returning rows (as I understand it)