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
Medium Priority
916 Views
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
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
• 3
• 2

LVL 15

Assisted Solution

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

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

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

LVL 15

Expert Comment

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

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

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

## Featured Post

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…
###### Suggested Courses
Course of the Month5 days, 18 hours left to enroll