Group Level Formula's & Grouping

[6453kelly]

I have the following questions regarding a report I'm trying to develop in Crystal.

1. I have a formula that is calculated at the group level. It is the sum (field A) divided by the distinct count of (field B). The formula works fine, but I now want to have a grand total for the formula, and it says it cannot create the running total. How do I get around that?

2. I have the report grouped by person. There is a summary field that has the max(field C). I want to group the groups by this # (max(field C)). How can I do that. I can sort by that field easy enough, but I want to group by it and have subtotals for it also.

Hope this makes sense...first time poster.

Any help would be appreciated.

 

[lbass]

1-By grand total, do you mean you want the sum of the group level formula results? Or do you want the same calculation at the grand total level?

2-What version of CR are you using? You should always post this, as potential solutions vary by version.

-LB

 

[6453kelly]

a sum of the group level formula results.

crystal 8.5

 

[lbass]

1-Use a variable to sum the summary, as in:

//{@accum} to be placed in the group section containing {@yoursummary}:
whileprintingrecords;
numbervar sumcalc := sumcalc + {@yoursummary};

//{@display} to be placed in the report footer:
whileprintingrecords;
numbervar sumcalc;

2-Create a SQL expression:

(
select max(A.`field`)
from Table A
where A.`groupfield` = Table.`groupfield`
)

You can now use this SQL Expression in a formula and/or to group on.

-LB

 

[6453kelly]

OK so #1 worked swimmingly. Thanks.

For #2 I'm a little confused, so I'm going to give you a more detailed description.

Sample Raw Data

Emp # > Rate > Amount
001 > 0.35 > 10
001 > 1.70 > 20
001 > 0.00 > 10
002 > 0.35 > 20
002 > 0.35 > 10
003 > 0.35 > 20
004 > 1.70 > 10

I grouped the data by Emp # and put in 2 summaries (maximum rate & sum amount) so the data looks like this (details suppressed)

001 > 1.70 > 40
002 > 0.35 > 30
003 > 0.35 > 20
004 > 1.70 > 10

Now this is where I run into trouble. I now want to group by the maximum rate so there is a subtotal of the amount by the max rate. Looking something like this:

0.35
002 > 0.35 > 30
003 > 0.35 > 20
0.35 Total = 50

1.70
001 > 1.70 > 40
004 > 1.70 > 10
1.70 Total = 50

 

[lbass]

You cannot group on a summary, so you need to create a SQL expression that creates a summary that is read like a database "field" in the main report. Please give it a try and you will see what I mean--the SQL expression WILL be available for grouping. Go to the field explorer->SQL expression and create it there.

-LB

 

[6453kelly]

(
select max(A.`field`)
from Table A
where A.`groupfield` = Table.`groupfield`
)

Could you define A, field, and groupfield for me.

The table that contains the rate is called 'eePayCodes', the table that contains the Employee # is called 'eeKeys'
So would it look like the following?
(
select max(eePayCodes.rate)
from eePayCodes
where ???? = eeKeys.empnumber
)

Your help is appreciated.

 

[lbass]

Try something like:

(
select max(A.rate)
from eePayCodes A, eeKeys B
where
A.empnumber = B.empnumber and
B.empnumber = eeKeys.empnumber
)

The first where clause should be based on the field used for linking the two tables--if it's not empnumber, change it. The A and B are aliases--leave them as is.

You would need to use the punctuation appropriate to your datasource, so check the database->show SQL query in on one of your reports accessing the same datasource, to see what the punctuation should look like around tables/fields in the SQL expression.

-LB