RE: show only first 30 records for each bid

[allyne]

Hi Everyone,

I have a report that is grouped by BID and within each bid are hundreds of records. What I need to do is show only the first 30 records for each bid and supress the rest. At this point I have 30 records on each page. Is there a way to show only the first page (or first 30 records) for each Bid? Can't seem to get this one.

I also have another problem I just can't seem to get.

I need to have a line drawn under every 5 records with each bid. So far I have If LineNumber = [5,10,15,20,25] Then "________________" but I need this to print for each group of records. Is there a way to do this?

Thanks in advance for all your help.

Cathy

 

[kenhamady]

What version of CR? Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training/Consulting and a
Quick Reference Guide to VB/Crystal (including ADO)

 

[dcm]

allyne: You don't state whether your 30 records are to be ranked in any order or whether it is merely the first 30 out of the database for each bid so here are 2 suggestions to cope with both.

If it is 30 records ranked in order (i.e. highest first) then first insert another group e.g. bid_record_id and then create a summary of Maximum(bid_amount,bid_record_id). You could then use the TopN expert to select only the top 30 records in bid_record_id and discard all others

If it is the first 30 records in each bid then you need to suppress printing of the details section with the following formula:

numbervar first30:=0;
if BID <> previous(BID) then first30:=0 else
first30:=first30+1;
first30>30

The last line of this formula produces a boolean true result to suppress printing when over 30 records read in

Your line can be drawn every five records if you place it on a subsection and suppress printing with the following formula:

Remainder(LineNumber,5)>0

This will only print line every 5th LineNumber David C. Monks
david.monks@chase-international.com

http://www.chase-international.com

Accredited Seagate Enterprise Partner

 

[kenhamady]

DCM is correct, except that his second formula has a typo. It should be RecordNumber as in:

Remainder(RecordNumber,5)>0


If you have version 7 or 8 there are simpler approaches to the first formula, but his will work. Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training/Consulting and a
Quick Reference Guide to VB/Crystal (including ADO)

 

[allyne]

Hi Everyone,

Thanks for your quick responses. I will try DCM's response to see if it will work.

Ken, I have CR 7.0 and would also be interested in hearing about a simpler approach to the first formula.

This is a great website, I have learned so much about CR!

Thanks again so much for all your help as I am new to CR and would not have figured this one out without all your help!

 

[kenhamady]

Version 7 has a running total feature. You can add a running total that counts a field (any field that is always filled in) and that resets at the end of each group.

Now on the detail band you can format the &quot;new page after&quot; setting with the following formula:

remainder({#RunningTotal},30) = 0

This will force a page break every 30 records. Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training/Consulting and a
Quick Reference Guide to VB/Crystal (including ADO)

 

[allyne]

Thanks again for your response. I will also give this a try as it is always nice to learn more than one way to do something!

Thanks again!

 

[allyne]

Hi Ken,

I created the formula you suggested and I now have 30 records per page but now I need to show only the first page for each bid in my report and suppress all other records/pages for each bid. Is there a way to do this?

Thanks again!

 

[MalcolmW]

It is possible, but not trivial. I would not recommend spending the time to do that unless it is critical for your report. Some alternatives:
Print only one line per record, so that you always that room for 30 records per bid.
Reduce the number of records from 30 to a number that fits on one page.
Both approaches are quick and easy to implement. Malcolm

 

[allyne]

Thought this wouldn't be easy. Unfortunately, this is critical to my report. I need to only show the first 30 records for each bid in my report instead of just printing the first 30 records in each bid. Is this possible? if so can you show me the correct syntax? I'm really at a loss on this one.

Thanks a bunch!

 

[MalcolmW]

&quot;I need to only show the first 30 records for each bid in my report instead of just printing the first 30 records in each bid. &quot;
Do you mean that showing just one line from each these records would be OK, and that currently, each record can take one or more lines? Malcolm

 

[kenhamady]

Foramt - Section
Highlight details
click the formula next to suppress:

{#RunningTotal} > 30

Of course, you will need the running total. Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training/Consulting and a
Quick Reference Guide to VB/Crystal (including ADO)

 

[allyne]

MalcomW,

Currently the report looks something like this:

SID/BID 01645 (Grouped BY)
NAME SID Current 30 60 (FieldNames)

ATT 411 30.00 20.00 10.00
United 211 18.00 30.00 30.00
(There are hundreds of records that show up under the SID/BID 01645)

SID/BID 4444(Grouped BY)
NAME SID Current 30 60 (FieldNames)

GTE 210 30.00 20.00 10.00
BELL 215 18.00 30.00 30.00
(There are hundreds of records that show up under the SID/BID 01645)

So to make this easy lets say that I only want to show the first record for each SID/BID so now I want my report to look like this:

SID/BID 01645 (Grouped BY)
NAME SID Current 30 60 (FieldNames)
ATT 411 30.00 20.00 10.00


SID/BID 4444(Grouped BY)
NAME SID Current 30 60 (FieldNames)
GTE 210 30.00 20.00 10.00

I know that this is confusing so let me know if this is making any sense.

Ken,

I tried the Foramt - Section
Highlight details
click the formula next to suppress:

{#RunningTotal} > 30
but still came up with all the records.

 

[allyne]

IT WORKS!!!!!!!!!!!

Ken,

The
Foramt - Section
Highlight details
click the formula next to suppress:

{#RunningTotal} > 30

Worked. I had the < Instead of the >

Ken and MalcomW,
Thanks so much for all your time and effort. This was very critical to my report and I really appreciate all your help!

 

[kenhamady]

If you put the running total field on your detail band, what values does it print? Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training/Consulting and a
Quick Reference Guide to VB/Crystal (including ADO)

 

[allyne]

Hi Everyone,

Now that I have that solved, I came up with another problem. Now that I have 30 records on one page for each bid and need a total for just those 30 records for each bid. So using the example above::

SID/BID 01645 (Grouped BY)
NAME SID Current 30 60 (FieldNames)

ATT 411 30.00 20.00 10.00
United 211 18.00 30.00 30.00
(There are hundreds of records that show up under the SID/BID 01645 That is now suppressed)

(Now I need to add a field that sums the top 2 records for each field)

Subtotal 48.00 50.00 40.00

SID/BID 4444(Grouped BY)
NAME SID Current 30 60 (FieldNames)

GTE 210 30.00 20.00 10.00
BELL 215 18.00 30.00 30.00


(There are hundreds of records that show up under the SID/BID 01645 That is now suppressed)
(Now I need to add a field that sums the top 2 records for each field)

Subtotal 48.00 50.00 49.00

Is there a way to do this? If so, can you please show me the correct syntax. I can't seem to get this to work now that the records are suppressed.

Thanks again for all your time and help!

 

[allyne]

Hi Everyone,

I figured it out. I had some wrong information. It now works.

Thanks again for all your time and help!