Crystal Report Detail Section Headers - How to repeat on next page if detail spills over into next page

No.  You could do it if they headers were in the group header but not the details.

Could you add them as page or group headers and suppress the appropriate ones.

mlmcc

Possibly but I am not exactly clear on what I would group on?  I dont have a need to group them but if that will work I can give it a try.  Can I just select anything from the repective detail sections to group on?

If you don't have a group adding one will change the ordering of the report unless you say group on the main table primary key.

You could put them in separate Page Header sections and suppress the ones you don't need on that page.

mlmcc

I don't mean to be funny but your posts contradict themselves and I'm finding it difficult to understand your requirements.

in you original post you state - "I have a report that has one group with 10 detail sections"

then in a later post you say - "I am not exactly clear on what I would group on?  I dont have a need to group "

Can you post an example of your data and how you would want this to look on the report

I am currently grouping on person identifier.  I dont actually need to do this as the report will always only pull one persons information.  All the detail sections are descrete columns of a single table.  The report has 10 detail sections with headers for the following activities.  

Group 1 - PersonID
- Detail A - Driving [Header Bold and with a different background]  
- Detail B - Driving Content
- Detail C - Stairs   [Header Bold and with a different background]  
- Detail D - Stairs Content
- Detail E - Walking [Header Bold and with a different background]
- Detail F - Walking Content
etc...
This is typically a one page report.  The problem I have is when the one of the sections grows based on content it is pushed to the next page and the person reading the report does not know which context the content is for without going back to the prior page to see which header it was associated with.  I would like to somehow repeate the content header i.e. Detail E Walking header would be on page one and if content in Detail F spilled over onto page 2 repeate Detail E Walking header above the content that has run over.  I understand from mlmcc that there is no way to do this with detail sections and I could possibly use a group or page header.  Just need a bit of direction on that as I have never used them in this way.  Please let me know if I am not being clear or if I am still contradicting myself as I often do. ;-)    

So what other fields are there in your data set, what identifies a record as a label or a data field  and how do you know which data record relates to which label record ?

Datafields come from the database.  Label fields are not database records they just text objects above the content sections.  

In that case why use seperate details sections ? just increase the height of a details section move the labels and data into the same details sections and then these will always stay together

I assume there are multiple records for a person and based on something in the record you are suppressing the detail sections that don't apply to that record.

What I suggested is to have multiple PersonID headers

GH1a - Current person header
GH1b - Detail A - Driving [Header Bold and with a different background]  
GH1c - Detail C - Stairs   [Header Bold and with a different background]
GH1d - etc

You can then suppress the various headers but they can be shown on the second page through use of variables.
The problem with your method is Crystal works top down
Details A is printed
Details B is printed - there is no way to go back to details A until the next record.
etc

mlmcc

mlmcc you are right on with you assumption!  I am conditionally suppressing headers if the detail sections below contain no content.  Thanks!  Hate to sound dense!  Where would the content live in your example above?  Would I put the content that belongs with the header in the Group footers or another GH1 iteration.  Doesnt that put me back into the same situation as multiple detail sections?  Would you care to expand or point me in the right direction on the variable approach to suppressing if no content is valued?

Can a details section run over multiple pages ?

I have a report that shows the basic idea I have.

Here is the basic idea

RH -
      Add formula to declare InDetails = 0
GH1a - Current person header
      Set to print on all pages
      Conditionally suppress with InDetails <> 0
GH1b - Detail A - Driving [Header Bold and with a different background]  
      Conditionally suppress with InDetails <> 1
GH1c - Detail C - Stairs   [Header Bold and with a different background]
      Conditionally suppress with InDetails <> 2
GH1d - etc
      Conditionally suppress with InDetails <> 3

- Detail A - Driving [Header Bold and with a different background]  
     Add formula to set InDetails =1
- Detail B - Driving Content
- Detail C - Stairs   [Header Bold and with a different background]  
     Add formula to set InDetails =2
- Detail D - Stairs Content
- Detail E - Walking [Header Bold and with a different background]
     Add formula to set InDetails =3
- Detail F - Walking Content
etc...

mlmcc
Q-26875282.rpt

I think that makes sense...  Let me give it a try.  THanks for the example!!!

you wouldnt want to share the mdb with me also so I can see your example in action would you?

The mdb really doesn't have any data in it.  I just added a database so I could get the group.  The group is on the table primary key.

If you just add a database to the report and change the group to use a field in the new table then you can delete my table.

mlmcc

Thank you mlmcc finally got a chance to check this out.  I see what it is doing and it appears to be doing exactly what I want.  I now want to incorp into my larger project.  Do you mind if I keep the question open until tomorrow noon?  In case I have additional question or would you rather I just open another?

No problem so long as the questions are a continuation of this discussion.

mlmcc

I've got to be doing something wrong.  I am working with your example and trying to adapt your example report to do what your instructional in post [03/10/11 09:12 AM, ID: 35097011] states to do.  i.e. adding the variables to the report etc. as your example did not have them included in the sections.  Unfortunatly I do not get the desired outcome.  The WALKNIG data section spills over onto the second page but the Group header for WALKING data does not re-print on the second page.  I've got to be missing a peice of the puzzle.  Do you see anything obvious?

Did you set the group header to print on all pages?
Right click the group header in the left margin
Click CHANGE GROUP
I think it is on the second tab

mlmcc

I did not but I just went in and did that and this had no impact on the report.  I have a feeling I am not following your directions properly.  I've attached the mdb and rpt.  Thank you so very much for you help with this!

 Images.mdb

 Q-268752821.rpt

mlmcc: Ok so I think I have set this up just as you suggest.  I am still unable to get the group header to repeat on the next page if the content spills over onto the next page.  I cannot find an example on here or web that demonstrates this can be done.  am I on a wild goose chase here.  Comments from anyone are appriciated!!  

I don't know.  I just played with your report and with other ideas for doing it.  It seems the group headers use the value or the variables when the groups are built not whrn they are printed.  In fact even in the detail sections the variables didn't seem to reflect the changing values.

mlmcc

Here is my report woth a different approach but it doesn't work either

mlmcc
Q-26875282-rev1.rpt

Well if you can't do it it must not be possible!  Respect and appreciate your assistance!  This is still a requirement for me s perhaps crystal is not the tool.  Any other suggestions are welcome!

Can you live with some white space at the bottom of a page?

If so you could try an idea like

Details A
   Driving Header
   Driving data
Details B
    Walking header
    Walking data
Details C
   Stairs Header
    Stairs Data
Etc

You can then set the KEEP TOGETHER for each section and a section will stay on a single page.

mlmcc

Yea that's how I rolled the report out to the users at first but the problem with that is the content is narrative so the doc could simply write no restriction or could go on for half a page.  Unfortunatly the waste of paper with this method is too great.

Good idea.  I didn't think of that.  

It seems the formulas in the group headers get evaluated before the details even with the whileprintingrecords so even though you change the value in the detail section when the group shown on the second page the value is still what it was when the group was entered.

I put a formula like this in each of the 6 detail sections.  It always showed 1
WhilePrintingRecords;
Global NumberVar x;
x := x + 1

mlmcc

Thanks James.  DIdn't want to drag you in but sometimes you can look at something a little differently than I do and then see an idea or a solution.

mlmcc

Before I tried the separate records approach, I tried changing the formulas to use WhileReadingRecords, just to see what would happen.  At first it actually seemed to be working.  I was getting the Walking header on the second page and a formula in the page header was showing the variable value as 3.  But then I did some tests and found out that it wasn't working after all.  I was thinking that maybe when the sections were produced, the variable might just end up with the final value from the final record (which happened to be 3 in this case), as opposed to the value from the last detail section produced, but it was weirder than that.  I tried swapping the Stairs and Walking sections, so the variable was set to 3 and then to 2, and I still got the Walking heading on the second page.  Even though the variable was theoretically being set to 2 last, it was still coming up 3 everywhere.  I still don't get how it was doing that.  I refreshed the report a number of times, which presumably got CR to reread the record and reprocess the sections, but it kept coming up 3.  <shrug>

 James

I am not abandoning this question.  James has provided what would appear to work.  Very complicated as you pointed out as i only gave you the concept in the sample report.  I actually have hundreds of fields on this report.  The SQL command object is an ugly option as I am doing most of teh work in a stored proc.  I am assuming I can do that work in the SP right? as opposed to the command object?

Really trying to understand this comment James.  "That could be changed to only read the appropriate field in each query, and put them all in the same field in the data.  So you'd have a field named DSW, for example, and in the first record it would have the value from Driving, in the second record it would have the value from Stairs, and in the third record it would have the value from Walking.  If you're reading a lot of records, or some of the fields are quite big, that could significantly cut down on the amount of data transferred to the report (as opposed to including all 3 fields in each record). "

It actually increases the total data passed to the report though each record is smaller because you are passing 3 records for each original record and passing the type and the personid 3 times.

mlmcc

 > I am assuming I can do that work in the SP right?

 Sure.  I'd do it in a stored procedure, because almost all of my reports use stored procedures.  I used a Command in that sample report because it was originally reading a table, so it was easy to switch the report from an internal table datasource to an internal Command datasource.

 As for my comment, in the sample report that I posted, I just copied the original query 3 times and added a Type column.  The drawback is that each query includes the Driving, Stairs and Walking columns, but each query only actually uses one of them.  For example, the first query is for the Driving column, so the report is not going to use the Stairs or Walking fields in those records, but the columns are still there.  That's just wasted space, and unnecessary bandwidth (assuming that the data is being sent from a db server, over a network, to the report).

 The query that I posted in that message is an alternate version of the query, where I replaced the separate Driving, Stairs and Walking columns with a single DSW column (DSW is just an acronym for Driving Stairs Walking).  In the first query, Driving is put in that column.  Stairs in the second query.  Walking in the third.  So, you don't have the extra, unused, 2 columns in each query.  Just one column that is filled with the appropriate value.


 If you're already using a stored procedure to produce the data for your report, this whole operation is even simpler, in a way, since you don't have to create a Command, or change the datasource for the report (from a table to a Command).

 In simple terms, if you've got a query in your stored procedure that produces rows with Activity.Driving, Activity.Stairs and Activity.Walking, you need to make 2 copies of that query, so that you have 3 total, one for each column, and add a "Type" column to each query.  You could also combine those 3 columns into one column, as described above.

 Note that this means duplicating everything else in the row 3 times, once for each of those 3 columns.  In your sample report that wasn't an issue, because those columns were the only thing in the report.  But if, for example, you have a quantity field and want to get a total, each quantity is going to be repeated in 3 records, so a regular CR summary will be inflated.  There's no way to know how much of a problem that's going to be without knowing more about your report.

 James

I dont have any quantity fields so that should not be a problem.  I am concerned that...  My main report is HUGE with over 100 data elements on the report.  If I were to use the first approach it sounds like I would have to duplicate the query over 100 times no?  That obviously is not desirable.  I've attempted to collaps them all into one column called DSW in the sample command, as you suggest, and it is not working.  Would you mind clarifying the syntax for that option for me?    

another question... can I up the points on an existing question?  I am very grateful for all of your help!

I am definitly confusing matters!  I have a total of 20 possible headers (I say possible becuase if the values is not present in the return I am suppressing the header) Under each header I have  roughly 15 records.  That said I think I will only need to do 10 unions right...?

More points? - Not in this question.  EE has a maximum of 500 points for a question

You would need 1 union for each possible field in the headers so in this case 20.

Are the fields the same for each type or do the fields change?

mlmcc

 > If I were to use the first approach it sounds like I
 > would have to duplicate the query over 100 times no?

 It depends.  You would duplicate the query once for each field that has a header that you want repeated if that field continues onto a second page.  If you have "over 100 data elements on the report" and all of those "data elements" are fields with headings like that, then you'd have to duplicate the query for each one.

 As for the syntax for "combining" multiple columns into one column, the basic syntax is pretty simple.  Maybe an example will help.  Let's say that your original query was

SELECT `Activity`.`PersonID`, `Activity`.`Driving`, `Activity`.`Stairs`, `Activity`.`Walking`
FROM   `Activity` `Activity`

 In the report that I posted, I just added a Type column and duplicated the query with all columns:

SELECT 1 AS `Type`, `Activity`.`PersonID`, `Activity`.`Driving`, `Activity`.`Stairs`, `Activity`.`Walking`
FROM   `Activity` `Activity`
UNION
SELECT 2 AS `Type`, `Activity`.`PersonID`, `Activity`.`Driving`, `Activity`.`Stairs`, `Activity`.`Walking`
FROM   `Activity` `Activity`
UNION
SELECT 3 AS `Type`, `Activity`.`PersonID`, `Activity`.`Driving`, `Activity`.`Stairs`, `Activity`.`Walking`
FROM   `Activity` `Activity`

 That would produce results like this:

1   PersonA   DrivingA   StairsA   WalkingA
1   PersonB   DrivingB   StairsB   WalkingB
1   PersonC   DrivingC   StairsC   WalkingC
2   PersonA   DrivingA   StairsA   WalkingA
2   PersonB   DrivingB   StairsB   WalkingB
2   PersonC   DrivingC   StairsC   WalkingC
3   PersonA   DrivingA   StairsA   WalkingA
3   PersonB   DrivingB   StairsB   WalkingB
3   PersonC   DrivingC   StairsC   WalkingC


 The Driving, Stairs and Walking fields are repeated in each record, but only one of them will actually be used in each record (Driving in the Type 1 records, Stairs in the Type 2 records, and Walking in the Type 3 records).

 So, my alternate query (posted in a previous message) combines them into one column, which I happened to name DSW.  The query above becomes:

SELECT 1 AS `Type`, `Activity`.`PersonID`, `Activity`.`Driving` AS `DSW`
FROM   `Activity` `Activity`
UNION
SELECT 2 AS `Type`, `Activity`.`PersonID`, `Activity`.`Stairs` AS `DSW`
FROM   `Activity` `Activity`
UNION
SELECT 3 AS `Type`, `Activity`.`PersonID`, `Activity`.`Walking` AS `DSW`
FROM   `Activity` `Activity`

 That would produce results like this:

1   PersonA   DrivingA
1   PersonB   DrivingB
1   PersonC   DrivingC
2   PersonA   StairsA
2   PersonB   StairsB
2   PersonC   StairsC
3   PersonA   WalkingA
3   PersonB   WalkingB
3   PersonC   WalkingC

 The 3 columns are Type, PersonID and DSW.

 In a nutshell, you put a different field in a specific column in each copy of the query.

 This, of course, would also require some changes to the report, since the old fields - Driving, Stairs and Walking - have been replaced by one field - DSW.  In the version of the report that I posted, you can map Driving to DSW, but CR won't let you map more than one field to another field, so the Stairs and Walking fields just disappear.  You can just replace them manually with the DSW field, and change the format to be the same as it was for the old fields.

 James

You last paragraph was the peice I was missing.  Let me give it a try.  Thanks for explaining!

This works great in the sample report.  However I am afraid both methods will not work for my actual report.  The UNION cannot be made as the fileds are all different (change) between sections that require the header...  Sorry should have answered that question when you asked mlmcc!  

Are the fields the same types?

You can also do add dummy fields to each line.

mlmcc

By type do you mean datatype?  They are not the same datatype.  how would the dummy fields make the union work if the fields do not match?

SOmething  like

SELECT 1 AS `Type`, `Activity`.`PersonID`, `Activity`.`Driving` AS `DSW`, 0 as NumberField
FROM   `Activity` `Activity`
UNION
SELECT 2 AS `Type`, `Activity`.`PersonID`, `Activity`.` ` AS `DSW,  'Stairs' as NumberField
FROM   `Activity` `Activity`
UNION
SELECT 3 AS `Type`, `Activity`.`PersonID`, `Activity`.`Walking` AS `DSW`, 0 as NumberField
FROM   `Activity` `Activity`

mlmcc

Thanks for the example although I cannot get that syntax to apply (invalid bracketing) to even see what it does.  Was the syntax above intended to work or just an example?  

Sorry.  It was intended as an example but since I just moved fields it should have worked.  However it will only work if STAIRS is numeric.  it should have been

SELECT 1 AS `Type`, `Activity`.`PersonID`, `Activity`.`Driving` AS `DSW`, 0 as NumberField
FROM   `Activity` `Activity`
UNION
SELECT 2 AS `Type`, `Activity`.`PersonID`, ` ` AS `DSW,  `Activity`.'Stairs' as NumberField
FROM   `Activity` `Activity`
UNION
SELECT 3 AS `Type`, `Activity`.`PersonID`, `Activity`.`Walking` AS `DSW`, 0 as NumberField
FROM   `Activity` `Activity`


mlmcc

There are several issues, or potential issues, with the second SELECT line.

SELECT 2 AS `Type`, `Activity`.`PersonID`, ` ` AS `DSW,  `Activity`.'Stairs' as NumberField

 Since the SQL is using ` to delineate names, I'm guessing that it won't work to define a character string.  So, the ` ` in the middle should probably be '' (two single quotes), or maybe "" (two double quotes).  (I also removed the space in between, but just because I don't think it's necessary)

 There should be a ` after DSW.

 I'm assuming that there should be ` around Stairs, instead of '.

 You may need ` around NumberField (the name for the new column).  I'd use them just because they're used on the other names.

 So, I would write that second SELECT line as:

SELECT 2 AS `Type`, `Activity`.`PersonID`, '' AS `DSW`,  `Activity`.`Stairs` as `NumberField`


 And, as mlmcc said, in this example, Stairs would have to be numeric.  What that query does is add a numeric column named NumberField that is just set to 0 on the Driving and Walking rows, and is set to Stairs on the Stairs rows, so it's assumed that Stairs is numeric.  It's also assumed that Driving and Walking are both character data types.

 However, this specific example doesn't make sense in the context of your original problem.  You had 3 long text fields that were running onto multiple lines, and each one had its own heading.  Obviously if one of them is numeric, it's not going to be on multiple lines (unless you're formatting it in a very unusual way).


 > The UNION cannot be made as the fileds are all different
 > (change) between sections that require the header...

 This doesn't really make a lot of sense to me.  Which fields are different?  Which fields are you trying to "consolidate" into one column?

 I was really only talking about trying to consolidate those long string fields (Driving, Stairs and Walking in your sample report) into one column, and, as mentioned above, they are pretty much the same data type, by definition.  Although, now that I think about it, I guess they could be a mixture of different "text" data types, like char, varchar and text (Memo type in CR).  I hadn't thought of that before.  I had just assumed that there were all the same data type.  If they're not, that could be a problem.  I'm not sure about having char and varchar in the same column, but trying to combine either of those with text probably wouldn't work without some massaging.  If so, you could probably use something like CAST or CONVERT (it depends on your db) to convert some columns so that they're all the same data type.  Then again, if those long text columns are different types like that, there's nothing that says that _all_ of them have to be consolidated into one column.  You could, for example, have one column with the char/varchar columns, and another with the text (memo) columns.

 Were you trying to combine any other columns, or was it just the long string columns that your "sections" were based on (like Driving, Stairs and Walking in your sample report)?

 And what db are you using?  If the columns are different data types and you need to convert them, your options will depend on your db.

 James

Wouldn't you know, I thought of this the moment I posted.  :-)

 If I'm right and you need ` around NumberField, you'd also need them on the other two SELECT lines, but I had only mentioned the second line.

 James

Database is SQL Server 2008.  The only reason I considered combining the columns was to cut down no the overhead of so many selects and Unions.  I have ove 100 fields on this report with multiple datatypes.  Primarily varchar(1500) and datetime but there are 5 memo fields that are Text Interpretation = HTML.  This is getting terribly complicated and I am thinking of throwing in the towel.  I do appriciate youre detailed explaination however I am afraid it is a bit over my head.  

I understand that this could be fairly complicated, especially if you haven't done this kind of stuff before, but FWIW ...

 As far as combining the columns goes, the datetime columns shouldn't be an issue.  The idea is that you've got some long text fields that run onto multiple lines on the report.  In order to get the heading for each of those fields to repeat on each page, you're creating a copy of the record for each of those long text columns, and putting each of those records in a separate group on the report (to get a group header that can be repeated at the top of a new page).  Since you are creating a separate copy of each record for each of those long text columns, you can put them in the same physical column in the data, if they're the same data type.  If they're not the same data type (like varchar and ntext), you could convert them to a common type, as long as you won't lose anything in the process.

 Meanwhile, any other columns besides those long text columns (datetime or numeric or whatever) would just be separate, like always.  So, if you had

 longtextcol1 longtextcol2 datetimecol1 datetimecol2 intcol1 intcol2 shorttextcol1 shorttextcol2

 and duplicated that row, so that there was one row for longtextcol1 and one row for longtextcol2, you would have

 longtextcol datetimecol1 datetimecol2 intcol1 intcol2 shorttextcol1 shorttextcol2

 longtextcol would have the value from longtextcol1 in one row and the value from longtextcol2 in the other row, and the other columns would be unchanged and just duplicated in each row.


 If you haven't already, it might be worthwhile to just try duplicating the query, without trying to combine any columns, and see how it goes.  If the report is not reading a lot of records, it might be fine.

 James