SSRS recursive account - how to get aggregates only for children, not for the parent?

Question

Disclaimer : I am SSRS n00b, so there are not too many rotten tomatoes :)

I have a hierarchy of employees and managers with whom I created Reporting Services using the Recursive Parent property . Here's a rough idea:

-Jim Bob Ray -Steve Ricky Bobby Terry 

For example, I need people counting under Steve, but when I call Count(Fields!EmployeeID.Value, "Details", Recursive) , I can get 3 instead of 2 . Of course, I can simply subtract 1 in the case of an account, but that will not work when I need the amount. So the real question is: how to get a recursive aggregate that excludes the parent?

Additional Information: I have only one group, which is the default group. It is configured just like this example , so I am grouping by EmployeeID and I have a recursive parent set for ManagerID.

---

Update

A few workarounds I tried to no avail:

• Adding a line inside a group gives the same results because it is still in the same area as another line of details.
• Adding a subtotal - does not work because it is outside the group of parts, so it displays once rather than once for each manager.

Any ideas?

Answer 1

You could consider a couple of things.

1) you could count the number of SQL queries supplying the report and then just report it for each group using

  =FIRST(Fields!FieldThatHasCount.Value). 

2). You can use the report code block to iteratively call a function, increment the counter by a common variable, or do some arithmetic between the parent sections and child sections to reflect the score when your report is presented. I know that I had difficulties with this approach depending on the version of SSRS and the level of complexity, nesting, subscriptions, etc. In your report.

Answer 2

How to get a recursive collection Sum that excludes a parent?

This should work, since you still have access to the current field value.

 =Sum(Fields!EmployeeSalary.Value, "Details", Recursive) - Fields!EmployeeSalary.Value