Detail Filter with Parameters (NULL prompt selection dose not work in filter)

[SupCog]

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

[SupCog]

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!!

[R Ambre]

HI

Then clause will always fail as you are using Boolean statement in it ([DEPARTMENT] <> 'XXX')

What is you exact requirement for then clause?

[Michael75]

Try to avoid if then else / case constructs in a filter, and use only and / or

Code Select Expand

(
?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  :)

[SupCog]

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

[Michael75]

Here's some amended code:

Code Select Expand

(
?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.

[SupCog]

Here's some amended code:

Code Select Expand

(
?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?)

[SupCog]

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

[SupCog]

hi,

still not working as expected !

[MFGF]

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.

[SupCog]

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!

[MFGF]

...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.