Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Help with SSRS code to get AVG from Total.

Posted on 2011-09-07
1
Medium Priority
?
839 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

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 …
Hi, I have heard from my friends that it’s not possible to create Label Printing report using SSRS. I am amazed after hearing this words not possible in SSRS. I googled lot and found that it is possible to some of people know about the Report Bui…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses

661 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