February 7, 2006 at 7:17 am
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
February 7, 2006 at 1:07 pm
Can you share the SQL statement with the join? You can generalize it.
February 7, 2006 at 1:17 pm
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!!!!!!
February 7, 2006 at 2:40 pm
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
February 8, 2006 at 5:34 am
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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply