Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


How to total a field with a complex expression in it

Posted on 2014-01-20
Medium Priority
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!!!!
Question by:HSI_guelph
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
  • 4
LVL 22

Accepted Solution

Nico Bontenbal earned 2000 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


Author Comment

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!

Author Comment

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.

Author Comment

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


Author Closing Comment

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

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

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

Written by Valentino Vranken. Introduction: The first step of creating a SQL Server Reporting Services (SSRS) report involves setting up a connection to the data source and programming a dataset to retrieve data from that data source.  The data…
In this short article I will be talking about two functions in the SQL Server Reporting Services (SSRS) function stack.  Those functions are IIF() and Switch().  And I'll be showing you how easy it is to add an Else part to the Switch function. T…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…

610 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