Solved

Summarized a Group calculation

Posted on 2006-10-24
3
554 Views
Last Modified: 2012-06-21
Crystal 9.0
I want to perform a calc (see “@Compare” formula below) on Group 4 subtotals.  I’d like to sum the result of this calc "@Compare" in the higher groups (Group 1, Group2, Group 3).
I am unable to use the Crystal’s Summary function (I assume because the result of “@Compare formula” is in a group section and not in the detail section).   Same problem with running total.   “@Compare” is not available to summarize.

@Compare formula = If column A> Column B then Column A else Column B

Example Groups and Sections
                Column ID:        A                   B                      C

Group 1 – Job  Subtotal                        Group #1 CC sum of @Compare formula      
Group 2 – CSI  Subtotal                        Group #2 CC sum of @Compare formula
Group 3 – CC   Subtotal                        Group #3 CC sum of @Compare formula
Group 4 – Cat   Subtotal                        @Compare formula
Detailed Trans  Section                   


Also - Users want to drill down to the “Detailed Trans” level
Can somebody recommend a direction?  My other attempts (in progress):
1. A subreport to summarize the “Detail Trans” section and pass Cat Subtotals from subreport to Main.    This will allow my main report’s detail section to be “Cat” thus allowing Crystal summary.
2. A formula in the detail section that summarizes amounts base on Cat value.  Link an “On Demand Subreport” if the user needs to drill down to detailed trans level.
0
Comment
Question by:LFreehauf
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 101

Expert Comment

by:mlmcc
ID: 17797847
WHat is the @Compare Formula?

mlmcc
0
 

Author Comment

by:LFreehauf
ID: 17798079
The formula (referred to as "@Compare formula")  is a simple 'if ...then'.  Using the examples above, it's  (If Column A> Column B then Column A else Column B

The actual cut and paste is
((if Sum ({@Total Estimate}, {JCT_CURRENT__TRANSACTION.Category}) > Sum ({@Total Commitment}, {JCT_CURRENT__TRANSACTION.Category})
then Sum ({@Total Estimate}, {JCT_CURRENT__TRANSACTION.Category}) else Sum ({@Total Commitment}, {JCT_CURRENT__TRANSACTION.Category}))
)

It takes the greater of Estimated $ vs. Commitment $ for each line in group 4.  With this formula in Group 4, I cannot perform the simple "Insert>Summary>" in higher groups.  The formula is not available for Summary via "Insert>Summary>"

0
 
LVL 42

Accepted Solution

by:
frodoman earned 350 total points
ID: 17798911
This formula won't be available for summary because of the if..else.  What you can do is store the results in a variable and then show that variable at higher levels.  In simple form it would look like this:

// @Compare
shared numberVar myTotal;
numberVar thisResult;
if ColumnA > ColumnB then
   thisResult := Sum(ColumnA, GroupBy)
else
   thisResult := Sum(ColumnB, GroupBy);

myTotal := myTotal + thisResult;
thisResult;

Now in the other groups you can create a formula to show the variable value:

// @ShowValue
shared numberVar myTotal;

If you need to reset the variable simply create a new formula in the appropriate section to set it to zero.  If you want to reset at different levels you may need to use multiple variables.

frodoman
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
If you're a developer or IT admin, you’re probably tasked with managing multiple websites, servers, applications, and levels of security on a daily basis. While this can be extremely time consuming, it can also be frustrating when systems aren't wor…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question