2 Datasets and Custom Code

Question

Post reply

2 Datasets and Custom Code

Kerrie Sorrell

SSC Eights!

Points: 921
More actions
February 7, 2006 at 7:17 am

#189080

Hi all,
SSRS2000 with VS.net
I was wondering if there is a to get two datasets on the same table with custom code? I have tried to do a subreport but it is not grouping like I need it to. And I can not get it all on the same dataset because it is bring in duplicate data. I was told that my joins are correct, it is just how the tables react. And I can not find a solution to my problem.
Any sugesstions would be appreciate it.
Thanks in advance,
Kerrie

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Michael E. Baker Say Hey Kid Points: 676 More actions · Answer 1

Can you share the SQL statement with the join? You can generalize it.

Kerrie Sorrell SSC Eights! Points: 921 More actions · Answer 2

Thanks for your reply!!!!

Here is from dataset1..

SELECT

SUM(CSIOWNER_ACS.V204060_INVD.AVAIL_INVENTORY) AS AVAIL_INVENTORY, SUM(CSIOWNER_ACS.V204060_INVD.LINE_INVENTORY)

AS LINE_INVENTORY, SUM(CSIOWNER_ACS.V204060_INVD.SCRAP_INVENTORY) AS SCRAP_INVENTORY,

SUM(CSIOWNER_ACS.V204060_INVD.RECV_INVENTORY) AS RECV_INVENTORY, SUM(CSIOWNER_ACS.V204060_INVD.DROP_INVENTORY)

AS DROP_INVENTORY, CSIOWNER_ACS.V204060_OORD.PLAN_RECV_DATE AS PLAN_RECV_DATE,

CSIOWNER_ACS.V204060_OORD.LINE_NBR AS LINE_NBR, CSIOWNER_ACS.V204060_OORD.QTY_ON_ORD AS QTY_ON_ORD,

CSIOWNER_ACS.V204060_OORD.QTY_RECVD AS QTY_RECVD, CSIOWNER_ACS.V204060_OORD.ORDERS_OVERDUE AS ORDERS_OVERDUE,

CSIOWNER_ACS.V204060_OORD.ORDERS_0TO20DAYS AS ORDERS_0TO20DAYS,

CSIOWNER_ACS.V204060_OORD.ORDERS_21TO40DAYS AS ORDERS_21TO40DAYS,

CSIOWNER_ACS.V204060_OORD.ORDERS_41TO60DAYS AS ORDERS_41TO60DAYS,

CSIOWNER_ACS.V204060_OORD.ORDERS_61TO90DAYS AS ORDERS_61TO90DAYS,

CSIOWNER_ACS.V204060_OORD.ORDERS_91TO180DAYS AS ORDERS_91TO180DAYS,

CSIOWNER_ACS.V204060_OORD.ORDERS_BEYOND AS ORDERS_BEYOND, CSIOWNER_ACS.V204060_OORD.HORIZON AS HORIZON,

CSIOWNER_ACS.V204060_OORD.ORD_STAT AS ORD_STAT, CSIOWNER_ACS.V204060_OORD.ORD_TYPE AS ORD_TYPE,

SUM(CSIOWNER_ACS.V204060_RQMT.DEMAND_OVERDUE) AS DEMAND_OVERDUE, SUM(CSIOWNER_ACS.V204060_RQMT.DEMAND_0TO20DAYS)

AS DEMAND_0TO20DAYS, SUM(CSIOWNER_ACS.V204060_RQMT.DEMAND_21TO40DAYS) AS DEMAND_21TO40DAYS,

SUM(CSIOWNER_ACS.V204060_RQMT.DEMAND_41TO60DAYS) AS DEMAND_41TO60DAYS,

SUM(CSIOWNER_ACS.V204060_RQMT.DEMAND_61TO90DAYS) AS DEMAND_61TO90DAYS,

SUM(CSIOWNER_ACS.V204060_RQMT.DEMAND_91TO180DAYS) AS DEMAND_91TO180DAYS,

SUM(CSIOWNER_ACS.V204060_RQMT.DEMAND_BEYOND) AS DEMAND_BEYOND,

CSIOWNER_ACS.V204060_RQMT.COMP_PART_NBR AS COMP_PART_NBR, CSIOWNER_ACS.V204060_OORD.ORD_NBR AS ORD_NBR

FROM CSIOWNER_ACS.V204060_RQMT, CSIOWNER_ACS.V204060_INVD, CSIOWNER_ACS.V204060_OORD

WHERE CSIOWNER_ACS.V204060_RQMT.COMP_PART_NBR = CSIOWNER_ACS.V204060_INVD.PART_NBR (+) AND

CSIOWNER_ACS.V204060_RQMT.COMP_PART_NBR = CSIOWNER_ACS.V204060_OORD.PART_NBR (+)

GROUP BY CSIOWNER_ACS.V204060_OORD.LINE_NBR, CSIOWNER_ACS.V204060_OORD.ORDERS_OVERDUE,

CSIOWNER_ACS.V204060_OORD.ORDERS_0TO20DAYS, CSIOWNER_ACS.V204060_OORD.ORDERS_21TO40DAYS,

CSIOWNER_ACS.V204060_OORD.ORDERS_41TO60DAYS, CSIOWNER_ACS.V204060_OORD.ORDERS_61TO90DAYS,

CSIOWNER_ACS.V204060_OORD.ORDERS_91TO180DAYS, CSIOWNER_ACS.V204060_OORD.ORDERS_BEYOND,

CSIOWNER_ACS.V204060_OORD.QTY_RECVD, CSIOWNER_ACS.V204060_OORD.HORIZON, CSIOWNER_ACS.V204060_OORD.QTY_ON_ORD,

CSIOWNER_ACS.V204060_OORD.ORD_STAT, CSIOWNER_ACS.V204060_OORD.ORD_TYPE, CSIOWNER_ACS.V204060_RQMT.COMP_PART_NBR,

CSIOWNER_ACS.V204060_OORD.ORD_NBR, CSIOWNER_ACS.V204060_OORD.PLAN_RECV_DATE

ORDER BY CSIOWNER_ACS.V204060_RQMT.COMP_PART_NBR

Dataset2

SELECT

CSIOWNER_ACS.V204060_INVD.AVAIL_INVENTORY, CSIOWNER_ACS.RQMT.COMP_PART_NBR, CSIOWNER_ACS.RQMT.DATE_REQD,

CSIOWNER_ACS.RQMT.QTY_REQD - CSIOWNER_ACS.RQMT.QTY_ISSUED AS DEMAND_DUE, CSIOWNER_ACS.V204060_OORD.PART_NBR

FROM CSIOWNER_ACS.PART, CSIOWNER_ACS.RQMT, CSIOWNER_ACS.V204060_OORD, CSIOWNER_ACS.V204060_INVD

WHERE CSIOWNER_ACS.PART.PART_NBR = CSIOWNER_ACS.RQMT.COMP_PART_NBR AND

CSIOWNER_ACS.PART.PART_NBR = CSIOWNER_ACS.V204060_OORD.PART_NBR (+) AND

CSIOWNER_ACS.PART.PART_NBR = CSIOWNER_ACS.V204060_INVD.PART_NBR (+) AND (CSIOWNER_ACS.RQMT.RQMT_CODE <> 'IS') AND

(CSIOWNER_ACS.PART.PART_TYPE = 'P') AND (CSIOWNER_ACS.RQMT.RQMT_CODE <> 'CL') AND (CSIOWNER_ACS.PART.PART_TYPE = 'P')

GROUP BY CSIOWNER_ACS.V204060_INVD.AVAIL_INVENTORY, CSIOWNER_ACS.RQMT.COMP_PART_NBR, CSIOWNER_ACS.RQMT.DATE_REQD,

CSIOWNER_ACS.RQMT.QTY_REQD - CSIOWNER_ACS.RQMT.QTY_ISSUED, CSIOWNER_ACS.V204060_OORD.PART_NBR

ORDER BY CSIOWNER_ACS.RQMT.COMP_PART_NBR

I am now trying to do it with a subreport and still not having much luck. but any help would be appreciate it!!!!

thanks!!!!!!

Michael E. Baker Say Hey Kid Points: 676 More actions · Answer 3

Wow!

I only have a few minutes left, but dataset 1 is all SUM()s of dataset 2, named the same and such...

Use the Union statement

Example:

SELECT SUM(myitem) AS myitem FROM mytable

UNION

SELECT myitem FROM mytable

GROUP BY myitem

ORDER BY myitem

Kerrie Sorrell SSC Eights! Points: 921 More actions · Answer 4

Thanks for the reply.

After going and reading through everything, I think your right. I will try that next.

Thank you so much for your help!!!!!

Kerrie