RDLC report - Duplicate data issue in grouping

Question

I have a RDLC report in which I want grouping by username and status which has value open and closed. I am getting duplicate username when both the value of status is present for the username.

In the expression, I have used below code -

=IIf (
Fields!Status.Value.ToString().ToLower().Trim() = "open",
Count(Fields!Status.Value),
0 )

=IIf (
Fields!Status.Value.ToString().ToLower().Trim() = "closed",
Count(Fields!Status.Value),
0)

My output is this one - Current Output

And I want this output - Required Output

Answer 1

I would simply use a matrix instead of a table.

You can then have a rowgroup by Username, a column group by Status and then the value would simple be =CountRows()

So, using your sample data as my dataset query.

declare @t table (UserName varchar(10), [Status] varchar(10))

INSERT INTO @t VALUES 
('User1', 'Closed'),
('User2', 'Closed'),
('User3', 'Closed'),
('User3', 'Closed'),
('User3', 'Closed'),
('User3', 'Closed'),
('User3', 'Closed'),
('User3', 'Closed'),
('User4', 'Closed'),
('User4', 'Closed'),
('User4', 'Closed'),
('User4', 'Open')

select * from @t

Create a new report and add a matrix control.

Drag the fields to the respective column and rows headers as shown below.

Next, set the Value cell's expression to =CountRows()

This will give us a basic matrix output.

Next to add the totals...

In the row and column group panel below the main design window, right-click the group, then select "add total" then "After", repeat this for both row and column groups.

Set the expression for the new cells to, again, =CountRows()

The final design looks like this.

(note all the cells that show expression are set the the same =CountRows() expression.)

The final output looks like this (after a bit of bold and shading)