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.

  • 2
    post your query and table details
    – dbenhur
    Apr 19 '12 at 21:53
  • The Sum function can take a second parameter for scope. From the top of my head, this can be the name of a dataset or a group name. Perhaps you can utilize that to get the correct total?
    – Jeroen
    Apr 22 '12 at 18:53

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)...

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.