Solved

How to total a field with a complex expression in it

Posted on 2014-01-20
5
399 Views
Last Modified: 2014-01-21
I have a field that has a complex expression for the textbox value.
=Switch((Fields!Project_Budget.Value - Fields!TotalLabourSpent.Value)<= 0, 0,
         (Fields!EndDate.Value < Today()), 0,
         (Fields!Project_Budget.Value - Fields!TotalLabourSpent.Value) > 0, (Fields!Project_Budget.Value - Fields!TotalLabourSpent.Value))

Open in new window


But somehow I have to calculate a total for this.  I tried sum(ReportItems!LabourRemaing.Value) but it tells me that 'Aggregate functions can be used only on report items contained in page headers and footers.'

Any idea of how to do this?  I could sum it but tell it to ignore the values where the EndDate has passed already or the negative values but offhand I am not sure how to do it.

Any help is greatly appreciated!!!!
0
Comment
Question by:HSI_guelph
  • 4
5 Comments
 
LVL 22

Accepted Solution

by:
Nico Bontenbal earned 500 total points
ID: 39796231
Did you try:
=Sum(Switch((Fields!Project_Budget.Value - Fields!TotalLabourSpent.Value)<= 0, 0,
         (Fields!EndDate.Value < Today()), 0,
         (Fields!Project_Budget.Value - Fields!TotalLabourSpent.Value) > 0, (Fields!Project_Budget.Value - Fields!TotalLabourSpent.Value)))

Open in new window

0
 

Author Comment

by:HSI_guelph
ID: 39796983
That gives me an #Error but I'm going to try fiddling around to see if I can't get that to work.  Thanks!
0
 

Author Comment

by:HSI_guelph
ID: 39797004
[rsAggregateOfMixedDataTypes] The Value expression for the textrun ‘Textbox3.Paragraphs[0].TextRuns[0]’ uses an aggregate function on data of varying data types.  Aggregate functions other than First, Last, Previous, Count, and CountDistinct can only aggregate data of a single data type.
0
 

Author Comment

by:HSI_guelph
ID: 39797029
Oh, adding a CDec to the 0s of my if fixed it!!

=Sum(Switch((Fields!Project_Budget.Value - Fields!TotalLabourSpent.Value)<= 0, CDec(0),
         (Fields!EndDate.Value < Today()), CDec(0),
         (Fields!Project_Budget.Value - Fields!TotalLabourSpent.Value) > 0, (Fields!Project_Budget.Value - Fields!TotalLabourSpent.Value)))

Open in new window

0
 

Author Closing Comment

by:HSI_guelph
ID: 39797041
Thanks!  Just adding a CDec to the expression got it working!
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Written by Valentino Vranken. Introduction: In a previous article (http://www.experts-exchange.com/articles/Microsoft/Development/MS-SQL-Server/MS-SQL_Reporting/Reporting-On-Data-From-Stored-Procedures-part-1.html) I announced that I would writ…
A recent questions about how to add SSRS named instances, couldn't find any that talks about SQL server 2008, anyway I decided to help by creating some screen shots. The installation is straightforward, you just pop the SQL server 2008 installati…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, just open a new email message. In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…

896 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now