Multiple tables in same report?

Is it possible to use 2 tables in the same report with different dataset, but
able to pass one field as parameter from one table to the other one?
For example I have one table that has the field ITEM_ID and SALE. I have
another table that has the field TOTAL_COST. I want to use the ITEM_ID as
the parameter to get the TOTAL_COST in table 2. These 2 tables will be
aligned to look like one table.
Please help. Thanks in advance.Can't you do it in your storedprocedure?
I mean your storedprocedure returns your ITEM_ID and SALE and
TOTAL_COST(Grouped by ITEM_ID and SALE)?
HTH
ALI-R
"chang" <chang@.discussions.microsoft.com> wrote in message
news:CC8E3E28-7375-477A-82A4-3DA05C28973E@.microsoft.com...
> Is it possible to use 2 tables in the same report with different dataset,
but
> able to pass one field as parameter from one table to the other one?
> For example I have one table that has the field ITEM_ID and SALE. I have
> another table that has the field TOTAL_COST. I want to use the ITEM_ID as
> the parameter to get the TOTAL_COST in table 2. These 2 tables will be
> aligned to look like one table.
> Please help. Thanks in advance.|||ALI-R thanks for responding. The problem with combining the 2 queries into
one stored procedure is a problem because the second query has more records
in it and does not output the intended data output. For example if I combine
the 2 queries, the TOTALCOST field tends to be higher because of more
records. If I do it separately and only use the ITEM_ID field as parameters
then I get the intended output for whatever ITEM_ID it is.
I already have the 2 queries created into 2 stored procedures. Here is what
the 2 queries looks like:
Stored Procedure 1:
SELECT DISTINCT CUSTOMER.ITEM_ID, SUM(CUSTOMER.AMOUNT) AS
[SalesAmount]
FROM CUSTOMER INNER JOIN
CUSTOMER_SALE ON CUSTOMER.ORDER_ID = CUSTOMER_SALE.ORDER_ID
WHERE (CUSTOMER.ITEM_ID IS NOT NULL) AND (CUSTOMER.DATE BETWEEN @.STARTDATE
AND @.ENDDATE)
GROUP BY CUSTOMER.ITEM_ID
ORDER BY CUSTOMER.ITEM_ID
Stored Procedure 2:
SELECT SUM(MAT_COST + LAB_COST + BUR_COST + SER_COST) AS [Total Cost]
FROM INV_TRANSACTION
WHERE (TRANS_ID IN
(SELECT MAX(TRANS_ID)
FROM CUSTOMER, INV_TRANSACTION, CUSTOMER_SALE
WHERE CUSTOMER.ORDER_ID = INV_TRANSACTION.ORDER_ID AND
CUSTOMER_SALE.ORDER_ID = CUSTOMER.ORDER_ID AND CUSTOMER.ITEM_ID = @.ITEMID
AND (CUSTOMER.DATE BETWEEN @.STARTDATE AND @.ENDDATE)
GROUP BY CUSTOMER.ORDER_ID))
The @.ITEMID parameter is to be use to reference the ITEMID field from
dataset 1 which is the stored procedure 1. I was able to do this
successfully by using a subreport and pass the ITEMID field from the main
report to the subreport to be use in the parameter @.ITEMID. The problem came
up when it was time to sum the TOTALCOST field in the subreport and display
it in the main report.
I heard that it might be possible to use 2 tables or more in the main report
and pass the field from one table to the other one via parameters. The
question is how would I do this?
"ALI-R" wrote:
> Can't you do it in your storedprocedure?
> I mean your storedprocedure returns your ITEM_ID and SALE and
> TOTAL_COST(Grouped by ITEM_ID and SALE)?
> HTH
> ALI-R
> "chang" <chang@.discussions.microsoft.com> wrote in message
> news:CC8E3E28-7375-477A-82A4-3DA05C28973E@.microsoft.com...
> > Is it possible to use 2 tables in the same report with different dataset,
> but
> > able to pass one field as parameter from one table to the other one?
> >
> > For example I have one table that has the field ITEM_ID and SALE. I have
> > another table that has the field TOTAL_COST. I want to use the ITEM_ID as
> > the parameter to get the TOTAL_COST in table 2. These 2 tables will be
> > aligned to look like one table.
> >
> > Please help. Thanks in advance.
>
>|||You would have to use a subreport or have all the data returned by one STP.
You can have two datasets in one report but there is no way to link them
together.
Subreports are slow. I stuggled with this myself and ended up rewritting my
reporting STP's. It ends up that you return more data then needed but it in
one call to the DB unlike subreports (called STP for each detail). I would
create a table variable containing all the fields from both stp's, plus a
Flag field ie (1 = stp1, 2 = stp2), then populate the table from your
existing stp's, then in the report use the Flag fields in a table/list
Filter. Hope this helps, I dont like it either but just give yourself more
time to develop reports
"chang" wrote:
> ALI-R thanks for responding. The problem with combining the 2 queries into
> one stored procedure is a problem because the second query has more records
> in it and does not output the intended data output. For example if I combine
> the 2 queries, the TOTALCOST field tends to be higher because of more
> records. If I do it separately and only use the ITEM_ID field as parameters
> then I get the intended output for whatever ITEM_ID it is.
> I already have the 2 queries created into 2 stored procedures. Here is what
> the 2 queries looks like:
> Stored Procedure 1:
> SELECT DISTINCT CUSTOMER.ITEM_ID, SUM(CUSTOMER.AMOUNT) AS
> [SalesAmount]
> FROM CUSTOMER INNER JOIN
> CUSTOMER_SALE ON CUSTOMER.ORDER_ID = CUSTOMER_SALE.ORDER_ID
> WHERE (CUSTOMER.ITEM_ID IS NOT NULL) AND (CUSTOMER.DATE BETWEEN @.STARTDATE
> AND @.ENDDATE)
> GROUP BY CUSTOMER.ITEM_ID
> ORDER BY CUSTOMER.ITEM_ID
> Stored Procedure 2:
> SELECT SUM(MAT_COST + LAB_COST + BUR_COST + SER_COST) AS [Total Cost]
> FROM INV_TRANSACTION
> WHERE (TRANS_ID IN
> (SELECT MAX(TRANS_ID)
> FROM CUSTOMER, INV_TRANSACTION, CUSTOMER_SALE
> WHERE CUSTOMER.ORDER_ID = INV_TRANSACTION.ORDER_ID AND
> CUSTOMER_SALE.ORDER_ID = CUSTOMER.ORDER_ID AND CUSTOMER.ITEM_ID = @.ITEMID
> AND (CUSTOMER.DATE BETWEEN @.STARTDATE AND @.ENDDATE)
> GROUP BY CUSTOMER.ORDER_ID))
> The @.ITEMID parameter is to be use to reference the ITEMID field from
> dataset 1 which is the stored procedure 1. I was able to do this
> successfully by using a subreport and pass the ITEMID field from the main
> report to the subreport to be use in the parameter @.ITEMID. The problem came
> up when it was time to sum the TOTALCOST field in the subreport and display
> it in the main report.
> I heard that it might be possible to use 2 tables or more in the main report
> and pass the field from one table to the other one via parameters. The
> question is how would I do this?
> "ALI-R" wrote:
> > Can't you do it in your storedprocedure?
> >
> > I mean your storedprocedure returns your ITEM_ID and SALE and
> > TOTAL_COST(Grouped by ITEM_ID and SALE)?
> >
> > HTH
> > ALI-R
> >
> > "chang" <chang@.discussions.microsoft.com> wrote in message
> > news:CC8E3E28-7375-477A-82A4-3DA05C28973E@.microsoft.com...
> > > Is it possible to use 2 tables in the same report with different dataset,
> > but
> > > able to pass one field as parameter from one table to the other one?
> > >
> > > For example I have one table that has the field ITEM_ID and SALE. I have
> > > another table that has the field TOTAL_COST. I want to use the ITEM_ID as
> > > the parameter to get the TOTAL_COST in table 2. These 2 tables will be
> > > aligned to look like one table.
> > >
> > > Please help. Thanks in advance.
> >
> >
> >|||Kenwood, thanks for responding. I have created this with subreport as you
had done in the past also. It works great and yes very slow. The problem
came when trying to Sum the field and have it display in the main report.
I think what you suggested is probably what I need to do. I'm not really
clear with what you are describing though. If possible can you walk me
through what you are describing in detail? I'm new to SQL queries and
Reporting Services.
Let try to see if I can describe what you are recommending.
1) Create 1 stored procedure with temp table with the fields from my 2 old
stored procedure.
2) Create a Flag field for that temp table (not sure what flag field is).
3) Populate the temp table with my 2 stored procedure within the stored
procedure I created from step 1.
4) In reporting services use the flag fields in table/list filter
Not sure if this is what you meant, but if possible can you look at my 2
queries and provide a stored procedure example of what you described?
Thanks again.
"KENWOOD" wrote:
> You would have to use a subreport or have all the data returned by one STP.
> You can have two datasets in one report but there is no way to link them
> together.
> Subreports are slow. I stuggled with this myself and ended up rewritting my
> reporting STP's. It ends up that you return more data then needed but it in
> one call to the DB unlike subreports (called STP for each detail). I would
> create a table variable containing all the fields from both stp's, plus a
> Flag field ie (1 = stp1, 2 = stp2), then populate the table from your
> existing stp's, then in the report use the Flag fields in a table/list
> Filter. Hope this helps, I dont like it either but just give yourself more
> time to develop reports
> "chang" wrote:
> > ALI-R thanks for responding. The problem with combining the 2 queries into
> > one stored procedure is a problem because the second query has more records
> > in it and does not output the intended data output. For example if I combine
> > the 2 queries, the TOTALCOST field tends to be higher because of more
> > records. If I do it separately and only use the ITEM_ID field as parameters
> > then I get the intended output for whatever ITEM_ID it is.
> >
> > I already have the 2 queries created into 2 stored procedures. Here is what
> > the 2 queries looks like:
> >
> > Stored Procedure 1:
> >
> > SELECT DISTINCT CUSTOMER.ITEM_ID, SUM(CUSTOMER.AMOUNT) AS
> > [SalesAmount]
> > FROM CUSTOMER INNER JOIN
> > CUSTOMER_SALE ON CUSTOMER.ORDER_ID = CUSTOMER_SALE.ORDER_ID
> > WHERE (CUSTOMER.ITEM_ID IS NOT NULL) AND (CUSTOMER.DATE BETWEEN @.STARTDATE
> > AND @.ENDDATE)
> > GROUP BY CUSTOMER.ITEM_ID
> > ORDER BY CUSTOMER.ITEM_ID
> >
> > Stored Procedure 2:
> > SELECT SUM(MAT_COST + LAB_COST + BUR_COST + SER_COST) AS [Total Cost]
> > FROM INV_TRANSACTION
> > WHERE (TRANS_ID IN
> > (SELECT MAX(TRANS_ID)
> > FROM CUSTOMER, INV_TRANSACTION, CUSTOMER_SALE
> > WHERE CUSTOMER.ORDER_ID = INV_TRANSACTION.ORDER_ID AND
> > CUSTOMER_SALE.ORDER_ID = CUSTOMER.ORDER_ID AND CUSTOMER.ITEM_ID = @.ITEMID
> > AND (CUSTOMER.DATE BETWEEN @.STARTDATE AND @.ENDDATE)
> > GROUP BY CUSTOMER.ORDER_ID))
> >
> > The @.ITEMID parameter is to be use to reference the ITEMID field from
> > dataset 1 which is the stored procedure 1. I was able to do this
> > successfully by using a subreport and pass the ITEMID field from the main
> > report to the subreport to be use in the parameter @.ITEMID. The problem came
> > up when it was time to sum the TOTALCOST field in the subreport and display
> > it in the main report.
> >
> > I heard that it might be possible to use 2 tables or more in the main report
> > and pass the field from one table to the other one via parameters. The
> > question is how would I do this?
> >
> > "ALI-R" wrote:
> >
> > > Can't you do it in your storedprocedure?
> > >
> > > I mean your storedprocedure returns your ITEM_ID and SALE and
> > > TOTAL_COST(Grouped by ITEM_ID and SALE)?
> > >
> > > HTH
> > > ALI-R
> > >
> > > "chang" <chang@.discussions.microsoft.com> wrote in message
> > > news:CC8E3E28-7375-477A-82A4-3DA05C28973E@.microsoft.com...
> > > > Is it possible to use 2 tables in the same report with different dataset,
> > > but
> > > > able to pass one field as parameter from one table to the other one?
> > > >
> > > > For example I have one table that has the field ITEM_ID and SALE. I have
> > > > another table that has the field TOTAL_COST. I want to use the ITEM_ID as
> > > > the parameter to get the TOTAL_COST in table 2. These 2 tables will be
> > > > aligned to look like one table.
> > > >
> > > > Please help. Thanks in advance.
> > >
> > >
> > >|||You understand what I meant, by flag field I just mean I a Field that you set
in your STP and use in the report design and not a field the user will see.
very simple SQL statement to get you started, It looks like you know SQL well
enough to make it work. Hope this helps.
DECLARE @.myTempTable TABLE(a_field_1 int, a_field_2 int, a_field_3 int,
SelectID varchar(10))
INSERT INTO @.myTempTable(a_field_1,a_field_2,SelectID) VALUES (1,1,'Select1')
INSERT INTO @.myTempTable(a_field_3,SelectID) VALUES (2,'Select2')
SELECT * FROM @.myTempTable
Then in the report you filter on SelectID (Fields!SelectID.Value ='Select1') and it is like you have two datasets. Which is what your after
correct?
"chang" wrote:
> Kenwood, thanks for responding. I have created this with subreport as you
> had done in the past also. It works great and yes very slow. The problem
> came when trying to Sum the field and have it display in the main report.
> I think what you suggested is probably what I need to do. I'm not really
> clear with what you are describing though. If possible can you walk me
> through what you are describing in detail? I'm new to SQL queries and
> Reporting Services.
> Let try to see if I can describe what you are recommending.
> 1) Create 1 stored procedure with temp table with the fields from my 2 old
> stored procedure.
> 2) Create a Flag field for that temp table (not sure what flag field is).
> 3) Populate the temp table with my 2 stored procedure within the stored
> procedure I created from step 1.
> 4) In reporting services use the flag fields in table/list filter
> Not sure if this is what you meant, but if possible can you look at my 2
> queries and provide a stored procedure example of what you described?
> Thanks again.
> "KENWOOD" wrote:
> > You would have to use a subreport or have all the data returned by one STP.
> > You can have two datasets in one report but there is no way to link them
> > together.
> > Subreports are slow. I stuggled with this myself and ended up rewritting my
> > reporting STP's. It ends up that you return more data then needed but it in
> > one call to the DB unlike subreports (called STP for each detail). I would
> > create a table variable containing all the fields from both stp's, plus a
> > Flag field ie (1 = stp1, 2 = stp2), then populate the table from your
> > existing stp's, then in the report use the Flag fields in a table/list
> > Filter. Hope this helps, I dont like it either but just give yourself more
> > time to develop reports
> >
> > "chang" wrote:
> >
> > > ALI-R thanks for responding. The problem with combining the 2 queries into
> > > one stored procedure is a problem because the second query has more records
> > > in it and does not output the intended data output. For example if I combine
> > > the 2 queries, the TOTALCOST field tends to be higher because of more
> > > records. If I do it separately and only use the ITEM_ID field as parameters
> > > then I get the intended output for whatever ITEM_ID it is.
> > >
> > > I already have the 2 queries created into 2 stored procedures. Here is what
> > > the 2 queries looks like:
> > >
> > > Stored Procedure 1:
> > >
> > > SELECT DISTINCT CUSTOMER.ITEM_ID, SUM(CUSTOMER.AMOUNT) AS
> > > [SalesAmount]
> > > FROM CUSTOMER INNER JOIN
> > > CUSTOMER_SALE ON CUSTOMER.ORDER_ID = CUSTOMER_SALE.ORDER_ID
> > > WHERE (CUSTOMER.ITEM_ID IS NOT NULL) AND (CUSTOMER.DATE BETWEEN @.STARTDATE
> > > AND @.ENDDATE)
> > > GROUP BY CUSTOMER.ITEM_ID
> > > ORDER BY CUSTOMER.ITEM_ID
> > >
> > > Stored Procedure 2:
> > > SELECT SUM(MAT_COST + LAB_COST + BUR_COST + SER_COST) AS [Total Cost]
> > > FROM INV_TRANSACTION
> > > WHERE (TRANS_ID IN
> > > (SELECT MAX(TRANS_ID)
> > > FROM CUSTOMER, INV_TRANSACTION, CUSTOMER_SALE
> > > WHERE CUSTOMER.ORDER_ID = INV_TRANSACTION.ORDER_ID AND
> > > CUSTOMER_SALE.ORDER_ID = CUSTOMER.ORDER_ID AND CUSTOMER.ITEM_ID = @.ITEMID
> > > AND (CUSTOMER.DATE BETWEEN @.STARTDATE AND @.ENDDATE)
> > > GROUP BY CUSTOMER.ORDER_ID))
> > >
> > > The @.ITEMID parameter is to be use to reference the ITEMID field from
> > > dataset 1 which is the stored procedure 1. I was able to do this
> > > successfully by using a subreport and pass the ITEMID field from the main
> > > report to the subreport to be use in the parameter @.ITEMID. The problem came
> > > up when it was time to sum the TOTALCOST field in the subreport and display
> > > it in the main report.
> > >
> > > I heard that it might be possible to use 2 tables or more in the main report
> > > and pass the field from one table to the other one via parameters. The
> > > question is how would I do this?
> > >
> > > "ALI-R" wrote:
> > >
> > > > Can't you do it in your storedprocedure?
> > > >
> > > > I mean your storedprocedure returns your ITEM_ID and SALE and
> > > > TOTAL_COST(Grouped by ITEM_ID and SALE)?
> > > >
> > > > HTH
> > > > ALI-R
> > > >
> > > > "chang" <chang@.discussions.microsoft.com> wrote in message
> > > > news:CC8E3E28-7375-477A-82A4-3DA05C28973E@.microsoft.com...
> > > > > Is it possible to use 2 tables in the same report with different dataset,
> > > > but
> > > > > able to pass one field as parameter from one table to the other one?
> > > > >
> > > > > For example I have one table that has the field ITEM_ID and SALE. I have
> > > > > another table that has the field TOTAL_COST. I want to use the ITEM_ID as
> > > > > the parameter to get the TOTAL_COST in table 2. These 2 tables will be
> > > > > aligned to look like one table.
> > > > >
> > > > > Please help. Thanks in advance.
> > > >
> > > >
> > > >|||Ken,
Thanks for the example. However I'm still having problems. Here's what I did.
CREATE PROCEDURE SP_TEMPTABLE
AS
DECLARE @.TEMPTABLE TABLE(CUSTOMER_CODE NVARCHAR(40), SALE_AMOUNT VARCHAR(30)
, COST VARCHAR(30))
INSERT INTO @.TEMPTABLE(CUSTOMER_CODE, [SALE AMOUNT])
Exec CW_PRODUCT
INSERT INTO @.TEMPTABLE(COST)
Exec CW_TOTALCOST
SELECT * FROM @.TEMPTABLE
Here's the error that I'm getting:
"EXECUTE cannot be used as a source when inserting into a table variable."
I'm not clear with the example that you provided. I understand that you
created a temp table and insert some values that you put in. And for the
flag field it's Select1 and Select2. The question is how would I use my
stored procedures that I created to use with a 3rd stored procedure as a temp
table that you described.
"KENWOOD" wrote:
> You understand what I meant, by flag field I just mean I a Field that you set
> in your STP and use in the report design and not a field the user will see.
> very simple SQL statement to get you started, It looks like you know SQL well
> enough to make it work. Hope this helps.
> DECLARE @.myTempTable TABLE(a_field_1 int, a_field_2 int, a_field_3 int,
> SelectID varchar(10))
> INSERT INTO @.myTempTable(a_field_1,a_field_2,SelectID) VALUES (1,1,'Select1')
> INSERT INTO @.myTempTable(a_field_3,SelectID) VALUES (2,'Select2')
> SELECT * FROM @.myTempTable
>
> Then in the report you filter on SelectID (Fields!SelectID.Value => 'Select1') and it is like you have two datasets. Which is what your after
> correct?
>
> "chang" wrote:
> > Kenwood, thanks for responding. I have created this with subreport as you
> > had done in the past also. It works great and yes very slow. The problem
> > came when trying to Sum the field and have it display in the main report.
> >
> > I think what you suggested is probably what I need to do. I'm not really
> > clear with what you are describing though. If possible can you walk me
> > through what you are describing in detail? I'm new to SQL queries and
> > Reporting Services.
> >
> > Let try to see if I can describe what you are recommending.
> >
> > 1) Create 1 stored procedure with temp table with the fields from my 2 old
> > stored procedure.
> >
> > 2) Create a Flag field for that temp table (not sure what flag field is).
> >
> > 3) Populate the temp table with my 2 stored procedure within the stored
> > procedure I created from step 1.
> >
> > 4) In reporting services use the flag fields in table/list filter
> >
> > Not sure if this is what you meant, but if possible can you look at my 2
> > queries and provide a stored procedure example of what you described?
> >
> > Thanks again.
> >
> > "KENWOOD" wrote:
> >
> > > You would have to use a subreport or have all the data returned by one STP.
> > > You can have two datasets in one report but there is no way to link them
> > > together.
> > > Subreports are slow. I stuggled with this myself and ended up rewritting my
> > > reporting STP's. It ends up that you return more data then needed but it in
> > > one call to the DB unlike subreports (called STP for each detail). I would
> > > create a table variable containing all the fields from both stp's, plus a
> > > Flag field ie (1 = stp1, 2 = stp2), then populate the table from your
> > > existing stp's, then in the report use the Flag fields in a table/list
> > > Filter. Hope this helps, I dont like it either but just give yourself more
> > > time to develop reports
> > >
> > > "chang" wrote:
> > >
> > > > ALI-R thanks for responding. The problem with combining the 2 queries into
> > > > one stored procedure is a problem because the second query has more records
> > > > in it and does not output the intended data output. For example if I combine
> > > > the 2 queries, the TOTALCOST field tends to be higher because of more
> > > > records. If I do it separately and only use the ITEM_ID field as parameters
> > > > then I get the intended output for whatever ITEM_ID it is.
> > > >
> > > > I already have the 2 queries created into 2 stored procedures. Here is what
> > > > the 2 queries looks like:
> > > >
> > > > Stored Procedure 1:
> > > >
> > > > SELECT DISTINCT CUSTOMER.ITEM_ID, SUM(CUSTOMER.AMOUNT) AS
> > > > [SalesAmount]
> > > > FROM CUSTOMER INNER JOIN
> > > > CUSTOMER_SALE ON CUSTOMER.ORDER_ID = CUSTOMER_SALE.ORDER_ID
> > > > WHERE (CUSTOMER.ITEM_ID IS NOT NULL) AND (CUSTOMER.DATE BETWEEN @.STARTDATE
> > > > AND @.ENDDATE)
> > > > GROUP BY CUSTOMER.ITEM_ID
> > > > ORDER BY CUSTOMER.ITEM_ID
> > > >
> > > > Stored Procedure 2:
> > > > SELECT SUM(MAT_COST + LAB_COST + BUR_COST + SER_COST) AS [Total Cost]
> > > > FROM INV_TRANSACTION
> > > > WHERE (TRANS_ID IN
> > > > (SELECT MAX(TRANS_ID)
> > > > FROM CUSTOMER, INV_TRANSACTION, CUSTOMER_SALE
> > > > WHERE CUSTOMER.ORDER_ID = INV_TRANSACTION.ORDER_ID AND
> > > > CUSTOMER_SALE.ORDER_ID = CUSTOMER.ORDER_ID AND CUSTOMER.ITEM_ID = @.ITEMID
> > > > AND (CUSTOMER.DATE BETWEEN @.STARTDATE AND @.ENDDATE)
> > > > GROUP BY CUSTOMER.ORDER_ID))
> > > >
> > > > The @.ITEMID parameter is to be use to reference the ITEMID field from
> > > > dataset 1 which is the stored procedure 1. I was able to do this
> > > > successfully by using a subreport and pass the ITEMID field from the main
> > > > report to the subreport to be use in the parameter @.ITEMID. The problem came
> > > > up when it was time to sum the TOTALCOST field in the subreport and display
> > > > it in the main report.
> > > >
> > > > I heard that it might be possible to use 2 tables or more in the main report
> > > > and pass the field from one table to the other one via parameters. The
> > > > question is how would I do this?
> > > >
> > > > "ALI-R" wrote:
> > > >
> > > > > Can't you do it in your storedprocedure?
> > > > >
> > > > > I mean your storedprocedure returns your ITEM_ID and SALE and
> > > > > TOTAL_COST(Grouped by ITEM_ID and SALE)?
> > > > >
> > > > > HTH
> > > > > ALI-R
> > > > >
> > > > > "chang" <chang@.discussions.microsoft.com> wrote in message
> > > > > news:CC8E3E28-7375-477A-82A4-3DA05C28973E@.microsoft.com...
> > > > > > Is it possible to use 2 tables in the same report with different dataset,
> > > > > but
> > > > > > able to pass one field as parameter from one table to the other one?
> > > > > >
> > > > > > For example I have one table that has the field ITEM_ID and SALE. I have
> > > > > > another table that has the field TOTAL_COST. I want to use the ITEM_ID as
> > > > > > the parameter to get the TOTAL_COST in table 2. These 2 tables will be
> > > > > > aligned to look like one table.
> > > > > >
> > > > > > Please help. Thanks in advance.
> > > > >
> > > > >
> > > > >|||Ken,
I tried playing with it all day yesterday and still no luck. Here's what I
got so far, but when I try running it from Reporting Services, it doesn't
work:
CREATE PROC CW_PRODUCT_TEMP
@.STARTDATE NVARCHAR(10),
@.ENDDATE NVARCHAR(10)
AS
INSERT INTO #TempTable1
SELECT DISTINCT CUSTOMER.ITEM_ID, SUM(CUSTOMER.AMOUNT) AS [Sale Amount]
FROM CUSTOMER INNER JOIN
CUSTOMER_SALE ON CUSTOMER.ITEM_ID =CUSTOMER_SALE.ITEM_ID
WHERE (CUSTOMER.ITEM_ID IS NOT NULL) AND (CUSTOMER.SHIPPED_DATE BETWEEN
@.STARTDATE AND @.ENDDATE)
GROUP BY CUSTOMER.ITEM_ID
ORDER BY CUSTOMER.ITEM_ID
INSERT INTO #TempTable2
SELECT SUM(MAT_COST + LAB_COST + BUR_COST + SER_COST) AS [Total Cost]
FROM INV_TRANSACTION
WHERE (TRANS_ID IN
(SELECT MAX(trans_id)
FROM customer, inv_transaction,
customer_sale
WHERE customer.cust_order_id =INVENTORY_TRANS.order_id AND customer_sale.order_id =
customer.order_id AND
customer.line_no =inv_transaction.order_no AND inv_transaction.type = 'O' AND
inv_transaction.class ='I' AND customer.item_id = #TempTable1.item_id AND
(customer.shipped_date BETWEEN @.startdate and @.enddate)
GROUP BY customer.CUST_ORDER_ID,
customer.LINE_NO))
SELECT * FROM #TempTable2, #TempTable1
WHERE #TempTable2.item_id = #TempTable1.item_id
Error that i'm getting is:
"Invalid object name '#TempTable1'"
Please advise.
Thanks again.
"chang" wrote:
> Ken,
> Thanks for the example. However I'm still having problems. Here's what I did.
> CREATE PROCEDURE SP_TEMPTABLE
> AS
> DECLARE @.TEMPTABLE TABLE(CUSTOMER_CODE NVARCHAR(40), SALE_AMOUNT VARCHAR(30)
> , COST VARCHAR(30))
> INSERT INTO @.TEMPTABLE(CUSTOMER_CODE, [SALE AMOUNT])
> Exec CW_PRODUCT
> INSERT INTO @.TEMPTABLE(COST)
> Exec CW_TOTALCOST
> SELECT * FROM @.TEMPTABLE
> Here's the error that I'm getting:
> "EXECUTE cannot be used as a source when inserting into a table variable."
> I'm not clear with the example that you provided. I understand that you
> created a temp table and insert some values that you put in. And for the
> flag field it's Select1 and Select2. The question is how would I use my
> stored procedures that I created to use with a 3rd stored procedure as a temp
> table that you described.
>
> "KENWOOD" wrote:
> > You understand what I meant, by flag field I just mean I a Field that you set
> > in your STP and use in the report design and not a field the user will see.
> > very simple SQL statement to get you started, It looks like you know SQL well
> > enough to make it work. Hope this helps.
> >
> > DECLARE @.myTempTable TABLE(a_field_1 int, a_field_2 int, a_field_3 int,
> > SelectID varchar(10))
> >
> > INSERT INTO @.myTempTable(a_field_1,a_field_2,SelectID) VALUES (1,1,'Select1')
> > INSERT INTO @.myTempTable(a_field_3,SelectID) VALUES (2,'Select2')
> >
> > SELECT * FROM @.myTempTable
> >
> >
> > Then in the report you filter on SelectID (Fields!SelectID.Value => > 'Select1') and it is like you have two datasets. Which is what your after
> > correct?
> >
> >
> >
> > "chang" wrote:
> >
> > > Kenwood, thanks for responding. I have created this with subreport as you
> > > had done in the past also. It works great and yes very slow. The problem
> > > came when trying to Sum the field and have it display in the main report.
> > >
> > > I think what you suggested is probably what I need to do. I'm not really
> > > clear with what you are describing though. If possible can you walk me
> > > through what you are describing in detail? I'm new to SQL queries and
> > > Reporting Services.
> > >
> > > Let try to see if I can describe what you are recommending.
> > >
> > > 1) Create 1 stored procedure with temp table with the fields from my 2 old
> > > stored procedure.
> > >
> > > 2) Create a Flag field for that temp table (not sure what flag field is).
> > >
> > > 3) Populate the temp table with my 2 stored procedure within the stored
> > > procedure I created from step 1.
> > >
> > > 4) In reporting services use the flag fields in table/list filter
> > >
> > > Not sure if this is what you meant, but if possible can you look at my 2
> > > queries and provide a stored procedure example of what you described?
> > >
> > > Thanks again.
> > >
> > > "KENWOOD" wrote:
> > >
> > > > You would have to use a subreport or have all the data returned by one STP.
> > > > You can have two datasets in one report but there is no way to link them
> > > > together.
> > > > Subreports are slow. I stuggled with this myself and ended up rewritting my
> > > > reporting STP's. It ends up that you return more data then needed but it in
> > > > one call to the DB unlike subreports (called STP for each detail). I would
> > > > create a table variable containing all the fields from both stp's, plus a
> > > > Flag field ie (1 = stp1, 2 = stp2), then populate the table from your
> > > > existing stp's, then in the report use the Flag fields in a table/list
> > > > Filter. Hope this helps, I dont like it either but just give yourself more
> > > > time to develop reports
> > > >
> > > > "chang" wrote:
> > > >
> > > > > ALI-R thanks for responding. The problem with combining the 2 queries into
> > > > > one stored procedure is a problem because the second query has more records
> > > > > in it and does not output the intended data output. For example if I combine
> > > > > the 2 queries, the TOTALCOST field tends to be higher because of more
> > > > > records. If I do it separately and only use the ITEM_ID field as parameters
> > > > > then I get the intended output for whatever ITEM_ID it is.
> > > > >
> > > > > I already have the 2 queries created into 2 stored procedures. Here is what
> > > > > the 2 queries looks like:
> > > > >
> > > > > Stored Procedure 1:
> > > > >
> > > > > SELECT DISTINCT CUSTOMER.ITEM_ID, SUM(CUSTOMER.AMOUNT) AS
> > > > > [SalesAmount]
> > > > > FROM CUSTOMER INNER JOIN
> > > > > CUSTOMER_SALE ON CUSTOMER.ORDER_ID = CUSTOMER_SALE.ORDER_ID
> > > > > WHERE (CUSTOMER.ITEM_ID IS NOT NULL) AND (CUSTOMER.DATE BETWEEN @.STARTDATE
> > > > > AND @.ENDDATE)
> > > > > GROUP BY CUSTOMER.ITEM_ID
> > > > > ORDER BY CUSTOMER.ITEM_ID
> > > > >
> > > > > Stored Procedure 2:
> > > > > SELECT SUM(MAT_COST + LAB_COST + BUR_COST + SER_COST) AS [Total Cost]
> > > > > FROM INV_TRANSACTION
> > > > > WHERE (TRANS_ID IN
> > > > > (SELECT MAX(TRANS_ID)
> > > > > FROM CUSTOMER, INV_TRANSACTION, CUSTOMER_SALE
> > > > > WHERE CUSTOMER.ORDER_ID = INV_TRANSACTION.ORDER_ID AND
> > > > > CUSTOMER_SALE.ORDER_ID = CUSTOMER.ORDER_ID AND CUSTOMER.ITEM_ID = @.ITEMID
> > > > > AND (CUSTOMER.DATE BETWEEN @.STARTDATE AND @.ENDDATE)
> > > > > GROUP BY CUSTOMER.ORDER_ID))
> > > > >
> > > > > The @.ITEMID parameter is to be use to reference the ITEMID field from
> > > > > dataset 1 which is the stored procedure 1. I was able to do this
> > > > > successfully by using a subreport and pass the ITEMID field from the main
> > > > > report to the subreport to be use in the parameter @.ITEMID. The problem came
> > > > > up when it was time to sum the TOTALCOST field in the subreport and display
> > > > > it in the main report.
> > > > >
> > > > > I heard that it might be possible to use 2 tables or more in the main report
> > > > > and pass the field from one table to the other one via parameters. The
> > > > > question is how would I do this?
> > > > >
> > > > > "ALI-R" wrote:
> > > > >
> > > > > > Can't you do it in your storedprocedure?
> > > > > >
> > > > > > I mean your storedprocedure returns your ITEM_ID and SALE and
> > > > > > TOTAL_COST(Grouped by ITEM_ID and SALE)?
> > > > > >
> > > > > > HTH
> > > > > > ALI-R
> > > > > >
> > > > > > "chang" <chang@.discussions.microsoft.com> wrote in message
> > > > > > news:CC8E3E28-7375-477A-82A4-3DA05C28973E@.microsoft.com...
> > > > > > > Is it possible to use 2 tables in the same report with different dataset,
> > > > > > but
> > > > > > > able to pass one field as parameter from one table to the other one?
> > > > > > >
> > > > > > > For example I have one table that has the field ITEM_ID and SALE. I have
> > > > > > > another table that has the field TOTAL_COST. I want to use the ITEM_ID as
> > > > > > > the parameter to get the TOTAL_COST in table 2. These 2 tables will be
> > > > > > > aligned to look like one table.
> > > > > > >
> > > > > > > Please help. Thanks in advance.
> > > > > >
> > > > > >
> > > > > >