How to insert Page Break when Summary value in Group Footer changes?

[Former Member]

Hello experts,

That seemed to be very easy ;(

I have built a report on data of hierarchical nature.

The report has a single group. It displays only Group lines and hides Details section.

I group by NmDocID field. Employee Code that prints in a group footer is Max(Empl_Code) in that group. 

                NmDocID             Employee Code {@Max_EmpL}

GF1        23                                           ALB900

GF1        28                                           ALB900

                --- Page Break ----

GF1        57                                           BAR900

--- Page Break ---

GF1        63                                           BIT900

                ...

I need to print each Employee on a separate page. For that I need to insert a Page Break when Group Summary (Employee field) in the next line is different from Group Summary in the current line (see the sample above).

Please note that Employee Code is present only in one detail line, and there is no way that I know of to get it into every Detail line without ruining the whole report. Typical group contains three and more Detail lines.

Things I tried so far:

• - To apply NEXT function to a formula that prints Group Summary {@Max_EmpL}. However Formula Editor says: ‘This formula has no previous or next value’.
• - Created formulas with Next and Previous functions and applied them to Detail lines. Unfortunately Crystal can read only ONE line up or down from the current line. With my typical group being three lines long the values are still one row apart, and so I cannot compare them to trigger a page break

I know the hurdles have to do with multi-pass model that Crystal uses to process data.

However the task seems to be too simple not to have a solution...

Thank you for any ideas.

MJ

[JWiseman · Accepted solution]

hi Max,

there's a couple of ways you can do this.

a) use a sql expression to get the max employee id

have a look at the attached report. extract the contents and change the .txt extension to .rpt. have a look at the employee sql expression on the report.

(select max(`O`.`Employee ID`) from `Orders` `O` where `Customer`.`Customer ID` = `O`.`Customer ID`)

this is running a sub query to grab the maximum employee id for every customer id. note that in the sql expression, an alias is being used 'O' so that the where clause can reference the field from the main report. in the sample report, the first group is on the employee id and the second group is the other id. therefore creating a new page after group footer 1 is simple.

note that using a select in a sql expression is not supported but this may work for you. usually oracle is problematic for this method.

b) write a new report  using a command instead of tables

if you copy the show sql from your report, database menu, then you have a base query to use as command object. then create a new report and in the database expert press "Add Command" and paste this syntax in.

now you need to add a subquery to grab the employee id...here's an example using sql that works in access...your sql may vary depending on your database.

SELECT

`Customer`.`Customer ID`,

(select max(`O`.`Employee ID`) from `Orders` `O` where `Customer`.`Customer ID` = `O`.`Customer ID`) AS EmployeeID

FROM 

`Customer` `Customer` INNER JOIN `Orders` `Orders`

ON `Customer`.`Customer ID`=`Orders`.`Customer ID`

once you have that employee id value as a normal field, then it's easy to create a first group on that id and then a second group on your second id field. then a new page after group footer 1 is simple.

i hope this helps,

jamie

newpageafternewmaxusingsqlexpression.txt.zip

[Former Member · Accepted solution]

Hi Jamie,

Thanks a lot for your quick reply!

That makes a lot of sense. Eager to try that.

My report is built on Command Object from the beginning so I will try to add a subquery to it as you describe in Option B.

Thanks,

Max

[Former Member · Accepted solution]

Hi Jamie,

As to your advice I added two subqueries (I use UNION) to SELECT clauses of my Command Object, then placed the field they return into Details section. After that a page break I need became a breeze it should be!

It’s funny that having developed the whole report in Command Object, I stopped one step short of completing the job there, and have been trying to solve the very last task with pure Crystal Reports means. Probably because it seemed too easy... It will be a good lesson for me.

Just curious: can the same be achieved with Hierarchical Grouping in Crystal?

Thanks a lot for your help!

MJ

[JWiseman · Accepted solution]

hi Max,

i'm glad that it works.

the hierarchical grouping option would most likely not be something that would work for this as it would also require that you the employee code is present on every line. and once you use this particular feature, the formatting options can sometimes be limiting.

-jamie