Solved

Calculating percent with sums

Posted on 2012-03-16
6
906 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
  • 3
  • 2
6 Comments
 
LVL 15

Assisted Solution

by:Tim Humphries
Tim Humphries earned 250 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 250 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 run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

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…
A recent questions about how to add SSRS named instances, couldn't find any that talks about SQL server 2008, anyway I decided to help by creating some screen shots. The installation is straightforward, you just pop the SQL server 2008 installati…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

758 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now