Force loop through all field values before showing the rest of the data set?

[leveetated]

Hello all,

CR 2008, SQL Server 2005

I've pored over the threads on CR looping, have tried the various do/while and reset/accum/displ formula approaches, plus subreports, and nothing is working - and I'm stuck.

My data set: publisher, author, title, date, genre.

I simply want to loop through ALL the possible publishers for the given data set, and display like so:

publisher1, publisher2, publisher 3, etc.

Then go on to show the rest of the data set:

author1
title1
date1
genre1

author2
title2
date2
genre2

author3
title3
date3
genre3

...and so on. This must be simple, right? Can any gentle genius advise? Many thanks in advance,

Lee.

 

[IanWaterman]

You can not do that with variables or arrays as these are whileprinting so will not display until after data has been processed.

I suggest you use a subreport based on same data set as main report and show list of publishers in that. You can place SR in Report Header.

Ian

 

[leveetated]

Thank you for your suggestion, IanWaterman. I'm not using variables or arrays to represent any of the data set, just the straight {table.field} format.

I'm not sure if that changes anything, but wanted to mention this in case my original message was unclear.

With thanks,

Lee.

 

[Madawc]

As IanWaterman says, you can't easily do it without a subreport.

If you were willing to do a lot of coding, you could read the records without printing anything, collect data and then print everything in the group footer. Try adapting something I wrote to collect postcodes:

// Accumulate using a formula field (suppressed) in the detail line.  Allow for nulls and blanks.
whileprintingrecords;
if not isnull({Recc.Postcode}) and {Recc.Postcode} <> " "
then stringvar pst := pst + {Recc.Postcode} +", "
else if length(pst) = 0 
then stringvar pst := pst + "Excluding blanks; "
else stringvar pst := pst

// Show in using a formula field in the group footer.
whileprintingrecords;
stringvar pst;
left(pst,len(pst)-2)

//  Clear using a formula field in the group header.
whileprintingrecords;
stringvar pst := "";

Note that clearing in the footer avoids problems with group headers repeating on a new page, which does clear everything for the group. Provided the 'clear' is placed in the section AFTER the display, it will do them in that order.

To get what you specified without a subreport, you'd need to do this for each field, and then extra complications to show the details.

If you were willing to show the publishers last rather than first, you could show the main details as details and just collect publishers.

Madawc Williams (East Anglia, UK). Using Crystal 2008 with SQL and Windows XP

 

[leveetated]

Thank you Madawc, you captured it exactly when you said

you could read the records without printing anything, collect data and then print everything in the group footer.

I adapted your code and placed it exactly as mentioned: the accum in details, the reset in GH3, and the display in GF3. The rest of the publication data is in GF2a, 2b, 2c, etc.

This is want I want to see:

publisher1, publisher2, publisher3

author1
title1
date1
genre1

publisher2
author2
title2
date2
genre2

publisher3
author3
title3
date3
genre3

However, this is how the data prints out:

publisher1
author1
title1
date1
genre1

publisher2
author2
title2
date2
genre2

publisher3
author3
title3
date3
genre3

I have also tried this exact approach with a subreport with same results. Subreports work fine when I have a one-to-many relationship between tables, but this is not the case; I simply want to iterate through all the values of a given field for a given result set before displaying the rest of the result set.

With many thanks,

Lee.

 

[lbass]

Of course you can use a subreport. Place it in the report header. Use the same record selection formula as in your main report. Then collect the publishers names in a variable like this:

//{@accum} to be placed in the detail section of the sub:
Whileprintingrecords;
stringvar x := x + {table.publisher}+", ";

//{@display} to be placed in the subreport report footer:
Whileprintingrecords;
stringvar x;
left(x, len(x)-2)

Then suppress all sections of the subreport except the footer.

-LB

 

[leveetated]

Thank you lbass. I did as you suggested, and I am getting an error at run time: "Error in formula accum: 'Whileprintingrecords;' A string can be at most 65534 characters long. Details: errorKind".

Any guidance is much appreciated, with thanks,

Lee.

 

[leveetated]

Ah, typed a bit too soon; I was able to fix the string length issue by using a different view.

However, now the subreport only displays one of the credit lines. I have tried different groups in the SR but with no success.

With thanks,

Lee.

 

[lbass]

How are you linking the sub to the main report? What are the groupings in the main report, and in which report section is the sub placed?

-LB

 

[leveetated]

Hello lbass - I am linking the subreport on the publication ID.

The subreport is placed in the main report header.

There is one group in the main report, which groups on a sorting field in the publication table.

There is one group in the subreport (and the display formula is placed in its footer) and it is grouped on the publication ID.

There is no record selection formula in the main report or the subreport (apart from the linking between the sub and the main reports).

With thanks.

 

[lbass]

You need to remove the link and I would also change the {@accum} formul as follows:

//{@accum} to be placed in the detail section of the sub:
Whileprintingrecords;
stringvar x;
if not({table.publisher} in x) then
x := x + {table.publisher}+", ";

-LB

 

[leveetated]

Hello and thank you lbass - I did as you suggested and the report returned the error "The Report Application Server Failed" and did not display any results.

Perhaps I should mention that the report is run after a search result, i.e. a set of results is sent to the report and the report should return all the given publishers in the result set, followed by the rest of the information (laid out in an earlier post). It seems that not linking the report would return ALL the publishers in the database, or do I misunderstand?

With thanks,

Lee.

 

[lbass]

You have to use the same selection criteria in the subreport as in the main report.

-LB