Reporting in VS 05 - Sum group totals, not over detail?
-
I am working on my first SSRS report and trying to get the hang of this. I got a report manager up and running and once I get this report up, I hope to learn how to make a subscription so users can choose the database to run it against and pick criteria. For now, I am just trying to get the meat of the report set up right.
I have the layout like this:
The problem is that I am trying to sum my ItemCount, ItemTotal, RecordsWritten, Files, and Size for the entire report, and it looks like it is Summing everything over the grouping that the query has, not the group that The report has.
The query is as follows:
SELECT tblEDSessions.Created, tblCustodians.Name AS Custodian, tblDoc.DocExt, MAX(tblEDSessions.ItemCount) AS ItemCount,
MAX(tblEDSessions.ItemTotal) AS ItemTotal, MAX(tblEDSessions.RecordsWritten) AS RecordsWritten, MAX(tblEDSessions.Size) AS Size,
COUNT(tblDoc.DocID) AS CountOfDocID
FROM tblDoc WITH (nolock) LEFT OUTER JOIN
tblCustodians WITH (nolock) ON tblCustodians.ID = tblDoc.CustodianID LEFT OUTER JOIN
tblEDSessions WITH (NOLOCK) ON tblDoc.EDSessionID = tblEDSessions.ID
GROUP BY tblEDSessions.Created, tblCustodians.Name, tblDoc.DocExt
The sessions tie to the doc table and 1 session has many docs processed. The tbledsessions table has a value of the ItemCount, ItemTotal, RecordsWritten, Files, and Size. In my query that amount repeats for the grouped level of the count of files by doc extension. It looks like this:
My actual report has the session as the main group, with a breakdown by custodian within a session under that, an then a count of files processed within that session, for that custodian by doc ext.
In the end, I want to sum the ItemCount, ItemTotal, RecordsWritten, Files, and Size values for all of the sessions that are being reported on. My problem is that the totals I am getting are enormous, as if it is repeating these values for each count by docext and summing all of that up:
The layout that I showed before allows this query to show with the created as 1 line. Everything in that line is from the tblEDSessions table and those values are only applicable at that level. The drill down links all docs from the tbldoc table that were created within that created session. As you can see by the image of the sample data above, the itemcount, total, etc repeats for every item with a created date. So this is what I need to do - sum these columns for all dates, but only 1 time per date. Make sense?
Can anyone please tell me how to fix this? I have been looking everywhere and I am so close to getting this completed (at least this part of the puzzle!)
Thanks!
-
Okay, I think I understand your issue. Based on all you have shared, you are correct in assuming that you are summing the repeating value in each row. Because your "detail" rows are repeated for each custodian and doc type you are summing the repeated rows. Would doing something like this work?
Sum(Fields!ItemCount.Value/Fields!CountOfDocId.Value)Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question -
Thank you so much for responding. I have been looking everywhere and it has been so hard to find help on this issue. I tried your solution and when I preview the report it says "The Value expression for the textbox "txtSumItemCount' refers to the field 'CountOfDocId'. Report item expressions can only refer to fields within the current data set scope or, if inside an aggregate, the specified data set scope.
Umm, yeah. I'm kind of trying to hold my head still... It keeps spinning. :hehe:
-
ok, I found some extra crap in my report controls that I had from testing. Once I got all that out, I now get results again, but the total is now coming to 159,331,068. I think we are on to something. IT is still not right, because this total should be 739,583. I am playing around with other ideas based on yours, but nothing seems to be working. Any other thoughts? We are so close I can taste it!
-
How about adding a Group Footer for date and doing the SUM there. IF that works you can remove the table footer.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question -
The thing is that right now, the preview of the initial report is showing the date header. Adding a footer and trying to sum there, creates a line right under that and repeats for each date. I need this to just be as a report footer at the end of the entire report, only totaling these columns 1 x per date. I keep trying different calculations but nothing seems to be working,:crying:
-
You can specify a scope to the SUM function so you could try this in the footer:
=Sum(Fields!Column.Value, "DateGroupName")Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question -
Thanks for sticking with me. When I show the Created group footer, and put that in there, it looks like this:
I only did it for the ItemCount as a test and you can see how it shows immediately after the header for Created (which is a drill down that is collapsed.)
the formula in this is =Sum(Fields!ItemCount.Value, "Created")
What I want to do is have it just show at the bottom, with the other totals, but obviously we can see the counts are not right.
What I am thinking is that showing you some of the data may help. So here is that information, to better illustrate what I am working with.
The tblEDSessions table has records for each session:
This is the tblCustodians:
This is the tblDoc:
As you can see there are totals in the ED sessions table, but they are only reportable at this level.
Beyond that, I am tying in the sessions table to see what docs are processed in the tblDoc table pulling the EDSessionID to ID of tblEDSessions. I also pull the Name of the custodian from the custodians table for that part of the drill down. My count of doc exts, is a count of the tblDoc ID by Doc ext, by Custodian...
Does this help to explain my data? Is my query maybe set up wrong?
-
Did you give up on me? 🙁
-
No, I didn't give up, but I had Friday (the 10th) off because it was Good Friday and then had the weekend, and I don't do much with SSC on days off, I use that time for church and family.
Off the top of my head, and I should have recommended this originally, I'd recommend just returning the details, no aggregations, in my query and do all the aggregation using groups in SSRS. If this doesn't work, let me know.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question -
Thank you. I certainly appreciate that you have a life and are helping me out of kindness. I tried your suggestion. It looks like it might work, but the report generation takes forever. I am testing this against a db that has a tbldoc with about 1,000,000 records in it. I used this one, because most of my dbs may be in the 100,000's but I have some that have many more and want to be sure it can report that many.
As I typed this I see that it finished running. It looks like it is just giving me the total for the first session, but not the total for all sessions, totalling each session one time.
The only other thing is that this takes about 30 -60 seconds to run. I uploaded it to the report manager to run it from the web page. Any thoughts?
Thank you again, I really appreciate your help. I am happy to take any suggestions. Anything is better than scouring the internet and finding no help at all!:-)
Viewing 11 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply