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-03-19 : 12:43:10
|
| I have learned from other posts on the web that using sub-reports is not best practices. I am a complete newbie. I watched a tutorial I found on making a report. I made one, and a subreport that ties in the data with a variable. I also set up a report server. Among the many things I am trying to accomplish, one is to use best practices and get this report together as 1 single report, rather than a report with a subreport.Trying to make all this one datasource is hard! I have 3 tables. This is a list of files in one table called tblDoc. It ties the Custodian field to a custodian table with a custodian ID, and a third table for sessions, tying the session id.What I want in the output of the report is to have each session (grouping on the created date/time of the session) then each custodian within that session, and a count off all the documents by doc extension. So here are the table layouts, specifying the fields used:tblEDSessionsIDCreated - date/timeItemCount - numberItemTotal - numberRecordsWritten - numberSize - numbertblCustodiansIDName - Text name of custodiantblDocIDCustodianIDSessionIDDocExt(otherStuff)So I my sql query shows this:SELECT tblEDSessions.Created, tblEDSessions.ItemCount, tblEDSessions.ItemTotal, tblEDSessions.RecordsWritten, tblEDSessions.Size, tblCustodians.Name AS Custodian, tblDoc.DocExt, tblDoc.IDFROM (tblDoc LEFT JOIN tblCustodians ON tblDoc.CustodianID = tblCustodians.ID) LEFT JOIN tblEDSessions ON tblDoc.EDSessionID = tblEDSessions.ID;The session counts and numbers would list at the top, then by custodian, count the ID from tblDoc by DocExt. Make sense? I have this all looking the way I want when I use a sub-report, but in trying to construct one report on one table, I am getting totally lost. I am looking around for articles on grouping and such to see what I can find. Then and I might try to make an access report and upload that to see if that will show me how I need to set it up.Can anyone point me in the right direction as to how I group these right, so that I can get the layout I am looking for? I have it in my solution that uses a summary report and a detail report, but I am getting so stuck trying to make this in 1 report. :-(Any guidance is greatly appreciated. :-)Thanks! |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-03-19 : 13:19:42
|
| can you post your final report structure? |
 |
|
|
misscrf
Starting Member
10 Posts |
Posted - 2009-03-19 : 13:27:41
|
| how do you mean? A picture? the layout or the preview? I'm not sure what you mean.It would be kind of like this:Created1/1/09CustodianJohn DoeFile Type / File CountDoc / 25Xls / 20Ppt/ 15John Doe 60 Docs totalCustodianJane SmithFile Type / File Counthtml / 35vsd / 22msg / 10Jane Smith 67 Docs totalSession / Total Docs1/1/09 / 127Thanks! |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-03-19 : 14:04:37
|
| yup... a picture would be fine. put it in some shared server and post the path |
|
|
|
misscrf
Starting Member
10 Posts |
Posted - 2009-03-19 : 15:12:52
|
| The layout : http://www.geocities.com/misscrf/reportLayout.bmpThe preview of the results (but wrong information): http://www.geocities.com/misscrf/reportpreviewresultswrong.JPGThe results by custodian for the session on 3/17 should be as follows:Custodian1 34,681 Custodian2 622 Custodian3 112,490 Custodian4 38,626 Custodian5 31,828 I also have my query limited to the top 40,000 I did that because it takes forever to render the whole report just for testing. There are over 1,000,000 records to group and total in this dataset.Thanks!Thanks! |
|
|
|
|
|
|
|
|
Consolidating sub-report to single grouped report?
