asked on

Summarizing a percent by a grouped category

Hi, I am new to crystal reports, and picked up a half finished report form a predecessor. Looking to calculate a percent of usage for a category.

Structure looks like this.
GH1 - category summary - total hours for category
GH2 - nothing
GH3 - Item Summary - total hours for item, % for each item (summary of detail/potential)
Details - entry line for each item

Had two ways iu was trying to get this piece of information. Either getting a count of each item in a category and dividing the total hours by this and our set potential amount, or summarizing the percent, but i cannot get either of these to work.

any help is greatly appreciated

Mike McCracken

You want the detail total / potential

Which field is potential?

Sum({Detail Value}, {Summary Group}) / Sum(Potential Field{)

mlmcc

Cameron Neiswender

ASKER

The potential field is hard number 173, per each item, its not a field in the report

Mike McCracken

SO it should be

Sum({Detail Value}, {Summary Group}) / (Count({Detail Value}, {Summary Group}) * 173)

Cameron Neiswender

ASKER

So, This is what i have,

(Sum ({@VariableHrlyHours}, {EMEM.Equipment}) / (DistinctCount ({EMEM.Equipment},{@GroupCombinedCategories} )*173))

It is returning a value for the first item in the group, and for a value of 1/Nth of what it should be, N being the number of items in the group, i.e. 1/5th for a group with 5 items.

Mike McCracken

Where did you put it?

It has to be in the group 3 header or footer.

mlmcc

Cameron Neiswender

ASKER

Putting it in the group 3 header gives me a value for each item, not for the group, but the value is still 1/Nth of what it should be
Design-Capture.PNG
Preview-Capture.PNG

Mike McCracken

Can you show the detail section and take another snap shot?

mlmcc

Cameron Neiswender

ASKER

Preview with details
PPreview-with-details.PNG

Mike McCracken

It looks like the totals are correct for the hours. Where is the problem with the value?

Is that the @Test% formula?

The value looks correct. If you want to display it as a % then set the format. You may have to multiply by 100 to move the decimal

mlmcc

Cameron Neiswender

ASKER

the 76 % was existing, the %test formula is the one i created, it shows as 0.38 on the first section. What i am trying to get is a percent by category, i.e the 10 Wheel dump, (grey Bar). the value there is a summary of total hours for all items in the category, i need a percent by category, but cannot sum the percent of each item

Mike McCracken

How is the 76% calculated?

Change the %test to the below and show as a %. It will be 38.xx%

(Sum ({@VariableHrlyHours}, {EMEM.Equipment}) / (DistinctCount ({EMEM.Equipment},{@GroupCombinedCategories} )*173)) * 100

Open in new window

mlmcc

Cameron Neiswender

ASKER

Formula CalcEquip Usage and a format formula to add the *100 & "%"

Sum ({@VariableHrlyHours}, {EMEM.Equipment})/173

the 76% is correct, the .38 is that value divided by count in that category

attached is a slide with notes,
PPreview-with-detailsand-notes2.PNG

ASKER CERTIFIED SOLUTION

Mike McCracken

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Cameron Neiswender

ASKER

That worked perfectly. Thank you for all of your help, it is truly appreciated