Report showing data NOT in a table 1

[CJSilver]

We do labor tracking on the items we manufacture.
I have a table called Actual:
Date Job# Start Time Badge# Stat_Flag
12/16/04 24629 09:07 1060 Started
12/16/04 24658 06:42 1092 Paused
12/16/04 24667 10:28 1114 Finished
12/16/04 24654 14:13 1092 Started

Another Table Called Production_Employees
Employee Badge#
John Ramirez 1060
Stacy Keys 1092
Joe Franks 1114
Eren Phillips 1129

I need to write a report that shows who is NOT started on any job on the current date.

So with the above example I would be looking for:

Employee
Joe Franks
Eren Phillips

The way I have tried to accomplish this is by linking the two tables by Badge number with Production_Employees not equal to Actual. In the selection expert I have:
{@dateconversion}=currentdate and
{Actual.STAT_FLAG} = "STARTED"
//@dateconversion is converting the date field which is a CHAR in the SQL table, to a date.
IN the detail of the report all I have is Employee.

WHen I run the report it is not showing me what I want to see, it shows me everyone in my Production_Employee table duplicated over and over many times. I added job# to my report to get an idea what was happening and it seems that it is showing me every order that was clocked into on the currentdate but had a status that was paused or finished. Then it is duplicating the order for every person that did not work on it.
Using the above example this is what I am seeing:

Employee Job#
John Ramirez 24658
Joe Franks 24658
Eren Phillips 24658
John Ramirez 24667
Stacy Keys 24667
Eren Phillips 24667

I am using Crystal Reports 8.5

Any suggestions on how I can get this report accomplished?

 

[Turkbear]

Hi,
Why Not Equal on your link?

If you link
Production_Employees
to
Actual

Using the Badge # ( an EquiJoin )

and then use
{@dateconversion}=currentdate and
{Actual.STAT_FLAG} <> "STARTED"

as your formula - then you should only
see employees whose status is anything other than STARTED for that date. ( one row for each record that matches their badge number and meets the criteria)..

 

[CJSilver]

Thanks for the reply TurkBear but that is not what I am looking for. Basically I am looking for data that may not be in the actual table at all. If you look at the last badge# in the Production_Employee table 1129, that is not in the Actual table at all so any type of equal join as far as I can see would not work.
I am looking for employees not "started" on a job, that does not mean that they are paused or finished, they may not have worked on any job on that day.

 

[lbass]

I think you have to use a left join from Production_Employees to Actual, linking on the badge number. You cannot use any selection criteria on the Production_Employees table. Group on {Production_Employees.BadgeNo}. Then create a formula {@metcrit}:

if isnull({Actual.Stat_Flag}) or
(
{Actual.Stat_Flag} <> "STARTED" and
{@dateconversion} = currentdate
)
then 0 else 1

Then go to report->edit selection formula->GROUP and enter:

sum({@metcrit},{Production_Employees.BadgeNo}) = 0

You can suppress the details section and just display the group header for the badge number.

-LB

 

[CJSilver]

Thank you for the reply lbass. I tried what you suggested and it still is not giving me what I am looking for. Nothing shows up on the report at all, but there are two people not here today so they should be showing up.

I removed the selection expert criteria that I had in the report originally. joined Production_Employees.[badge number] by a left outer join to actual.[badge number]
Created a group on production_Employee.[badge number]
Created @metcrit
added to the group selection formula editor
Sum ({@metcrit}, {Production_Employees.[Badge Number]}) = 0

But the report is blank.

 

[Madawc]

Link using a left join from Production_Employees to Actual, as lbass suggested.

On selection criteria, exclude everything except 'Started' for the current date.

Suppress employees unless the employee number for 'Actual' is null. Right-click on the detail section and choose Format Section. Then choose the formula icon (x+2 and a pencil) for suppression.
Test not isnull({Actual.BadgeNo}).
This should give you just those employees who have not started for that day.

Madawc Williams (East Anglia, UK)

 

[CJSilver]

Hello Madawc. I tried your suggestion and the suppression does not work correctly. As soon as I put
not isnull ({actual.[badge number]}) to suppress the detail section I loose all data.
At this time we are closed so nobody is started on a job, so everyone should be showing up on my report. But nothing shows up when I put the suppression in. I believe it is because you are asking for null values in the actual table, but there is no entry in the actual table at all. I do not think that is considered a null value.

 

[kskid]

When selecting an element from a left outer join, you have to include the null

Try this
(
isnull({actual.{badge_no}) or
{Actual.Stat_Flag} <> "STARTED"
) and ....

This should give you everyone that has not started a job

 

[lbass]

To troubleshoot my suggestion, try placing the formula I suggested in the detail section without using the group selection, and then report back on what is happening for the people who you know are not present.

Make sure that your link is FROM the Production_Employees to Actual (not the reverse), that you have no record selection criteria, and also make sure that in file->report options you do NOT have "Convert null values to default" checked.

-LB

 

[CJSilver]

Wichitakid, Thanks for replying.

The problem is that everyone not equal to started is not necessarily what I am looking for, I am looking to find out who is not currently working on a job, someone could have worked on a job then finished it and started a second job, since they are started on a job I do not want to see their name, but by saying or <> "started" I am going to get their name since they do have a job that is finished. Getting all nulls is what I am looking for, but when I try that the report is blank.

 

[CJSilver]

Thank you for your help lbass,

I have nothing in the selection criteria, convert null values to default was not checked, I put @metcrit in the detail section. I got a report 193 pages long, it took everything in the actual database that was not "started", but it took any date, not just todays. I put the job# in so that I could get an idea what was happening, the only places 0 showed up was on jobs with the currentdate, and stat_flag not equal to "started".
I did not get any null values at all.

The problem seems to be that it is only giving me data that exists in the actual table, I am not getting any null records. I have 2 people that are out today, so they should be turning up on the report for today. When I look at their badge number they show up many times, but all are for jobs paused or finished prior to today. They do not show up at all with zeros only ones.
One thing I want to make sure you understand, just because a person has a job that has a status of "paused" or "finished" does not necessarily make it a name that should show up on my report. I want people that are not started on a job, period. They may have finished one job and started another job today, so I don't want that person to show up. If they paused or finished a job and have NOT started a job yet, then I do want them to show up.

Craig Johnson
Silverado cable

 

[Davest11]

Do you want to see only employees where the most recent entry for them is not "Started"? In other words, you want them in the report if their most recent entry is "Finished" or "Paused"?

If so, I suggest grouping by Production_Employees.[badge number] and sorting by the DateTime field. The value of {Actual.STAT_FLAG} when you get to the group footer will be the most recent value for that Employee.
You can then put your output in the group footer, suppressing where {Actual.STAT_FLAG} = "STARTED".

 

[CJSilver]

Hello David56765, as I stated in the last two posts I am not necessarily looking for employees that are paused or finished, I am looking for ones that are NOT started.

 

[lbass]

Please change {@metcrit} to:

if isnull({Actual.Stat_Flag}) or
not(
{Actual.Stat_Flag} = "STARTED" and
{@dateconversion} = currentdate
)
then 0 else 1

Then use the group selection formula I suggested earlier. You can then suppress the details and display only the names at the group level.

-LB

 

[CJSilver]

lbass, the first problem is that I am not getting any null values, changing your formula is not going to fix that. The other issue is, the way you have changed the formula you are saying, anything not started and currentdate is zero, that is giving me any order that is paused or finished as long as it's not from today. That's not what I am looking for.

It seems to me the way to accomplish this is to take the data from the actual table that I am using for my report i.e.
stat_flag = "started" and @dateconversion = currentdate

then I want anything where the production_employees table is not equal to the actual table.
But when I try to set it up this way it doesn't work, I have the same problem as we are having now with your suggestions. I get only data that is in the actual table, no null values.
My joins are going from production_employees to actual.

When I try it per lbass's suggestions I have a left outer join from production_employees.[badge number] to actual.[badgenumber]

 

[synapsevampire]

I think that you need a subreport here, unless you can generate real SQL in a View or SP.

Main report: Production_Employees
Record selection: date = currentdate
Group by Badge
Formula in Group header:
whileprintingrecords;
shared Booleanvar IsGood := false;


Subreport: Actual (In the Group Footer A section, right click and create an additional group section for display)
Record selection:
Stat_Flag = "Started"
and
date = currentdate

Link by badge

Create a formula in the subreport details which has:
whileprintingrecords;
shared Booleanvar IsGood;
If not(isnull({table.badge})) then
IsGood := true
else
IsGood := false

Now you can reference the IsGood variable to suppress the display (right click the section and select format section, X2 next to suppress using):
whileprintingrecords;
shared Booleanvar IsGood

Should get you there.

-k

 

[lbass]

CJSilver,

Please note that if an employee works for you, you will never have a null for him/her in the actual table, so looking for nulls isn't the answer. I thought you wanted a list of people who did not have a start on today's date. Using my second suggestion should work. Everyone who has not had a start on today's date will have detail records for other dates or of other types. The point is to get their names on your list, not to display the detail records. Please try the second suggestion before ruling it out.

-LB

 

[CJSilver]

<chuckles> I happily apologize lbass, you are quite right. I had tried it before I posted last, but when I saw 18 pages of names I figured it had not worked. But the names repeating over and over are the names of the people not started on a job. As you said earlier, I can suppress the detail, so that takes care of the 18 pages.

The only senario I see that this would not work on is if we had a new employee and they missed their first day so no data would exist in the actual table. Then I would need to do something like synapsevampire suggested. But I can live with that risk.

Thank you for your help!

Craig Johnson
SIlverado Cable

 

[lbass]

Craig,

If you have a new employee, then the isnull({Actual.Stat_Flag}) will pick them up as long as they are in the Production_Employees table.

-LB