printing report if no corresponding data is in a file 1

[kimprogrammer]

Hello
I was wondering if I could get some help understanding the approach I need to create a report.

I have an employee master file and a paycode file that data is entered for every payroll.

I need a report that lets me know which employees have not has data entered for them. So I need records on the employee master that have not records in the paycode file.

I'm not sure how to begin with this and would appreciate the help.

 

[kskid]

I assume that the link between the Employee Master and Paycode file is employee_id.

With the Employee Master file on the left, do a left outer join with the paycode file on employee_id

In your record selection include the following selection

isnull({paycode.employee_id})

You may need an additional date selection if the paycode file contains a paycode record for each pay period.

 

[lbass]

The problem will come in when you want to specify a date range. You could use no selection criteria on your paycode table, and then add a the employee name field to the detail section. Create a formula {@inrange} like this:

if isnull({paycode.employeeID) or
not({paycode.date} in {?daterange}) then
0 else 1

Insert a group on {employee.ID} and then go to report->selection formula->GROUP and enter:

sum({@inrange},{employee.ID}) = 0

Or you could use a command as your datasource, and set it up like this:

Select employee.`name`
from employee left outer join paycode on
employee.`ID` = paycode.`employeeID` and
paycode.`date` >= {?startdate} and
paycode.`date` < {?enddate}+1
where paycode.`employeeID` is null

You would then just need to add {command.name} to your detail section.

-LB

 

[kimprogrammer]

Thank you for two different ways to do this - I choose to do it with the command(just because I havn't tried this before) I am new to Crystal Reports

But I do get an error when I try to save my command. It says failed to retrieve data - no value given for parameters. I assume it is pointing to the ?startdate because I am prompting the user to enter start date. But is there something else I need to do?

Here is the select statement:
--------------------------------------------
Select CPY10100.PEmployeeID
from CPY10100 left outer join SwipeAccumulativeHours on
CPY10100.EmpNum = SwipeAccumulativeHours.EmpNum and
CPY10100.PInactive = 1 and
SwipeAccumulativeHours.PPDay >= {?startdate} and
SwipeAccumulativeHours.PPDay < {?startdate}+13
where SwipeAccumulativeHours.EmpNum is null
sorted by CPY10100.PDepartment

 

[kimprogrammer]

Also my data in my parameter comes from a PayPeriod Start Date table.

 

[lbass]

First, I'm assuming you created the parameter within the command (on the right). Then for the command to compile, you must enter a date. I'm not sure what went wrong here, so it would help if you detailed the steps you took. You say the data in your parameter comes from another table, but a parameter is user entered, so I don't know what you mean.

-LB

 

[kimprogrammer]

In the add command to report I put the select statement on the left. But when I went into Command Parameter I didn't add anything. It gives me 4 boxes to enter. (Parameter Name,Prompting text, value type and default type) but I wasn't sure how this connects to the PayPeriodStartDate Parameter I created under the Parameters Field in the Field explorer. This is where I'm prompting the user to select the payperiod they wish to print which is a table that I use to populate the dropdown box.

 

[lbass]

You should create the parameter within the command. In fact, you should have no tables or record selection formula in the main report. Your command should hold all fields that you need. Just add the End Date name in the parameter creation area, add the prompt text, and change the datatype to date. Don't add a default. Then click OK, and select a date so it can compile. Then in the main report, add the employee field to the detail section. Go into the field explorer->parameters, and you will see the {?End Date} parameter listed.

Add a second command to the report that contains the dates for your dropdown list, as in:

Select PayPeriod.StartDate
From PayPeriod
Where //etc.

Select {command_1.startdate} to populate the dropdown box within the edit parameter screen. Do not link the commands to each other in the linking screen.

Note that if you go back into a command to edit it, the parameter will lose its dropdown list of values, and the list will have to be recreated.

-LB

 

[kimprogrammer]

Thank you for your help. I changed it to the first option you gave me since I had all the parameters set up already(and it works great). I will try doing it the other way when I have more time to look at.

 

[kimprogrammer]

I checked my data closer and it is not quite working:
----------------------------------------------------
If I use the following code I get the employees who have data entered on the file.
if isnull({SwipeAccumulativeHours.EmpNum}) or
not({SwipeAccumulativeHours.PPDay} <= {PPStartDate.PPStart} and {SwipeAccumulativeHours.PPDay} >= {PPStartDate.PPEnd}) then
0 else 1
----------------------------------
But If I
if isnull({SwipeAccumulativeHours.EmpNum}) or
({SwipeAccumulativeHours.PPDay} <= {PPStartDate.PPStart} or {SwipeAccumulativeHours.PPDay} >= {PPStartDate.PPEnd}) then
0 else 1

I get two of the employees who showed up in the first example on this report as well.
---------------------------------------------
I've tried all the combinations of using not or removing it, and's and or's, and played with the<= signs and I either get the whole list of the employees that have data on the file, the two showing up that have records or no records at all. I should have 4 employees on the list that were not on the first list.

 

[lbass]

Your first formula is incorrect, but the second one should work. Did you add the group selection formula? What was your record selection formula?

-LB

 

[kimprogrammer]

This is my record select: I also have a prompt to print by division and PInactive is for active employees on the masterfile
----------------------------------------------
{PPStartDate.PPStart} = {?PayPeriodStartDate} and
{SwipeAccumulativeHours.PPDay} >= {?PayPeriodStartDate} and
{SwipeAccumulativeHours.PPDay} <= {PPStartDate.PPEnd} and
{CPY10050.PClass} = {?Division} and
{CPY10100.PInactive} = 0
------------------------------------------------------

I do not have a group selection

 

[lbass]

You implemented my suggestion incorrectly--it REQUIRED group selection to work.

You also are referencing tables here that you did not mention earlier, and I have no idea how you are linking to them or how it affects the results. Please identify all tables and how you are linking to them. Also clarify the actual name of the paycode table.

-LB

 

[kimprogrammer]

I'm sorry I thought I have all the info - and I went back to your example and I thought I remembered putting in the "sum({@inrange},{employee.ID}) = 0" as you requested.

Here is all the information;

I have prompting as follows;
First Prompt - Payperiod Start Date:
I get this from file:
PPStartDate - the fields I use are PPStart,PPEnd.

Second Prompt - Division:
File name: CPY10050
Fields PClass(this is the key)
PDescription - division name
Note: I have a command set up for this so the user can run the report for all divisions; it uses PClass

SELECT PCLASS FROM CPY10050
UNION
SELECT '...ALL' FROM CPY10050

--------------------------
The employee master table is -
CPY10100
Fields I use: PEmployeeID,PInactive,PLastName, PFirstName
---------------------------------
The paycodes table is
SwipeAccumulativeHours:
Fields I use: EmpNum,PPDay(this is the date the paycode was created)
-----------------------------
Command PCLass is linked to CPY10050 by Pclass

SwipeAccumulativeHours.EmpNum is linked to CPY10100.PEmployeeID

CPY10050.PClass is linked to CPY10100.PEmployeeClass

PPStartDate is not linked to anything

----------------------------------
This is my record selection (note: I havn't figured out how to put "...All" in the code so the user can print all of the divisions)

{PPStartDate.PPStart} = {?PayPeriodStartDate} and
{CPY10050.PClass} = {?Division} and
{SwipeAccumulativeHours.PPDay} >= {?PayPeriodStartDate} and
{SwipeAccumulativeHours.PPDay} <= {PPStartDate.PPEnd} and
{CPY10100.PInactive} = 0

-------------------------
No Group selection at the moment (as I mentioned at the beginning I though I had added it)
--------------------------
On the report I'd like to print EmpNum,PlastName,Pfirstname
----------
And I'd like to have a total of employees printed at the division level and the payroll start date level.
--------------------

I think that's all the information and I havn't overlooked anything

 

[lbass]

I will assume that all your joins are equal joins except you should have a left join FROM DPY10100 to Employee. Then do the following:

1) Change the record selection formula to:

(
{?Division} = "All" or
{CPY10050.PClass} = {?Division}
) and
{CPY10100.PInactive} = 0

You cannot reference any fields from SwipeAccumulativeHours in the record selection formula, because that will in effect "undo" the left join that you need to get all employee records. You also should not be referencing the PPStartDate table--just use that unlinked table to populate your PayPeriodStartDate pick list.

2) Next go into the field explorer->formula->new->name it {@inrange} and enter:

if isnull({SwipeAccumulativeHours.EmpNum}) or
{SwipeAccumulativeHours.PPDay} < {?PayPeriodStartDate} and
{SwipeAccumulativeHours.PPDay} > {?PayPeriodStartDate} + 13 then 0 else 1

3) Insert a group on {CPY10100.PEmployeeID} and then go to report->selection formula->GROUP and enter:

sum({@inrange},{CPY10100.PEmployeeID}) = 0

4) Place the employee fields in the detail section.

You should now see only those employees with no recorded hours during the selected pay period. If you have a group on division, it should be group #1, with the employeeID as group #2. To get totals at the division level, you must use a running total that does a distinct count of {CPY10100.PEmployeeID}, evaluate for each record, reset on change of division. For the grand total (pay period level), create a second running total with reset = never. You cannot use regular inserted summaries, because they would include non-group selected records.

-LB

 

[kimprogrammer]

I have this in (later I'm going to have to spend some time uderstanding how all this works)

I have a couple issues but I pretty much think it works.

I have the missing employees on the report for the payperiod I was working with. The only thing is, the way I had things before I got the correct Payperiod Start Date and End date in the Page header. Now what prints is the first entry in the PPStartDate table. And when I changed the date in the prompt to one where I've entered no data for any employee; I expect to see them all printed - but the same two employees show.

Also with my fields in the detail - I see multiple displays for the same employee. I can fix this by dropping them down into the group footer - But is this an appropriate way of doing this.

 

[lbass]

In the page header, use a formula like this:

totext({?PayPeriodStartDate},"MM/dd/yyyy") + " to "
totext({?PayPeriodStartDate}+13,"MM/dd/yyyy")

Note that you should NOT be referencing the PPStartDAte table anywhere in the body of your report--onlly use this to create your date picklist.

Yes, all employees should show in the instance you describe. Make sure you don't choose "use saved data" when you refresh the report.

Sure, put the fields in the group header or footer and suppress the detail.

-LB

 

[kimprogrammer]

I was hoping I would not have to ask any more questions.
I'm still can't get the data to change when I select a new date. PPStartDAte table is not on my report. When I hit the refresh button, I select prompt for new paramter option and choose a different date. I still get the same 2 employees no matter what date I choose.

I know the box that pops up and asks to use saved data - it has popped up in other report I've written but is not with this one.

 

[lbass]

Can't help unless I know exactly what you did.

-LB

 

[kimprogrammer]

From the previous instructions you gave;

I linked the CPY10100.PEmployeeID to SwipeAccumulativeHours.Empnum - I did this by clicking on PEmployeeId and dragging it to Empnum so the arrow is pointing to Empnum (on the link diagram). In the link options I have left outer join selected - Not Enforced - Link type is =.

All the others are inner joins - not enforced and =.
-----------------------------
The record selection says
(
{?Division} = "All" or
{CPY10050.PClass} = {?Division}
) and
{CPY10100.PInactive} = 0
-------------------------------
The group selection says
Sum ({@InRange}, {CPY10100.PEmployeeID}) = 0.00
--------------------------
The InRange field I created under the formula fields says
if isnull({SwipeAccumulativeHours.EmpNum}) or
{SwipeAccumulativeHours.PPDay} < {?PayPeriodStartDate} and
{SwipeAccumulativeHours.PPDay} > {?PayPeriodStartDate} + 13 then 0 else 1
---------------------------------
My Employee fields I want printed are in GF2 - I have GH2 and detail suppressed.
----------------------------
GH1 is connected to CPY10050.PClass - which is the division
GH2 is connected to CPY10100.PEmployeeID - masterfile
-----------------
The running totals have been set up - they look like the work ok.
I created 2 formulas to print the start date and in date from the parameter - this are working ok.
----------------------------
I press the refresh button and change just the date of my prompt. And the data stays the same.