Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Help with SSRS code to get AVG from Total.

Posted on 2011-09-07
1
Medium Priority
?
846 Views
Last Modified: 2012-05-12
Hello,

I have field that totals up a column on a report. The expression in this field is:

[Sum(TOTAL_COST_PER_UNIT_AREA)]

This field with the expresion is named TOTAL_COST_PER_UNIT_AREA1. I like to get an average of the subtotals in this field. So, I have this custom code (see code) that is in this field, and I call the custom code via the following:

=Code.CalculateAverage(AVG(Fields!TOTAL_COST_PER_UNIT_AREA.Value), InScope("TOTAL_COST_PER_UNIT_AREA"))

The problem is TOTAL_COST_PER_UNIT_AREA is not the field I want to average. I want to average what is in TOTAL_COST_PER_UNIT_AREA1, the subtotals. However, TOTAL_COST_PER_UNIT_AREA1 is not recongized as a field. What can I change to get the average of the subtotal?
Private m_total As Double
Private m_count As Integer

Public Function CalculateAverage(average As Double, inTotalCostAcre as Boolean) As Double

If Not inTotalCostAcre Then
   ' Average of TotalCostAcre
    Return average

  Else If inTotalCostAcre Then

   ' Subtotal of inTotalCostAcre
   m_total = m_total + subtotal
   m_count = m_count + 1
   Return subtotal

Else 
        ' Average of Subtotal
       Dim avg as Double
       avg = m_total / m_count

       m_count = 0
       m_total = 0
        Return avg
End If
End Function

Open in new window

0
Comment
Question by:gtrapp
1 Comment
 
LVL 17

Accepted Solution

by:
dbaSQL earned 2000 total points
ID: 36502013
>>> However, TOTAL_COST_PER_UNIT_AREA1 is not recongized as a field. What can I change to get the average of the subtotal?
I believe you just need a totals value.  Right-click on your group, Add Totals, this should allow you to put your Avg(TOTAL_COST_PER_UNIT_AREA1) in the cell.
 
Here's a more detailed write up on the same:
http://beyondrelational.com/blogs/jason/archive/2010/07/03/aggregate-of-an-aggregate-function-in-ssrs.aspx


I think MAYBE for v2005 you'd need to add the subtotal column, use =IIF(Inscope("TOTAL_COST_PER_UNIT_AREA"),SUM(Fields!Amount.value),AVG(Fields!Amount.value))
For v2008, you could just modify the total column cell expression like this: =AVG(Fields!Amount.value)

I say 'maybe' because I cannot test it.  but, the totals value should get you there.

0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Time Corrections for Reports Working with a report, we made some interesting discoveries about the time corrections/updates We are using the following Parameters: Starting Entered Date (Date) formatted as Data type: "Date/Time" Ending Entered …
Hi All, I am here to write a simple article to move SSRS (SQL Server Reporting Services) reports from one server to another. When I have faced the same issue to move reports those were developed by developer on development server and now need to …
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
Loops Section Overview
Suggested Courses

971 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question