Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

MAtrix report counting a field ??? Expand / Collapse
Author
Message
Posted Tuesday, July 9, 2013 9:56 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 7:34 AM
Points: 299, Visits: 498
HI
I have a matric report which gets data for a specific month first thru last day of month

it then displays name as row and day of month in column the details show an "R" if a criteria is met.

It works ok, but at the end I want to count the number of "r"s problem is a date may have more than one

example

Name..........1...2...3...4...... total
John...........R...R.................... 2

But John may have 3 for the 1st thus showing me 4 as a total


Thanks
Joe
Post #1471723
Posted Tuesday, July 9, 2013 11:41 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 7:34 AM
Points: 299, Visits: 498
Well its a little or maybe a lot hacky...
But what I did was to take the dayofyear and did a distinctcount on it

Post #1471767
Posted Tuesday, July 9, 2013 4:47 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 5:49 PM
Points: 1,787, Visits: 5,722
The other way is to use the CountDistinct aggregate function in the total field of your report...

Like this, which counts "A"s...

=CountDistinct(Iif(LEFT(Fields!Column1.Value,1)="A","A",""))



MM


  • MMGrid Addin
  • MMNose Addin


  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • Post #1471898
    « Prev Topic | Next Topic »

    Add to briefcase

    Permissions Expand / Collapse