Solved

Calculating percent with sums

Posted on 2012-03-16
6
913 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
Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

 
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

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

Hi All, I am here to write a simple article to move SSRS (SQL Server Reporting Services) reports from one server to another. When I have faced the same issue to move reports those were developed by developer on development server and now need to …
It is helpful to note: This is a cosmetic update and is not required, but should help your reports look better for your boss.  This issue has manifested itself in SSRS version 3.0 is where I have seen this behavior in.  And this behavior is only see…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

820 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