Crystal Grouping/Selection problem - this is truly impossible

I think that I am toast, but here is my problem:

I am a payroll company.  Any given employee might work for two or more clients each week.  All of those clients are paid to the employee on the same payroll check.  So, Bob works for client 1234, 4567, and 8912 in the same week.  All of those hours for all of those clients are paid on the same payroll check.

My payroll check print file groups by client company.  Therefore, Bob's check - the exact same check - will appear in all three groups (once for each client).  You can imagine the problems.

Now, I cannot change my groupings - I have to group by client for a variety of reasons.  What I want to do is the following in my selection:

Add a filter that says:  If a distinct check has multiple client numbers, suppress (or simply do not select) everything except the lowest value client.  In other words, in the example above, ONLY select the check (or ONLY group with) for client 1234 (all clients are numerical).

Can this be done?  I have tried everything and I have asked ever CR writer that I know.  You are my only chance at this.

Thanks
Daniel
dcox513Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

David Johnson, CD, MVPOwnerCommented:
I am a payroll company.  Any given employee might work for two or more clients each week.  All of those clients are paid to the employee on the same payroll check.  So, Bob works for client 1234, 4567, and 8912 in the same week.  All of those hours for all of those clients are paid on the same payroll check I can't think of a way to issue just one check.  The only way is to issue a check for each client the employee works for.  This could mean many checks for each employee.  Is there a way to work in the rate of pay and have multiple areas? Each rate of pay in this instance would be exactly the same. Instead of having:
Rate 1 = 10.00
Rate 2 = 10.25
Rate 3 = 10.50
Make them all 10.00 and assign each client a different rate of pay field.
0
dcox513Author Commented:
You are correct.  I cannot have multiple checks.  And, more often than not, Bob makes the same rate of pay at each job.

What bothers me is that I cannot find a sql statement to accomplish this in each group:

If this check is attached to multiple company numbers and this group is not the lowest numerical value of the company numbers attached to this distinct record, suppress it.
0
James0628Commented:
There is a Crystal Reports section here.  If you're doing this in CR, you should add the CR section.  I don't know if you can add a section after you post a question.  If not, you could probably use the "Request attention" link to ask a mod to do it for you.

 A variable would be one option.  Assuming the check numbers are unique, you could save each new check number in an array.  If a check number is already in the array, that means it showed up earlier in the report, in which case I guess you'd suppress the appropriate section(s) of the report, so that the check wasn't printed a second time.  You might want to add a note or something, explaining that the check was printed with an earlier client's data.  If you wanted, you could even use a second array to store the number/name for the first client with each check number, and then show that when the check number came up again (eg. "Check # 1234 was printed with client 5678").

 How many check numbers could there be in one report?  CR arrays are limited to 1000 elements, so if you could have more than 1000 checks in a single report, you'd have to work around that limit (eg. use multiple arrays).

 Assuming that the report won't have more than 1000 checks ...

 Create the following formula (call it whatever you like) and put it in the report header:

WhilePrintingRecords;
Global NumberVar Array check_number;
Global NumberVar check_count;

// Start check_number with 100 elements
Redim check_number [ 100 ];

""

Open in new window

The "" at the end is partly so that the formula doesn't produce any visible output on the report.  You could also suppress that field, or the section.  But CR also doesn't like formulas to produce an array result, which is what you get from the Redim, so you need some simple value after that Redim statement.


 If you only have one section to suppress when you don't want to print a check, you could use a formula like the one below as the suppression formula, to see if a check number is already in the array, and then add it, if it's not.

WhilePrintingRecords;
Global NumberVar Array check_number;
Global NumberVar check_count;

Local BooleanVar check_printed;

check_printed := {check # field} in check_number;

if not check_printed then
(
  check_count := check_count + 1;
  if check_count > UBound (check_number) then
  (
//  Add 10 elements to the array at a time, just because
// that's probably more efficient than adding 1 at a time
    Redim Preserve check_number [ check_count + 9 ];
    ""
  );
  check_number [ check_count ] := {check # field}
);
// Output the result of the earlier array check, for the suppression condition
check_printed

Open in new window


 The "" after the Redim is just because CR doesn't like an if-else to end with an array, so I put an empty string after the Redim.  A number or True/false or any other single value should work just as well.

  If you have multiple things to suppress, the simplest thing would be to split the tasks -- Have one formula that just checks to see if a check number is already in the array, and then, somewhere in a section below that, have another formula that would add a new check number to the array (obviously you need to do any checks on the array contents before you add a new number to the array).

 James
0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

mlmccCommented:
What tables do you have?

How are you getting an employee into multiple groups with another group number?
Are you using a subreport to gather the employee data?

Does an employee have separate records based on the client?

How many employees?

Can you build a view or stored procedure in the database?

mlmcc
0
dcox513Author Commented:
Couple of things:
1)  No, there would never be more than 1000 checks in a print file
2)  The checks themselves are created by a stored procedure.

So, the timesheet data is keyed into the software.  You run a stored procedure (code to which I do not have access) and all of the data required to print a check is created.  Each "check" created by the stored procedure is given the same "Process ID".

That process ID is the parameter for the CR that is the actual print file.

The report is pulling from the views created by the stored procedure - all of the gross pay, withholdings, and net pay PLUS the timesheet data (timesheet data is Client Number and hours worked for that client).

It is then grouping by the client number.  As you can see, if there are multiple client numbers, the check is going to appear in multiple groups (same exact check under each client number).

Now, within the selection, I suppose that I could create the array suggested above, but that exceeded my CR/sql knowledge.
0
David Johnson, CD, MVPOwnerCommented:
Then you have to change your stored procedure.. and then not print by Customer Number. and instead by Employee Number. Where you add up the # of hours by each customer number to then get a Total Hours * Rate = Gross Pay. Now do your tax calculations and if Gross Pay > 0 print the check.
0
dcox513Author Commented:
If only,,,  I cannot make changes to the code - 3rd party software and they steadfastly refuse.

But logically, I don't understand why Crystal cannot accomplish the following:

Grouping checks by client means that Crystal is going to pull each check that is associated with Client 1234.  Now, as Crystal is pulling that record, Crystal is going to check to see if any other client numbers are associated with that check.  If there are, suppress for this Group unless this client number is the lowest numerical value client associated with the check.

This is the full sql query:

 SELECT DISTINCT "RV_CHECK_MAIN"."PROCESS_GROUP_ID", "RV_CHECK_MAIN"."CHECK_ID", "RV_CHECK_MAIN"."CHECK_NUMBER", "RV_CHECK_MAIN"."FIRST_NAME", "RV_CHECK_MAIN"."LAST_NAME", "RV_CHECK_MAIN"."SOCIAL_SECURITY_NUMBER", "RV_CHECK_MAIN"."GROSS_AMOUNT", "RV_CHECK_MAIN"."TOTAL_WITHHOLDINGS", "RV_CHECK_MAIN"."TOTAL_DEDUCTIONS", "RV_CHECK_MAIN"."NET_AMOUNT", "RV_CHECK_MAIN"."CHECK_DATE", "RV_CHECK_MAIN"."EMPLOYEE_ID", "RV_CHECK_MAIN"."BRANCH_NAME", "RV_CHECK_MAIN"."CHECK_HANDLING", "RV_CHECK_MAIN"."CHECK_TYPE", "RV_CHECK_MAIN"."YTD_HOURS", "RV_CHECK_MAIN"."YTD_GROSS_WAGES", "RV_CHECK_MAIN"."CORPORATION_ID", "RV_CHECK_MAIN"."ADDRESS_LINE2", "RV_CHECK_MAIN"."ADDRESS_LINE1", "RV_CHECK_MAIN"."CITY", "RV_CHECK_MAIN"."STATE", "RV_CHECK_MAIN"."ZIP", "RV_CHECK_MAIN"."MIDDLE_INITIAL", "RV_CHECK_MAIN"."SUFFIX", "RV_CORPORATION_BANK_ACCOUNT"."ROUTING_TRANSIT_NUMBER", "RV_CORPORATION_BANK_ACCOUNT"."ACCOUNT_NUMBER", "RV_CHECK_TIMESHEET"."COMPANY_NAME"
 FROM   "SSUITE"."RV_CHECK_MAIN" "RV_CHECK_MAIN", "SSUITE"."RV_CORPORATION_BANK_ACCOUNT" "RV_CORPORATION_BANK_ACCOUNT", "SSUITE"."RV_CHECK_TIMESHEET" "RV_CHECK_TIMESHEET"
 WHERE  ((("RV_CHECK_MAIN"."CORPORATION_ID"="RV_CORPORATION_BANK_ACCOUNT"."CORPORATION_ID") AND ("RV_CHECK_MAIN"."CORPORATION_BANK_ACCOUNT_ID"="RV_CORPORATION_BANK_ACCOUNT"."CORPORATION_BANK_ACCOUNT_ID")) AND ("RV_CHECK_MAIN"."BANK_ACCOUNT_NAME"="RV_CORPORATION_BANK_ACCOUNT"."BANK_ACCOUNT_NAME")) AND (("RV_CHECK_MAIN"."PROCESS_GROUP_ID"="RV_CHECK_TIMESHEET"."PROCESS_GROUP_ID") AND ("RV_CHECK_MAIN"."CHECK_ID"="RV_CHECK_TIMESHEET"."CHECK_ID")) AND "RV_CHECK_MAIN"."PROCESS_GROUP_ID"=327975
 ORDER BY "RV_CHECK_TIMESHEET"."COMPANY_NAME", "RV_CHECK_MAIN"."LAST_NAME", "RV_CHECK_MAIN"."FIRST_NAME"

Thank you all so much.
0
James0628Commented:
Now, as Crystal is pulling that record, Crystal is going to check to see if any other client numbers are associated with that check.
The basic problem with that is that CR works on one record at a time.  The Previous and Next functions will give you values from the record before and the record after the current one, but there's no simple way to know if a value in the current record has appeared in another record somewhere earlier in the report.  That's where formulas like the ones I posted would come in, by saving a list of the values in an array.

 You mentioned a stored procedure, but then posted a query.  Is that query the stored procedure, or something else?

 James
0
dcox513Author Commented:
The stored procedure creates the data from the query pulls to create the check print file - I don't know how to create an array within CR/the selection :(
0
PatHartmanCommented:
Change the query to use group by instead of distinct.  Group by all the fields EXCEPT for the Client field.  Use Min(Client) for that one.
0
dcox513Author Commented:
OMG - that could be the winner - how do I do that???

Using CR10 and I cannot figure out where that piece is :(
0
PatHartmanCommented:
I don't use crystal so I can't give you specific directions.  If Crystal has a graphic query builder, look for a sigma button.  That is what the Access QBE uses to convert a select query into a "totals" query.

The general syntax is:
Select fld1, fld2, Min(fld3), fld4
From yourtable
where fld1 = 1
Group by fld1, fld2, fld4;

Make sure ALL fields EXCEPT the aggregated fields are included in the group by clause.
0
mlmccCommented:
Unfortunately Crystal doesn't have a graphic query builder.

You can try building a COMMAND as the data source.
Create a NEW report
When adding the data source choose the COMMAND option then type the SQL into the window.

If that works you may be able to use that command and the SET DATASOURCE LOCATION in your existing report to map the data from the tables to the command and avoid having to rebuild the report.

mlmcc
0
James0628Commented:
I don't know how to create an array within CR/the selection
You probably can't use formulas like the ones I posted for the record selection, but you could use them to suppress a section on the report (eg. the detail section).  It's hard to be more specific without seeing the report.

 It might a big help if you could just post the report, so we could see the layout, etc. for ourselves, and let us know exactly what you don't want printed when a check is repeated.

 James
0
PatHartmanCommented:
The problem needs to be fixed by modifying the query.

It also looks like your where clause should be replaced by using inner joins leaving only the group ID in the Where clause.  You can build an SQL Server view and use that for the report if you are more comfortable using SSMS.  It has a graphic view similar to Access but not as flexible.
0
dcox513Author Commented:
Here you go - attached and still driving me bananas.

Thank you all so much
Payroll-Check-tester.rpt
0
PatHartmanCommented:
You can't attach an .rpt file since there is no reader to render it.

What does your query look like with the changes I suggested?  Does running the query by itself produce the results you expect?
0
James0628Commented:
You can't attach an .rpt file since there is no reader to render it.
It's a Crystal Reports report file, and it opens just fine in CR.


 dcox513,

 Exactly which sections do you want to _not_ print on a repeated check?  All of the group 4 headers and footers, and the detail section, or just some of those?

 James
0
James0628Commented:
Assuming that you want to suppress all of the group 4 headers and footers, and the detail section, you could try the attached version of your report.  I added the init_check_vars formula to the report header, to declare the variables, use check_printed as the suppression formula for the sections, and use update_check_array to add new check numbers to the array.

 Rather than suppress GH4a, GH4b and GH4c separately, I went into the Section Expert and entered a suppression formula for GH4, which should suppress all of the GH4 sub-sections.

 I added a new group 4 footer section, GF4b, for the update_check_array formula and unconditionally suppressed that section.  I used the check_printed formula to suppress GF4a.

 James
Payroll-Check-tester-J.rpt
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dcox513Author Commented:
HOLY SMOKES!  THE RIDDLE OF THE SUPPRESSION HAS BEEN SOLVED!!!!!!

James0628 - bonus points - THANK YOU!!!!!!  It works.  I validated it 10 ways to Sunday - this is exactly what I wanted.

Thank you!!
0
dcox513Author Commented:
This was the most exceptional exercise I have ever gone through - what an incredible forum/resource.
0
James0628Commented:
You're welcome.

 FWIW, the difference between what I did in that report and my original formulas is that, because there were multiple sections to suppress, I put the array update in a separate formula, so that it could be done after all of the sections that needed to be suppressed.  Obviously if I added new check numbers to the array right away, the sections after that would see that the check number was already there.  So I add the new numbers to the array later (in GF4b), after all of the sections that need to be suppressed when a check is duplicated.

 James
0
PatHartmanCommented:
You can't attach an .rpt file since there is no reader to render it.
I stand corrected - there is no commonly installed reader to render it.  You must have the Crystal product installed.

@dcox513
Did you ever fix the query to get rid of the data you didn't want?  It would have been a simpler solution and it would be useful to understand how grouping works in queries so you don't have to make Crystal work so hard.
0
James0628Commented:
I stand corrected - there is no commonly installed reader to render it.  You must have the Crystal product installed.
FWIW, the question is in the CR section (now), so posting CR reports is not unusual.  However, the question was originally in an MS SQL section, which may be how you got involved.  That may have led to some confusion.

 James
0
PatHartmanCommented:
I understand it was a Crystal question that got sent to me due to the dual category but it turns out that the answer really was to fix the query.  I was trying to determine if the OP ever actually tried.
0
James0628Commented:
Of course only the OP can really answer, but that may not have been an option.  They did say that they were using a stored procedure that they couldn't modify, although it's possible that they could have created a new query from that.  Maybe they just didn't feel up to that.

 James
0
dcox513Author Commented:
LOL - how about the kid just was not smart enough to fully understand it.  I was playing around with PatHarman's solution when James0628 came in with the cavalry.
0
PatHartmanCommented:
The important thing is that you have a solution.

For your own education though, I suggest that you take a stab at using the query I suggested.  It will solve the problem without having to create macros in Cristal to suppress items.

There are online courses on how to create queries that you might find useful and they are usually targeted to a specific RDBMS since each one uses slightly different syntax.  They should help you with reporting.
0
dcox513Author Commented:
You are 100% correct.  I have always been told that stored procedures and refined queries are a FAR superior alternative to having Crystal do the work.

Thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.