Problem in Creating Report

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

Please help me in creating this report.

Thanks
Pandy

 

[AndersonCJA]

From what I understand about what you need, I would do the following:
1. Create a forumula field that seperates the year from the close date.
2. create a formula field to initialize a numbervar in the unit id group.
3. create a formula using the numbervar that will increase the variable by 1 each time the formula field = 1753.
3. suppress the detail section when the count is less than 2.

Does this information help? Do you need help on how to create the fields and where to place them?

 

[pandy1]

Thank you Anderson,

But by creating the formua and counting, will I get the latest record in each group which has a closed date of 1753?

Can you show me how to create them and where to place them? and also I need to display the NAME field from the employees table.

the report should look like this:

Name unitid

Thanks
Pandy

 

[AndersonCJA]

I can see now that I did not understand the facts clearly the first time. To make sure I understand, please tell me if my summery is correct.

01. If an employee has a unit number 100 (date not significant for unit 100)
and any other unit for that employee has a close date of 1/1/1753 (the close date will always be 1/1/1753)
And the record has the highest record number for that employee id, then keep the record.

 

[AndersonCJA]

Sorry, I should have said the default date will always have the same month year and day being 1/1/1753 when it is the default date. I understand the close date is not always 1/1/1753.

 

[pandy1]

I will explain once again.

01. Employee should have a unit 100 (the latest record should be Open in unit 100, i.e., date should be 1/1/1753, date is significant)

and any other unit(125,126)latest record has a closed date of 1/1/1753.

The latest record means not for the whole employee record, its the latest in each unit group.

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

when you see the assignments table above, the latest record for employee ID 1 in unit 100 is Open.
and also the latest record for employee ID 1 in unit 126 is Open. so that employee should be printed on the report.

it is not meeting the criteria for employee 2. it should not print in the report.

Hope I am clear.

thanks
pandy

 

[AndersonCJA]

groups should look like this
group 1 empId
group 2 unitid
group 3 Rec no

I am using the Everything You Need Know about runing Totals documentation that I printed from a site from Crystal reports.

Create the number fields.

field 1 to initial first counter
WhilePrintingRecords;
Numbervar RuningTotal1;
RunningTotal1 :=0;

field 2 to initial 2nd counter
WhilePrintingRecords;
Numbervar RuningTotal2;
RunningTotal2 :=0;
Place both fields 1 and 2 in group header 2

field 3 = counts when unitid = 100
WhilePrintingRecords;
Numbervar RuningTotal1;
if unitid = 100
then
RunningTotal1 := RunningTotal1 + 1;

field 4 = Counts when unitid ne 100
WhilePrintingRecords;
Numbervar RuningTotal2;
if
unitid <> 100
then
RunningTotal2 :=RunningTotal2 + 1;
Place fields 3 and 4 in the detail section

field 5 = displays the variable

WhilePrintingRecords;
Numbervar RuningTotal1;
RunningTotal1;

field 6 = displays the variable
WhilePrintingRecords;
Numbervar RuningTotal1;
RunningTotal1;

Place these fields in group 2 footer and format the fields to hide them.

Format the detail section to hide.

Place the Empname and Unit id field in group footer 2.

format group footer 2 by right clicing in the left side in group footer 3. select format section, click in the suppress box, click on the x-2 box.

formula that fits the purpose,
we want to suppress when field 3 = 0 or
field 3 gt 0 and field 4 = 0.

Please let me know what happens.

 

[pandy1]

Thank you AndersonCJA. I did not understand completely. I created 3 groups as you said. Then you said create the number fields. what do you mean by that? and also you used RunningTotal1, RunningTotal2 etc., what are those RunningTotals?

Can you please explain?

thanks
pandy

 

[AndersonCJA]

http://support.businessobjects.com/communityCS/TechnicalPapers/docs_development.pdf

Try this link and search on number variables for a technical explaintion.

Basically we have two number variables the first on is called RunningTotal1 and the second one is called RunningTotal2.

There are three steps to use when creating variables,
Initializing, calculating, displaying

We initialize the number variable in the header section in emp id so that each time the emp id changes the variable goes back to zero so we start a fresh count.

We are using RunningTotal1 to count the number of times an employee id has a unit id of 100 because if there are none, we do not want to see the record.

Second we are using RunningTotal2 to determine if there is (for the same employee) a unit id that is not 100 and there is a closedate of 1/1/1753
By the way, field 4 should be changed to:


WhilePrintingRecords;
Numbervar RunningTotal2;
if
unitid <> 100 and closingdate = date(1753,1,1)
then
RunningTotal2 :=RunningTotal2 + 1;

Then we will check our fields (field 3 and field 4)
If there is at least one record with a unit id of 100, and there is also a record that is not 100 but it has a date of
1/1/1753 than we want to see it.

First start with creating and placing the formula fields, you can copy and paste the fields I created, replacing the text unit id with your field ect. Be careful of my typos, the variable names should either be RunningTotal1 or RunningTotal2.
Also, the punctution is important, the placement of the :, = and ; is important
I apologize if I skip through information, I am giving this my best try. Good look and keep me posted.

 

[pandy1]

Thank you Anderson for your help, I tried another method and got the report. I will try this also later.

pandy