Check if parameter is null

[NewGuy1983]

Is it possible to check if a parameter is null within a detail filter? I've tried if statements(which I read you can't use in a detail filter), and many other ways and I can't seem to get the syntax right.

Basically I need to do something like below

case when (?parameter? is null) then ('use different column for where clause') else ('column' = ?parameter?) end


thank you

[adam_mc]

A filter needs to resolve to a Boolean condition, so you need to change the syntax of the filter to be something like:

case when ?parameter? is null then [Other Value] else ?parameter? end = [Whatever Value you Want to compare Result Against]

Hope this helps,
Adam.

[NewGuy1983]

Thanks for the response. I guess I'm a little confused. Wouldn't anything after 'end' cause syntax errors?

I've tried multiple cases...and also tried boolean conditions like below and when I generate the SQL, the 'else' required is not being generated when my parameter is null

(?parameter? is null and 'column' = 'another column')
or
('column' = ?parameter?)

[NewGuy1983]

My prompt is an optional prompt also if that helps.

The report is being passed prompts from a dashboard. If a user doesn't specifiy a filter from the dashboard then I run to run the report for something else but I am unable to check the value of the optional prompt within a detail filter.

[MFGF]

Thanks for the response. I guess I'm a little confused. Wouldn't anything after 'end' cause syntax errors?

I've tried multiple cases...and also tried boolean conditions like below and when I generate the SQL, the 'else' required is not being generated when my parameter is null

(?parameter? is null and 'column' = 'another column')
or
('column' = ?parameter?)

Hi,

It might be worth adding an extra test to see if the parameter value is blank rather than null, eg

((?parameter? is null or ?parameter? = '') and [column] = [another column])
or
([column] = ?parameter?)

Just a thought :)

MF.

[NewGuy1983]

No luck on adding the additional check.

Is it possible to set a multi prompt to all the values returned from a query?

I thought about trying to make the prompt mandatory. So from the dashboard if the prompt is set then my report will run for the parameter passed otherwise I would like to set to all the values returned from a query.

Is that possible?

Would still like to be able to check for an empty parameter but am open to other ideas.

Thanks!

[adam_mc]

What is your environment (CA 11, Cognos 10.X), browser etc...?
What are you trying to run a dashboard, workspace, report?
You initially mentioned a detail filter (which to me means a report), but later a dashboard so I am not sure.

Is it returning results (no results at least being a result) or is it failing to validate/execute?
If it is failing what is the error message?

Can you put the last filter you have tried in the conversation (obfuscating any critical data) so that we can see what is not working.

Thanks,
Adam. 

[NewGuy1983]

Yes sorry...using 11.0.12


I built a dashboard from a stored procedure that has "who are you" logic. So the dashboard will display that users company info. From the dashboard I built a drill through report passing the users company to the report. The problem I'm facing is the user can deselect the company filter on the dashboard and then try the drill through. At this point the report will run for all companies because the company parameter is empty.

At the report level I want to check to see if that parameter is null and if so then do some other logic.

My company prompt is optional and I've tried many attempts to check for nulls. Case statemtents, if statements, boolean statements and tried by looking at the paramCount. When I generate the SQL from my query within the report, the where clause is never generated.

[adam_mc]

I presume it is working that way because the prompt in the report is optional and a null Company is causing it to function as if there is no parameter and hence no where clause is created.
Does that make sense, because I feel as if I am going round in circles trying to clarify what I'm trying to say?

Rather than passing the Company Number from the Dashboard which can be null, can you pass it some sort of calculation to force a value other than null which you can then handle in your report.
So, in your dashboard you could have a calculation like:

     pass_Company = case when [Company] is null then 'XXXX' else [Company] end

Then, change the drill-thru parameter to use pass_Company instead.

Then, in your report, you will always have a value and you can create your filter like:

     case when ?receive_Company? <> 'XXXX' then ?receive_Company? else [Default Company] end = [Compare Company]

Hope I'm understanding this correctly,
Adam.

[NewGuy1983]

Thanks Adam. Sorry I didn't explain clearly on my original post.

That sounds like a great solution. I'll try and post if that works. Sounds like it should. Thanks again