Reference Objects? - SQL Server Forums

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

Reference Objects?

Author

Topic

chriskhan2000
Aged Yak Warrior

544 Posts

Posted - 2004-12-13 : 16:51:25

I know I asked this question once, but I'm in desperate help here. I have try creating a stored procedure for this but no luck. Here's the situation.

I have one query that will extract information from the needed tables and another one to extract some other information. Now I want to combine the two together base on a primary key. This is kind of confusing to explain, but here's my query.

Query 1:
SELECT R.Part_ID, R.Cost, PO.O_Status
FROM Request R, Purchase_Order PO, Request_Binary RB
WHERE R.Order_Base_ID = '11223'
AND R.Part_ID *= RB.Order_Base_ID
AND R.Sub_ID IS NULL

Query 2:
SELECT TOP 1 S.Supply_ID, S.Supply_Req_No, PO.Status
FROM Supply S, Purchose_Order PO, Request R
WHERE S.Supply_ID = '11223'
AND S.Demand_ID = PO.ID
AND R.Request_ID = S.Sub_Request_ID

The problem with combining the two together is that if there are null for the second query, it doesn't doesn't display it. I want it to display even if the second query has null value.

I also try using a Group By, but I get some error like:
The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.

What I'm hoping to be able to do in Reporting Services is just display the fields from the second query to the table of the first query if the R.Order_base_id matches.

Here's the expected output:

Part.ID R.Cost PO.O_Status S.Supply_ID S.Supply_Req_NO PO.Status
1 $2.00 Shipping 123 2312 Stock
2 $3.00 None 232 2321 Stock
3 $2.00 None Null Null Null
4 $6.00 Shipping Null Null Null

Here's the output that I'm getting that I don't want.

Part.ID R.Cost PO.O_Status S.Supply_ID S.Supply_Req_NO PO.Status
1 $2.00 Shipping 123 2312 Stock
2 $3.00 None 232 2321 Stock
5 $2.00 Shipping 1234 4322 Stock
6 $600 None 556 32342 Stock

So as you can see with this example, it skipped those that has null value for the second query. I don't know how to get it to display even if it's null. Please advise.

jhermiz

3564 Posts

Posted - 2004-12-14 : 09:56:10

You are using an inner join.

May want to try a left join, also have you looked in the criteria to specify IS NULL OR IS NOT NULL ? That should always return true

jhermiz

3564 Posts

Posted - 2004-12-14 : 10:03:18

Customers
-----------
CustomerID
Customer

Orders
-------------
OrderID
CustomerID
Order

Show only orders with a valid customer:
SELECT o.CustomerID, c.Customer FROM Orders o
INNER JOIN Customers c ON o.CustomerID=c.CustomerID

Show all orders:
SELECT o.CustomerID, c.Customer FROM Orders 0
LEFT JOIN Customers c ON o.CustomerID=c.CustomerID

chriskhan2000
Aged Yak Warrior

544 Posts

Posted - 2004-12-14 : 11:48:57

Jon,

With the first query i'm using a Left Outer Join by using the *=. However, for the second query, I can't use Left Outer Join. If I do I get this message.

The table "Request" is an inner member of an outer-join clause. This is not allowed if the table also participates in a regular join clause.

I'm wondering if it would be possible to use custom code to fill in for the second query. Have the first query output and then fill in the second query with custom code to insert the query where ever it matches the R.Order_Base_ID. Or like in ASP, just reference another object.

For example in my asp report, I have the first query fill the report and then reference the R.Order_base_ID object with the second query and fill in which ever column base on that. Here's what I have in my asp page.

First Query:

SELECT R.Part_ID, R.Cost, PO.O_Status
FROM Request R, Purchase_Order PO, Request_Binary RB
WHERE R.Order_Base_ID = '"&Order_base_ID&"'
AND R.Part_ID *= RB.Order_Base_ID
AND R.Sub_ID IS NULL

Second Query:
Function GETPO(Order_base_ID, Part_ID, Sub_ID)
SELECT TOP 1 S.Supply_ID, S.Supply_Req_No, PO.Status
FROM Supply S, Purchose_Order PO, Request R
WHERE S.Supply_ID = '"&Order_base_ID&"'
AND S.Demand_ID = '"&Part_ID&"'
AND R.Request_ID = '"&Sub_ID&"'

IF NOT OBJRS2.EOF THEN
PO=objRS2("Supply_ID")
IF NOT ISNULL(PO) THEN
PO=PO & "/"
PO=po & objRS2("Supply_Req_No") & "-" & objRS2("Status")
END IF
GETPO = PO
END IF

I think using custom code will work for this. For example, if the S.Supply_ID is NOT NULL for query 2, then fill in query 2 base on the object. Not sure how I would write this in the custom code section though. Let me know if this makes sense.

Only if there's a way to reference objects in Reporting Services, like for example if query 2 fields matches query 1 objects like
'"&Order_base_ID&"', '"&Part_ID&"', "&Sub_ID&"' then insert into that row else leave it null.

chriskhan2000
Aged Yak Warrior

544 Posts

Posted - 2004-12-14 : 12:03:37

I'm thinking about creating two datasets. One for query 1 and the other one for query2, but no way I can use the where clause to referenec objects from dataset 1. Any Ideas?

jhermiz

3564 Posts

Posted - 2004-12-14 : 14:28:19

quote:
Originally posted by chriskhan2000

I'm thinking about creating two datasets. One for query 1 and the other one for query2, but no way I can use the where clause to referenec objects from dataset 1. Any Ideas?

I think you need 3. One for query 1, one for query 2, and a new one that joins both. And this new one will allow you to use the where condition.

Remember, the parameters you use for query 3 can be the datasets of query 1 and query 2. I do this all the time to seperate the various types of data and output for parameters. I think create a central or main store procedure that uses the parameters of all my smaller datasets. To me I think this is the cleanest and most efficient.

chriskhan2000
Aged Yak Warrior

544 Posts

Posted - 2004-12-14 : 16:17:57

I'm not sure how you would join the two dataset into the 3rd. How would I do that? Also do I put the fields from the 3rd dataset in my report?

chriskhan2000
Aged Yak Warrior

544 Posts

Posted - 2004-12-16 : 15:01:27

Jon,

Can you explain how to put two datasets into a 3rd dataset? I don't know how you would do this. I have a lot of reports that reference another object from another datasets, and if I can get this to work, that'll be extremely helpful.

jhermiz

3564 Posts

Posted - 2004-12-16 : 16:23:29

Sorry getting back to you late...

Yes your first two data sets are for your picklists or lookups, your third verifies your 2 other data sets (parameters in your sproc) and uses that data to create the report.

For instance, let us say you want to do a report of customers with orders. Your first dataset may be to capture the customers (used for one parameter):


CREATE PROCEDURE rsp_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

This will give you a listing of your customers as well as all so that you may run a report with all customers as well..

Now lets say you have another parameter for orders. So you select a customer you get ALL of those orders for that customer (this is used for your second parameter):


CREATE PROCEDURE rsp_customer_orders @CustomerID integer  AS
SET NOCOUNT ON
SELECT  Order.OrderID, Order.Order
FROM        Order
INNER JOIN
                      Customer ON Customer.CustomerID = Order.CustomerID
WHERE     (Order.CustomerID = @CustomerID)
UNION SELECT Null, 'All'
 ORDER BY Customer.Customer
SET NOCOUNT OFF
GO

This dataset (your second one) will give you all orders based on your first dataset (the customer).

Now you should have an overall dataset (dataset3) that takes the parameters @CustomerID, @OrderID (probably defaulting them to null) and ANY other fields that you need in the report and base the report on this dataset (dataset3).

An example:

dataset3:


CREATE PROCEDURE rsp_orders  (
						@CustomerID bigint=NULL,
						@OrderID bigint=NULL,
					   )
AS
BEGIN
SET NOCOUNT ON
SELECT    dbo.Customer.CustomerID, dbo.Customer.Customer, dbo.Order.OrderID, dbo.Order.Order
FROM         dbo.Customer INNER JOIN
                      dbo.Order ON dbo.Order.CustomerID = dbo.Customer.CustomerID
WHERE     	
(@CustomerID IS NULL OR dbo.Customer.CustomerID=@CustomerID)
AND (@OrderID IS NULL OR dbo.Order.OrderID=@OrderID)
Set NOCOUNT OFF
END
GO

chriskhan2000
Aged Yak Warrior

544 Posts

Posted - 2004-12-16 : 17:54:42

I thought that you set the dataset in Reporting Services, but this looks like it's in SQL Server. Looks like this will work for me. I will play with it and let you know.

jhermiz

3564 Posts

Posted - 2004-12-16 : 23:01:28

quote:
Originally posted by chriskhan2000

I thought that you set the dataset in Reporting Services, but this looks like it's in SQL Server. Looks like this will work for me. I will play with it and let you know.

A dataset is in RS. You create a dataset and have three choices when choosing the type of dataset. Text, Stored Procedure, or a direct table. The most efficient and expandable solution is to use a stored procedure. That way you can send various parameters that may be based on other datasets.

So to answer your question the dataset is in reporting services but it can use a stored procedure. In fact this is the preferred method. It maintains the security, its much more resuable, and it's easier to debug / modify.

Jon

chriskhan2000
Aged Yak Warrior

544 Posts

Posted - 2004-12-18 : 01:46:40

Wow. I learned something. I thought that I can only create stored procedure in SQL server and then use Reporting Services to run it. Never know that I can create stored procedures within Reporting Services.

jhermiz

3564 Posts

Posted - 2004-12-18 : 13:40:12

No not within RS.

You create the stored procedure in SQL SERVER (which I showed you the procedures above). You then reference them in RS. You do not create the procedure directly in RS (that would make no sense, SQL SERVER has that capability). If that were the case MS would have to deal with security, etc. on RS as well as SQL SERVER. RS has been tightly coupled with SQL SERVER very well.

So my examples are stored procedures inside of SQL SERVER. RS simply uses them.

Jon

chriskhan2000
Aged Yak Warrior

544 Posts

Posted - 2004-12-20 : 13:53:41

Yeah. That's what I thought too hehe. But I must've misunderstood what you were saying. I'm going to try to get this to work today, and hopefully this solves most of my problems from migrating our reports from asp to reporting services.

chriskhan2000
Aged Yak Warrior

544 Posts

Posted - 2004-12-20 : 15:00:49

Jon,

Do you think there's a way to do this within Reporting services in the custom code? For example, all I needed is just my first query to generate most of the fieds, but there are like one field that I want it to check the object and if it matches then fill it up.

For example. I have field Customer.ID and Customer.Name. I create a query to retreive these data and fill in the report. Now in the Custom code I would create:

IF customer.ID = Field!ID.Value AND Cusomter.Name = Field!Name.Value THEN
=Field!Order_ID.Value & ' ' & =Field!Order_Date.Value
END IF

Do you think something like this would work? Question is how would I write this to begin with? I have an idea, but not sure if the syntax is correct.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-12-20 : 15:10:45

You can do this inside the cell using IIF.

Tara

chriskhan2000
Aged Yak Warrior

544 Posts

Posted - 2004-12-20 : 15:28:46

Tara,

Thanks for responding. Just a little quick overview of what I'm trying to do. The IIF inside the cell might seem like it will work, but I'm a little confuse at how it will work. Here's the situation.

I have two select queries.

Query 1:

Select Customer.Cust_ID, Customer.Name
From Customer

Query 2:

Select Order.O_ID, Order.Status
From Order

I can't combine the two together because it keeps giving me aggregate error even if I use a group by. Seems like one uses outer join while the other one uses inner join.

The output for the cell:

Customer.ID, Customer.Name, Order.ID/Order.Status

So here's my IIF Expression:

=IIF(Field!O_ID.value = Field!Cust_ID.value AND Field!Order_Name.value = Field!Name.Value, Field!O_ID'/'Field!Status.Value, 'N/A')

So is this how it will work? Create two datasets and then use the IIF expression.

jhermiz

3564 Posts

Posted - 2004-12-20 : 15:31:15

Post the combination query for us to look at ...
remember when you do a group by with aggregates you may have to specify more than just one field for the group by.

Your iif wont work as another dataset.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-12-20 : 15:34:21

How are these queries being used in the report? Do you have two different tables in it? Or are you using one query for a parameter? Need some more info to help.

How would you combine these? Do you just want one result set from the two queries? If so, just use UNION ALL. If that doesn't work for you, we're going to need to see the table structure of the two tables and let us know how they are related to each other.

Tara

chriskhan2000
Aged Yak Warrior

544 Posts

Posted - 2004-12-20 : 16:10:07

Here's the actual query that I'm working with. The ones posted before are just to simplify the query down to make it easier for you guys to explain it to me.

Query1:

SELECT R.PART_ID, P.DESCRIPTION, CAST(CAST(RB.BITS AS BINARY(8000)) AS VARCHAR(40)) AS SPECS, R.WORKORDER_SUB_ID, R.OPERATION_SEQ_NO,
R.PIECE_NO, R.QTY_PER, R.CALC_QTY, R.ISSUED_QTY, P.STOCK_UM,
R.EST_MATERIAL_COST + R.EST_LABOR_COST + R.EST_BURDEN_COST AS ESTIMATED_COST,
R.ACT_LABOR_COST + R.ACT_BURDEN_COST + R.ACT_MATERIAL_COST AS ACTUAL_COST
FROM REQUIREMENT R LEFT OUTER JOIN
PART P ON R.PART_ID = P.ID RIGHT OUTER JOIN
REQUIREMENT_BINARY RB ON R.WORKORDER_BASE_ID = RB.WORKORDER_BASE_ID AND
R.WORKORDER_SUB_ID = RB.WORKORDER_SUB_ID AND R.OPERATION_SEQ_NO = RB.OPERATION_SEQ_NO AND
R.PIECE_NO = RB.PIECE_NO
WHERE (R.WORKORDER_BASE_ID = @workorder) AND (R.SUBORD_WO_SUB_ID IS NULL)
ORDER BY R.PART_ID, R.WORKORDER_SUB_ID, R.OPERATION_SEQ_NO

Query2:

SELECT TOP 1 SUPPLY_BASE_ID, SUPPLY_SEQ_NO, PO.STATUS
FROM DEMAND_SUPPLY_LINK DSL, PURCHASE_ORDER PO
WHERE DSL.SUPPLY_BASE_ID = PO.ID

I'm not sure if I can use the 2nd query with the custom code. For example:

SELECT TOP 1 SUPPLY_BASE_ID, SUPPLY_SEQ_NO. PO.STATUS
FROM DEMAND_SUPPLY_LINK DSL, PURCHASE_ORDER PO
WHERE DSL.SUPPLY_BASE_ID = PO.ID
AND DEMAND_BASE-ID = FIELD!WORKORDER_BASE_ID.VALUE
AND DEMAND_SUB_ID = FIELD!WORKORDER_SUB_ID.VALUE
AND DEMAND_SEQ_NO= FIELD!OPERATION_SEQ_NO.VALUE
AND DEMAND_NO = FIELD!PIECE_NO.VALUE

That's what I meant be referencing those field objects. If it matches then display it in the cell, otherwise just skip it. Any ideas?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-12-20 : 16:11:05

I am not understanding still. If what matches? And no you can't reference RS fields in the query. If you showed us an example of your data, the output of both queries, and what you want to do, we can probably handle this at the stored procedure layer.

Tara

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources