Solved

Calculating percent with sums

Posted on 2012-03-16
6
914 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 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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
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

Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Validating VB6 Function 19 72
Executing multiple ssrs reports from ssis package 20 75
SSRS Dynamic Column Names - Last 8 Weeks 3 63
Reformat SQL - so SSRS can read the columns 25 47
After much searching across the internet I have found that you could not set the name of the file you were attaching to dynamic report subscriptons in Microsoft Reporting Services. I did manage to find one article showing you how your could make a s…
Introduction Earlier I wrote an article about the new lookup functions (http://www.experts-exchange.com/A_3433.html) that ship with SQL Server 2008 R2.  In this article I’m going to show you another new feature of SSRS 2008 R2, this time in the vis…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

739 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