Active TopicsMemberlistSearchHelpRegisterLogin

Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Report Design
Message Icon Topic: Percentages in Crystal report Post Reply Post New Topic
Author Message
andrew
Newbie
Newbie
Avatar

Joined: 11 Nov 2008
Location: United Kingdom
Online Status: Offline
Posts: 35
Quote andrew Replybullet Topic: Percentages in Crystal report
     Posted: 09 Apr 2009 at 4:50am
(reposted as was originally in the wrong forum)

Hi All,

I'm not sure if this is actually possible within the scope of crystal reports but i'll ask anyway...

I'm writing a report that shows our engineer call outs and revenue earned....

so:

Engineer A - Job 1 - $100

Engineer B - Job 2 - $200

Which runs fine... but, sometimes they double up on jobs and thus it needs to show in the report, otherwise the financial figures are wrong.

So:

Job 3 = $100

Currently comes up as:

Engineer A - Job 3 - $100

Engineer B - Job 3 - $100

Revenue = $200

Desired:

Engineer A - Job 3 - $50

Engineer b - Job 3 - $50

Revenue - $100

------------
Is this possible to achieve within Crystal reports without any database alterations (as in having a formula run within while report is printing)?

Many thanks in advance!
IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4374
Quote lockwelle Replybullet Posted: 09 Apr 2009 at 6:11am
Interesting question.  Stored procedure would make life easier, but let's see if there is a Crystal only solution.
 
First question, is the report about jobs or engineers?  If it is grouped on jobs then you could create a formula that takes the cost of the job and divides it by the distinct count of engineers. 
 
If it is by engineer, the only thought that I have is a subreport, and the subreport would still divide the job cost by the distinct count of engineers, shared variables could be used to transport the subreport values back to the main report for summing in a footer.
 
Hope this helps, or at least provides a path...
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 09 Apr 2009 at 6:35am

If you can group by Job # you can use the Summary Function to create a Summary as a COUNT for that group then create a formula field using the cost field / count summary field and place it on the detail row.
IP IP Logged
andrew
Newbie
Newbie
Avatar

Joined: 11 Nov 2008
Location: United Kingdom
Online Status: Offline
Posts: 35
Quote andrew Replybullet Posted: 15 Apr 2009 at 2:09am
Hi guys, thanks for the replies!
 
In answer to the questions asked:
 
The report is about engineers, and works out the revenue they have made for the company, so it will runs through by name and outputs the jobs they have done, i.e,:
 
Joe bloggs
Job name 1 - location - revenue
Job name 2 - location - revenue
 
Jane bloggs
Job name a - location - revenue
.etc
 
---------
 
With this in mind, I think DBlank suggests a good idea that i might try - as im severely limited to how much manipulation I can do database wise (as the report gets its variables passed by a program not to dissimilar from something like SAP - which is causing the headache).
 
I'll get back to you guys shortly and hopefully will have some good news  
IP IP Logged
andrew
Newbie
Newbie
Avatar

Joined: 11 Nov 2008
Location: United Kingdom
Online Status: Offline
Posts: 35
Quote andrew Replybullet Posted: 20 Apr 2009 at 2:53am
Hi Guys,
 
Sorry for asking a simple question with regards to this, but is it possible to use the forumla editor to create more drilled down queries without using an SQL query (as im not interacting directly with the SQL server),
 
For example if I do a count of a record where another record = something
 
I.e, count({Revenue.SEQNUM}) where {job.ID} = 3
 
or is this beyond the scope of the formula editor?
 
Thanks!
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 20 Apr 2009 at 4:38am
You can either use a formula and sum it:
If field =3 then 1 else 0
Or do a running total with a formula as the condition to include:
Field=3
IP IP Logged
andrew
Newbie
Newbie
Avatar

Joined: 11 Nov 2008
Location: United Kingdom
Online Status: Offline
Posts: 35
Quote andrew Replybullet Posted: 20 Apr 2009 at 8:17am
Sorry DBlank but i've been trying all day with various diferent ideas and they all return junk or just simply dont run.
 
After faffing around i've decided that due to the way it works if I can get just 1 field to count itself - it will work.
 
So....
 
count({Revenue.Seqnum}) will work but will obviously count *everything* as apose to just the records I want.
 
I dont know the value the report is bringing up, so will have to use a variable but as im still trying to learn Crystal reports - don't know how to create the forumla.
 
As it will be pushing out a report with hundreds of values/ engineers.etc (and needs to do it for each record), I take it I do a whileprintingrecords?
 
Do I need to do a while loop to + 1 each time it sees the corresponding record? or is there a way to do it in selection expert?
 
It's just so frustrating to be near to a solution that will really help complete this report and getting stuck with something so trivial due to both the lack of my crystal knowledge. 
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 20 Apr 2009 at 8:26am
No problems.
I am not sure exactly what you are trying to do here. If you want a sum of how many instances there a jobid=3 then you can create a formula field and place it on your detail row (to validate it works):
if {job.ID} = 3 then 1 else 0.
This will drop 1 or 0 on each row of data.
Now you just SUM that formula at the group level or report level.
Does this make sense?
 
IP IP Logged
andrew
Newbie
Newbie
Avatar

Joined: 11 Nov 2008
Location: United Kingdom
Online Status: Offline
Posts: 35
Quote andrew Replybullet Posted: 21 Apr 2009 at 4:18am
Hi Dblank
 
Thanks for your continued support with this.
 
Yeah that makes sense - but the '3' was just an example from an earlier post.
 
The report is going to be spread over say 20 or so pages with maybe 30 engineers all working on diferent jobs (and some together).
 
So the formula wont be using static figures but variables. if ({job.id} = {something}).
 
For this reason, doing a sum like the way you suggested either will only do one record instead of them all - which i'm sure you're aware.
 
To keep things simple I thought rather than do anything facy with cross referencing, it would just check on every row and do something like this...
 
(row prints)
get Job ID (Seqnum) and count how many rows it appears in the database table (so its got to count itself, and ignore everything else - if that makes any sense.
If it occurs once, do nothing - just move on.
If it occurs more than once, then do something (work out the percentage.etc).
(move on to next row).
 
I realize this isnt the most efficient of ways to go about doing something - but if I can at least get it to count the instances that would be a miracle!
 
Hopefully that makes some sense to you dblank about the problem i'm facing.
 
Again thanks for the time your putting in to try and help me!


Edited by andrew - 21 Apr 2009 at 4:19am
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 21 Apr 2009 at 9:06am
Sorry but having a hard time totally picturing this set up and I think I had the wrong concept of the grouping you were using. I think I have it now...
You are tring to get a total amount of revenue per engineer so your group1 is on engineer.
Each engineer may have shared a job so you need to split the amount based on the total # times that job appears in the db. then get a sum from that per engineer. However, based on your group1 the jobs are split out into various parts of the report hence you cannot do any counts to get your # to divide the job amount by for any engineer that shared a job.
Is that correct? If so I think you need to go back to Lockwelles suggestion to use a subreport and shared variable. Sorry for leading you down another path. I was thinking you could group on Job ID and was using that as a starting point. here is what I would do:
Group on Engineer.
Place jobs on details.
Create a sub report that is linked on the jobid (not engineer) on each detail row.
In the subreport do a count of the job ID and divide the amount by it.
Used a shared variable between the sub report and the main report to move that total into the main report per row.
In the main report Sum the shared variable at group footer 1 to get the total per engineer or at report footer to get the sum for all jobs.
Hope this is on target Confused
IP IP Logged
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

HOME | FREE EBOOK | PROGRAMMING GUIDE | PURCHASE | REVIEWS | CONTACT ME | FAQ | LINKS | PRIVACY | CLICK PLAYS | KONACAD

© 2004-2026 Brian Bischof. All Rights Reserved.