Print Page | Close Window

Summing groups

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2022
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=313
Printed Date: 04 May 2025 at 12:24am


Topic: Summing groups
Posted By: TokyoAnt
Subject: Summing groups
Date Posted: 13 Mar 2007 at 2:42am
Hi, this is a forgotten question still in need of an answerCry
 
I want a group delimited as below with sums:
 
InfrastructureServices  80
IS-ext                           50
detail1                          20
detail2                          20
detail3                          10         
IS-int                            30
detail1                          10
detail2                          20
ManagedServices         120
MS-ext                          90
detail1                          90
MS-int                           30
detail1                          20
detail2                          10
 
 
The red 'groups' are sums of the dark blue 'groups' which are in turn sums of the details. It needs to be formatted like this. Is this possible?
 
I've tried all sorts of ways. I tried summing in the details but this raised an error. I'm using global variables to hold the summary values but where are they summed?
 
This is what I'm using to sum it:
 
Global NumberVar ISSum1;
Global NumberVar MSSum1;
Global NumberVar MCSum1;

if GroupName ({ResourceUsageReport.OrganizationName}) = "Infrastructure Services"
then 
    if Left(({ResourceUsageReport.OrganizationName}),2) = "IS"
    then
        ISSum1 := ISSum1  + ( Sum ({ResourceUsageReport.Period1}, {ResourceUsageReport.OrganizationName})
        / sum( mailto:%7b@AvailWkHrs1%7d,%7bResourceUsageReport.OrganizationName%7d%29*100 - {@AvailWkHrs1},{ResourceUsageReport.OrganizationName})*100 )
   
else if GroupName ({ResourceUsageReport.OrganizationName}) = "Managed Services"
then
    if Left(({ResourceUsageReport.OrganizationName}),2) = "MS"
    then
        MSSum1 := MSSum1 + (Sum ({ResourceUsageReport.Period1}, {ResourceUsageReport.OrganizationName})
        / sum( mailto:%7b@AvailWkHrs1%7d,%7bResourceUsageReport.OrganizationName%7d%29*100 - {@AvailWkHrs1},{ResourceUsageReport.OrganizationName})*100 )
.
.
.
.
 
else
    Sum ({ResourceUsageReport.Period1}, {ResourceUsageReport.OrganizationName})
    / sum( mailto:%7b@AvailWkHrs1%7d,%7bResourceUsageReport.OrganizationName%7d%29*100 - {@AvailWkHrs1},{ResourceUsageReport.OrganizationName})*100
 
Where does this go? If I put it in the details, it throws an error so I put it on the footer but it returns no results for Infra...., Manag....., only the small IS, MS type groups.
 
 
This is what I'm using to determine which global variable to display. I'm putting this in the group header:
 
Global NumberVar ISSum1;
Global NumberVar MSSum1;
Global NumberVar MCSum1;

if GroupName ({ResourceUsageReport.OrganizationName}) = "Infrastructure Services"
then 
    ISSum1
   
else if GroupName ({ResourceUsageReport.OrganizationName}) = "Managed Services"
then
     MSSum1
else if GroupName ({ResourceUsageReport.OrganizationName}) = "Management Consulting"
then
     MCSum1
else
    Sum ({ResourceUsageReport.Period1}, {ResourceUsageReport.OrganizationName})
    / sum( mailto:%7b@AvailWkHrs1%7d,%7bResourceUsageReport.OrganizationName%7d%29*100 - {@AvailWkHrs1},{ResourceUsageReport.OrganizationName})*100
 
The above formula returns zero for all conditions except 'else'.
 
@AvailWkHrs1 is from the header & is this:
 
round({ResourceUsageReportHeader.WorkHours1}/8)*8
 
I've been trying to work this out for weeks. Any ideas on how to do this would be most appreciated!
 
Many thanks
Ant
 
 
 
 
 
 
 
 
 
 
 


Replies:
Posted By: hilfy
Date Posted: 13 Mar 2007 at 10:25am
You're really going about this the hard way!
 
You can just do a sum of the details at each group level by using Crystal's Summary Expert (on the menu - Insert|Summary, or the button with an E on it).  This way Crystal does all of the work and you're not doing sums of sums.
 
-Dell


-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics

Posted By: TokyoAnt
Date Posted: 13 Mar 2007 at 4:48pm

Ahh, if only it were that simple...

 
Thanks Hilfy for your input but your answer wouldn't work for two reasons.
Firstly, note the chart below:
 
InfrastructureServices  80
IS-ext                           50
detail1                          20
detail2                          20
detail3                          10         
IS-int                            30
detail1                          10
detail2                          20
ManagedServices         120
MS-ext                          90
detail1                          90
MS-int                           30
detail1                          20
detail2                          10
 
 
If I simply summed the details, there would be no summary for InfrastructureServices or ManagedServices as these have no details & rely on the sum of IS-n & MS-n for their details summary. Do you understand this point? For example, InfrastructureServices needs to sum all details in IS-ext & IS-int for its total.
 
The second reason why a simple sum solution won't work is because if you look at the code, it isn't a simple sum. It actually needs to be a precentage of @AvailWkHrs.
 
Thanks very much for your idea on this Hilfy, but by other answers I've received, I'm coming to the conclusion that this can't actually be done in Crystal, though I challege anyone to prove me wrong on this.
 
Many thanks though
 
Ant
 
 
 

Posted By: satcire
Date Posted: 14 Mar 2007 at 4:39am
What kind of database are you using ?  If I had to do this, I will be doing all the sum in a oracle view before going in the Crystal Report.  It's easier. Or you can do it in a SQL command.
 
Good luck.

Posted By: hilfy
Date Posted: 14 Mar 2007 at 10:36am

I still think summing the details would work.  Assuming Group 1 is at the level of Infrastructure Services/Managed Services and Group 2 is at the level of IS-ext/IS-int etc.  The formulas would look like this:

Sum({detail table.count}, {table.Group1 Field})

and

Sum({detail table.count}, {table.Group2 Field})

Give it a try and see what I mean!

-Dell



-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics

Posted By: TokyoAnt
Date Posted: 14 Mar 2007 at 6:50pm
Hi Hilfy,
 
Thanks for your ideas, but I don't think that will work. You see there is only one 'level'. Even though you & I see a hierachy (IS is a sub group of Infrastructure), Crystal sees only one group, (OrganizationName).
 
For example: The table these are being pulled from have a column named, OrganizationName. This column includes the values:
 
Infrastructure
IS-ext
IS-Int
ManagedServices
MS-ext
MS-int
 
As you can see, they are all coming from one column, hence they are grouped in the same group.  The summary may be done for each change, but it cannot determine what you & I see as sub groups (i.e. IS, MS) as as far as crystal is concerned, IS, MS & Infrastrcuture are just different values of the same group. (Group by Organisation name in SQL terminlogy)
 
I need to use string functions to differentiate any value starting with IS etc. in order to sum all values starting with IS, regardless of IS-ext or IS-int & display this as the Infrastructure total which is why I have tried this failed approach. Otherwise, Infrastructure will always be 0 as Infrastructure itself has no value. It relies on ISn etc.
 
Does this make sense? Can you see another solution to this?
 
Thanks for your interest :)
 
 

Posted By: hilfy
Date Posted: 15 Mar 2007 at 7:06am
Ok, your data structure gives me the missing piece I needed to fully understand what the problem is.  In the future, you might want to include this type of info when you first ask the question so that you can get a better answer sooner.
 
If Infrastructure and Managed Services are your only two top level "groups", you'll need several formulas to do something like this:
 
mailto:%7b@IsInfrastructure - {@IsInfrastructure }
If left({table.OrganizationName}, 2) = 'IS' then {detail table.count} else 0
 
mailto:%7b@IsManagedServices - {@IsManagedServices }
If left({table.OrganizationName}, 2) = 'MS' then {detail table.count} else 0
 
mailto:%7b@TopLevelSum - {@TopLevelSum }
If {table.OrganizationName} = 'Infrastucture' then
  Sum( mailto:%7b@IsInfrastructure - {@IsInfrastructure })
else
  Sum( mailto:%7b@IsManagedServices - {@IsManagedServices })
 
If you have more top level "groups", you'll need an additional "Is..." formula for each and you'll add to the If statement in mailto:%7b@TopLevelSum - {@TopLevelSum }.
 
-Dell


-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics

Posted By: BrianBischof
Date Posted: 15 Mar 2007 at 11:16am
Wow. This is a major thread happening over here. I hope you guys can get it working!

-------------
Please support the forum! Tell others by linking to it on your blog or website:<a href="http://www.crystalreportsbook.com/forum/">Crystal Reports Forum</a>

Posted By: TokyoAnt
Date Posted: 18 Mar 2007 at 5:21pm
Hi Hilfy,
 
Thank you, this is starting to look more like what I need. Please understand I'm a newbie to Crystal & don't quite understand your answer completely though.
 
The code below for example:
 
mailto:%7b@IsInfrastructure - {@IsInfrastructure }
If left({table.OrganizationName}, 2) = 'IS' then {detail table.count} else 0
 
What is mailto:%7b@IsInfrastructure - {@IsInfrastructure }?
 
I'm guessing this is the formula in which I would put
"If left({table.OrganizationName}, 2) = 'IS' then {detail table.count} else 0" in. Correct?
 
 
Also, if mailto:%7b@IsInfrastructure - @IsInfrastructure  & mailto:%7b@TopLevelSum - @TopLevelSum  are formulas as I assume you are saying, where do these go in the report?
 
My guess is mailto:%7b@TopLevelSum - @TopLevelSum  goes in the header & mailto:%7b@IsInfrastructure - @IsInfrastructure  goes in the details? Is this correct?
 
Thank you very much for your clarification.
 
many thanks
Ant 

Posted By: hilfy
Date Posted: 19 Mar 2007 at 5:46am
Yes, mailto:%7b@IsInfrastructure - {@IsInfrastructure } is the formula.  mailto:%7b@IsInfrastructure - {@IsInfrastructure } will not actually be placed on the report.  It is there so that mailto:%7b@TopLevelSum - {@TopLevelSum } can use its value.
 
mailto:%7b@TopLevelSum - {@TopLevelSum } will be placed on the report where you want the totals at the "Infrastructure" and "Managed Services" level.
 
The mailto:%7b@Is - {@Is ...} functions get the values for the specific types of details, returning 0 if the detail record is not of the type that's specific to the formula (IS or MS).  This way you can use these values to sum all of the items that are IS only or MS only.
 
-Dell


-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics

Posted By: TokyoAnt
Date Posted: 25 Mar 2007 at 8:46pm
Hi Hilfy,
 
Thanks fro your response & suggestions.
In implementing what you put forward, I came across what I feel is the crux of the problem here. It's in the
 
If left({table.OrganizationName}, 2) = 'IS' then {detail table.count} else 0
 
conditional.
 
For some reason, my code completely ignores this. It never choses it, even though I've tested the string to be 'IS' when manipulated (by displaying it).
 
I'm currently putting this in a formula as suggested. I've tested the formula to return a simlpe value "7" for example. That works fine, but when I create a global variable, & increment it in the "If" conditional above , it returns 0. Why would this be the case.  Even if I create a simple globalVar := globalVar + 1; globalVar;  it always returns 0.
 
Shouldn't this increment for every row in each group that starts with "IS"?
 
Thanks very much for your thoughts on this
 
Ant
 
 
 

Posted By: TokyoAnt
Date Posted: 25 Mar 2007 at 8:50pm
Even this returns 0;
 
if Left(({ResourceUsageReport.OrganizationName}),2) = "IS"
    then
        111;
 
 
I've displayed Left(({ResourceUsageReport.OrganizationName}),2)
& it returns "IS"
 
Hope this helps.
 
Ant

Posted By: hilfy
Date Posted: 26 Mar 2007 at 7:25am

How are you using the formula?  You don't put the actual formula on the report, but instead you'll use the SUM of the formula.  I created the formula based on your example - does the field data actually start with 'IS'?

Just as a test, put the formula in the details line next to the actual count field.  It should display either the count or 0 depending on what kind of a line it's on.
 
-Dell


-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics

Posted By: TokyoAnt
Date Posted: 30 Mar 2007 at 12:20am
Hey Hilfy,
 
Thanks for all your input. Fortunately (phew) the company structure has changed which meant I just went into the Backend table & trimmed all the fields to the first two characters IS,MC etc. So now I don't need to worry about this conundrum. Apologies for this. It is a very interesting problem without yet a solution. Something tells me that this cannont be done in Crystal as I've tried so many differerent way, many being your ideas as well.
 
Oh well, it's all a learning experience!Ouch
 
Thanks very much anyway!
 
Ant


Print Page | Close Window