suppressed records and record count

[mperera]

Hi,

I've got a report with a suppression formula on the detail section. Basically, I only want one record per person to appear on the report, and this is working fine. However, I am using the Special Field 'Record Number' on the report, and it seems that the suppressed records are being 'counted' (although not displayed) in this field. For example: Bob Smith has two records and Mike Jones has one record. What is showing is as follows:
Name Record Number
Bob Smith 1
Mike Jones 3

But what I want is Mike Jones to show 2 for his record number.

Does anyone know of a way around this? Maybe a formula field that I can use instead of record count to display the nubmer? Any help appreciated!

Thanks.

 

[bjrollet]

Have you tried to "Select Distinct" in your report? Look under the "Database" menu, down towards the bottom.

 

[synapsevampire]

It showing what you ask for.
If you want to have a number increment for each record, then build a formula akin to this:

<not tested>

whileprintingrecords;
numbervar mycounter;

if {SOMEDATA.NAME} <> previous({SOMEDATA.NAME}) then
mycounter = mycounter+1;

mycounter

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[SmpTraining]

The previous suggestion is fine for grand totals but if you want to extend this to show totals at a group level use the standard three formula approach as follows:

Admittedly, you might not need this fuller solution for your particular problem, but in case you do...

Step 1. Create the three formulas as shown:

@ResetVars
WhilePrintingRecords;
NumberVar MyCounter

@UpdateVars
WhilePrintingRecords;
NumberVar MyCounter;
if <condition> then
MyCounter := MyCounter + 1;

@ShowMyCounter
WhilePrintingRecords;
NumberVar MyCounter

Step 2. Place the formulas in your report

Place @ResetVars in the report header and in the group footer of the section where you want the count reset

Place @UpdateVars wherever appropriate.

In your case, you would place it in the Group Header and not include the line &quot;if <condition> then&quot; in your formula. You only need to include this line if you are conditionally counting something. For example, you might be counting sales figures that are higher than average, in which case you would need to apply the conditional bit.

Place @ShowMyCounter wherever you need to see the count value. This would normally be in the group footer section where you need to see the count.

DO remember to include the reset formula AFTER the show formula in the group footer section.

You might ask why place @ResetVars in the report header AND in the group footer - why not simply place it in the Group Header? The reason is simple - if you have the 'repeat group header on each new page' option ticked, you get incorrect results if you place the formula in the group header section. Basically, each new page will cause the group to be printed again and at the same time, the counts would be reset.

Hope this helps,
Steve Steve Phillips, Crystal Consultant

 

[mperera]

Thanks for the help. I don't need anything complex, so the first formula seems like it should work fine. However, I'm getting strange results.

My formula looks like:
WhilePrintingRecords;
numbervar mycounter ;
if {STUDENT_ALL_V.STUDENTID} <> previous({STUDENT_ALL_V.STUDENTID}) then
mycounter + 1;

However, in the report, the value returned by the formula never increments - it is always 1. It's as if the values for mycounter is not persisting each time the formula is evaluated (for each record). Any thoughts?

Thanks!

 

[mperera]

Ooops! I see my error, and it's working fine now.
Thanks all!

 

[mperera]

Spoke to soon. I do have one more question. How do I handle the first record of the report where previous() is null? The evaluation of the if statement fails - it doesn't like isNull(previous({STUDENT_ALL_V.STUDENTID})) - which is really what I need - a way to determine if there *is* a previous record. Thanks!

formula:
WhilePrintingRecords;
numbervar mycounter ;

if {STUDENT_ALL_V.STUDENTID} <> previous({STUDENT_ALL_V.STUDENTID}) then
mycounter := mycounter + 1
else
mycounter := mycounter + 0;

mycounter

 

[Martin97]

Hi,
Use the PreviousIsNull function instead

like this
previousisnull({STUDENT_ALL_V.STUDENTID})

HTH,
Martin