How to pass NULL values to CR parameter

[system]

I have a parameter (checked the multiple values box), I am not able to retrieve the record with NULL on that column. here is the sample code

SELECT * FROM WORKORDER
WHERE
(1=
( CASE
WHEN workorder.crewid in {?CREWID} then 1 else 0
END)
OR
workorder.crewid like
(CASE
WHEN workorder.crewid in {?CREWID} then ‘’ else ‘%’
END)
)[/b]

---

SIMONXU (BOB member since 2010-06-17)

[system]

In CR-2008 you can use the “optional prompt” as described in this post…

120×120 10.4 KB

Hi, I have created a prompt to get desired data in the report. Also, i need to get all data in the report even when user wont provide any input to the prompt and click on OK button. For this i have created a formula like, if isnull({?prompt}) then ‘*’ else (?prompt) This i included in the record selection as, @formula=(?prompt). But am not getting all data even if input is not provided to the prompt. Can anyone help me on this? Thanks in advance. K Radhika (BOB member since 2010-01-0…

In earlier versions of CR you would have to use an IsNull = true option in all your SELECT formulas.

---

MJRBIM (BOB member since 2007-03-23)

[system]

Thanks for your response, fyi I have CR 2008.
When I select the Option Prompt = TRUE, and leave the prompt blank at run time, it does not retrive any data. here is my selecttion on parameter

Optional Prompt = TRUE
Allow multiple Values = TRUE
Allow Discrete Values = TRUE
Allow range values = FALSE

Anything I missed, right now, it only pick up the record which meets my selection list.

---

SIMONXU (BOB member since 2010-06-17)

[system]

Look at the HELP files in CR-2008 to see more details on using the HasValue() function to check if that parameter has a value before evaluating it in your SELECT formulas.

---

MJRBIM (BOB member since 2007-03-23)

[system]

I guess The HasValue function is used inside report, I am trying to pass the parameter values into command object query, I am not sure if I can, If you can show me an example, it will be appreciated. I have been using the HasValue function in select record, group record etc …

---

SIMONXU (BOB member since 2010-06-17)

[system]

I did per your suggestion

1. define the parameter {?CREWID} in command object
2. change the parameter {?CREWID} to optional propmt
3. add record selection as follow:
   (not HasValue({?CREWID}) OR {Command.CREWID} IN {@ff_crewid})

I am expecting NOT HasValue() function will remove the Where clause ‘CREWID in {?CREWID)’ in command object, it does not. I don’t get any record since it does not remove where clause.

The CR version is 2008 SP1, Anything I missed in the above steps?

---

SIMONXU (BOB member since 2010-06-17)

[system]

…not sure if you can use the Optional Prompt in a COMMAND…

---

MJRBIM (BOB member since 2007-03-23)