Sum values at a group level rather than detail

Question

I have an invoice report I'm developing for some project billing. The main tablix has several groups, in this order:

• ProjectGroup
• TicketGroup
• NoteGroup
• Details (actual billing details)

The data can end up looking something like this:

project_id  ticket_id   note_id     hours        billing_agreement_id billed_hours
----------- ----------- ----------- ------------ -------------------- ------------
1           1           1           0.50         4                    0.75
1           1           2           0.50         5                    1.00
1           1           3           1.00         7                    1.00
1           1           8           5.00         7                    3.00
1           1           8           5.00         2                    2.00
2           2           4           4.00         1                    4.00
2           2           5           2.00         6                    3.00

In other words, a ticket is assigned to a project, notes are entered on a ticket (with a corresponding number of hours worked), and note hours are then billed to one or more agreements/contracts (if a block time agreement is exhausted, the remainder may spill over to another agreement, for example). This is all working fine, as far as the application is concerned.

So now I'm creating the invoice report, and I need to provide a grand total of hours worked at the end of the report. Take a look at the 4th and 5th rows (with note_id = 8). The report footer uses Sum(hours), which then ends up counting that note as 10 hours. Both note_id and hours come from the note row, and should be summed accordingly. Including hours in the grouping expression for NoteGroup has no effect on the behavior of Sum, naturally.

Is there a straight-forward way to make Reporting Services sum up hours, looking only at the grouped values for that group, rather than the detail rows? I can't think of anything particularly elegant as of yet. I can fall back to weird custom code stunts if necessary.

Answer 1

Well, after a lot of experimenting, I couldn't find a good way to do it in Reporting Services. I tried using a custom function to accumulate total hours (called in the cell displaying the hours in the NoteGroup header), then another custom function in the table footer which would retrieve the value, but the accumulated values would reset on page breaks.

In the end, I've mangled the query into assorted subqueries. It sucks, but it works. Would be sweet if you could do something like SUM(hours) OVER(DISTINCT note_id PARTITION BY client_id)...