CODEWITHSUNDEEP
ssrs-2008reporting-services
Kartik Patel
Kartik Patel

Reputation: 9603

Find Average in SSRS Report

Hello I have following column in SSRS Report.

Column Name(Title)

Mehta, Natasha(18)----its Title+(count) value
Rodriguez, Lauren(19)
Harwood, Matt(19)
Lloyd, David(19)

I want to find Average(18+19+19+19/4) from above column in SSRS Report.How can i do?

Upvotes: 1

Views: 894

Answers (2)

Adrian Torrie
Adrian Torrie

Reputation: 2844

Assuming the column is called 'Title', the following may work:

SQL

=SUM(CAST(LEFT(RIGHT([Title], 3), 2)) AS int) / COUNT([Title]) * 1.0

SSRS

=Sum(CDbl(Left(Right(Fields!Title.Value, 3), 2))) / CDbl(Count(Fields!Title.Value))

If we use the first row of data (Mehta, Natasha(18)) as a test string to step through this, we get:

  1. Sum(CDbl(Left(Right(Fields!Title.Value, 3), 2))) / CDbl(Count(Fields!Title.Value))
    • This takes the 3 right-most characters of the string 'Mehta, Natasha(18)' which will return the string: '18)'
  2. Sum(CDbl(Left(Right(Fields!Title.Value, 3), 2), 2))) / CDbl(Count(Fields!Title.Value))
    • or Sum(CDbl(Left('18)'), 2), 2))) / CDbl(Count(Fields!Title.Value))
    • This takes the 2 left-most characters from the sting '18)', returning the string: '18'
  3. Sum(CDbl(Left(Right(Fields!Title.Value, 3), 2))) / CDbl(Count(Fields!Title.Value))
    • or Sum(CDbl('18')) / CDbl(Count(Fields!Title.Value))
    • Converts the string '18' into an double, returning: 18.0
    • The CAST() function in SQL (which can perform the equivalent of CDbl()) can be read about here.
  4. Sum(CDbl(Left(Right(Fields!Title.Value, 3), 2))) / CDbl(Count(Fields!Title.Value))
    • This will sum the converted amounts that exist in the column, if we assume all your rows mentioned in your example are in the column then this will give the equivalent of...
    • Sum(18.0, 19.0, 19.0, 19.0) / CDbl(Count(Fields!Title.Value))
    • This will return the integer value of: 75
  5. Sum(CDbl(Left(Right(Fields!Title.Value, 3), 2))) / CDbl(Count(Fields!Title.Value))
    • or 75 / CDbl(Count(Fields!Title.Value))
    • This will count the number of rows, in this case, and convert to a double.
    • Note: This will force a conversion after the summation, i.e. converting the integer value of 4 and returning a double value of: 4.0
  6. Sum(CDbl(Left(Right(Fields!Title.Value, 3), 2))) / CDbl(Count(Fields!Title.Value))
    • or 75 / 4.0
    • Giving the final result of: 18.75
    • Note: If the denominator is not a double it will perform truncation of the number (in SQL it will round the number)

Upvotes: 1

Ido Gal
Ido Gal

Reputation: 527

I would have used SQL functions in the dataset query itself, in order to separate the numbers into an independent column. It righter to get the data properly into the report than fixing it inside.

Upvotes: 0

Related Questions