February 1, 2016 at 10:29 am
I have a report that displays the sum of a particular field like this:
=SUM(iif(Fields!isFinished.Value = 1, Fields!isFinished.Value, 0))
Sometimes it works, but sometimes I get the wrong totals because it's counting one particular field more than once because that field happens to contain the same Id more than once.
Here is some sample data that shows the results that I'm dealing with:
SELECT 'Jimmy' AS Person,'12345' As weaponId,1 AS isFinished
UNION ALL SELECT 'Jimmy','12345',0
UNION ALL SELECT 'Jimmy','789',1
UNION ALL SELECT 'Alex','12345',1
UNION ALL SELECT 'Alex','12345',1
UNION ALL SELECT 'Alex','12345',0
UNION ALL SELECT 'Kate','53274',1
UNION ALL SELECT 'Kate','789',1
UNION ALL SELECT 'Kate','83752',1;
I only need the report to SUM the row on each questionId once. So a sample report would just have totals like this:
only count each weapondId ONCE
Totals:
Jimmy = 2
Alex = 1
Kate = 3
After some googling, I tried playing around with ROW_NUMBER and RANK in my SQL query, but that didn't solve my problem.
Is there a way to do this in SSRS or even the SQL?
Thanks!
February 1, 2016 at 1:06 pm
Should your Output have Alex = 2?
February 1, 2016 at 1:08 pm
sharonsql2013 (2/1/2016)
Should your Output have Alex = 2?
No because it's the same weaponId. I only want to count isFinished once for each weaponId.
Thanks
February 2, 2016 at 7:13 am
You can do it in SQL using a subquery, turn it into a distinct list there and then summarise.
WITH CTE AS (
SELECT 'Jimmy' AS Person,'12345' As weaponId,1 AS isFinished
UNION ALL SELECT 'Jimmy','12345',0
UNION ALL SELECT 'Jimmy','789',1
UNION ALL SELECT 'Alex','12345',1
UNION ALL SELECT 'Alex','12345',1
UNION ALL SELECT 'Alex','12345',0
UNION ALL SELECT 'Kate','53274',1
UNION ALL SELECT 'Kate','789',1
UNION ALL SELECT 'Kate','83752',1
)
SELECT Person, SUM(isFinished)
FROM (SELECT DISTINCT Person, weaponId, isFinished
FROM CTE) t
GROUP BY Person
February 2, 2016 at 8:12 am
Thanks!
I was wondering, do you know if it would be possible to combine both queries so that I get something like this?
SELECT 'Jimmy' AS Person,'12345' As weaponId,1 AS isFinished, 2 As Finished_Total
UNION ALL SELECT 'Jimmy','12345',0, 2 As Finished_Total
UNION ALL SELECT 'Jimmy','789',1, 2 As Finished_Total
UNION ALL SELECT 'Alex','12345',1, 1 As Finished_Total
UNION ALL SELECT 'Alex','12345',1, 1 As Finished_Total
UNION ALL SELECT 'Alex','12345',0, 1 As Finished_Total
UNION ALL SELECT 'Kate','53274',1, 3 As Finished_Total
UNION ALL SELECT 'Kate','789',1, 3 As Finished_Total
UNION ALL SELECT 'Kate','83752',1, 3 As Finished_Total;
I realize that the 'Finished_Total' column would repeat, but I need that figure in my final SSRS report.
Thanks!
February 2, 2016 at 10:16 am
I feel like there is probably a more elegant way of doing it, but this was what I came up with. Just joining the summarised dataset to the original dataset.
;WITH CTE AS (
SELECT 'Jimmy' AS Person,'12345' As weaponId,1 AS isFinished, 2 As Finished_Total
UNION ALL SELECT 'Jimmy','12345',0, 2 As Finished_Total
UNION ALL SELECT 'Jimmy','789',1, 2 As Finished_Total
UNION ALL SELECT 'Alex','12345',1, 1 As Finished_Total
UNION ALL SELECT 'Alex','12345',1, 1 As Finished_Total
UNION ALL SELECT 'Alex','12345',0, 1 As Finished_Total
UNION ALL SELECT 'Kate','53274',1, 3 As Finished_Total
UNION ALL SELECT 'Kate','789',1, 3 As Finished_Total
UNION ALL SELECT 'Kate','83752',1, 3 As Finished_Total
), CTE_totals AS (
SELECT Person, SUM(isFinished) AS Finished_Total
FROM (SELECT DISTINCT Person, weaponId, isFinished
FROM CTE
) t
GROUP BY Person
)
SELECT
CTE.*, CTE_totals.Finished_Total
FROM
CTE INNER JOIN CTE_totals ON CTE.Person = CTE_totals.Person
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply