Print last 2 records in the second page if the report spills over to t 1

[krishna]

Hi

We have a table like report. If the report is more than one page, we would like to move atleast 2 records to the next page. The table header should also print in the second page.
This table like report is a subreport.

We created a fake group and inserted the table header details in the group header and checked the Property 'Repeat group header on each page'. We created a group footer and copied the contents of the detail section to the new group footer. On the detail section, we created a conditional formula to suppress the section for the last record. This way we can print the last record along with the group footer to the next page. But I dont know how to move the last 2 records.

Another issue I noted is that if the group footer starts at the top of the second page, then the group header doesnt print at the second page. Can we avoid this? I want to print the group header also in the second page. Is this possible?

Regards
Krishna Kumar

 

[Madawc]

In Crystal 8.5, at least, the second problem is easy. Right-click for [Change Group] and choose [Repeat Group Header On Each Page].

Choosing the last two records is much trickier. You could maybedo a summary count to determine at the start how many records there are.

Madawc Williams
East Anglia, Great Britain

 

[krishna]

Dear Madawc Williams

Thank you for your reply. I have checked print group header on each page property. The problem is that my detail section print to the bottom of the page and group footer starts on the second page. If group footer starts on the second page, then the group header will not print. I think one of the solution is to create another subgroup and move the current group footer to the subgroup footer.

Could you please explain the solution for the second method?

Regards
Krishna Kumar

 

[lbass]

If all records selected for the report are displayed (i.e., if a running total within a group would equal a count of rows within the group), the following would work:

Create a running total {#countwithingrp} by selecting a field that recurs for each row--let's call it {table.ID}, select count, evaluate for each record, reset on change of group.

Now go to format section->details->new page before-> X+2 and enter:

{#countwithingrp} = count({table.ID},{group.field})-1

If you have 18 records in the group, this will cause a page break before record 17, resulting in two records on the following page. You no longer need to have the detail fields displayed in the group footer in order to capture the last detail field and keep it with the footer, and therefore you no longer need to suppress the last detail.

This wouldn't work if there were suppressed records or a group select though, since the record count would not equal the running total within groups.

-LB

 

[krishna]

Hi lbass

Thank you for your reply. But I have a doubts. Wouldnt the solution you offered always print the last 2 record in the next page even if there are enough space in the first page. I want to print the last 2 records in the next page only if there is no space to print the group completely in the first page.

Regards
Krishna Kumar

 

[lbass]

Krishna--

You are absolutely right. I had a good laugh at myself for such a silly "solution." I'll think about this some more.

-LB

 

[lbass]

Okay, I think I have a solution. Copy the detail section fields into the Group Footer. Then create a formula for each of the detail fields, using the following syntax {@previousdetail}:

if count({group.field}, {group.field}) > 1 then
previous({table.detail}) else ""

//where {group.field} is the field you are grouping on. If
the detail field is numerical, use "0" and then do a field suppress: {@previousdetail} = 0

Place the "previous" detail fields in the group header above the regular detail field you have already copied there.

Now create a running total {#countwingrp}: select {group.field}, count, evaluate for each record, reset on change of group (group.field).

Then go to format section->detail-> suppress->X+2 and enter:

{#countwingrp} in [count({group.field}, {group.field}) -1, count({group.field}, {group.field})]

I tested this, and it seems to work, but this does assume as I noted earlier, that the running total equals the count, i.e., that there are no (other) suppressed records or non-group selected records which would make a count different from a running total.

If you only one "fake group", and therefore you do not need to reset the count based on change of group, you could substitute "recordnumber" for {#countwingrp} in the section suppression formula and it would correspond exactly to the count summaries, despite any other suppression or group selects you might have done.

-LB

 

[krishna]

Hi lbass

Thank you for your reply.I had problems in understanding what you said. So I will explain what I did.

1) Created another Group Footer and placed the previous fields in that group

2) Supress the detail secton for Lastrecord and lastrecord-1

3) Suppresses the group footer for all the records except last record.

4) Checked the Keep together of Group Footer.

I hope this what ur solution is. Now I would like this to extend to other reports which have 2 or more groups. I will like the things to be placed in the inner most group footer. I am planning to do the following things

1) Place the table header (which has to be printed in every page if the report exceeds one page) in the outer most group header.
2) Suppress all other group headers
3) Insert a condtional formula for detail section so that section is suppressed for the first record,2nd last record and last record with in the inner subgroup.
4) Suppress the subgroup footer for all the records execpt the last record within the group.

Could you please tell me whether the logic is correct?

Regards
Krishna Kumar

 

[lbass]

Can you tell me which part of my suggestion you did not understand? Did you try it and it worked or did not work?

I have a few questions about your planned approach. Are you planning to put the first detail in your group header and that is why you plan to suppress the first record? Why are doing this?

I was able to recreate your problem with the header not printing when only the group footer was on the page. To get the group header to print on every page, go to the change group expert, select options, and check BOTH "Keep Group Together" and "Repeat Group Header on Each Page."

My method did not require creating another group footer. If you did, it should have been for the same group--the new section should be Group #1b.

Why are you planning to "suppress the group footer for all the records except last record"? Maybe it would help if you showed the layout you are getting that requires this suppression.

-LB

 

[krishna]

Dear LBass

Ok. Lets start with your solution and we would talk about one thing at a time

>> Copy the detail section fields into the Group Footer. >> Then create a formula for each of the detail fields, >> >> using the following syntax {@previousdetail}:

>> if count({group.field}, {group.field}) > 1 then
>> previous({table.detail}) else ""

>> //where {group.field} is the field you are grouping on. >> If the detail field is numerical, use "0" and then do a >> field suppress: {@previousdetail} = 0".

Here do we have to suppress each of the field. Cant we suppress the group footer section itself based on the formula count({group.field}, {group.field}) <= 1.

>> Place the &quot;previous&quot; detail fields in the group header >> above the regular detail field you have already copied >> there.

This logic I didnt understand. why you are placing the fields in the group header?

>> Now create a running total {#countwingrp}: select
>> {group.field}, count, evaluate for each record, reset on >> change of group (group.field).

>> Then go to format section->detail-> suppress->X+2 and
>> enter:

>> {#countwingrp} in [count({group.field}, {group.field}) ->> 1, count({group.field}, {group.field})]

I didnt understand the last section within the formula count({group.field}, {group.field}).

Regards
Krishna Kumar

 

[lbass]

Thanks for explaining. I see that I caused the confusion by accidentally saying to place the &quot;previous&quot; formulas in group header, when I meant group footer. The layout I meant was:

GF 1: {@previousdetailfield1} {@previousdetailfield2}
{detailfield1} {detailfield2}

That was why I also suggested field suppression--because I was envisioning your putting the previous fields in the same group footer section as the detail fields. If you create a GF#1a and GF#1b, you could put the previous fields in #1a and the details in #1b, and then do a conditional section suppress on #1a as you planned.

As to the last part of the detail suppression formula:

count({group.field}, group.field})

You could subsitute any field that repeats in every row within the group for the first {group.field}, but the second one has to be the field you grouped on. I just used {group.field} in both cases, since {group.field} automatically will appear in every row within the group. You wouldn't want to choose a field that could be null.

Also your copy of my formula didn't appear correctly. It should be:

{#countwingrp} in [count({group.field}, {group.field})-1, count({group.field}, {group.field})]

As a suppression formula, this states that if the running total is in the array (summary count of records within the group) which includes the next to the last or the last record within the group, suppress the row.

I did test this and it worked, but please let me know if you run into any more snags.

-LB

 

[krishna]

Hi

The group footer section is clear now. But I have doubts in the detail suppression formula

{#countwingrp} in [count({group.field}, {group.field})-1, count({group.field}, {group.field})]

As a suppression formula, this states that if the running total is in the array (summary count of records within the group) which includes the next to the last or the last record within the group, suppress the row.

The last record is represented by count({group.field})
The previous record to the last record is represented by count({group.field})-1.

But what does count({group.field}, {group.field})] this represent?

Regards
Krishna Kumar

 

[lbass]

In an earlier post you said that you wanted to extend this design to a report with multiple groups. The count({group.field}, {group.field}) just says to count a field within the group and reset on change of the group:

count({any recurring field within the group}, {the field you are grouping on})

If you are creating a fake group, with the group actually including all records, it's fine to just use count({group.field}) and count({group.field})-1. If you have multiple groups, you need to use the second {group.field} in your formula.

-LB

 

[krishna]

Hi Lbass

Thank you for your help. I am very clear now.

Regards
Krishna Kumar