Definition for filter

[ratnalein]

Hallo everyone,

I have 4 status:
dead
alive
sick
unknown

I want to create filter that produces me:
The parameter filter_status can be filled or can be left blank.
if filter_status is filled, then please filter the report as defined in filter_status.  If filter_status is not filled, please filter this produce me the report without the "unknown" status. 

My code was like:

Code Select Expand


ID in ?filter_status?


--> this is to say "filter the report as definied in filter_status"

How can I add the code to produce "if filter_status is not filled, produce me the report without unknown status"?
Pseude-code is somethink like:

Code Select Expand


if filter_status != null
  then "filter the report as defined in filter_status"
else "produce the report only with alive people, dead people, and sick people"


Sorry if the case is kinda vulgar, but it is not a fictive case.  Thank you very much.

Regards,

Ratna :)

[CognosPaul]

Don't worry about vulgarity when it comes to data. I've had to deal with far more morbid data sets, health care and traffic accident data can be very depressing.

If I understand correctly, right now you have
[Status] in ?filter_status?

This is insufficient, as it will return unwanted data (unknown) if not selected. You cannot add an additional filter to remove unknown, as the user may need to select that when running the report.

Instead, set the prompt to required and use a prompt macro.

Code Select Expand

[Status] #promptmany('filter_status','string',' <> ' +sq('unknown'),' in (','',')')#

That code looks more complex than it is.

If the user doesn't select anything in filter_status, it will use the default value, '<>'+sq('unknown'). The sq function wraps fragments in single quotes, so that will resolve to [Status] <> 'unknown'

If the user does select something, it will put the ' in (' before, and the ')' after what the user selects. So that might resolve to:
[Status] in ('dead','unknown')

[ratnalein]

Hallo PaulM,

thank you so much for the tipp, it worked very fine.  But as I want to apply the same procedure once again, but this time, the datatype (datatype of Date ID) is date, it didnt work.  The code of my filter is like this:

Code Select Expand


[...].[Date ID] #promptmany('prompt_date','date',' > date('2013-04-01')', ' in (','',')')#


prompt_date is the parameter used.  I want to say:

Code Select Expand


if prompt_date != null
then "filter the report as how it is defined in prompt_date"
else "produce me the report beginning only from the 1st of April 2013"


Do you have any clue, what I wrote wrongly here?  Thank you.

Regards,

Ratna :)

[CognosPaul]

Your prompt is a multi select for dates? You're probably getting a parsing error on this from the date('2013-04-01') bit.

Since the macro parameters are wrapped in single quotes, you can't use single quotes as part of it.

Try the following instead

' > date(' + sq('2013-04-01') + ')'

The sq function will wrap Single Quotes around the string when it's returned to the expression.

[ratnalein]

Hallo PaulM,

yes, my prompt is a multi select of dates.

I just tested it, it works very fine, thank you very much.

Best regards,

Ratna

PS.  How long have you been working with Cognos BI Tools, if I may ask? :)

[CognosPaul]

I've been working with Cognos for about 7 years. Plus 2 years of Hyperion Brio. Plus a year of writing VBA in Excel and Access to automate data entry I was supposed to be doing manually. And if they fired me instead of promoting me, who knows where I'd be now.