question

Eshwar-8614 avatar image
0 Votes"
Eshwar-8614 asked Joyzhao-MSFT commented

SSRS Report Showing distinct sum based on two columns

HI,
I am trying to get sum based on multiple columns below is the requirement sample data:
141553-activityrpt.jpg

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


sql-server-reporting-services
activityrpt.jpg (80.4 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

Joyzhao-MSFT avatar image
1 Vote"
Joyzhao-MSFT answered Joyzhao-MSFT commented

Hi @Eshwar-8614 ,
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:

141859-design.jpg

Preview:

141951-preview.jpg

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.




design.jpg (15.1 KiB)
preview.jpg (58.5 KiB)
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

I will attach a video of the operation:

141896-18.gif


0 Votes 0 ·
18.gif (1.4 MiB)