grouping data to a field

Hey
Im doing a report wich is causing me some trouble:
Im doing a dataset selecting all values regarding the company, and then
im mapping the fields to textboxes in my VS designer. This should
result in a list with all my customers and their data.
Now below each customer i would like to place a table holding data from
another dataset, but i only want the data in the table to correspond to
the company just above the table.
I have placed both the customer data and the table in a list item, but
the problem seems to be the data in the table. I either get evrything
each time for all customers, or i only get the first customer data
below each customer.
Can anyone tell me how i can make this work.
Thanks
JimmyHi
I think there are two ways to solve this
first and the easiest :
get all the data in one dataset and use grouping facility in the reporting
services to group the data
second :
use sub report to build the details and pass the company id from the
parent report to the sub report
Joe
"Jimbo" wrote:
> Hey
> Im doing a report wich is causing me some trouble:
> Im doing a dataset selecting all values regarding the company, and then
> im mapping the fields to textboxes in my VS designer. This should
> result in a list with all my customers and their data.
> Now below each customer i would like to place a table holding data from
> another dataset, but i only want the data in the table to correspond to
> the company just above the table.
> I have placed both the customer data and the table in a list item, but
> the problem seems to be the data in the table. I either get evrything
> each time for all customers, or i only get the first customer data
> below each customer.
> Can anyone tell me how i can make this work.
> Thanks
> Jimmy
>|||Hey Joe
Ok, its not optimal, but it got the job done.
Im actually using a combination of the two.
Your posting lead me on the way though :)
Thanks
Jimmy

Group By??

Hi,

I have a pretty simple problem for a SQL programmer regarding GROUPINGS:

I have two tables tblShoppingCarts and tblProducts.
The first table stores all shopping cart data.
I would like to retrieve all this data (cart and all related product
information) BUT need to GROUP BY tblShoppingCarts.sessionid.

Also, any suggestions on fields to include in the shopping cart would be
much appreciated.

Thanks for your help.

NOTE: To retreive all cart information run the stored procedure EXEC
spGetShoppingCarts NULL, NULL

--------------

CREATE TABLE [dbo].[tblProducts] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[category] [int] NOT NULL ,
[publishdate] [smalldatetime] NOT NULL ,
[title] [varchar] (100) NOT NULL ,
[shorttitle] [varchar] (30) NULL ,
[version] [float] NOT NULL ,
[build] [int] NOT NULL ,
[shortDescription] [varchar] (1000) NULL ,
[description] [varchar] (4000) NOT NULL ,
[retailPrice] [money] NOT NULL ,
[oemPrice] [money] NULL ,
[hasImage] [bit] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblShoppingCarts] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[sessionid] [bigint] NOT NULL ,
[publishdate] [smalldatetime] NOT NULL ,
[modifieddate] [smalldatetime] NULL ,
[productid] [int] NOT NULL ,
[quantity] [int] NOT NULL
) ON [PRIMARY]
GO

CREATE PROCEDURE spGetShoppingCarts

@.id int = NULL,
@.sessionid bigint = NULL

AS

-- Get all shopping carts
IF (@.id IS NULL) AND (@.sessionid IS NULL)

BEGIN

SELECT
sc.[id],
sc.sessionid,
sc.publishdate,
sc.modifieddate,
sc.productid,
p.[title] AS ProductTitle,
p.[shorttitle] AS ProductShortTitle,
sc.quantity,
COUNT(sc.sessionid) AS ProductCount
--p.discount,
--p.quantity AS DiscountQuantity,
--p.code
FROM
tblShoppingCarts sc,
tblProducts p
WHERE
sc.productid = p.[id]
GROUP BY
sc.[id],
--I WANT TO GROUP BY SESSIONID!!!!!!!!!!!!!!!!!! WHY DOESNT THIS
WORK??
sc.sessionid,
sc.publishdate,
sc.modifieddate,
sc.productid,
p.title,
p.shorttitle,
sc.quantity

ORDER BY
sc.[modifieddate] DESC

END

ELSE

BEGIN

-- Get shopping cart by session
IF (@.id IS NULL)

BEGIN

SELECT
sc.[id],
sc.publishdate,
sc.modifieddate,
sc.productid,
p.[title] AS ProductTitle,
p.[shorttitle] AS ProductShortTitle,
sc.quantity,
ProductCount = ( SELECT COUNT(sc.productid) FROM tblShoppingCarts sc
WHERE sc.sessionid = @.sessionid )
--p.discount,
--p.quantity AS DiscountQuantity,
--p.code
FROM
tblShoppingCarts sc,
tblProducts p
WHERE
sc.productid = p.[id]
AND sc.[sessionid] = @.sessionid

END

-- Get shopping cart by id
ELSE

BEGIN

SELECT
sc.[id],
sc.publishdate,
sc.modifieddate,
sc.productid,
p.[title] AS ProductTitle,
p.[shorttitle] AS ProductShortTitle,
sc.quantity,
COUNT(sc.productid) AS ProductCount
--p.discount,
--p.quantity AS DiscountQuantity,
--p.code
FROM
tblShoppingCarts sc,
tblProducts p
WHERE
sc.productid = p.[id]
AND sc.[id] = @.id

GROUP BY
sc.[id],
sc.publishdate,
sc.modifieddate,
sc.productid,
p.title,
p.shorttitle,
sc.quantity
END

END
GOThanks everyone for your help!

I finally worked out after a bit of reading exactly how the GROUP BY
statement works and why I would only use it if I where aggregating
information (ie SUM, COUNT etc).
The solution for the stored procedure was:

CREATE PROCEDURE spGetShoppingCarts

@.id int = NULL,
@.sessionid bigint = NULL

AS

-- Get all shopping carts
IF (@.id IS NULL) AND (@.sessionid IS NULL)

BEGIN

SELECT
sc.sessionid,
LastUpdated = MAX( sc.publishdate ),
TotalPrice = SUM(sc.quantity * p.retailPrice)
FROM
tblShoppingCarts sc,
tblProducts p
WHERE
sc.productid = p.[id]
GROUP BY
sc.sessionid
ORDER BY
2 DESC

END

ELSE

BEGIN

-- Get shopping cart by session
IF (@.id IS NULL)

BEGIN

SELECT
sc.[id],
sc.publishdate,
sc.modifieddate,
sc.productid,
p.[title] AS ProductTitle,
p.[shorttitle] AS ProductShortTitle,
sc.quantity,
TotalPrice = (sc.quantity * p.retailPrice),
sc.discount,
sc.code
FROM
tblShoppingCarts sc,
tblProducts p
WHERE
sc.productid = p.[id]
AND sc.[sessionid] = @.sessionid

END

-- Get shopping cart by id
ELSE

BEGIN

SELECT
sc.[id],
sc.publishdate,
sc.modifieddate,
sc.productid,
p.[title] AS ProductTitle,
p.[shorttitle] AS ProductShortTitle,
sc.quantity,
TotalPrice = (sc.quantity * p.retailPrice),
sc.discount,
sc.code
FROM
tblShoppingCarts sc,
tblProducts p
WHERE
sc.productid = p.[id]
AND sc.[id] = @.id

GROUP BY
sc.[id],
sc.publishdate,
sc.modifieddate,
sc.productid,
p.title,
p.shorttitle,
sc.quantity,
sc.discount,
sc.code ,
p.retailPrice
END

END
GO

"Stephen McCormack" <stephenm@.mwebsolutions.com.au> wrote in message
news:XdOMa.235$JI4.5088@.news-server.bigpond.net.au...
> Hi,
> I have a pretty simple problem for a SQL programmer regarding GROUPINGS:
> I have two tables tblShoppingCarts and tblProducts.
> The first table stores all shopping cart data.
> I would like to retrieve all this data (cart and all related product
> information) BUT need to GROUP BY tblShoppingCarts.sessionid.
> Also, any suggestions on fields to include in the shopping cart would be
> much appreciated.
> Thanks for your help.
> NOTE: To retreive all cart information run the stored procedure EXEC
> spGetShoppingCarts NULL, NULL
> --------------
> CREATE TABLE [dbo].[tblProducts] (
> [id] [int] IDENTITY (1, 1) NOT NULL ,
> [category] [int] NOT NULL ,
> [publishdate] [smalldatetime] NOT NULL ,
> [title] [varchar] (100) NOT NULL ,
> [shorttitle] [varchar] (30) NULL ,
> [version] [float] NOT NULL ,
> [build] [int] NOT NULL ,
> [shortDescription] [varchar] (1000) NULL ,
> [description] [varchar] (4000) NOT NULL ,
> [retailPrice] [money] NOT NULL ,
> [oemPrice] [money] NULL ,
> [hasImage] [bit] NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[tblShoppingCarts] (
> [id] [int] IDENTITY (1, 1) NOT NULL ,
> [sessionid] [bigint] NOT NULL ,
> [publishdate] [smalldatetime] NOT NULL ,
> [modifieddate] [smalldatetime] NULL ,
> [productid] [int] NOT NULL ,
> [quantity] [int] NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE PROCEDURE spGetShoppingCarts
> @.id int = NULL,
> @.sessionid bigint = NULL
> AS
> -- Get all shopping carts
> IF (@.id IS NULL) AND (@.sessionid IS NULL)
> BEGIN
> SELECT
> sc.[id],
> sc.sessionid,
> sc.publishdate,
> sc.modifieddate,
> sc.productid,
> p.[title] AS ProductTitle,
> p.[shorttitle] AS ProductShortTitle,
> sc.quantity,
> COUNT(sc.sessionid) AS ProductCount
> --p.discount,
> --p.quantity AS DiscountQuantity,
> --p.code
> FROM
> tblShoppingCarts sc,
> tblProducts p
> WHERE
> sc.productid = p.[id]
> GROUP BY
> sc.[id],
> --I WANT TO GROUP BY SESSIONID!!!!!!!!!!!!!!!!!! WHY DOESNT THIS
> WORK??
> sc.sessionid,
> sc.publishdate,
> sc.modifieddate,
> sc.productid,
> p.title,
> p.shorttitle,
> sc.quantity
> ORDER BY
> sc.[modifieddate] DESC
> END
> ELSE
> BEGIN
> -- Get shopping cart by session
> IF (@.id IS NULL)
>
> BEGIN
> SELECT
> sc.[id],
> sc.publishdate,
> sc.modifieddate,
> sc.productid,
> p.[title] AS ProductTitle,
> p.[shorttitle] AS ProductShortTitle,
> sc.quantity,
> ProductCount = ( SELECT COUNT(sc.productid) FROM tblShoppingCarts sc
> WHERE sc.sessionid = @.sessionid )
> --p.discount,
> --p.quantity AS DiscountQuantity,
> --p.code
> FROM
> tblShoppingCarts sc,
> tblProducts p
> WHERE
> sc.productid = p.[id]
> AND sc.[sessionid] = @.sessionid
> END
> -- Get shopping cart by id
> ELSE
> BEGIN
> SELECT
> sc.[id],
> sc.publishdate,
> sc.modifieddate,
> sc.productid,
> p.[title] AS ProductTitle,
> p.[shorttitle] AS ProductShortTitle,
> sc.quantity,
> COUNT(sc.productid) AS ProductCount
> --p.discount,
> --p.quantity AS DiscountQuantity,
> --p.code
> FROM
> tblShoppingCarts sc,
> tblProducts p
> WHERE
> sc.productid = p.[id]
> AND sc.[id] = @.id
> GROUP BY
> sc.[id],
> sc.publishdate,
> sc.modifieddate,
> sc.productid,
> p.title,
> p.shorttitle,
> sc.quantity
> END
>
> END
> GO