Referencing a Group Value from an Inner Group Value

Question

Referencing a Group Value from an Inner Group Value

SCoffman

Old Hand

Points: 325
More actions
June 13, 2006 at 11:58 am

#173052

I have seen a number of questions on this, but no solution.
I have a report with multiple groups. I perform a SUM aggregate funtion on an upper level group and want to reference that value in a calculation on a lower level group. Basically creating a percentage of the upper level group. Any references using Scope in the SUM aggregate function in the lower level group against the upper level group gives me:
"The value expression for the textbox ‘textbox54’ has a scope parameter that is not valid for an aggregate function. The scope parameter must be set to a string constant that is equal to either the name of a containing group, the name of a containing data region, or the name of a data set."
I can't seem to find a solution for this problem. Any help would be appreciated.
--Steve

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Dave Klug SSCertifiable Points: 5811 More actions · Answer 1

As an alternative, could you return the summed amount in your SQL and then use this in the report? I've used this approach quite a bit to get around this. Another option may be to be use the RunningValues funtion but would depend on how your data is coming back.

Dave

Mike Kendall SSC Eights! Points: 958 More actions · Answer 2

The alternative that I prefer is to give the text box I want to refer back to a meaningful name. Its contents can be referenced elsewhere ( including inner groups ) using the ReportItems! function. The inner groups expression might look something like

= SUM(Fields!InnerGroupColumn.Value) / ReportItems!OuterGroupSum.Value )

Regards

Mike

DAVNovak Hall of Fame Points: 3120 More actions · Answer 3

Gentlemen,

I believe the SUM aggregate function takes two parameters with the second parameter being optional. The second parameter is group level you want the SUM to be taken from. ie.. =SUM(Fields!GroupColumn.Value) / SUM(Fields!GroupColumn.Value, "{name of parent group here}")

The second parameter is a string value and needs to be encased in double quotes.

Dave N

SCoffman Old Hand Points: 325 More actions · Answer 4

I have tried the ReporItems reference as you mention. However, the error I get on that is that ReportItems can only reference items in the Report Header or Report Footer, not a Group Header or Footer.

I have tried referencing the scope value as the group name of the higher level group. That doesn't seem to help. I get the same error.

I have considered modifying my stored procedure to supply the values, however, I am having to use the OPENQUERY method join data from an Oracle Server in another domain to data from one my my SQL Servers in my departmental domain. I would have to use a number of correlated subqueries that would be demanding on both servers everytime it ran. I am hoping to avoid that option if at all possible.

Running Sum is an option that I have considered, but was hoping to handle it with a reference to a higher level group if possible.

Steve