Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Calculating percent with sums

Posted on 2012-03-16
6
Medium Priority
?
916 Views
Last Modified: 2012-03-25
I'm trying to calculate the remaining money in the budget left in dollars and percents and I'm getting no where fast.   I'm calculating the actual money spent with
=SUM(Fields!Labour.Value) + SUM(Fields!Expenses.Value)
and the remaining $ amount with
=Fields!Budget.Value - (Sum(Fields!Labour.Value)+ SUM(Fields!Expenses.Value))
but I'm getting wierd results when I try to get a %.

I've formated the text box to display as a % so all I should have to do inside is take the remaining $ and divide by the budget to get the remaining as a percent.  To do this I've had to use
= (Fields!Budget.Value - (Sum(Fields!Labour.Value)+ SUM(Fields!Expenses.Value))) / Fields!Budget.Value

Is there no way I can access the value of the textbox instead?  I tried ReportItems.txtRemaining.value but I got an error when I ran the report.  Is there a more elegant way to code this?  Maybe I should have done it in the query?
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
  • 3
  • 2
6 Comments
 
LVL 15

Assisted Solution

by:Tim Humphries
Tim Humphries earned 750 total points
ID: 37732320
Hi,

The way you are performing the calculation is basically correct. There is no way of referencing an individual cell in a report table. You could do this sort of thing  in the query - it depends how many such calculations you have to do and how complex / messy this may make the report.
I have lots of reports with lots of calculations in them - sometimes it just isn't easy to do these in the source query. In your instance you would have to perform a sub query to get the totals and join to that to get the percentage figures. Best to do this in the report in the way you have.
However, you are obviously summing a number of values for labour and expenses, implying that you are performing this calculation at a group level. The value that will exist for Budget will be the value for the last row in the rowset at that grouping level. You need to make sure that the value for budget is the correct one at the group level you are performing the SUMs on the other fields.

Tim
0
 
LVL 25

Assisted Solution

by:TempDBA
TempDBA earned 750 total points
ID: 37733500
Doing it from the query will be a better solution. However your methodology also looks fine. What are the errors you are receiving? By weird result do you mean to say that the result is 0?
0
 

Author Comment

by:HSI_guelph
ID: 37738300
I was getting the wrong figures because I was calculating the wrong items.  I was ending up with the % spent so then I tried to minus that from 100 (= 100 - (calculation that gives me 93.5%) and then I'd get 9350 %, lol.  I've got it worked out now but I was wondering if I could access the textbox value or store it into a variable.  I'm just think that calculating the same thing multiple times (=SUM(Fields!Labour.Value) + SUM(Fields!Expenses.Value) is used in 3 seperate textbox expressions for each row in the report) is a bit of a waste and would affect the performance of the report.
0
How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

 
LVL 15

Expert Comment

by:Tim Humphries
ID: 37738393
Hi,

You can't reference the cells in a report. You could only store the values if you created a second table linked to the same dataset and then called a function you placed in code to hold the values.

Unless your report returns thousands of rows and is running on a limited server and performance is critical I wouldn't expect these calculations to make any significant difference to your report. Try it - run the report without these calculations and then put them in and see if there's any difference.
The Report Server log (if enabled) will show the difference in rendering time - I don't think it'll mkae much difference.

Tim
0
 

Accepted Solution

by:
HSI_guelph earned 0 total points
ID: 37743985
Our server is VERY slow for some reason.  We virtualized it recently but there is sometimes a 5-10 second pause as we wait for the report to load up.
0
 

Author Closing Comment

by:HSI_guelph
ID: 37762396
Thanks for the input!  My inexperience is the main issue here as I become familiar with the development program.
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

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…
A recent question popped up and the discussion heated up regarding updating a COMMENTS (TXT) field in a table using SSRS. http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/MS-SQL_Reporting/Q_27475269.html?cid=1572#a37227028 (htt…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

688 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