Improve company productivity with a Business Account.Sign Up

x
?
Solved

Help with SSRS code to get AVG from Total.

Posted on 2011-09-07
1
Medium Priority
?
857 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: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

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

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

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 …
A recent question popped up and the discussion heated up regarding updating a COMMENTS (TXT) field in a table using SSRS. http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/MS-SQL_Reporting/Q_27475269.html?cid=1572#a37227028 (htt…
Free Data Recovery software is an advanced solution from Kernel Tools to recover data and files such as documents, emails, database, media and pictures, etc. It supports recovery from physical & logical drive after a hard disk crash, accidental/inte…
Through the video, you can check the migration process of Outlook PST file to PDF. Kernel for Outlook to PDF tool can convert Outlook emails with all attributes like Subject, To, From, Cc, Bcc and other folders such as Inbox, Outbox, Sent Items, Jun…

606 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