I have a report with two dates that users have the option of filtering on. I have a prompt page that asks the user if they want to filter on date1 (yes/no) and if they want to filter on date2 (yes/no). Based on those answers, there's a second prompt page which displays date prompts -- a begin date pormpt and an end date prompt for each date they say "yes" to.
In the query, there's a data item based on those answers:
IF (?date1? = 'Yes' AND ?date2? = 'No')
THEN ('date1')
ELSE IF (?date1? = 'No' AND ?date2? = 'Yes')
THEN ('date2')
ELSE IF (?date1? = 'No' AND ?date2? = 'No')
THEN ('neither')
ELSE IF (?date1? = 'Yes' AND ?date2? = 'Yes')
THEN ('both')
ELSE
('unknown')
Then I have optional filters based on the dataitem:
Filter 1: [Data Item1] = 'date1' AND to_date('date1') between ?begin date1? and ?end date1?
Filter 2: [Data Item1] = 'date2' AND to_date('date2') between ?begin date2? and ?end date2?
The problem I'm having is that the business requirement is that if the users chooses "yes" for both, then the report should use an "OR" to return data where date1 is within the chosen range OR date2 is within the chosen range. So I have another optional filter:
Filter 3: [Data Item1] = 'both' AND ((to_date('date1') between ?begin date1? and ?end date1?) OR ('date2' AND to_date('date2') between ?begin date2? and ?end date2?))
When I run the report, it returns the expected results when the DataItem is 'date1', 'date2', or 'neither'. But when the DataItem is 'both', it returns no records. It seems like that since the date prompts have values, that it's applying all three filters, not just Filter 3, so it's filtering out everything.
Any suggestions?
Thanks,
Chris Funkhouser
In the first prompt use as usevalue
(to_date('date1') between ?begin date1? and ?end date1?) for YES and
(to_date('date2') between ?begin date2? and ?end date2?) for NO
Respecitvaly for the second YES/NO prompt
(to_date('date2') between ?begin date2? and ?end date2?) for YES and
(to_date('date1') between ?begin date1? and ?end date1?) for NO
Create variables in order to hide the prompts in the second page if the user selects no
and a filter with the following definition
#prompt('yes/no1','token')+' OR '+prompt('yes/no2','token')#
Make the filter optional and do not create the data item with if statement that you have already created.
Its an idea unfortunately I can not tested but you can give it a try
A single filter might do the trick. Only one section will evaluate as true based on the user responses. You won't need the data item in your query.
(
?date1? = 'Yes' and ?date2? = 'No' and to_date('date1') between ?begin date1? and ?end date1?
)
or
(
?date1? = 'No' AND ?date2? = 'Yes' and to_date('date2') between ?begin date2? and ?end date2?
)
or
(
?date1? = 'No' AND ?date2? = 'No'
)
or
(
?date1? = 'Yes' AND ?date2? = 'Yes' and
(
((to_date('date1') between ?begin date1? and ?end date1?) OR ('date2' AND to_date('date2') between ?begin date2? and ?end date2?))
)
)