Multi selected prompt in report filter

[pcog]

Hi,

I have report with date prompt, status prompt and some other. Status has only two values either 1 (status=open) or 2 (status=closed) and based on what we select for the 'Status prompt' date prompt should be filter.
ex: for Status 1, date prompt should be filter like [Open date] between ?start_dt? and ?end_dt?. like wise for 2 [Closed Date] btw ?start_dt? and ?end_dt?

so, I created a conditional filter using the status prompt in Case statement.but it is working only for single select, but I want it to work for multi-select (user can select both at the same time)

Working single select filter:
Case
    when ?p_Status? = 1
     then ([Open Date] between ?p_Start_Date? and ?p_End_Date?)
     when ?p_Status? = 2
     then ([Close Date] between ?p_Start_Date? and ?p_End_Date?)
end

Not working multi-select filter:
Case
    when ?p_Status? in (1, 2)
    then (([Open Date] between ?p_Start_Date? and ?p_End_Date?) and ([Close Date] between ?p_Start_Date? and ?p_End_Date?))
     when ?p_Status? = 1
     then ([Open Date] between ?p_Start_Date? and ?p_End_Date?)
     when ?p_Status? = 2
     then ([Close Date] between ?p_Start_Date? and ?p_End_Date?)
end

Where I'm I doing mistake?

[Francis aka khayman]

create two prompts... one prompt if status = 1 and another if status = 2.

use the value of status to hide one of the prompts and show the other.

[navissar]

Hi pcog!
A quick lesson in logic:
First, I always try to avoid condition clauses (if, case) in filters, because filters are already condition clauses - they basically mean "fetch all records if <condition> is met.
Now, your status prompt can have one of three values: Either its (1), (2) or (1,2) - that's what a multi select answer looks like.
The first expression we need will filter records based on [open date] if status has 1 in it (if its value is either (1) or (1,2). So the first part of this expression will check for that:

Code Select Expand

1 in (?p_status?)
This will return TRUE if there's 1 in the status prompt. If that's the case, we want to filter records by [open_date], so:

Code Select Expand

1 in (?p_status?) AND [open_date] between ?fromDate? and ?toDate?
Now, when Cognos runs over the records in the database, it will only return records for which the filter value is TRUE. This filter expression will return TRUE only if 1. status value has 1 in it and 2. the record's open date is between the desired dates. If the user selected Close Date in status, the expression will always be false.
Now, we build the second expression which is basically the same:

Code Select Expand

2 in (?p_status?) AND [close_date] between ?fromDate? and ?toDate?
Now, we want the records to return if either of these expressions returns TRUE, and if both returns TRUE. The correct operator for that is OR. So your final filter expression is:

Code Select Expand

(1 in (?p_status?) AND [open_date] between ?fromDate? and ?toDate?) OR (2 in (?p_status?) AND [close_date] between ?fromDate? and ?toDate?)
This expression will go over each record in the database. If "open" is selected in status prompt it will filter by open date, if "Close" - by close date, and if both - by both!

[adik]

I would go about this
in the query filter say: #prompt('p_status', 'MUN')# between ?p_start_date? and ?p_end_date?
for the p_status parameter set
use: [Open Date] display: 1 (or open)
use: [Close Date] display: 2 (or closed)
drag both [Open Date] and [Close Date] in your quey subject

[navissar]

Adik,
Note that #prompt()# will only allow a single select. pcog noted that he wants to allow multi select for the status prompt. Also, 'MUN' (Or Member Unique Name) is for dimensional members, not static choices, where a token prompt is more suitable.
Your solution is great for a single select setup. For a multi select setup it won't work (Even if we use #promptmany()# instead of #prompt()#), because if the user selects both, your end statement will be:
[Open Date],[Close Date] between ?from? and ?to?
And this is a statement which will fail. You could probably break the result down, but it seems like a lot of work...

[adik]

yes, i didn't pay attention that it was relational, in this case token instead of MUN
as for the multiselect part, i guess your sollution is the easiest to implement

[pcog]

Thanks Nimrod, its working now.
Thanks again.

Pcog

[pcog]

Hi pcog!
A quick lesson in logic:
First, I always try to avoid condition clauses (if, case) in filters, because filters are already condition clauses - they basically mean "fetch all records if <condition> is met.
Now, your status prompt can have one of three values: Either its (1), (2) or (1,2) - that's what a multi select answer looks like.
The first expression we need will filter records based on [open date] if status has 1 in it (if its value is either (1) or (1,2). So the first part of this expression will check for that:

Code Select Expand

1 in (?p_status?)
This will return TRUE if there's 1 in the status prompt. If that's the case, we want to filter records by [open_date], so:

Code Select Expand

1 in (?p_status?) AND [open_date] between ?fromDate? and ?toDate?
Now, when Cognos runs over the records in the database, it will only return records for which the filter value is TRUE. This filter expression will return TRUE only if 1. status value has 1 in it and 2. the record's open date is between the desired dates. If the user selected Close Date in status, the expression will always be false.
Now, we build the second expression which is basically the same:

Code Select Expand

2 in (?p_status?) AND [close_date] between ?fromDate? and ?toDate?
Now, we want the records to return if either of these expressions returns TRUE, and if both returns TRUE. The correct operator for that is OR. So your final filter expression is:

Code Select Expand

(1 in (?p_status?) AND [open_date] between ?fromDate? and ?toDate?) OR (2 in (?p_status?) AND [close_date] between ?fromDate? and ?toDate?)
This expression will go over each record in the database. If "open" is selected in status prompt it will filter by open date, if "Close" - by close date, and if both - by both!

Hey Nimrod,
I need help again. That filter was working fine until we find few issues.
It was pulling the data out of the date range I've given.
See below..

Item    Open Date      Close Date   Status

1   12/30/2008      04/17/2013      Closed
2   01/24/2013      02/07/2014      Closed
3   01/31/2013      08/07/2013      Closed
4   02/03/2013                             Open

Note: Date range will be always 01/01/2013 to 12/31/2013

Case 1: When I select only OPEN status I should get Item # 4
Case 2: When I select only CLOSED status I should get Item # 1 and 3
Case 3: When I select OPEN & CLOSED statuses I should get Item # 3 and 4

But, with this filter with multi select OPEN & CLOSED statuses I'm getting ALL items, where I supposed to get only Item # 3 and 4. Single select prompt is working fine. Any idea?

Thanks in advance for your time and help.

[navissar]

Case 3: When I select OPEN & CLOSED statuses I should get Item # 3 and 4

Oh I see. That'll be a tad more complex, because you want to apply both filters when selecting all, rather than display elements that would pass each of the filters which is the usual requirement.
Try this on for size:

Code Select Expand


(1 in (?p_status?) AND 2 not in (?p_status?) and [open_date] between ?fromDate? and ?toDate?) OR (2 in (?p_status?) AND 1 not in (?p_status?) AND [close_date] between ?fromDate? and ?toDate?) OR (1 in (?p_status?) AND 2 in (?p_status?) AND [open_date] between ?fromDate? AND ?toDate? AND  [close_date] between ?fromDate? and ?toDate?)



This should do the trick.

[pcog]

Thanks Nimrod, It did do the trick :)
As you saying the usual requirement should be the one we tried last time, but this is what my user was asking. May be they will come back and say the old one is giving the data what they are looking for because this will eliminate most of the data.

Thanks
Pcog

[navissar]

Just to sum this up, usually when a prompt is used to defined what should be filtered, selecting more than one value means this OR the other, not this AND the other. But whatever, the customer is ALWAYS right.