Referencing a Group Value from an Inner Group Value

  • 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

  • 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

  • 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
  • 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

  • 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

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

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