Count & Reset based on Crystal Report Rows

My data is grouped and may have several hundreds of detail rows but all descriptions are unique and have a SQL key identifier. Prices are calculated for each record but due to constraints of displayed data a client wants a report to look like the attached sample where every group of similar descriptions has a grey break, and I have a way to accomplish that with the Key ID and some yes no fields in the database. What I am having a problem with is the highlighted yellow area.

Sample goal
I need the text to change on each successive row for 4 records and reset, even across page breaks as it shows highlighted. I can create 4 text fields, one under another on the row and display or hide them but I am unsure of the best way to approach this. I thought I could count records after each grey line and display or hide my "PART" text box by the record count, or trigger the count by counting from previous row descriptions where it is the same for the new one but I am unsure of what to do at a page breaks, or simply count from the grey bar (this is a similar record with a unique ID but a flag that I hide all text except the description and change the background of the section to grey.

My goal is to have the first record below the grey bar displays "PART 1" in a text box, 2nd record displays "PART 2" text box and hides first text record box and so on for 4 records then reset the counter. I read that something similar can be accomplished by a formula field but I am not quite sure how to accomplish this either.

Help is greatly appreciated. I am using CR 2011 fed from SQL server where all the sorting occurs.
JeremyCTOAsked:
Who is Participating?
 
mlmccConnect With a Mentor Commented:
Glad you figured out the formula. It is tough to troubleshoot without  data in the report.

What causes the detail row to grow?

mlmcc
0
 
mlmccCommented:
Are the grey bands sections like a group header?

You could add a variable and use it to count.

In the group header set it to 0.
WhilePrintingRecords;
Global NumberVar Counter;
Counter := 0'
""

Open in new window


IN the detail section increment it then use it to display the value as

WhilePrintingRecords;
Global NumberVar Counter;
Counter := Counter + 1;
"Part " & ToText(Counter),0)

Open in new window


mlmcc
0
 
JeremyCTOAuthor Commented:
That is the problem, these detail records with the grey banding are set up as detail records, the group level is much higher and consists of several pages of detailed records before I change to another group. I am trying to get the text changed on each record row under one grouping of records based on a field value on one record that is a boolean and reset  the count after every fourth row, or after the change in the boolean field. I am looking for a solution using either of the criteria.

Basically after each highlighted detail record row (highlight is set by a boolean field) I want the first row after to display a certain text field and the second a different, third a different text and on the fourth it will go to a highlighted record and I want to reset the counter.
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
mlmccCommented:
So these are detail rows.

Can you upload the report file so I can see how you are controlling it.

mlmcc
0
 
JeremyCTOAuthor Commented:
Thanks for looking at this.
TechRepairGuide_w_index_NICO_Red_201.rpt
0
 
mlmccCommented:
So Detail A has the Part # you want to vary and Detail B has the shaded section?

Try this idea

In the Group 2 header add a formula
Name - SetCount
WhilePrintingRecords;
Global NumberVar PartNum;
PartNum := 0;
''

Open in new window


In the detail A section add a formula
Name - CountPart
WhilePrintingRecords;
Global NumberVar PartNum;
PartNum := PartNum mod 4 + 1;
"Part " & CStr(PartNum,0,'')

Open in new window


mlmcc
0
 
JeremyCTOAuthor Commented:
So this gives me the row count and the text! Great and many thanks but it appears it is also counting in the row count the grey row which I do have a boolean field in called "ExcludeFromKimmons" which allows me to display or suppress this row based on a TRUE FALSE condition. These rows may or may not have data in the description field but I have no problem displaying them as needed based on the boolean.

The problem I see is the count needs to start on the row beneath every grey record  or it shifts out of place. So my question is can I change the Mod 4 + 1 to 5 + 1 and make the text to go to the correct record or, how do I reset the count based on it seeing the boolean value in the highlighted rows if that is a better solution? I do have the same report without a details b and I suppress or display the highlighting and bolded Description text field base on my boolean  for that record. Would consolidating to one detail page be part of the solution?

One final question, since many pages will have records to the bottom of the page as space allows (averages 26 records but that is not a hard number because of growing information fields) How can I force a page break if the highlighted row is at the bottom or near the bottom so I don't get half my parts on one page and my header highlighted row on the previous page. Can I do this by setting a position at the bottom of the page and saying anything below X position where a highligh field falls between X and end of page gets pushed to new page?

I roughly understand page breaks but I admit I am not in Crystal Reports to do much but relocate text or data fields so I am not familiar with the Crystal syntax to accomplish this page break and my coding is pretty limited to SQL server 2000, so I apologize in advance for asking questions that may appear naive and the answers may be very obvious to you as an expert with the product.

Thanks again for all the help!

JL
12-28-2017-6-51-24-AM.jpg
0
 
JeremyCTOAuthor Commented:
OK, Fixed the number shift issue with modifying the CountPart formula to,

WhilePrintingRecords;
Global NumberVar PartNum;
if {ado.ExcludeFromKimmons} = TRUE then
PartNum := 0
else
PartNum := PartNum mod 4 + 1;
"Part " & CStr(PartNum,0,'')

Now my only question is how to force a page break so I can keep the last group of detail records on the page and force a new page for the next set.
0
 
JeremyCTOAuthor Commented:
There is one field in the details A section that may cause the report section to grow by several lines. I ran the report with the fixes you suggested and was able to see a maximum record row per page and just inserted a page break on that record count.

Thanks for all the help
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.