Crystal Reports Counting number of records

Technical Questions
Crystal Reports Forum : Crystal Reports 9 through 2020 : Technical Questions

Topic: Counting number of records
	<< Prev Page of 2

Author	Message
DBlank Moderator Joined: 19 Dec 2008 Online Status: Offline Posts: 9053	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...
	IP Logged

KevV Senior Member Joined: 19 May 2011 Online Status: Offline Posts: 106	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
	IP Logged

DBlank Moderator Joined: 19 Dec 2008 Online Status: Offline Posts: 9053	Posted: 24 Jul 2013 at 8:40am
	when you say "total # of rcords" do you mean total number of unique clients + paycodes?
	IP Logged

KevV Senior Member Joined: 19 May 2011 Online Status: Offline Posts: 106	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.
	IP Logged

DBlank Moderator Joined: 19 Dec 2008 Online Status: Offline Posts: 9053	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,"") Edited by DBlank - 24 Jul 2013 at 8:58am
	IP Logged

KevV Senior Member Joined: 19 May 2011 Online Status: Offline Posts: 106	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).
	IP Logged

DBlank Moderator Joined: 19 Dec 2008 Online Status: Offline Posts: 9053	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.
	IP Logged

KevV Senior Member Joined: 19 May 2011 Online Status: Offline Posts: 106	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?
	IP Logged

KevV Senior Member Joined: 19 May 2011 Online Status: Offline Posts: 106	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
	IP Logged

DBlank Moderator Joined: 19 Dec 2008 Online Status: Offline Posts: 9053	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
	IP Logged

<< Prev Page of 2

Post Reply

Post New Topic

Printable version

Printable version

Forum Jump

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum

This page was generated in 0.031 seconds.