Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Technical Questions
Message Icon Topic: Basic Totaling Issues Post Reply Post New Topic
Author Message
fcaruso
Newbie
Newbie


Joined: 23 Jul 2009
Online Status: Offline
Posts: 2
Quote fcaruso Replybullet Topic: Basic Totaling Issues
    Posted: 23 Jul 2009 at 1:56am

Want to be able to show as a percentage a group total of an oraganization total.

Have a report with two groups: Organization and Group. I total the number of records by for each group and total for each organization. I want to divide the total for each group by the total for the organization to get a percentage. I know I can do this using Summary fields but the counting of records by group is conditional. I cannot get the percentages at the group level to be based off the total at the organization level. It only basis the the calculaiton off the running organization vlaue.
 
How to get Crystal to let me base my group percenatages off of the grand total for an organization? This should be simple but I cannot get it to work.
 
Thank you.
IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
Quote lockwelle Replybullet Posted: 23 Jul 2009 at 6:27am
If your counting is conditional, then as you know the built in aggregates don't work.  you will need to use something else.  Running Totals, might be a way, those are DBlank's specialty...I haven't used them.  I use shared variables for all of my calculations.  It would seem that they are running totals, but with more flexibility.
 
you will need several, depending on how many groups you have.  And if your group total is conditional, you will probably need a subreport or some such to calculate the 'total' for the average.
 
Shared variables are really easy to use:
in a formula just 'declare' them, and they are available for the entire report. Like:
shared numbervar aVariableName
 
assignment uses the := operator, equality testing uses =
in order to access an existing shared variable you need to 'declare' it like above.  See Crystal's help for the valid variable types, they are pretty easy to remember/use.  In general a variable will have 3 formulas, 1 to reset, 1 to display and 1 to increment.
 
I know tha this is vague, but it should give an idea of how to proceed.
IP IP Logged
fcaruso
Newbie
Newbie


Joined: 23 Jul 2009
Online Status: Offline
Posts: 2
Quote fcaruso Replybullet Posted: 23 Jul 2009 at 7:10am

I have read about using variables and have been successful accumulating values. However, where I am not having much luck is being able to reference one groups accumulation from a higher accumulation. For example:

Tier1
    Value 1 = 100
    Value 2 = 50
    Value 3 = 150
Total Tier 1 = 300
 
I need to be able to calculate each Value as a percentage of the total for  the tier but I cannot figure out how to get the total to accumulate before calculating the percentage. The only thing I can do is get the percentages based of the running total: 100, 150, 300.
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 23 Jul 2009 at 7:28am

You can use RT or variables here but if you are trying to display the percentage of value1 of tier 1 total at the value 1 level then for your total N for tier 1 it would have to be a SUMMARY that is NOT conditional using the Insert Summary function.

Is this the case or is there something else going on here?
IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
Quote lockwelle Replybullet Posted: 23 Jul 2009 at 8:04am
The way that would find the tier 1 total, so that you can create the average is to use a subreport that reads through the records and returns the count using the same logic that the subgroup groups use.  You can then place this subreport in the group header.  You don't need to display anything in the subreport, you just need it to update the shared variable that is the Tier1 total.  Now the Tier1 total is available to all sublevels.
 
The draw back to this method is that the data is read twice or more, and this can slow down the report.  If you were using a stored proc, you could have that calculcate the Tier1 total and have it in the row, which would make your calculations easier...Ah we don't always get to be lucky.
 
Hope this helps a bit more.
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 23 Jul 2009 at 8:09am
Before you go down that path there is potentially an easier way.
If it is conditional and there are no dupes in your records (meaning you can do a count rather than a distinctcount) you can create a formula field as:
if conditions to include here then 1 else 0
You can then create a SUM at GF1 using the insert summary function. This SUM field can be used in a formula field on GF2.
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 23 Jul 2009 at 8:22am

If your conditional inclusion of records can be done via this formula than you can also get your percentages via this pretty easily.

For this example the formula for the "1 else 0" is named "Count".
Create another formula for you percentage and include an if then for the sum = 0 option to avoid a division by zero error...
 
if SUM({@Count},group1field)=0 then 0 else
 
Place this on your group footer 2.
IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
Quote lockwelle Replybullet Posted: 23 Jul 2009 at 8:27am
DBlank is right...I would much rather use an aggregate function than a subreport.
 
Since, I use stored procs, I don't come across this scenario too often...I'd just modify the store proc to give me what I need.  The the Server which is probably more powerful than the desktop and the DB software which is optimized for work on the data by groups do the work and leave the formatting to Crystal, which it is optimized for.
 
 
IP IP Logged
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.029 seconds.