Solved

How to total a field with a complex expression in it

Posted on 2014-01-20
5
393 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
Comment Utility
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
Comment Utility
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
Comment Utility
[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
Comment Utility
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
Comment Utility
Thanks!  Just adding a CDec to the expression got it working!
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

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…
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…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

744 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

15 Experts available now in Live!

Get 1:1 Help Now