Summarizing with multiple criteria

[CarlaBee]

Hi,
Help...I'm having difficulty.

I have a field on labor entry screen that allows the input of daily scrap numbers. The scrap field requires a reason code each time the qty is added to that field. I have two reason codes - FMS and FSS. The report needs to total each of these reasons separately for a specified time frame.

I also need to be able to total each of these colums by a few other groups so I need a calulation with will allow me to do that.

I created a group on the reason codes and can summarize a breakdown as follows, by job number, reason code and total quantity.

The report looks like this....

Job # labor qty Scrap Reason scrap qty %

12345 500 FMS 18 3.6%
FSS 27 5.4%
67890 14 FMS 1 7.14%
FSS 1 7.14%
TOTAL 514 47 9.14%

I'd like the report to look like this...

Job # Labor qty FMS % FSS %

12345 500 18 3.6% 27 5.4%
67890 14 1 7.14% 1 7.14%
TOTAL 514 19 3.69% 28 5.44%

I need each of these reason codes to be able to total up separately even though the data comes out of the same field.

Let me know if you need more information. Any help would be appreciated.

Thanks.
Carla

 

[CarlaBee]

sorry it looks so sloppy!

 

[lbass]

In the main report, insert a group on job #. Then insert a crosstab where you use job# as the row field, labor quantity as your first summary, and then create these formulas and add them as summaries:

//{@FMS}:
if {table.reason} = "FMS" then {table.qty}

//{@FMS%}:
{@FMS}%sum({table.laborqty},{table.job#})

//{@FSS}:
if {table.reason} = "FSS" then {table.qty}

//{@FSS%}:
{@FMS}%sum({table.laborqty},{table.job#})

Then go to the customize style tab and choose horizontal display and show labels for the summary fields.

This assumes your version of CR can handle this. You should always identify your version.

-LB

 

[CarlaBee]

thanks LB. I've never worked with crosstabs so I'll give it a shot and let you know how it works out. I'm on crystal 8.5. Sorry for leaving that out.

thanks.

 

[lbass]

This won't work in 8.5. While there is a way to calculate the percentages using weighted averages, the display of summaries would be vertical, so you probably should do a manual crosstab.

Insert a group on Job# and then place the formulas {@FMS} and {@FSS} I gave you earlier in the detail section and insert summaries on them at the job ID level. Then drag the results into the group header and suppress the detail section. For the percentages, use:

sum({@FMS},{table.job#})%sum({table.laborqty},{table.job#})

sum({@FSS},{table.job#})%sum({table.laborqty},{table.job#})

Place these in the group header.

-LB

 

[CarlaBee]

thanks I'll try it that way.

 

[CarlaBee]

Hi again,
that's actually where I first started out trying it myself, but I can't get a summary on the {@FMS} and {@FSS} fields.

 

[lbass]

If the formulas are set up as I suggested you can insert summaries on them. You didn't take an extra step and add "whileprintingrecords," did you? Of course they have to be in the detail section to do this, too.

-LB

 

[CarlaBee]

Nope, followed your directions exactly, still no luck.

 

[lbass]

You should be inserting summaries on these (right click->insert summary->sum):

//{@FMS}:
if {table.reason} = "FMS" then {table.qty}

//{@FSS}:
if {table.reason} = "FSS" then {table.qty}

The last two formulas should just be placed in the group header.

-LB

 

[CarlaBee]

Ha! It works! I figured out what I was doing wrong and you don't wanna know. LOL

You're the best!
thanks.