hi, :o
my report had a query with data items and two parameters (value prompts); what I'm trying to do is to filter the query as the following:
IF(ParamDisplayValue('Par_college') = 'A' AND ParamDisplayValue('Par_dept') is NULL)
THEN ([COLLEGE] = 'A' and [DEPARTMENT] <> 'XXX')
ELSE ([COLLEGE] = ParamDisplayValue('Par_college') and [DEPARTMENT] = ParamDisplayValue('Par_dept'))
unfortunately, when I do not choose any value for prompt 'Par_dept' the filter doesn't work properly!
should I put this filter in an expression !? then how can I filter my query!! :s
Hi again,
I tried the following as a detail filter:
IF(#prompt('Par_college','string',sq(' '))# = 'A' AND #prompt('Par_dept','string',sq(' '))# = ' ' ) THEN ([COLLEGE] = 'A' and [DEPARTMENT] <> 'XXX')
ELSE ([COLLEGE] = ?Par_college? and [DEPARTMENT] = ?Par_dept?)
unfortunately, THEN() clause still not working!!
HI
Then clause will always fail as you are using Boolean statement in it ([DEPARTMENT] <> 'XXX')
What is you exact requirement for then clause?
Try to avoid if then else / case constructs in a filter, and use only and / or
(
?Par_college? = 'A'
and
?Par_dept? is null
and
[COLLEGE] = 'A'
and
[DEPARTMENT] <> 'XXX'
)
or
(
?Par_college? <> 'A'
and
?Par_dept? is not null
and
[COLLEGE] = ?Par_college?
and
[DEPARTMENT] = ?Par_dept?
)
With credit to Lynn :)
hi,
i have two parameters (Optional value prompt) as the following:
Par_college and it's LOV(A,B,C)
Par_dept and it's LOV(xxx,yyy,zzz)
i have also a query with data items as the following:
ID
college
department
what i need is:
Scenario #1:
if the user chose the value prompt as:
Par_college (A)
Par_dept (NULL) -- the user did not select any value for this value prompt
the query should fetch all ID's with [college]=A and [department] in (yyy,zzz) -- except (xxx)
Scenario #2:
and if the user chose the value prompt as:
Par_college (A)
Par_dept (yyy)
the query should fetch all ID's with [college]=A and [department]=yyy
Scenario #3:
and if the user chose the value prompt as:
Par_college (NULL) -- the user did not select any value for this value prompt
Par_dept (xxx)
the query should fetch all ID's with [department]=xxx
Scenario #4:
if the user chose the value prompt as:
Par_college (B)
Par_dept (NULL) -- the user did not select any value for this value prompt
the query should fetch all ID's with [college]=B
Here's some amended code:
(
?Par_college? = 'A'
and
?Par_dept? is null
and
[COLLEGE] = 'A'
and
[DEPARTMENT] <> 'XXX'
)
or
(
?Par_college? = 'A'
and
?Par_dept? is not null
and
[COLLEGE] = 'A'
and
[DEPARTMENT] = ?Par_dept?
)
or
(
?Par_college? is null
and
?Par_dept? is not null
and
[DEPARTMENT] = ?Par_dept?
)
or
(
?Par_college? <> 'A'
and
?Par_dept? is null
and
[COLLEGE] = ?Par_college?
)
But note that in your scenario #1, you specify that a <null> department selection should take all departments except xxx, whereas in scenario #4, for the same case you don't make the same stipulation. The code above corresponds to what you requested.
Quote from: Michael75 on 11 Aug 2015 12:29:21 PM
Here's some amended code:
(
?Par_college? = 'A'
and
?Par_dept? is null
and
[COLLEGE] = 'A'
and
[DEPARTMENT] <> 'XXX'
)
or
(
?Par_college? = 'A'
and
?Par_dept? is not null
and
[COLLEGE] = 'A'
and
[DEPARTMENT] = ?Par_dept?
)
or
(
?Par_college? is null
and
?Par_dept? is not null
and
[DEPARTMENT] = ?Par_dept?
)
or
(
?Par_college? <> 'A'
and
?Par_dept? is null
and
[COLLEGE] = ?Par_college?
)
But note that in your scenario #1, you specify that a <null> department selection should take all departments except xxx, whereas in scenario #4, for the same case you don't make the same stipulation. The code above corresponds to what you requested.
hi,
where i should put this! do you mean i put it in a filter as simple as this!?
i tried but the first scenario faild!
my question why is the following not working fine!
IF(#prompt('Par_college','string',sq(' '))# = 'A' AND #prompt('Par_dept','string',sq(' '))# = ' ' ) THEN ([COLLEGE] = 'A' and [DEPARTMENT] <> 'XXX')
ELSE ([COLLEGE] = ?Par_college? and [DEPARTMENT] = ?Par_dept?)
hi, :D
i think i should try this:
(#sq(prompt('Par_college','string',' '))# = 'A' AND #sq(prompt('Par_dept','string',' '))# = ' ' and [COLLEGE] = 'A' and [DEPARTMENT] <> 'XXX')
OR ([COLLEGE] = ?Par_college? and [DEPARTMENT] = ?Par_dept?)
i will test it ASAP and tell u the result!!
soooo excited
hi,
still not working as expected !
Quote from: SupCog on 12 Aug 2015 02:30:43 AM
hi,
still not working as expected !
Hi,
Can you elaborate? What does this mean? Nobody can help you to fix a problem you don't describe :)
MF.
Quote from: SupCog on 11 Aug 2015 09:25:02 AM
hi,
i have two parameters (Optional value prompt) as the following:
Par_college and it's LOV(A,B,C)
Par_dept and it's LOV(xxx,yyy,zzz)
i have also a query with data items as the following:
ID
college
department
what i need is:
Scenario #1:
if the user chose the value prompt as:
Par_college (A)
Par_dept (NULL) -- the user did not select any value for this value prompt
the query should fetch all ID's with [college]=A and [department] in (yyy,zzz) -- except (xxx)
Scenario #2:
and if the user chose the value prompt as:
Par_college (A)
Par_dept (yyy)
the query should fetch all ID's with [college]=A and [department]=yyy
Scenario #3:
and if the user chose the value prompt as:
Par_college (NULL) -- the user did not select any value for this value prompt
Par_dept (xxx)
the query should fetch all ID's with [department]=xxx
Scenario #4:
if the user chose the value prompt as:
Par_college (B)
Par_dept (NULL) -- the user did not select any value for this value prompt
the query should fetch all ID's with [college]=B
hi,
I already described if any one had a question you are more than welcome to ask.
my last test was to add detail filter as the following:
(#prompt('Par_college','string',sq(' '))# = 'A' AND #prompt('Par_dept','string',sq(' '))# = ' ' and [COLLEGE] = 'A' and [DEPARTMENT] <> 'XXX')
OR (#prompt('Par_college','string',sq(' '))# <> 'A' and #prompt('Par_dept','string',sq(' '))# <> 'XXX' and [COLLEGE] = ?Par_college? and [DEPARTMENT] = ?Par_dept?)
OR (#prompt('Par_college','string',sq(' '))# ' ' and #prompt('Par_dept','string',sq(' '))# = 'XXX' and [COLLEGE] = 'A' and [DEPARTMENT] = 'XXX')
When I select from the prompt page
'Par_college' =(A)
'Par_dept' -- I did not select any value
it should fire the first clause in the filter
(#prompt('Par_college','string',sq(' '))# = 'A' AND #prompt('Par_dept','string',sq(' '))# = ' ' and [COLLEGE] = 'A' and [DEPARTMENT] <> 'XXX')OR (#prompt('Par_college','string',sq(' '))# <> 'A' and #prompt('Par_dept','string',sq(' '))# <> 'XXX' and [COLLEGE] = ?Par_college? and [DEPARTMENT] = ?Par_dept?)
OR (#prompt('Par_college','string',sq(' '))# ' ' and #prompt('Par_dept','string',sq(' '))# = 'XXX' and [COLLEGE] = 'A' and [DEPARTMENT] = 'XXX')
unfortunately didn't work as expected!
Quote from: SupCog on 12 Aug 2015 06:14:44 AM
...unfortunately didn't work as expected!
Ok, but - that doesn't tell anyone what results you got. Are you getting too many rows? Too few? None? Rows with the wrong college? Rows with the wrong department? Something else? What is happening? You are the only one who can see the results you are getting, so if you don't elaborate I don't see how you can expect people to be able to help you pinpoint the issue here?
MF.