We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

Summarized a Group calculation

LFreehauf
LFreehauf asked
on
Medium Priority
569 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.
Comment
Watch Question

Mike McCrackenSenior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013

Commented:
WHat is the @Compare Formula?

mlmcc

Author

Commented:
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>"

Commented:
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

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.