0

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

mradima07
  • 3
  • 1
  • 1
  • 5

1 Answers1

0

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. enter image description here

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

This will give us a basic matrix output.

enter image description here

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.

enter image description here

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

enter image description here

Alan Schofield
  • 19,839
  • 3
  • 22
  • 35
  • Thanks a lot for the detailed explanation. It works fine now. – mradima07 Oct 01 '20 at 07:29
  • Glad to help. If this answered you question, can you please mark the answer as accepted. – Alan Schofield Oct 01 '20 at 08:14
  • I did that but as I am a new user so it gives me this message - "Thanks for the feedback! Votes cast by those with less than 15 reputation are recorded, but do not change the publicly displayed post score". – mradima07 Oct 01 '20 at 12:30
  • Don't use the voting buttons, click the 'check mark' under them. Take a look at this article for guidance. https://stackoverflow.com/help/someone-answers – Alan Schofield Oct 01 '20 at 12:33