Solved

How to total a field with a complex expression in it

Posted on 2014-01-20
5
425 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
[X]
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
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

How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
How can I setup ssrs report  as UTF-8 . 7 28
Learning SQL Reporting Services 2014 1 56
performance query 4 43
SSRS Deployment to Web Error 1 33
Have you ever had to extract data from a Microsoft SQL Server database and export it to an Excel file, but did not want to use a DTS package? The concept in this article is not new, but it is the answer and will also work on 64-bit SQL boxes.   …
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…
Suggested Courses

738 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