Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Subreport totals not adding up to main report figures

Posted on 2012-03-13
6
Medium Priority
?
972 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
[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
  • 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 1400 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 600 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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 

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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

609 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