Sql-server – Sum values at a group level rather than detail

sql-server-2008ssrs

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.

Best Answer

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

Related Solutions

Sql-server – SSRS Merge Data Across 2 Group Values

It is a bit kludgey, but here's an idea sketch... instead of merging the cells, could you fake it by placing an A in the left cell aligned to the right and a D in the right cell aligned to the left? You could then use expressions to control value, alignment, color, and borders of the two separate cells.

Let me know if you need more details. I think this would give you the visual you want. Good luck!

Sql-server – how to group data against more than one conditions

The reason this isn't working for you when you have multiple rows in the employee table is that it is not correlated back to the main query. By this I mean that SQL has no way of knowing which employee to count in your subqueries for a given row in your main query. You can avoid this buy adding a reference back to your main query like this:

SELECT 
    Employees.name as EmpName,
    (SELECT COUNT(LateDays.LateDate)*200 
        FROM Employees E
        INNER JOIN LateDays LD 
            ON E.EmpId = LD.EmployeeEmpId
        WHERE E.EmpId = Employees.EmpId) 
    as TotalFine,

Notice how I aliased the tables in the subquery so they had a different name from those in the outer query. Then in the WHERE clause I referenced the table name from the outer query.

However this is not how I would write your query. It will probably be less than well performing. Instead you can use a pattern where you use a CASE statement to define your condition returning a 1 or a 0 then SUM the values.

The final query looks like this:

SELECT
    Employees.name AS EmpName,
    COUNT(LateDays.LateDate)*200 AS TotalFine,
    SUM(CASE WHEN LateDays.FinePaid = 1 AND LateDays.LateDate IS NOT NULL THEN 1 ELSE 0 END) AS PaidFine,
    SUM(CASE WHEN LateDays.Informed = 1 THEN 1 ELSE 0 END) AS InformedDays
FROM Employees 
INNER JOIN LateDays
    ON Employees.EmpId = LateDays.EmployeeEmpId
GROUP BY Employees.EmpId, Employees.Name

A couple of things of note. I am checking that LateDate is not NULL in the first SUM to match your COUNT. COUNT doesn't actually count a value if it is NULL. Second is that I changed the GROUP by to Employees.EmpId. You generally do not want to GROUP BY a NAME column. What happens when two employees have the same name? It's rare but it does happen. You are better off grouping on the ID column.

EDIT:

Based on having test data I made a few corrections to the query and have a question.

Change1: I had missed a comma in the SELECT list.

Change2: I had forgotten to add Employees.Name to the GROUP BY. You should still have Employes.EmpId to ensure grouping by distinct employees but because you are pulling Employees.Name you do have to include it.

Question: In your test data rows 6 & 7 have the same date but the same EmployeeEmpId. Should this be counted as two for the TotalFine calculation?

Note: Rows 6 & 7 of your test data have an EmployeeEmpId of 6 but that Id doesn't exist in your Employees table test data. I changed it to a 3 for my testing.

Best Answer

Related Solutions

Sql-server – SSRS Merge Data Across 2 Group Values

Sql-server – how to group data against more than one conditions

Related Question