Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Help with SSRS code to get AVG from Total.

Posted on 2011-09-07
1
Medium Priority
?
851 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: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying 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

After much searching across the internet I have found that you could not set the name of the file you were attaching to dynamic report subscriptons in Microsoft Reporting Services. I did manage to find one article showing you how your could make a s…
Steps to solve SSRS SQL 2008 R2 User Access Control (UAC) Permission Error With the introduction of SQL Server 2008 R2 and Vista (Windows 7 as well) came new enhanced security features. One of the features included was User Access Control (UAC) t…
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…
Suggested Courses

578 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