Report Creation Problem 1

[pandy1]

Hi,
I am having a problem in creating a report meeting my criteria:
I am using crystal reports 8.5

The tables I am using for the report are:
1. Employee
2. Assignments

The primary field in the employee table is ID
The primary field in the Assignments table is ID. both are linked.

The Data in the tables are as follows:

Employee table:
ID Name
1 xyz
2 abc
3 aaa
4 bbb
5 ccc
6 ddd

Assignments table data: (It consists of multiple records for each ID and also for each unitid)

Recnum ID unitid closeddate
1 1 100 12/2/2003
2 1 100 1/1/1753 (default date in sql server)
3 1 125 12/10/2003
4 1 126 1/1//1753 (it means no date, i.,e Open Unit)

5 2 100 12/05/2003
6 2 100 1/1/1753
7 2 125 12/4/2003
8 2 126 12/7/2003

The criteria is: The ID should have Unitid 100 (which is a MUST) and any (one or more)of the other units i.e.,125 or 126 and the closed date for the latest record(we can find the latest record by the Record number which is generated by the system)should be 1/1/1753 which is the default date which means the unit is OPEN.

Each ID and unit might end up with multiple records in the Assignments table. But we need to consider the latest record for each unit which is OPEN that means with a closed Date 1/1/1753.

As per the criteria, the data with ID 1 from the above Assignments table should be on the report because it has unit id 100 (with the latest record (recnum:2) open) and also it has unit id 126 with the latest record open.

The data with ID 2 from the above Assignments table should not come on the report because it has unit id 100 with the latest record open but with it does not have any open record with other units i.e., 125 or 126.


I need to get the latest record in each group which has a closed date of 1/1/1753.


Name or ID(employee table) unitid (Assignments table)

Thanks
Pandy

 

[kenhamady]

I have an article that will show you part of what you need, however because you are working at 2 differnt group levels (person and unit) this solution may not work. It may point you in the right direction.

http://www.kenhamady.com/news0408.html

One question, can a unit be open, but NOT be the last record for that unit?

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Tips and Tricks / Guide to Crystal in VB

http://www.kenhamady.com/

- TEK2(AT)kenhamady.com

 

[pandy1]

Yes, a Unit can be Open but may not be the last record for that unit. That means it previously open and now its closed.

Thanks
Pandy

 

[kenhamady]

How many different Unit codes are there. I assume it is not limited to the 3 in this example.

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Tips and Tricks / Guide to Crystal in VB

http://www.kenhamady.com/

- TEK2(AT)kenhamady.com

 

[pandy1]

I have total 6 units to filter for this report. But if I know how to work on, then I may add the other units.

thanks
pandy

 

[kenhamady]

Try this:

1) Write a formula called {@106} that is:

if {code} = '106'
then
ToText ({recnum} , "00000") +
ToText ({Date} , "yyyy-MM-dd")
else ""

2) Add a similar formula for each code you are targeting.

3) Make sure you are grouped by ID
4) Write a GROUP selection formula that says:

Maximum ({@100}, {ID}) [ 6 to 15 ] = "1753-01-01"
and
( (Maximum ({@106}, {ID}) [ 6 to 15 ] = "1753-01-01" or
(Maximum ({@107}, {ID}) [ 6 to 15 ] = "1753-01-01" or
....)




Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Tips and Tricks / Guide to Crystal in VB

http://www.kenhamady.com/

- TEK2(AT)kenhamady.com

 

[pandy1]

I added(putting all other units etc.,)the same way you explained, When I save the group selection formula I am getting the error :"A number, currency amount, boolean, date, time, date-time or string is expected here". The data types are:

Unitid - smallint
recnum - decimal
closeddate - datetime 8 default '01/01/1753'
ID - decimal.

thanks
pandyh

 

[pandy1]

sorry, that error is because of brackets...I fixed it..

 

[pandy1]

Thank you Ken Hammady, Its brilliantly working. I will let you know once I am done with complete testing.

once again thank your very much for your help.

pandy

 

[pandy1]

I tested it, its not picking up all the records. for example I have an employee with unit 100, and unit 125, with date closed is 1/1/1753. Its not picking up that record. It has each record for each unit which are latest with date closed is 1/1/1753. there are no records with others units ie., 126 etc.,

thanks
pandy

 

[pandy1]

I think its not picking up all the records because we have only group i.e, ID but we are comparing each unit in that ID group. Because we have group selection formula, so its calculating for the whole ID group instead of each unit. Please help me in this report.

thanks
pandy

 

[kenhamady]

That is why we have a separate formula for each code, so that we can select the whole ID group based on that criteria.

Post your Group selection formula and you formula for code 100.

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Tips and Tricks / Guide to Crystal in VB

http://www.kenhamady.com/

- TEK2(AT)kenhamady.com

 

[pandy1]

group selection formula:

(Maximum ({@NewUnit100}, {ID}) [ 6 to 15 ] = "1753-01-01")
and
( (Maximum ({@NewUnit125}, {ID}) [ 6 to 15 ] = "1753-01-01") OR
(Maximum ({@NewUnit126}, {ID}) [ 6 to 15 ] = "1753-01-01") or
(Maximum ({@NewUnit127}, {ID}) [ 6 to 15 ] = "1753-01-01") or
(Maximum ({@NewUnit128}, {ID}) [ 6 to 15 ] = "1753-01-01") or
(Maximum ({@NewUnit129}, {ID}) [ 6 to 15 ] = "1753-01-01")
)

formula for unit 100:

if {UNIT_ID} = 100
then
ToText ({RECNUM},"00000") +
ToText ({DATE_CLOSED},"yyyy-MM-dd")
else ""


Thanks
Pandy

 

[kenhamady]

It looks correct. Are both of these an exact copy? I don't see any table names in the 100 formula.


Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Tips and Tricks / Guide to Crystal in VB

http://www.kenhamady.com/

- TEK2(AT)kenhamady.com

 

[pandy1]

No, I added table names to the original formula. I am unable to understand why it is not picking up all the records.

Thanks
Pandy

 

[kenhamady]

Would you copy and paste the exact formulas.

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Tips and Tricks / Guide to Crystal in VB

http://www.kenhamady.com/

- TEK2(AT)kenhamady.com

 

[pandy1]

Yes, I copied the exact formulas.

 

[kenhamady]

Sorry, I meant would you copy your exact formulas from the report and post them here.

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Tips and Tricks / Guide to Crystal in VB

http://www.kenhamady.com/

- TEK2(AT)kenhamady.com

 

[pandy1]

Yes, But I have taken out the table name in the formula.

Thanks
Pandy

 

[kenhamady]

Sorry, I don't know why they don't work.

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Tips and Tricks / Guide to Crystal in VB

http://www.kenhamady.com/

- TEK2(AT)kenhamady.com