locked
Grouping Problem RRS feed

  • Question

  • Hi all-

    Another grouping issue.

    I have a tablix with 5 groups, which works fine.  The grouping is as follows:

    Level1: Business

    Level2: System

    Level3: TrxTime

    Level4: TrxType

    Level5: (Details)

    Due to a change in the bus. reqmts I need to add a sixth group, product, as a child of system.  Adding the group is not a problem, however when I right click the group and select "Add Total" it adds 2 new rows, each of which is linked to the "System" group, not the "Product" group.  In one of those rows it adds the default SUM calculations (=sum(Fields1.Total).  In the other row it copies the expressions I put in for the System group (=sum(Fields1.Total, "System").  

    I delete one of these rows as it is not necessary.  However, with the remaining row if I try and write an expression to sum by product (i.e. =sum(fields.total,"product") it give me the old "scope parameter is not valid" error.  

    Any idea where I am going wrong?


    Bonediggler

    Thursday, October 18, 2012 2:38 PM

Answers

  • Hi Bonediggler,

    In Reporting Services, the “Add Total” command uses the “SUM” function by default and it is context-sensitive. For example, when we add totals for a row group and click “Add Total” on the shortcut menu in the Group pane, a new row outside the current group is added. This behavior is by design. So, the default scope of the aggregate value in the new total row is the parent group of the row group. If the row group is the outmost group, the default scope is the dataset.

    In your case, when specifying the expression with the scope of the row group “product”, the report will display error. Just as you posted above, we can manually insert a row inside the “product” group to get the exact aggregate value.

    For more information, please see:
    Calculating Totals and Other Aggregates:
    How to: Add a Total to a Group or Tablix Data Region:

    Regards,
    Fanny Liu


    Fanny Liu

    TechNet Community Support

    • Proposed as answer by Fanny Liu Monday, October 22, 2012 3:37 AM
    • Marked as answer by Bonediggler Monday, October 22, 2012 1:25 PM
    Monday, October 22, 2012 3:36 AM

All replies

  • The solution (at least so far) is to chuck the standard method and rather:

    1) Manually insert a row in the desired location

    2) Tell it to group on product (which creates the product group)

    3) Manually insert aggregate expressions in appropriate text boxes.

    This seems to circumvent the frustration described in my first post.    


    Bonediggler

    Thursday, October 18, 2012 3:17 PM
  • If you tried inserting the new group by clicking System and adding child group, try, instead, adding a parent to TrxTime. In a hierarchy, you can have more than one child but only one parent.
    Thursday, October 18, 2012 3:26 PM
  • Hi Bonediggler,

    In Reporting Services, the “Add Total” command uses the “SUM” function by default and it is context-sensitive. For example, when we add totals for a row group and click “Add Total” on the shortcut menu in the Group pane, a new row outside the current group is added. This behavior is by design. So, the default scope of the aggregate value in the new total row is the parent group of the row group. If the row group is the outmost group, the default scope is the dataset.

    In your case, when specifying the expression with the scope of the row group “product”, the report will display error. Just as you posted above, we can manually insert a row inside the “product” group to get the exact aggregate value.

    For more information, please see:
    Calculating Totals and Other Aggregates:
    How to: Add a Total to a Group or Tablix Data Region:

    Regards,
    Fanny Liu


    Fanny Liu

    TechNet Community Support

    • Proposed as answer by Fanny Liu Monday, October 22, 2012 3:37 AM
    • Marked as answer by Bonediggler Monday, October 22, 2012 1:25 PM
    Monday, October 22, 2012 3:36 AM