Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
misscrf
Starting Member
10 Posts |
 Posted - 2009-04-08 : 11:55:30
|
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:http://www.geocities.com/misscrf/ReportLayout.JPGThe 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 CountOfDocIDFROM tblDoc WITH (nolock) LEFT OUTER JOIN tblCustodians WITH (nolock) ON tblCustodians.ID = tblDoc.CustodianID LEFT OUTER JOIN tblEDSessions WITH (NOLOCK) ON tblDoc.EDSessionID = tblEDSessions.IDGROUP 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. 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:http://www.geocities.com/misscrf/ReportPreview.JPGCan 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! |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-04-12 : 13:59:20
|
| can you show how data exists in table? are there are one to many relations involved? |
 |
|
|
misscrf
Starting Member
10 Posts |
Posted - 2009-04-13 : 09:15:32
|
sure. the tblEDSessions table looks like this: This ties to tblDoc, with the EDSessionID. The tblDoc table looks like this: I then pull the custodian name from tblCustodians on the CustodianID. The tblCustodians table looks like this: Then, my query in my original post, looks like this, tying everything in.  I realize that the info in my tblEDsessions is repeating superfluously, but that is the only way I can understand to report the layout I am looking for. Each session has a created date and time. The values that this table contains for the ItemCount, Size, etc does not break down by docext and custodian. It is just a hard value. The counts from the doc table will not equal that either. (you can process 1 zip file and end up with 10 Items).So that is basically it. Please let me know if you have other questions.Thanks! |
|
|
|
|
|
|
|
|
Visual Studio 2005 Report Sum Problem
