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
 Pass variable from 1 dataset to another?

Author  Topic 

chriskhan2000
Aged Yak Warrior

544 Posts
Posted - 2005-04-19 : 09:40:26
Would it be possible to create 2 dataset and pass the variables from one to another? For example create a column for the first dataset and pass the variable from the second to that first.

jhermiz

3564 Posts
Posted - 2005-04-19 : 09:53:38
Hi Chris,

Yes this is possible as long as you are talking about parameter wise. What you will do is create two parameters. Parameter B depends on Parameter A. So what that means to sql server is that you have two stored procedures. What that means to RS is that you have two parameters. I am assuming you are trying to cascade some combo boxes...That is filter Parameter B (combo box 2, based on combo box 1's (Parameter A's) value). If so let me get you started.

Lets go through a basic example:

Customers (Stored Procedure 1)
Potential values include:
Ford
GM
Chrysler

But you'd like the ability to also include 'All'. So here is your first stored procedure:


CREATE PROCEDURE rsp_client_customers AS
SET NOCOUNT ON
SELECT DISTINCT Customer.CustomerID, Customer.Customer
FROM         Customer 
UNION SELECT Null, 'All'
ORDER BY Customer.Customer
SET NOCOUNT OFF
GO


That takes care of the customers drop down allowing the user to select one or 'All' customers.
Next you need another stored procedure which takes the parameter of your initial dataset (you customer dataset)
and filters say the sites for that customer. Values could include the following:

Sites:
Ford -> Livionia
Ford -> Detroit
GM -> NYC
DCX -> Atlanta

That is the customer one to many sites associated with it. But in this dataset you also want to be able to select ALL sites.
So here we go with procedure 2:


CREATE PROCEDURE rsp_customer_sites @CustomerID bigint
AS
SET NOCOUNT ON
SELECT     dbo.ClientCustomerSites.SiteID, dbo.Site.Site
FROM         dbo.ClientCustomerSites INNER JOIN
                      dbo.Site ON dbo.ClientCustomerSites.SiteID = dbo.Site.SiteID
WHERE dbo.ClientCustomerSites.CustomerID=@CustomerID
UNION SELECT Null, 'All'
ORDER BY dbo.Site.Site
SET NOCOUNT OFF
GO


Now this procedure filters your second combo box based on the parameter from your first dataset.

But you see these procedures are just used for the parameters of the report. Obviously you will need a third and final
dataset (or stored procedure) where you will merge all of this information and any related data that you would like to see on a report in this last dataset. This one is usally the easiest since you've taken care of your parameters.

Hope this helps,

Jon


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

chriskhan2000
Aged Yak Warrior

544 Posts
Posted - 2005-04-19 : 10:22:40
Jeff,

Thanks for the detail example. That's what I need. How would I combine the 2 to make a third on though?
Go to Top of Page

chriskhan2000
Aged Yak Warrior

544 Posts
Posted - 2005-04-19 : 10:22:45
Jeff,

Thanks for the detail example. That's what I need. How would I combine the 2 to make a third on though?
Go to Top of Page

chriskhan2000
Aged Yak Warrior

544 Posts
Posted - 2005-04-19 : 10:28:35
Here are the 2 queries i'm trying to pass variables from one to the other.

Query 1:

CREATE PROC SP_PRODUCT
@STARTDATE NVARCHAR(10),
@ENDDATE NVARCHAR(10)

AS

SELECT DISTINCT CUST_ORDER_LINE.PRODUCT_CODE, SUM(CUST_ORDER_LINE.TOTAL_AMT_SHIPPED) AS [Sale Amount]
FROM CUST_ORDER_LINE INNER JOIN
CUSTOMER_ORDER ON CUST_ORDER_LINE.CUST_ORDER_ID = CUSTOMER_ORDER.ID
WHERE (CUST_ORDER_LINE.PRODUCT_CODE IS NOT NULL) AND (CUST_ORDER_LINE.LAST_SHIPPED_DATE >= @STARTDATE) AND
(CUST_ORDER_LINE.LAST_SHIPPED_DATE <= @ENDDATE)
GROUP BY CUST_ORDER_LINE.PRODUCT_CODE
ORDER BY CUST_ORDER_LINE.PRODUCT_CODE


Query 2:

CREATE PROC SP_TOTALCOST
@ORDERID NVARCHAR (80),
@PRODUCTCODE NVARCHAR(80),
@STARTDATE NVARCHAR(10),
@ENDDATE NVARCHAR(10)

AS
select sum(act_material_cost+act_labor_cost+act_burden_cost+act_service_cost) as [Total Cost]
from inventory_trans
where transaction_id in
(SELECT MAX(it.TRANSACTION_ID) AS TRANS
FROM CUST_ORDER_LINE col INNER JOIN
INVENTORY_TRANS it ON col.CUST_ORDER_ID = it.CUST_ORDER_ID AND col.LINE_NO = it.CUST_ORDER_LINE_NO INNER JOIN
CUSTOMER_ORDER co ON col.CUST_ORDER_ID = co.ID
WHERE (col.CUST_ORDER_ID = @ORDERID) AND (col.PRODUCT_CODE = @PRODUCTCODE) AND (it.TYPE = 'O') AND (it.CLASS = 'I') AND
(col.LAST_SHIPPED_DATE BETWEEN @STARTDATE AND @ENDDATE)
GROUP BY it.CUST_ORDER_LINE_NO)

I need to be able to combine these two and be able to reference the parameters by using the field from main to the parameter to the second query.
Go to Top of Page

jhermiz

3564 Posts
Posted - 2005-04-19 : 10:33:02
I'm not Jeff chris I am Jon... :)

The third dataset is always the most simple since it takes into account the initial 2 (parameters). Using what I posted here is an example of a hideous :) solution...


CREATE PROCEDURE rsp_my_issues  (
						@ClientID integer, 
						@Originator bigint=NULL,
						@IssueStatus integer=0,
						@CustomerID bigint=NULL,
						@SiteID bigint=NULL,
						@CommissionID bigint=NULL,
						@IssueTypeID bigint=NULL,
						@StartDate datetime=NULL,
						@EndDate datetime=NULL,
						@IssueID bigint=NULL
					   )
AS
BEGIN
SET NOCOUNT ON
SELECT    dbo.Issue.IssueID, dbo.Issue.OpenDate, dbo.Issue.TargetDate, dbo.Issue.ClosedDate, dbo.Issue.Issue, 
                      dbo.Commission.Commission, dbo.Site.Site, dbo.Customer.Customer, dbo.IssueType.IssueType
FROM         dbo.Issue LEFT OUTER JOIN
                      dbo.Commission ON dbo.Issue.CommissionID = dbo.Commission.CommissionID LEFT OUTER JOIN
                      dbo.Site ON dbo.Issue.SiteID = dbo.Site.SiteID LEFT OUTER JOIN
                      dbo.Customer ON dbo.Issue.CustomerID = dbo.Customer.CustomerID LEFT OUTER JOIN
	        dbo.IssueType ON dbo.Issue.IssueTypeID = dbo.IssueType.IssueTypeID
WHERE     	
(dbo.Issue.ClientID=@ClientID) 
--perform some boolean alegebra to get the expected results
AND (@CustomerID IS NULL OR dbo.Issue.CustomerID=@CustomerID)
AND (@SiteID IS NULL OR dbo.Issue.SiteID=@SiteID)
AND (@CommissionID IS NULL OR dbo.Issue.CommissionID=@CommissionID)
AND (@IssueTypeID IS NULL OR dbo.Issue.IssueTypeID=@IssueTypeID)
--make sure orig is upper case.
AND (@Originator IS NULL OR dbo.Issue.LoginID=@Originator)
--issues
AND (@IssueStatus <> 0 OR  dbo.Issue.ClosedDate IS NULL)
AND (@IssueStatus <> 1 OR dbo.Issue.ClosedDate IS NOT NULL)
AND (@IssueStatus <> 2 OR dbo.Issue.ClosedDate IS NOT NULL AND dbo.Issue.TargetDate < DATEADD(d, DATEDIFF(d, 0, GetDate()), 0))
AND (@IssueStatus <> 3 OR (dbo.Issue.ClosedDate IS NULL OR dbo.Issue.ClosedDate IS NOT NULL))
--dates
AND (@StartDate IS NULL OR dbo.Issue.OpenDate >= @StartDate)
AND (@EndDate IS NULL OR dbo.Issue.OpenDate <= @EndDate)
--do they want a single issue ?
AND (@IssueID IS NULL OR dbo.Issue.IssueID = @IssueID)
--do they want by wbs elementss
ORDER BY dbo.Issue.IssueID, dbo.Customer.Customer, dbo.Site.Site, dbo.Commission.Commission
Set NOCOUNT OFF
END
GO


Your report parameter takes care of the internals of the parameters for the third dataset. Your report should have as your parameters the first two that I had originally stated (CustomerID, SiteID).

Jon



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

chriskhan2000
Aged Yak Warrior

544 Posts
Posted - 2005-04-19 : 11:05:27
Thanks Jon. Will work with what you provided.
Go to Top of Page

chriskhan2000
Aged Yak Warrior

544 Posts
Posted - 2005-04-19 : 14:03:50
Jon,

The example you provided about joining the 2 stored procedures into a third, I don't see the connections there. Am I missing something or overlooked something? The example looks just like another query. How is it pulling the information from SP 1 and 2?

I am assuming that for the 3rd SP, you are inserting it to a temp table and executing the first 2 SP. Then do a select from the temp table there. Am I right on this assumption?
Go to Top of Page

chriskhan2000
Aged Yak Warrior

544 Posts
Posted - 2005-04-19 : 14:03:56
Jon,

The example you provided about joining the 2 stored procedures into a third, I don't see the connections there. Am I missing something or overlooked something? The example looks just like another query. How is it pulling the information from SP 1 and 2?

I am assuming that for the 3rd SP, you are inserting it to a temp table and executing the first 2 SP. Then do a select from the temp table there. Am I right on this assumption?
Go to Top of Page

jhermiz

3564 Posts
Posted - 2005-04-19 : 15:18:41
No there is no temp tables at all. I think you are looking too deep at how RS works. Think of it like this:
1 stored procedure that i posted is for a customer drop down: dsCustomers
2nd stored procedure that i posted is for customer sites drop down: dsCustomerSites
3rd stored procedure is basically the data i want to see on the report: dsMain

RS wont show you exactly how the three got integrated...but behind the scenes you have to remember you have defined 2 parameters:
The Customer and the site.

So when you display your report dsMain (the dataset that has all the data you want to show), then it simply knows to filter
this dsMain by these 2 parameters.


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

chriskhan2000
Aged Yak Warrior

544 Posts
Posted - 2005-04-19 : 16:11:11
Seems like we are talking about two separate things here. From that explaination I do understand it now. But what I'm trying to do is being able to create 2 stored procedure and reference the parameters from each other.

Here's my example of what I'm trying to get at:

Query 1:

Create Procedure SP_Product
@STARTDATE SmallDateTime,
@ENDDATE SmallDateTime
AS
Select distinct co.id, co.desired_ship_date, c.name, col.product_code, co.salesrep_id,
col.part_id, dsl.supply_base_id, sales.sale_amount, 0 as cost, col.cust_order_id
From customer_order co inner join cust_order_line col on
co.id = col.cust_order_id inner join
customer c on c.id = co.customer_id inner join
demand_supply_link dsl on dsl.demand_base_id = col.cust_order_id
left outer join
(Select Sum(order_qty*unit_price) as Sale_Amount, col.cust_order_id
from cust_order_line col
where product_code<>'Trade'
group by cust_order_id) sales on co.id = sales.cust_order_id
Where co.status='R' and col.part_id is not null and co.desired_ship_date between @STARTDATE and @ENDDATE
Order By co.id


Query 2:


Create Procedure SP_Cost
@Part_ID Varchar(15)
AS
select distinct avg(act_material_cost+act_labor_cost+act_burden_cost+act_service_cost) as cost
from inventory_trans it
where part_id=@Part_ID and type='O' and cust_order_id in
(select top 5 cust_order_id
from inventory_trans
where part_id=@Part_ID and type='O'
order by transaction_date desc)
group by cust_order_id



From the 2 stored procedure above, I want to be able to somehow pass the field Part_ID from first query to parameter @Part_ID from second query.

This is possible through using subreport in RS where I just use the field as a parameter to the , but problem is that I can't total it and can't be view in Excel.
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site