Formula to suppress records in Detail Section

[dbew]

Hi everyone,

I use the following formula to supress duplicate employee records in the Details section:

Not onfirstrecord and {ado.EMPLOYEE} =
previous ({ado.EMPLOYEE})

I notice, however, it only supresses records that are DIRECTLY one after another. So for example, if the report is grouped by employee # then there is no problem.

Group: 123-45-6789
Employee #/Name/State
123-45-6789/Jane Doe/Alabama
123-45-6789/Jane Doe/Ohio
Group: 999-99-9999
999-99-9999/Mike Jordan/Alabama

The Ohio record for Jane Doe will be suppressed and only her Alabama record will display.


But if the report is grouped by State, then no Jane Doe record will be suppressed.

Group: Alabama
Employee #/Name/State
123-45-6789/Jane Doe/Alabama
999-99-9999/Mike Jordan/Alabama
Group: Ohio
123-45-6789/Jane Doe/Ohio

As I indicated earlier, since the Jane Doe Ohio record does not directly follow her Alabama record, the Ohio record is not suppressed and still remains in the report.

What can I do to make sure that records will be suppressed no matter how I group the report??? I have a feeling it has to do with my suppression formula but I have no idea what to do it. Please help!

 

[mmaz]

Your formula will only suppress the second (and more) of two consecutive records. When you group by state, your 2 Jane Doe records are NOT consecutive, they are separated by Mike Jordan, which is why the second Jane Doe is not suppressed.

Not sure how you could suppress the second record. Maybe by populating an array with employee ids, and then comparing each record to that array...? Or format your data before you display it on the report.

HTH!

Marie

 

[dbew]

Hi Marie!

Thanks for your reply to my problem. You're absolutely correct; because the users of this report require that it be grouped by State I.D., some employee records will not be consecutive. The formula I have will not be of any use since this is the case.

I'm still new to Crystal Reports 8.5 designing. Can you explain more about "arrays" and how would I create one?

Thanks so much,
dbew

 

[Tailgun]

why not do this in your recordset ? using Select Distinct ??

 

[dbew]

Hi Tailgun,

I'm not sure I understand what you mean. Can you please clarify?

Thanks,
dbew

 

[dgillz]

Click on database, select distinct records.
Software Training and Support for Macola, Crystal Reports and Goldmine
251-621-8972
dgilsdorf@mchsi.com

 

[Tailgun]

Your quick dgillz At least he should be able to do what he needs to do now.

 

[dbew]

Hi dgillz and Tailgun,

Thanks for helping me! One problem though: I click on the "Database Menu" and see the "Select Distinct Records" option but it's greyed out (I cannot select it). Is there something I need to do?

I access my data through an OLE DB connection.

Thanks,
dbew

 

[dgillz]

I am at a loss. Normally when this is greyed out, it is becuase you have access the database natively instead of thru ODBC or OLE.

Try an ODBC connection if it is available. Software Training and Support for Macola, Crystal Reports and Goldmine
251-621-8972
dgilsdorf@mchsi.com

 

[synapsevampire]

I'm using OLE DB and it's available.

Perhaps you're using a Stored Procedure?

And distinct records won't help, they are distinct when they are in 2 different states...you'll still get the same results...

This needs to be addressed with more elaborate SQL.

I would do this within a Stored Procedure or View, if you have a DBA, approach them with this issue.

You may be best served with a subquery, which Crystal Doesn't handle well.

If you don't have local resources available, submit your table layout and I'll attack it.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[Ngolem]

Using Select Distinct won't work since you cannot select that way for a single field, rather for the whole record and since the states are different this won't work.

But I have to ask you why you would want to suppress the employee info if you are grouping by state Is not the employee by state information important??? Under which state do you want the employee listed??? The first alphabetical state possible? Is that relevant??

If it is then the array approach has some merit and would work regardless of any way the data was sorted. One would cature each new employee number in an array and make comparisons as new data came in...setting a flag to suppress or not suppress a given record.

You would require several formulas

@initialization
//(suppressed in a main header if this report is repeated
// or in the report header if the report is done once)

WhilePrintingRecords;
//Initialize 50% more array elements then are currently
//required to a maximum of 1000.
StringVar array EmployeeNumber := ["","","",......,""];
NumberVar iCounter := 0;

*********************

@Update_FlagSet //suppressed in the group header
WhilePrintingRecords;
StringVar array EmployeeNumber ;
NumberVar iCounter ;
BooleanVar Flag ;

if {table.employeenumber} in EmployeeNumber then
Flag := true;
else
(
iCounter := iCounter + 1;
EmployeeNumber[iCounter] := {table.employeenumber};
Flag := false;
) ;

Then in the detail line place the following in the conditional suppress for the record

WhilePrintingRecords;
BooleanVar Flag ;
Flag ;

That should work as long as the number of employees are les that 1000
Jim Broadbent

 

[dbew]

THANK YOU, everyone, for helping me out!

dgillz: We are accessing data from an ERP called Lawson Software. They have a tool called a Query Builder that uses an OLE DB connection to pull data from the Lawson tables and transfers them over to Crystal Reports 8.5.

synapsevampire: Thanks so much for your offer!!! I'm going to first try the array approach outlined by "Ngolem." If it doesn't work, however, may I seek your assistance?

Ngolem: Thanks so much for taking the time to type out those formulas! I noticed you stated: That should work as long as the number of employees are less than 1000. Well before we bust our brains for nothing, the number of employee records to be evaluated is 30,961! I'm wondering if the array method can handle such a huge number of records.

 

[synapsevampire]

No, an array is limited to 1000 if you have CR 8.5 or less, and it's a very messy approach, try getting the database folk involved.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[dbew]

Hi synapsevampire,

I really appreciate your Crystal Reports expertise that you've shared with me thus far.

Yes, we create our reports with Crystal Reports 8.5 Professional. And looking at the cr_arrays.pdf from Crystal Decisions, it confirms what you and Ngolem stated:

In CR version 8 the limit of elements allowed in the Array are 1000

I can see why this would be a difficult approach as I have over 30,000 records and it's probably not as simple as creating 30 arrays (which isn't as easy as it sounds, I'm betting).

I'll check with the DBA on this. Thanks again!

 

[Ngolem]

SV - It isn't that messy...it all depends on your control over the database. If you don't have the privileges to do what you suggest then one must do what one must do. I have worked on several contracts where I was severely limited because I was not allowed to write stored proceedures and staff was limited so they could not be done for me.

Using this array approach worked very well for me on several occassions.

dbew - 30,000 + employees is too much to work with. It could be done if you REALLY had to by setting up multiple arrays to be filled and checked...or you could add employee numbers together to make array element strings of 254 characters (packing in 254/11 employee numbers per array element which would extend the capacity using 1000 elements to about 20,000 employees on a single array. But this becomes cumbersome and if you can go the stored proceedure route that SV has suggested.

However you didn't answer my questions in the last post.

But I have to ask you why you would want to suppress the employee info if you are grouping by state Is not the employee by state information important??? Under which state do you want the employee listed??? The first alphabetical state possible? Is that relevant??


Jim Broadbent