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?
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.
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:
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:
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:
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:
(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!
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
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...
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
Thanks Nimrod, its working now.
Thanks again.
Pcog
Quote from: Nimrod Avissar on 27 Feb 2014 01:06:35 AM
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:
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:
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:
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:
(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/2013Case 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.
Quote from: pcog on 11 Mar 2014 04:46:50 PM
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:
(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.
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
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.