How to diff. the parameters, which are pointing to the same faild?

[rajrev]

Hi,
I'll try to explain the problem as simple as i can.

Crystal Reprot 8.5, SQL Server.

Tables used

1. Emp
Empid, EmpName, Locationid

2. JobExp
Empid, Comp, Position, Years, Locationid

3. LookupLocation
Locationid, LoactionName

I hope you people understand the link. Any way,
Emp.Empid ----->JobExp.Empid
Emp.Locationid ----->LookupLocation.Locationid
JobExp.Locationid---->LookupLocation.Locationid

The Parameters are :
1. EmpLocation (EmpLoc)
2. JobLocation (JobLoc)

(JobExp table contains same empid with diff.location ids
ie
empid loc.id
111 -- 10
222 -- 22
333 -- 23
111 -- 29
333 -- 29

and in the Emp table locations contains the employee's current location (ie)
(for example empid 111 has 2 loc.id. 10 and 29 emp 111 worked in loc. 29 1/1/00 to 1/1/02 and in the loc.id 10 - 1/1/03 to 1/1/04. Here the loc. 10 is the very recent location)

formula:
(
if ({?EmpLoc} <>&quot;ALL&quot then
{?EmpLoc}={@EmpLc}
else if ({?EmpLoc} =&quot;ALL&quot then
true
)
and
(
if ({?JobLoc} <>&quot;ALL&quot then
{?JobLoc}={@JobLc}
else if ({?JobLoc} =&quot;ALL&quot then
true
)

The Loc.id form both emp&job tables pointing to the same lookup table(lookupLocation). But we the results as follow:

Parameter 1. EmpLoc --> ALL
and 2. JobLoc --> 29

Output:
EmpName Empid Location (emplocid)
aaa 111 10
eee 333 23

(here we need the current emp location, but the seaching using the JobLoc 29 ) ie only the emp, who has job exp. in location 29; but in the output need to display the current location (may be the same location or different like the emp 111.

Please try to read my explanization, if you need more info. /clarification please let me know.

Thanks
MK

 

[synapsevampire]

Presumably you've added the location table twice for this purpose.

I understood pretty much everything up until:

&quot;(here we need the current emp location, but the seaching using the JobLoc 29 ) ie only the emp, who has job exp. in location 29; but in the output need to display the current location (may be the same location or different like the emp 111. &quot;

Aside from broken English, all of a sudden something called emplocid was introduced, which doesn't exist anywhere in the tables.

And why do the select formulas point at formulas instead on the tables directly?

-k

 

[rajrev]

Thanks for your reply . I apologize for the mistake.
Its Emp.Locationid not Emp.Locid

I'll try to explain the broken English:
For example
The Employee 111's current location is 10 (say 2003 to till date)
But he worked previously in the location 29 (2001 to 2003)
Now the end user wants the following output
They select the value in the parameter as
Ex.1
EmpLoc ---> ALL and
JobLoc ---> 29
They need to see the Emp 111 in the report output, but they want to show up the current location (10) under location. Here Job location is not viewable, it’s only for filtering.

If the end user select as
Ex.2
EmpLoc ---> 10
JobLoc ---> ALL
It list all emp who are belongs to location 10 and also print 10 under location column. (Its working fine)

And the formula contains only the table field at present, but have to change the conditions in the feature.

If it’s not yet clear, Please feel free to ask question.
Thanks
MK

 

[synapsevampire]

Wouldn't:

&quot;They select the value in the parameter as
Ex.1
EmpLoc ---> ALL and
JobLoc ---> 29
They need to see the Emp 111 in the report output, but they want to show up the current location (10) under location. Here Job location is not viewable, it’s only for filtering. &quot;

Also show Empid 333 as they are location 29? And how do you know the current location, because it's the first row for an EMPID, or is there a date which designates this that you're not sharing.

You're trying to replace technical information with text, which I don't understand.

Demonstrate a sample set of data.

Show parameters passed.

Show what the complete output would be.

-k

 

[rajrev]

Sure I'll. Forget about the prvious examples

Before entering into the report, its good to see the emp. resume first

1. RESUME1
Name : AAA
ID : 111
Location: CA ---(currently working location)
DOB : 01/01/1970
Job Exprence:
ABC Company------>01/01/2003 to Till Date------> CA
XYZ Company------>01/01/2001 to 12/31/2002-----> NY
LKJ Company------>01/01/2000 to 12/31/2000-----> AB
.....

2. RESUME2
Name : QQQ
ID : 333
Location: IL ---(currently working location)
DOB : 01/01/1970
Job Exprence:
CCC Company------>01/01/2002 to Till Date------> IL
BBB Company------>01/01/2001 to 12/31/2001-----> IO
ZZZ Company------>01/01/2000 to 12/31/2000-----> NY
.....

From the resume example,
Employee AAA is Current location is CA
Employee QQQ is Current location is IL

And in the Job Exprence, both resumes are sowing that employee AAA and QQQ had some work exp. in &quot; NY &quot; location.

(For this resume, we used subreport for getting the Job Exprence list )

----------------------------------------------------------
Now consider the tables
1. LookupLocation table
LocId--------LocName
10-----------CA
20-----------NY
30-----------IL
40-----------AB
50-----------IO

2. Employee table
EmpId-------EmpName------LocId
111----------AAA----------10
222----------BBB----------20
333----------QQQ----------30
444----------CCC----------10
555----------DDD----------30

3. JobExp Table
EmpId-------CompanyName------Date From/To-------LocId
111----------ABC Company----- 1/02-till----------10
111----------XYZ Company----- 1/01-12/02---------20
111----------LKJ Company-- --- 1/00-12/00---------40
222----------DDDCompany-- --- 1/00-12/04---------20
333----------CCC Company-- --- 1/02-till ------30
333----------BBB Company-- --- 1/01-12/01---------50
333----------ZZZ Company-- --- 1/00-12/00---------20
444----------ASD Company-- --- 1/01-12/04-------- 10

(here we sorted the table using EmpId and Date From/To fields)
111's current loctation
10
previous locations
20,40

333's current location
30
previous locations
50,20
-----------------------------------------------------
Parameters (multiple selection)
1. CurrentLocation : ALL
2. Exprence Locations: NY,AB
need the output as
EmpName----------CurrentLocation
111---------------CA
333---------------IL

Change the parameter as
1. CurrentLocation :CA
2. Exprence Location :ALL
Output:
EmpName------------CurrentLocation
111------------------CA
444------------------CA

Change the parameter vales as
1. CurrentLocation :IL
2. Exprence Locations:NY,AB
Output:
EmpName----------CurrentLocation
333------------------IL

Need more info plase let me know.
Thanks
MK

 

[rajrev]

Need More Info. Please let me know.
Thanks
Mk