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 |
|
chriskhan2000
Aged Yak Warrior
544 Posts |
 Posted - 2005-05-17 : 14:31:12
|
I need some help with this one report and hopefully someone can give me some advice. I have a stored procedure that pull some records. Some of these records are identical in that there might be a date difference. There's 2 fields that I have some up the value base on the primary ID. In Reporting Services, I'm grouping these records by Product Type. Everything shows up as intended but my footer sum is way off. It seems to not corresspond with the records being output. Here's the output without grouping:Product Code Part ID Qty Hours DateSTEEL 1234 12 15 01/02/2004STEEL 1234 12 15 02/21/2004RUBBER 4433 15 12 02/22/2004PLASTIC 3433 234 19 02/20/2004PLASTIC 3433 234 19 04/20/2004 ---------------- TOTAL: 80 Here's what I want and group it by Product Code and Part ID:Product Code Part ID Qty Hours DateSTEEL 1234 12 15 01/02/2004RUBBER 4433 15 12 02/22/2004PLASTIC 3433 234 19 02/20/2004 ---------------- TOTAL: 80 As you can see, I don't want the total to be 80 for the second example. I want it to sum what's being displayed, because I just want a summary of each product by product code. The problem is the date, or else Distinct would have removed it. The date is shown when it was release so it doesn't matter about the rest of the date except for the first.Any ideas? |
|
|
chriskhan2000
Aged Yak Warrior
544 Posts |
Posted - 2005-05-17 : 15:20:08
|
| After looking at this, is it possible to use custom code to sum what is already displayed? I wonder if that is possible or not. |
 |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2005-05-17 : 15:30:42
|
| you should use GROUP BY to consolidate your results, not DISTINCT, and take the MIN() or MAX() of the date column (or exclude the date column from your report). If you have two different date values, then it obviously will result in two rows.On the SQL Server side of things, read up and practice with GROUP BY clauses to make sure they make sense. It's a very important fundamental concept of SQL in general and also report writing.By the way -- never, ever wrap a DISTINCT around your SQL when you are writing reports and doing any kind of calculations on the result. I cannot stress this enough! Always make sure that you have grouped things properly and you have written a clear, logical SQL statement. Adding DISTINCT to reports to make the data "look right" is the worst thing you can do as a report writer. If you find you think you need to add DISTINCT to your SELECT statements, you should restructure them and re-write them properly, often with a derived table or two and proper usage of GROUPing.- Jeff |
|
|
|
chriskhan2000
Aged Yak Warrior
544 Posts |
Posted - 2005-05-17 : 16:25:34
|
Jeff,Thanks for the response and advice. I know it's a problem with my grouping, and will need to play around with it more. Min and Max will probably not work with my situation because sometimes some Product Code could be Null but same dates an everything.Example:ID PRODUCT CODE QTY DATE HOURS123 STEEL 25 01/02/2003 15322 NULL 14 03/24/2004 20322 PLASTIC 14 03/24/2004 20532 NULL 32 04/23/2003 15224 NULL 22 01/23/2004 32224 RUBBER 22 01/20/2004 32 As for my query, the calculation is done within a nested selected query with no distinct and then reference back to the main query base on Primary Key which is supposed to be distinct because of line number items. Meaning that a primary ID can have 50 different line per order, so distinct will prevent showing 50 records from same work order. The outer query only displays the primary key, product code, and date, while the nested query sum all hours for each line per item as described above. The problem comes into play when some line items, does not have the product code as description because the user who entered it got lazy or something. So then it will be NULL for product code. This shows 2 separate entries, one Null and the other not. Since I already did the sum with my nested query, it will display 2 identical sum of the hours for same primary key. Probably I can try not to sum it for the nested query and then do all the calculation within reporting services, but then the issue will be that it will display all 50 record per primary key.Any ideas? |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2005-05-17 : 16:31:00
|
| >>Min and Max will probably not work with my situation because sometimes some Product Code could be Null but same dates an everything.I can't really help you out if you change the conditions from post to post -- that was not part of your initial question ... I am afraid if I address your last post, there will be something ELSE you didn't mention, like the QTY column could be NULL or something.You've been at this site long enough and asked enough questions that you should know what information you need to provide to get help efficiently. How about the table structures, some sample data that covers ALL possibilities, and what you'd like the expected results to be? - Jeff |
|
|
|
chriskhan2000
Aged Yak Warrior
544 Posts |
Posted - 2005-05-17 : 17:32:48
|
| Sorry Jeff. It didn't occur to me until you mention about Min and Max and I tested it out. That's when I notice that there are a few out of a couple thousands of records that has same primary key but Null Product Code. That's why I stated that it doesn't work. The only workable way I know as of now is using sub reports. Using my main query to output and then reference it by object field with my nested query and display it that way. The main query displays the records I'm after, it's just the nested query that messed things up for me. Of course this will present problems when trying to export it to other formats, which subreports can't be displayed. I will play around with it a bit more to get a better grip of all the data that's there before asking more questions.Thanks. |
|
|
|
|
|
|
|
|
RS Group Help