Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 446
  • Last Modified:

How to total a field with a complex expression in it

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
HSI_guelph
Asked:
HSI_guelph
  • 4
1 Solution
 
Nico BontenbalCommented:
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
 
HSI_guelphAuthor Commented:
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
 
HSI_guelphAuthor Commented:
[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
 
HSI_guelphAuthor Commented:
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
 
HSI_guelphAuthor Commented:
Thanks!  Just adding a CDec to the expression got it working!
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.

  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now