Group Total Summary Help

I hope someone can help me with this one. I can't seem to find a way to solve my problem. I am converting a report from Crystal to RS. In Crystal I am using global variables to keep track of group totals for a final summary. I need a similar result from RS. Data example

Group A PK Field Summary Data Field 1 250 2 300 Group A Total 550 Group B 3 100 4 50 Group B Total 150 Grand Total 700

The underlying query contains detail data and I am using a table with two group levels. All details are hidden.

To calculate the totals at the detail level I need to know what the total value for the entire group is. This leads me to my problem, it is not possible (as far as I can tell) to summarize a summary (I get an error). I have tried using the code window to store variables but the value returns a 0. I found a suggestion here http://msdn2.microsoft.com/en-us/library/bb395166.aspx under Distinct Sum, but I can't call the function using the Sum command given that the formula to calculate the value is already using the sum command. I hope this makes sense.

Thanks,

Simone

Hello Simone,

In your example, I assume that the rows with 1, 2, 3, & 4 are the detail rows?

If so, In your Group A & B footer, put the expression =Sum(Fields!Data.Value)

Then place the same in your Grand Total row, summary column.

Hope this helps.

Jarret

|||

Hi Jarret,

Thanks for your response. The rows 1, 2, 3, & 4 are actually summary rows of the details. All details are hidden. This is where my problem lies. The details are in the dataset but in the report I have them rolled up. To obtain each summary I have a formula that needs to know the summary for each group to determine the outcome. For example:

Group Summary 1 > 0 Use Formula A

Group Summary 1 <= 0 User Formula B

I can get the correct summary at this level, but I then need to total all of the summary records and obtain a second group total. Finally I need to sum all secondary group totals to obtain a grand total.

Thanks,

Simone

|||

I'm not sure I understand what you are trying to do with the formula, but...

Try using the same expression in your Grand Total summary that you are using in your Group Footer 1 and 2.

Example data:

Fruit Count Date

Apples 35 4/12/07

Apples 10 4/12/07

Apples 15 4/13/07

Apples 10 4/13/07

Pears 5 4/12/07

Pears 16 4/13/07

Pears 4 4/13/07

Plums 30 4/13/07

Here's how I am picturing your table:

GH1

GH2

Details =Fields!Count.Value

GF2 (by Fruit) =Sum(Fields!Count.Value)

GF1 (by Date) =Sum(Fields!Count.Value)

Grand Total =Sum(Fields!Count.Value)

In this case, your report would look like (with the details hidden):

4/12/07

Apples 45

Pears 5

Date Total: 50

4/13/07

Apples 25

Pears 20

Plums 30

Date Total: 75

Grand Total 125

Hope this helps.

Jarret

|||

Thanks again for your detailed answer. You have the right idea with the data, but because the detail data needs the summary for the total group I am not able to carry the expression down. I get an error (can't sum expression with aggregates.). Using your example, we would need to know the summary of total fruit for the day to determine the percentage of daily fruit represented by pears (or apples or oranges). I would then need to summarize all percentages for an overall total. For now I overcame my issue by bringing back group totals in my query. I was just hoping I wouldn't need to do this. In crystal I was able to create a global variable that could be set with each group and displayed at the end. I can't find a way to do this in RS.

|||

Have you try this : RunningValue(Expression, Function, Scope)

|||

I get the same error message:

Aggregate functions cannot be nested inside other aggregate functions.

|||

Maybe this will help. Here is the expression located at the first group level:

=iif(sum(FieldA) < 0
,sum(FieldA)*FieldB
,iif(sum(FieldC) = 0
,0
,iif(sum(FieldD * FieldC)/sum(FieldC) < 0
,(sum(FieldC)-sum(FieldE))*5.25
,(sum(FieldC)-sum(FieldE))*(sum(FieldD * FieldC)/sum(FieldC))
)
)
)

I get the correct result at this level. Now I need to sum all of these values for a grand total. There are no values at the detail level.

|||

Try putting that same exact expression in your other group level, and your report footer (for your grand total).

=iif(sum(FieldA) < 0
,sum(FieldA)*FieldB
,iif(sum(FieldC) = 0
,0
,iif(sum(FieldD * FieldC)/sum(FieldC) < 0
,(sum(FieldC)-sum(FieldE))*5.25
,(sum(FieldC)-sum(FieldE))*(sum(FieldD * FieldC)/sum(FieldC))
)
)
)

Jarret

|||I don't get the right total because the formula depends on the summary of each individual group total and not the overall total. The expression above evaluates each detail record for the first group level and produces a result based on that group's total. I did try to add the scope to the expression in the second group level total, but this resulted in a scope error. Unfortunately, there doesn't seem to be an easy solution but I am extremely grateful for the advice.