Grouping help. - SQL Server Forums

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.

All Forums

Development Tools

Reporting Services Development

Grouping help.

Author

Topic

chriskhan2000
Aged Yak Warrior

544 Posts

Posted - 2005-06-24 : 11:02:45

Not sure how to put this but here goes.

I have a stored procedure that pulls all records and then I want to summarize it.

Example:


ID	PRODUCT_CODE	RELEASE_DATE	SHIPPED_DATE	SALE
12	ABALL		01-05-2005	01-21-2005	324.00
12	ABALL		01-05-2005	01-25-2005	324.00
14	RSRT200		02-03-2005	02-04-2005	234.00
15	RSTR300		02-03-2005	02-04-2005	234.00
15	RSTR300		02-03-2005	02-12-2005	234.00

					GRAND TOTAL:	1350

What I wanted to do is group it by "ID" and "PRODUCT_CODE" so that it will summarize any items with those two fields for the grand total.

Example:


ID	PRODUCT_CODE	RELEASE_DATE	SHIPPED_DATE	SALE
12	ABALL		01-05-2005	01-21-2005	324.00
						TOTAL:	324.00


14	RSRT200		02-03-2005	02-04-2005	234.00
						TOTAL: 	234.00

15	RSTR300		02-03-2005	02-04-2005	234.00
						TOTAL:	234.00

					GRAND TOTAL:	792.00

With the current situation, since the dates are different, when I try to sum the footer it takes the first example and sums all the data regardless of how I group it in reporting services. So this is what I get.


ID	PRODUCT_CODE	RELEASE_DATE	SHIPPED_DATE	SALE
12	ABALL		01-05-2005	01-21-2005	324.00
						TOTAL:	324.00


14	RSRT200		02-03-2005	02-04-2005	234.00
						TOTAL: 	234.00

15	RSTR300		02-03-2005	02-04-2005	234.00
						TOTAL:	234.00

					GRAND TOTAL:	1350.00

Is it a way or workaround to have it so that I can sum only what's within that group? ReportItems to add the field would be awesome but it doesn't work in the body section.

Any ideas?

jhermiz

3564 Posts

Posted - 2005-06-24 : 16:23:20

Groups have headers and footers, so you simply create the footer after each group and place a SUM or COUNT function in that footer.

Your example is a pretty simple one, if you have a specific question about it post it.

You could do it all in your query and just output in the details section, but you dont need to.

Jon

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]
Imperfection living for perfection -- [url]http://jhermiz.blogspot.com/[/url]

chriskhan2000
Aged Yak Warrior

544 Posts

Posted - 2005-06-28 : 12:31:56

I have been using Groups via headers and footers, but the problem is that if I change the Group Footer the Group Header change with it. Not sure if there's a way to group it separately.

The detail section does what I want, it's just the summarizing of the Total for "SALE" field. Instead of giving me the sum of what is grouped, it gives me the sum of what is pulled from the query.

The first example above shows what is pulled from query with Grand Total: 1350. When I group it from the Report, what the Grand Total should be is 792 and not 1350.

Last example should show what I'm getting and that's not what I want to do. I want what example 2 is showing. Was thinking about doing a SUM(ReportItems!SALE.Value), but at the current moment that can not be done in the body section of the report.

Got any ideas or workaround?

jhermiz

3564 Posts

Posted - 2005-06-28 : 13:41:07

I'm sort of confused (bare with me) on what your exact problem is...

Let us say you have the following data

Ford 100
GM 200
DCX 300
Acme 500

In your report you can group by the customer (Ford, GM, etc...)
and at the footer of this group you add one text box to sum it..in this case 1100 dollars is your total...this is if y ou are grouping by customer.

Now let us say you have the following data

Acme Socket 100
Acme Spare 300
JJ's Bracket 400
JJ's TV 100
JJ's Stereo 200

In this case you have customer - > many products. SO you group by customer and get data like so:


Acme
    Socket 100
    Spare  300
--------------
           400 (total of acme in acme footer)
-----------------------------
JJ's
    Bracket 400
    TV      100
    Stereo  200
---------------
            700 (total of jj's in group footer)
--------------------------------
---------------------------------
1100 (total of both customers in report footer)

So as you see here you can group by customer then show sums for those customers along with a total of all customers in the report footer.

If you want you can accomplish the same thing in a stored procedure (query)

SELECT Customer, SUM(SomePrice) As TotalSales FROM CustomerSales
GROUP BY Customer

Then in your report you just place TotalSales in the details section of each customer. Real simple and straight forward.
Maybe it will help with some sample data, your query, and your exact problem.

Jon

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]
Imperfection living for perfection -- [url]http://jhermiz.blogspot.com/[/url]

chriskhan2000
Aged Yak Warrior

544 Posts

Posted - 2005-06-30 : 11:27:36

Probably I'm not being very clear with my problem. Let me give it another shot. I'm going to walk you through step by step on what I'm trying to do.

1)

Actual data from query:


ID	PRODUCT_CODE	RELEASE_DATE	SHIPPED_DATE	SALE
12	ABALL		01-05-2005	01-21-2005	324.00
12	ABALL		01-05-2005	01-25-2005	324.00
14	RSRT200		02-03-2005	02-04-2005	234.00
15	RSTR300		02-03-2005	02-04-2005	234.00
15	RSTR300		02-03-2005	02-12-2005	234.00

					GRAND TOTAL:	1350.00

From the above example, I have a product that has same product code and ID and same sale price. Just that the ship date is different. What I want to do is just summarize that this product with this id and sale price was shipped on the first date for release and shipped.

So this example shows that what I want to output in Reporting Services:


ID	PRODUCT_CODE	RELEASE_DATE	SHIPPED_DATE	SALE
12	ABALL		01-05-2005	01-21-2005	324.00
						TOTAL:	324.00


14	RSRT200		02-03-2005	02-04-2005	234.00
						TOTAL: 	234.00

15	RSTR300		02-03-2005	02-04-2005	234.00
						TOTAL:	234.00

					GRAND TOTAL:	792.00

2)

The next example is what I'm getting in Reporting Services and it's not giving me what I'm after. I think reason being is that the group will group base on a specific group of fields that you define, but when you sum, it will sum all records being pull regarding what you define in the group. As you can see, everything looks right except for the grand total, which is 1350 and not 792.


ID	PRODUCT_CODE	RELEASE_DATE	SHIPPED_DATE	SALE
12	ABALL		01-05-2005	01-21-2005	324.00
						TOTAL:	324.00


14	RSRT200		02-03-2005	02-04-2005	234.00
						TOTAL: 	234.00

15	RSTR300		02-03-2005	02-04-2005	234.00
						TOTAL:	234.00

					GRAND TOTAL:	1350.00

Hopefully this makes more since. If not, let me know and I can explain more. This is very frustrating because I can use SUM(ReportItems!textbox2.Value) to get the right amount, but only problem is that ReportItems can only be use in Headers or Footers.

jhermiz

3564 Posts

Posted - 2005-06-30 : 12:09:16

quote:
Originally posted by chriskhan2000

Probably I'm not being very clear with my problem. Let me give it another shot. I'm going to walk you through step by step on what I'm trying to do.

1)

Actual data from query:

ID PRODUCT_CODE RELEASE_DATE SHIPPED_DATE SALE
12 ABALL 01-05-2005 01-21-2005 324.00
12 ABALL 01-05-2005 01-25-2005 324.00
14 RSRT200 02-03-2005 02-04-2005 234.00
15 RSTR300 02-03-2005 02-04-2005 234.00
15 RSTR300 02-03-2005 02-12-2005 234.00

GRAND TOTAL: 1350.00

From the above example, I have a product that has same product code and ID and same sale price. Just that the ship date is different. What I want to do is just summarize that this product with this id and sale price was shipped on the first date for release and shipped.

So this example shows that what I want to output in Reporting Services:

ID PRODUCT_CODE RELEASE_DATE SHIPPED_DATE SALE
12 ABALL 01-05-2005 01-21-2005 324.00
TOTAL: 324.00

14 RSRT200 02-03-2005 02-04-2005 234.00
TOTAL: 234.00

15 RSTR300 02-03-2005 02-04-2005 234.00
TOTAL: 234.00

GRAND TOTAL: 792.00

2)

The next example is what I'm getting in Reporting Services and it's not giving me what I'm after. I think reason being is that the group will group base on a specific group of fields that you define, but when you sum, it will sum all records being pull regarding what you define in the group. As you can see, everything looks right except for the grand total, which is 1350 and not 792.

ID PRODUCT_CODE RELEASE_DATE SHIPPED_DATE SALE
12 ABALL 01-05-2005 01-21-2005 324.00
TOTAL: 324.00

14 RSRT200 02-03-2005 02-04-2005 234.00
TOTAL: 234.00

15 RSTR300 02-03-2005 02-04-2005 234.00
TOTAL: 234.00

GRAND TOTAL: 1350.00

Hopefully this makes more since. If not, let me know and I can explain more. This is very frustrating because I can use SUM(ReportItems!textbox2.Value) to get the right amount, but only problem is that ReportItems can only be use in Headers or Footers.

Simple

SELECT ID, MAX(PRODUCT_CODE) As PC, MAX(RELEASE_DATE) AS RD, MIN(SHIPPED_DATE) AS SD, MAX(SALE) AS S FROM YOURTABLE GROUP BY ID

In your example you cannot use DISTINCT, since distinct works on the entire row and not based on a column, the solution is to use GROUP BY with aggregates.

This returns the following fields
ID
PC
RD
SD
S

Now in the report create a group header on ID as your first header. Drop the ID number and PC in that row (the header).
In the details section drop the RD, SD, and S fields. DO NOT FORGET TO ADD A GROUP footer to this group. In the group footer add a text box and do =SUM(S).

Finally, in the report footer put one more textbox and put =SUM(S) again. Run the report and see the results.

Jon

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]
Imperfection living for perfection -- [url]http://jhermiz.blogspot.com/[/url]

chriskhan2000
Aged Yak Warrior

544 Posts

Posted - 2005-06-30 : 16:11:38

Awesome. I did now know distinct applies to all rows and not only spefic fields that's listed. Thanks it works now.

chriskhan2000
Aged Yak Warrior

544 Posts

Posted - 2005-06-30 : 17:38:32

Just curious, does MIN pull NULL Values? Or is there a function that pulls even when it's NULL?

chriskhan2000
Aged Yak Warrior

544 Posts

Posted - 2005-06-30 : 17:40:06

Never mind. I see it now that NULL ignores null value. Is there a function that would recognize NULL Value?

jhermiz

3564 Posts

Posted - 2005-06-30 : 22:06:45

quote:
Originally posted by chriskhan2000

Never mind. I see it now that NULL ignores null value. Is there a function that would recognize NULL Value?

Glad you got it working, look in BOL for Coalesce or IsNull

Jon

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]
Imperfection living for perfection -- [url]http://jhermiz.blogspot.com/[/url]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources