Solved

Subreport totals not adding up to main report figures

Posted on 2012-03-13
6
926 Views
Last Modified: 2012-03-25
I'm having trouble summing with subreports.  I'm creating a budget report that lists all customers/projects with the budget, actual spent and remaining dollar figures.  I'd like the user to be able to click and see a detailed breakdown of what the expenses are and I've gotten this to work but the SUM doesn't work.  Its showing severly inflated sums (within the subreport).  I had the Actual spent set up to SUM the cost of labour and expenses which displayed a figure generally in line with the budget but if I list all the details in the subreport and then have a total line the figure can be 5x that of the budget even tho the items listed only add up to a couple thousand dollars (again which doesn't add up to the listed Actual spent).

(all the buttons on the body are greyed out so I can't format the code)
Main report query
SELECT     Clients.Cltname AS Client_Name, Clients.Cltnum AS Client_Code, Clients.Cinvfee AS Budget, Invoice.WCltName AS Project, SUM(Invoice.Wfee) + SUM(Invoice.Wexp)
                      AS Expenses, Department.DeptName
FROM         Clients INNER JOIN
                      Invoice ON Clients.ID = Invoice.WCltID INNER JOIN
                      Department ON Clients.Engdept = Department.DeptID
WHERE     (Invoice.WCodeCat = 'Bill') OR
                      (Invoice.WCodeCat = 'Exp')
GROUP BY Clients.Cltname, Clients.Cltnum, Invoice.WCltName, Clients.Cinvfee, Clients.Engdept, Department.DeptName
ORDER BY Client_Name, Project

My subreport query is
SELECT     WCltNum AS ClientCode, WCodeCat AS ServiceCategory, WCodeSer AS ServiceCode, WSCDesc AS ServiceDesc, Wexp AS Expenses
FROM         Invoice
WHERE     (WCodeCat = 'Exp') AND (WCltNum = @ClientCode)  - I'm passing the ClientCode from main report to filter the subquery's results
0
Comment
Question by:HSI_guelph
  • 4
6 Comments
 

Author Comment

by:HSI_guelph
ID: 37715548
Copied from original question I deleted.

Expert Comment


by: ValentinoVPosted on 2012-03-13 at 11:24:07ID: 37715230






(General tip: try to split up your story into smaller questions, you're likely to get more experts attracted and it makes things a bit easier too)

I'll try to tackle the "totals in subreport wrong" problem:

Are you sure the subreport query is correct?  You're filtering on ClientCode, but the Clients table is not joined.  In the other query I see that you join Clients and Invoice on Clients.ID = Invoice.WCltID but the filter uses Invoice.WCltNum.
Are the detail lines which your subreport is displaying the ones that you'd expected for that client?

Accept Multiple SolutionsAccept as Solution


 
Your Comment


by: HSI_guelphPosted on 2012-03-13 at 11:53:39ID: 37715433






Ok sorry I'll take out some and post seperate theads.  I'm using my supervisor's account, with his permission, so I'm not sure how the points work and I guess I'm a bit worried I'll max him out or he'll get a large bill in the email, lol.

Part of the problem is that the tables are not tied together correctly.  Some don't have any relationships at all.  I'm not sure who set it up but I think it wasn't set up to optimize retrieval and reports, I'm seeing redundent information everywhere.  

I've tied Clients.ID to Invoice.WCltID because one is the primary key but I could tie the CltNums (ClientCode) from both tables.  The IDs are incremental so I'm not sure how to get the parameter to use the value ID but display something else the user would understand and be able to make a selection on.  

The detail lines show the same client code as the client listed above it but the amounts aren't adding up to the correct amount for spent.  Each record in Invoice table holds only 1 item, such as a submission for gas or time/cost for a subcontractor.  I'm trying to group them so they don't see dozens of gas entries but one subcategory of gas with the sum of the amount.

Since the row above has a client code I thought passing that to the subquery would grab me the information I need.  Would it be better to grab all the data from one query and try to nest a tablix within a row in the main report?  I'm not sure exactly how to do that but when I tried it said I was trying to put dynamic data into static cell.

Edit CommentAccept Multiple SolutionsAccept as Solution
0
 
LVL 37

Accepted Solution

by:
ValentinoV earned 350 total points
ID: 37718639
"The IDs are incremental so I'm not sure how to get the parameter to use the value ID but display something else the user would understand and be able to make a selection on."
When you set up the Available Values in your report parameter, you select one field from the dataset as Value (ID) and one as Label (readable caption).  That way the parameter values are readable to the user and you'll still be able to pass the ID into the other dataset.

"The detail lines show the same client code as the client listed above it but the amounts aren't adding up to the correct amount for spent. "
Your main query shows this: SUM(Invoice.Wfee) + SUM(Invoice.Wexp) AS Expenses
And your subreport query retrieves this: Wexp AS Expenses

Isn't it normal then that the sums differ?

" Would it be better to grab all the data from one query and try to nest a tablix within a row in the main report?"
If performance is not an issue, you could just use one table, set up grouping on the table and implement the ToggleItem property.  In that scenario you actually just need to set up one dataset that retrieves all the data needed in the report.  Don't use GROUP BY, the tablix takes care of that.

If performance is an issue (might be lots of data which is never consulted if the user doesn't click the + icon), then the only option you have is a drillthrough report.  Note that this is not a subreport.  Drillthrough is set up by using the Action page in the textbox properties, with the "Go to report" setting.

More info: Drillthrough, Drilldown, Subreports, and Nested Data Regions (Report Builder 3.0 and SSRS)
0
 
LVL 11

Assisted Solution

by:srikanthreddyn143
srikanthreddyn143 earned 150 total points
ID: 37719877
Your main query is summing both both bill and Exp (  (Invoice.WCodeCat = 'Bill') OR
                      (Invoice.WCodeCat = 'Exp')) but your sub report is getting only 'Exp' . is this right? And also please make sure main query doesn't return any duplicate rows because of joins.
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:HSI_guelph
ID: 37726101
I am trying to incorporate 2 subreports, one for cost of labour and one for cost of expenses (gas, hotels, equipment, etc.).  So I only included the query from one subreport.

The other query is
SELECT     WCltNum AS ClientCode, WCodeCat AS ServiceCategory, WCodeSer AS ServiceCode, WSCDesc AS ServiceDesc, Wfee AS Labour, Whours AS Hours
FROM         Invoice
WHERE     (WCodeCat = 'Bill') AND (WCltNum = @ClientCode)

What I would like is a main report that lists the total $ amount spent (SUM(Invoice.Wfee) + SUM(Invoice.Wexp) AS Expenses) as well as the budget and the remaining $ left and a % (knowing that some will overspend).  Then when the user clicks for more information they will see a subreport listing all the labour costs and another listing all the expenses.  The subtotals of those two reports added together (if you took a calculator to compare it) would add to the Expenses displayed in the main report.

I think my issue is something to do with the join but I find it odd that the subreport sums are not equalling the items listed in the subreport.  The user doesn't need to see dozens or hundreds of entries for each and every cost, I want to group them as like tasks (i.e. gas, hotels, etc).
subreport-totalmess.jpg
0
 

Assisted Solution

by:HSI_guelph
HSI_guelph earned 0 total points
ID: 37738608
I think I have my answer.  The code I'm passing is a master code which can sometimes have subcodes for multiple subprojects related to one code.  I'm going to see about passing both or another id that will be unique so I don't get more results then I should.
0
 

Author Closing Comment

by:HSI_guelph
ID: 37762398
Advice was great, my execution was faulty, lol.  I am going on conflicting information/data along compounded by my lack of knowledge of the business system.  I'm now asking questions with multiple people involved in the business processes and corroborating the data I'm accessing to be sure I'm pulling the right information.
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Suggested Solutions

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
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…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

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

20 Experts available now in Live!

Get 1:1 Help Now