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.
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.
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.JPG
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!)
I took a look at your query and your report layout. With the layout it looks like you have an awful lot of grouping going on there.
Try distinctcount.. (there is a function report designer)
Personally I create stored procedures in my db server for my report server projects and then call them from the report writer, this way i can manipulate the front end venue to be anything I want not just reporting services.
The problem is that I am trying to sum the distinct values, not count them. The stored proceedure stuff sounds interesting, but I am not sure where to even begin. I have gotten this far by poking around and looking online for articles, posts and such. I got a report server set up and I can uload reports and datasources to it.
Now I am trying to get this grand total to sum and then I will have my first successful report. Next will be creating a data subscription so users can choose which database to run this report against. After that, allow for them to choose based on some criteria. Then more reports for different things. I am thinking that by then I will start to learn enough about stored proceedures (creating, I run them often enough) that I will be able to start trying those out too.
Anyway, any thoughts on how to get this to sum the distinct, or just sum all the values from the created group level, rather than the queried created, custodian,docext repeating level?
tblCustodians.Name AS Custodian,
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
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
See this article for your issue if the above does not work.
Secondly - subscription set up is easy follow the sql 2005 tutorial, and you will be set up in no time flat. Follow it religiously and everything should work as it should. I love the subscription aspect to reporting services. But word to the wise always set up an "admin subscription" for all of your reports with a retention period that you are comfortable with, if an end user makes a mistake you can always send them an historical snapshot from your admin subscription, plus you can put deeper alerts on missing data and validation etc this way before the end user calls you to tell you something is wrong you will hopefully be ahead of their calls(whining)
Yeah, that times out in execution. Besides, it won't help me to put a distinct on the query. The query is giving me data like this: (intentionally so I can drill down through the many groupings) http://www.geocities.com/misscrf/querydata.JPG
The layout that I showed before allows this query to show with the created as 1 line and then a drill down to show the custodians. They have a drill down to the doc extensions processed, which has a count with them. As you can see by the image above, the itemcount, total, etc repeats for every item with a created date. This is what I need to do - sum these columns for all dates, but only 1 time per date. Make sense?