Print Page | Close Window

Counting number of records

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=19804
Printed Date: 29 Apr 2024 at 8:55pm


Topic: Counting number of records
Posted By: KevV
Subject: Counting number of records
Date Posted: 23 Jul 2013 at 11:15am
I have a report with several groups with SUM fomulas in each one. I am trying to do a count of all the records that are showing in the report and adding it in the group footer. I have the numbers converted to strings but I keep getting errors.
 
 
Thanks
KevV


Replies:
Posted By: praveeng
Date Posted: 23 Jul 2013 at 11:41am
Try with Running total.

-------------
Praveen Guntuka,
praveen_guntuka@yahoo.com

Posted By: KevV
Date Posted: 23 Jul 2013 at 11:46am

Doing a running total only gives you the option of adding one group. I need the total of all records from all groups.

 
KevV

Posted By: DBlank
Date Posted: 23 Jul 2013 at 11:50am
you cannot sum a sum unless you use variable formulas
However you might just rethink the process from summing  sums to summing the details again at the report footer instead of the group footer.

Posted By: KevV
Date Posted: 23 Jul 2013 at 11:57am
I tried doing them in the detail section but I could not get the other calculations to come out right and if the field did not have anything in it, it would leave a blank line in the report even if I told it to suppress blank.
 
KevV

Posted By: DBlank
Date Posted: 23 Jul 2013 at 11:59am
what is your sum at the group level?

Posted By: KevV
Date Posted: 23 Jul 2013 at 12:10pm

It is in a payroll system and I have them grouped by their paycode. and then I do a running total and set it to evaluate for each record and reset on change of name. The sums are in the group footers for each. I technically only have one group the others are just sections of that group. like group 1a,1b and so on.

 
KevV

Posted By: DBlank
Date Posted: 24 Jul 2013 at 3:35am
create a new running total that is the same as your curretn one but do not not reset it.

Posted By: KevV
Date Posted: 24 Jul 2013 at 6:00am

I do a sum on the others so it adds up all the hours. What I need is a count of how many actuall records I have. So it may be 10 hours of time but it is only one record. If I just set it to never reset it just adds all the hours. I have tried doing a count but that did not work either.

 
KevV

Posted By: DBlank
Date Posted: 24 Jul 2013 at 6:06am
distinct count of a primary key (or combination of 2 primary keys)

Posted By: DBlank
Date Posted: 24 Jul 2013 at 6:07am
if that is not it can you please show smaple row level data and how you want it.
Working in the dark a bit without knowing your data set...

Posted By: KevV
Date Posted: 24 Jul 2013 at 7:36am
The formula I have is:
 
if {VP_TOTALS.PAYCODENAME}="Night" then({VP_TOTALS.TIMEINSECONDS}/6/6).
 
That gives me their time to the second without rounding. I then do a running total of those hours setting it to Evaluate for each record and Reset on change of group. I then have a formula to convert it to text:
 
right ("0000"+totext ({#Night_SU} ,0,""),4)
 
The reason for the extra stuff is I need the number to always have four digits and sometimes it may only have 1 so I add the 0's and just pull the last four digits. I then palce it in a group footer. The report is grouped by their name. The end result is something like:
 
ZTAHDV0001600099R103200
 
Wtih the last four numbers being the amount of hours for that paycode. I do this for each of the paycodes we have which is about 20 so an employye could have several lines on the report depending on how many different paycodes his hours are in. I can add the hours for each of those paycodes with out any problems but what I need is to be able to count the total number of records. I have tried all kinds of Sum's, counts,distinct counts and nothing seems to work. I have even set a formula in each group to give me numbers to try and do a count on but that does not seem to work either. I am not sure if I have done the report the right way but it does give me all the right answers except for this last one. I hope this is not too confusing.
 
KevV
 

Posted By: DBlank
Date Posted: 24 Jul 2013 at 8:40am
when you say "total # of rcords" do you mean total number of unique clients + paycodes?

Posted By: KevV
Date Posted: 24 Jul 2013 at 8:51am
"total # of records" so if an employee say has four different lines because they have hours in four different paycodes it would count them as four. I know that we have 145 people and there are 458 records between them so I need the result to be 458.
 
 

Posted By: DBlank
Date Posted: 24 Jul 2013 at 8:57am
I assuming you have an employeeid field and a paycode type field on each row.
concantentate them into one formula field and do a distinct count on that.
if they are numeric then use
totext(table.empid,0,"") + '-' + totext(table.paycode,0,"")

Posted By: KevV
Date Posted: 24 Jul 2013 at 10:39am
I have something else wrong still. The fields are both strings so I just did:
 
({VP_TOTALS.PERSONNUM})+'-' +( {VP_TOTALS.PAYCODENAME} )
 
and then did a distinct count, Evaluate for each record and never reset. the number I get is 1066 but the number I should get is 438 (I was actually wrong earlier).
 

Posted By: DBlank
Date Posted: 24 Jul 2013 at 10:46am
to trouble shoot,
place it on the detail section and you should see it incrementally add 1 to each "new row" or stay the same on a "duplicate".
if you see it acting oddly look at where it is happening. I suspect you will see it jump up quickly due to some suppressded data that you are not accounting for (but that is just a hunch).
also place the concantenated formula next to it to watch it change too as the RT is contingent on its accuracy.

Posted By: KevV
Date Posted: 24 Jul 2013 at 1:20pm

I went thru my select statement and eliminated some paycodes and it got me to 11. I then added it to the detail section and found the problem. I have a statement in the group footers that surpresses any line that comes back with a 0.0 value because it will mess up the system when it tries to process it:

{#Sick_SU}  =0.00 or isnull({#Sick_SU})
 
So they could have a line that said 0.0 hours of sick time and it is supressed but it is still counted. I am currently doing a distinct count on:
 
({VP_TOTALS.PERSONNUM})+'-' +( {VP_TOTALS.PAYCODENAME} )
 
in the detail section. Is there a way to add something in the formula to tell it not to count the record if the paycode brings back a 0.0 value?

Posted By: KevV
Date Posted: 25 Jul 2013 at 12:27am
I tried adding a formula to the footer and detail section that would give me a 1 or depending on hours for the pay code and then do a distinct count of that field I count 5 1's but the formula shows 2

Posted By: DBlank
Date Posted: 25 Jul 2013 at 3:46am
if your suppression is a row evaluated suppression and not a summarization across rows just use a RT with an evaluation formula
name=Totals
field to summarize=concantenated formula field
type=distinct count
evlauate=use a formula
{#Sick_SU}>0.00 (and set the formula option to "use defualt values for nulls")
reset-=never


Print Page | Close Window