locked
SSRS 2005: SUM function not behaving as expected RRS feed

  • Question

  • I have a 2005 SSRS report.  It has a List region and inside the List is a Table.  The Table has a bunch of SUM functions in the headers and footers. An example would be:
    =Sum(Fields!BudgetedHours.Value)
    This worked fine when both the List and the Table were using the same data set. But when I changed the List to a different data set, I now get errors saying that the expressions can only refer to the current Dataset scope, or if inside an aggregate, the specified dataset scope.

    I can fix this by adding the scope to the Sum function:

    =Sum(Fields!BudgetedHours.Value,"TableDataSet")
    My question is why I need to do this?  The documentation says: If you omit the Scope parameter, the scope of the aggregate is the innermost data region or grouping to which the report item belongs.

    So why do I need the scope?  It seems like if you don't put in a scope, it should mean that you want the innermost group, which is in the table.

    Tuesday, September 15, 2009 9:09 PM

Answers

  • It may let you do it, but it is not going to work properly.

    From: http://msdn.microsoft.com/en-us/library/cc627449.aspx

    By definition, nested data regions are based on the same report dataset. You cannot nest data regions that are based on different datasets. To display data from different datasets, consider using drillthrough reports or subreports. For more information, see Adding Links to a Report and Adding Other Report Items .

    So in your case, when you try to nest with different data sets, one of them must taking precedence (let's say dataset1) and when you try to add an aggregate from dataset2, it wants you to refer to it in aggregate since it is not within the scope of the current dataset.
    • Proposed as answer by dmlenz Wednesday, September 16, 2009 6:48 PM
    • Marked as answer by Charles Wang - MSFT Wednesday, September 23, 2009 8:34 AM
    Wednesday, September 16, 2009 6:48 PM

All replies

  • A table can only be linked to one dataset.  You can specify fields from that dataset by using the syntax Fields!BudgetedHours.Value because that table is only liked to one dataset

    When you do a sum(Fields!BudgetedHours.Value) the default scope is the current table group within that dataset (the same one the table is linked to).  As soon as you try to introduce a field from another dataset, you have to always refer to it in aggregate only (with the scope of the second data set) similar to what you have done
    =Sum(Fields!BudgetedHours.Value,"TableDataSet"
    )

    So, the reason the scope is required is because you are introducing a field that is not in the dataset that the table is linked to.  This should work fine for your case (if your only requirement is to display this value on the table footer).
    Tuesday, September 15, 2009 9:29 PM
  • Thanks for the reply.  Thing is, the field, in this case BudgetedHours, is in the table's dataset.  I didn't change anything with the table.  I only changed the dataset for the List that contains the table.  I had to do that to provide an outer grouping for this report.
    Tuesday, September 15, 2009 9:53 PM
  • I think any child object in a report has to use the same data set.  If a table is nested within a list, both must use the same dataset.  You can get around this if you need to by using subreports.

    Dan
    Tuesday, September 15, 2009 10:09 PM
  • No, they don't have to use the same dataset.  The List and its nested table can have different datasets and they basically work fine.  The only hassle is that the Sum function doesn't seem to be behaving according to how it is documented. 

    I am thinking though that moving the table in to a subreport might be the answer.  The table is identical to another table in another report, and I didn't want to have to change one and not the other.  Time to do some refactoring I think...
    Tuesday, September 15, 2009 11:17 PM
  • It may let you do it, but it is not going to work properly.

    From: http://msdn.microsoft.com/en-us/library/cc627449.aspx

    By definition, nested data regions are based on the same report dataset. You cannot nest data regions that are based on different datasets. To display data from different datasets, consider using drillthrough reports or subreports. For more information, see Adding Links to a Report and Adding Other Report Items .

    So in your case, when you try to nest with different data sets, one of them must taking precedence (let's say dataset1) and when you try to add an aggregate from dataset2, it wants you to refer to it in aggregate since it is not within the scope of the current dataset.
    • Proposed as answer by dmlenz Wednesday, September 16, 2009 6:48 PM
    • Marked as answer by Charles Wang - MSFT Wednesday, September 23, 2009 8:34 AM
    Wednesday, September 16, 2009 6:48 PM
  • I have found that when I need to have a list and a table with similar data sets, that I want to have some fields sumed from the list and some from the table.    I have found that I need to have either the list or table reside in a sub report and that I pass in the data that is needed.

    I would love to find another way to do this, but I have not figured out how so far.


    Good Luck
    Web Developer, App Developer, DBA
    Wednesday, September 16, 2009 9:35 PM