CODEWITHSUNDEEP
reporting-servicesssrs-2008
donviti
donviti

Reputation: 294

SSRS Pivot Columns

I'm trying to tackle pivoting my data in a way that captures duplicates, triplicates etc and places them into the proper column. I want a count of the number of times a customer appears more than one time in my data set. The number of times a customer appears more than 2x's, 3x's, etc. And place those counts into the proper column.

I have tried numerous Count(iif(value, 1, Nothing)) configurations and can't get one to work. Here is my latest stab that didn't result in what I wanted:

=Count(IIF(CountDistinct(Fields!Deposit_AcctNo.Value) > 1, 
Count(Fields!Deposit_AcctNo.Value), Nothing), "Accounts")

didn't work

=Count(IIF(Fields!Deposit_AcctNo.Value = 2, 1,Nothing))

didn't work 

=CountDistinct(IIF(Fields!Deposit_AcctNo.Value = 2, 1,Nothing))

didn't work

=Count(IIF(Fields!Deposit_AcctNo.Value = 2, 
Count(Fields!Deposit_AcctNo.Value),Nothing))

didn't work

=Count(IIF(Fields!Deposit_AcctNo.Value = 2, 
Count(Fields!Deposit_AcctNo.Value),Nothing), "DataSet1")

didn't work

=count(IIF(CountDistinct(Fields!Deposit_AcctNo.Value) = 2, 1, Nothing))

Here is a pic of my desired outcome:

enter image description here

Upvotes: 0

Views: 364

Answers (1)

stubaker
stubaker

Reputation: 1958

If possible, I'd recommend working this out in SQL as I'm sure it's more efficient to aggregate counts there compared to using expressions in reporting services.

That said, here's how I would aggregate counts in reporting services:

Create a table with a single row group and group on the column with duplicates (customer account in your example, FirstName in my image below):

Example table with one row group

Use an expression like the following to count the number of times an item appears in the list (the second parameter for Count is the group name which will scope the aggregate function to groups).

=IIF(Count(Fields!FirstName.Value, "FirstNameGroup") = 2, 1, 0)

This should give you a table with a 1 or a 0 for each list item indicating which set the item falls in:

Sample output using above expression

Finally change the visibility of your row group to hidden, and add a totals row with a sum function wrapped around the original expression:

=Sum(IIF(Count(Fields!FirstName.Value, "FirstNameGroup") = 2, 1, 0))

Which should give you the output you're looking for:

Sample output using totals and after hiding groups

Upvotes: 2

Related Questions