SSRS Report Showing distinct sum based on two columns

Question

HI,
I am trying to get sum based on multiple columns below is the requirement sample data:

In this the sum should be sum of distinct TimeTaken records based on UserName and ActionDate.
like for combination of TestUser1 and '2021-07-01 18:27:02.547' there are three records but sum should consider it as 1 record
so for TestUser1 the sum should be (102+115+220) for TestUser2it should be (102+147+147)

Appreciate your inputs.
Thanks in advance,
Eshwar

Answer 1

Hi @Eshwar ,
You want to select a unique [TimeTaken] value for summation based on different [ActionDate] of the same user.
I have tested it locally. The following are my steps.
First of all, when I created the dataset, I inserted an extra column [Rank] in the query statement, which does not affect your final table, but is used to filter the [TimeTaken] value in the same [ActionDate].
The query is as follows:

SELECT        UserName, AccntID, ActionDate, TimeTaken, ROW_NUMBER() OVER (PARTITION BY ActionDate  
    ORDER BY UserName ASC) AS Rank  
    FROM            YourTableName  

Then add the parent group, Group on [UserName]

Insert row——(Insige Group—Below)

Use expressions:

=SUM(IIF(Fields!Rank.Value = 1, Fields!TimeTaken.Value, 0))  

If you want to get the final sum, insert a row outside the group and use the same expression as above. The final report design we obtained is as follows:

Preview:

Best Regards,
Joy

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.