How to write a Formula for the Parameter value selection communation?

[rajrev]

Hi guys,
I'll try to expain the problem as clear as I can.

I have three (3) Parameters in a report. (Ex. Name, City and State).
And I'm using two (2) tables 1. EmpInfo and 2. Location

I'm getting the value for Name from E.EmpName &
for City & state from L.City & Location.State
(Linking these two tables with E.EmpNumber=L.EmpNumber)
-----------------------------------------------------------
The end user needs the following cases:
1. Select the values for all
(ie) ?Name = E.Name AND ?City = L.City AND ?State = L.State

2. Select the values for Name and City
(ie) ?Name = E.Name AND ?City = L.City

3. Select the values for Name and State
(ie) ?Name = E.Name AND ?State = L.State

-----------------------------------------------------------
2 and 3 are working good, but the first one is not working.
I'll past one of my formula :

if ({?Name = E.Name} AND {?City = L.City} AND {?State = L.State}) then
{?Name = E.Name} AND {?City = L.City} AND {?State = L.State}
else if ({?Name = E.Name} AND {?City = L.City})then
{?Name = E.Name} AND {?City = L.City}
else if ({?Name = E.Name} AND {?State = L.State})then
{?Name = E.Name} AND {?State = L.State}

I don't know! may be some problem in my logic or in my formula.

Please let me know if you people have any solutaion for this problem.
Thanks
MK

 

[synapsevampire]

The simplest way to accomplish this would be to set a default value for each parameter to "All", and check for it's existence in the record selection formula:

(
if {?Name} <> &quot;All&quot; then
{E.Name} = {?Name}
else
if {?Name} = &quot;All&quot; then
true
)
and
(
if {?City} <> &quot;All&quot; then
{L.City} = {?City}
else if {?City} = &quot;All&quot; then
true
)
and
( if {?State = L.State} <> &quot;All&quot; then
{L.State} = {?State}
else if {?State = L.State} = &quot;All&quot; then
true
)

Part of the reason for constructing the record selection formula this way (note the else if's are fully qualified) is to assure that the SQL is passed to the database.

The use of a default parameter set to &quot;All&quot; serves two purposes, it notifies the user what will happen if they do not change it, and allows you to key off of a value.

I have a FAQ in this foruma which goes into greater detail about this (see: faq767-3825 ), but the above should work for you.

-k

 

[fredp1]

Hi MK,

I'd approach it from a different angle,
change you Select statement to use the Like operator.
({E.Name} like {?Name} AND {L.City} like {?City} AND = {L.State} like {?State} )

With your parameters, set the default Name, City and State to be * (The wildcard char)
This was you can specify a Name or a State or a City and the report will show you what you need.
I'm also assuming that your using a SQL type db. If the * doesn't work try the %

Fred

 

[synapsevampire]

That would work, Fred, but it's less efficient overall.

You'll always be issuing the LIKE predicate in the WHere clause, which is not as efficient as the default of TRUE, which doesn't issue any where clause for that criteria.

BTW, the * would be correct, not %.

Take a look at my FAQ.

-k

 

[fredp1]

Hi Kai,

Thanks for the feedback regarding the Like clause and the efficieny.
Its something I have been taken for granted because my db isn't large.

Best Regards
Fred

 

[rajrev]

Thanks synapsevampire and fredp1.
sorry guys, I forgot to mension the DB server.. fredp1! you are right, we are using SQL Server.

synapsevampire, I hope your formula will help me a lot. I'll let you know the result tomorrow.
Thanks
MK

 

[rajrev]

Thanks once again to -k, the formula works fine.

MK