Multiple Queries - 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

SQL Server 2005 Forums

Analysis Server and Reporting Services (2005)

Multiple Queries

Author

Topic

CoachBarker
Posting Yak Master

170 Posts

Posted - 2009-03-16 : 11:15:01

OK so you can have more than one query (datset) for a report? In order to get the data I need I guess I have to do it that way. Now this query supplies all but one field for the report:


SELECT     CAST(detail_record.ticket_number AS int) AS TicketNumber, cmDelvTo.customer_number AS DeliverToNumber, 
              cmDelvTo.customer_name AS DeliverToName, cmDelvTo.city AS DeliverToCity, cmBillTo.customer_number AS BillToNumber, 
              cmBillTo.customer_name AS BillToName, cmBillTo.city AS BillToCIty, CAST(detail_record.pickup_dt AS datetime) AS PickupDate, 
              CAST(detail_record.deliver_dt AS datetime) AS DeliverDate, SUM(detail_record.pickup_weight) AS PickupPounds, 
              detail_record.hauler_number AS HaulerNumber, detail_record.deliver_date,
              detail_record.division AS Division
FROM       detail_record INNER JOIN 
              customer_master AS cmBillTo ON detail_record.customer_bill_to = cmBillTo.customer_number INNER JOIN
              customer_master AS cmDelvTo ON detail_record.customer_delv_to = cmDelvTo.customer_number 
WHERE     (detail_record.pickup_date >= @start_date) AND (@customer_bill_to IS NULL) AND (@customer_delv_to IS NULL) AND 
              (detail_record.pickup_date < DATEADD(day, 1, @end_date)) OR
              (detail_record.pickup_date >= @start_date) AND (detail_record.customer_bill_to = @customer_bill_to) AND (@customer_delv_to IS NULL) AND 
              (detail_record.pickup_date < DATEADD(day, 1, @end_date)) OR
              (detail_record.pickup_date >= @start_date) AND (detail_record.customer_delv_to = @customer_delv_to) AND (@customer_bill_to IS NULL) AND 
              (detail_record.pickup_date < DATEADD(day, 1, @end_date)) OR
              (detail_record.pickup_date >= @start_date) AND (detail_record.customer_delv_to = @customer_delv_to) AND 
              (detail_record.customer_bill_to = @customer_bill_to) AND (detail_record.pickup_date < DATEADD(day, 1, @end_date))
GROUP BY  cmBillTo.customer_number, cmBillTo.customer_name, cmBillTo.city, cmDelvTo.customer_number, cmDelvTo.customer_name, cmDelvTo.city, 
              detail_record.pickup_dt,  detail_record.deliver_dt, detail_record.ticket_number, detail_record.hauler_number, 
              detail_record.pickup_date, detail_record.division, detail_record.deliver_date
ORDER BY detail_record.pickup_dt

and this query provides the data for one field:


SELECT     load_pound_detail.ticket_number, load_pound_detail.pickup_date, load_pound_detail.ddp_weight

FROM       load_pound_detail
			
WHERE      (load_pound_detail.pickup_date BETWEEN @start_date AND @end_date) AND
           (load_pound_detail.ticket_number = detail_record.ticket_number)
ORDER BY   pickup_date, ticket_number

it gets rid of the repeating fields I was having trouble with before. Now I added a data source to my project, but when I try to use that data to get the field I need in a text box I get this:

quote:
The Value expression for the textbox ‘ddp_weight_1’ refers to the field ‘ddp_weight’. Report item expressions can only refer to fields within the current data set scope or, if inside an aggregate, the specified data set scope.

if I use =Sum(Fields!ddp_weight.Value, "LoadPounds")with LoadPounds the name of the new data set it runs but the figure is not what I wanted.

Is there a way to combine the 2 queries to get what I need?

Thanks for the help
CoachBarker

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-16 : 13:08:12

better to bring all the data in a single dataset

CoachBarker
Posting Yak Master

170 Posts

Posted - 2009-03-16 : 14:48:15

Yes I know that, but even with the help of many in the forums we have not been able to get all the data in with just one query. Unless you see a way taht we can.

Thanks for the help
CoachBarker

CoachBarker
Posting Yak Master

170 Posts

Posted - 2009-03-18 : 08:51:13

So I can count the number of rows of Divisions in my report ="# of rows: " & CountRows("TicketNumber"). Now I need to divide the SUM(Fields!LoadPounds.Value) by # of rows but they are in different levels of the report.

and I still haven't been able to figure out how to access different levels, at least everything I've tried has been wrong or returned something other than what I wanted.
So I guess I am asking if I can set a global variable in the report to the # of rows ao I can access it elsewhere?

Thanks for the help
CoachBarker

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-03-18 : 09:36:20

Am I missing something or is there an easy relation from your first query to the second?

Both return the load_pound_detail.ticket_number which sounds like a good candidate for a relationship?

Dows this not get you what you need in one dataset?


SELECT
	CAST(detail_record.ticket_number AS int) AS TicketNumber
	, cmDelvTo.customer_number AS DeliverToNumber
	, cmDelvTo.customer_name AS DeliverToName
	, cmDelvTo.city AS DeliverToCity
	, cmBillTo.customer_number AS BillToNumber
	, cmBillTo.customer_name AS BillToName
	, cmBillTo.city AS BillToCIty
	, CAST(detail_record.pickup_dt AS datetime) AS PickupDate
	, CAST(detail_record.deliver_dt AS datetime) AS DeliverDate
	, SUM(detail_record.pickup_weight) AS PickupPounds
	, detail_record.hauler_number AS HaulerNumber
	, detail_record.deliver_date
	, detail_record.division AS Division
	, lpd.[Pickup_Date]
	, lpd.[DDP_Weight]
FROM
	detail_record
	INNER JOIN customer_master AS cmBillTo ON detail_record.customer_bill_to = cmBillTo.customer_number
	INNER JOIN customer_master AS cmDelvTo ON detail_record.customer_delv_to = cmDelvTo.customer_number

	LEFT JOIN (
		SELECT
			load_pound_detail.ticket_number AS [Ticket_Number]
			, load_pound_detail.pickup_date AS [Pickup_Date]
			, load_pound_detail.ddp_weight AS [DDP_Weight]
		FROM
			load_pound_detail
		WHERE
			(load_pound_detail.pickup_date BETWEEN @start_date AND @end_date)
			AND (load_pound_detail.ticket_number = detail_record.ticket_number)
		)
		lpd ON lpd.[Ticket_Number] = detail_record.[ticket_Number]
WHERE
	(detail_record.pickup_date >= @start_date)
	AND (@customer_bill_to IS NULL)
	AND (@customer_delv_to IS NULL)
	AND (detail_record.pickup_date < DATEADD(day, 1, @end_date))

	OR (detail_record.pickup_date >= @start_date)

	AND (detail_record.customer_bill_to = @customer_bill_to)
	AND (@customer_delv_to IS NULL)
	AND (detail_record.pickup_date < DATEADD(day, 1, @end_date))

	OR (detail_record.pickup_date >= @start_date)

	AND (detail_record.customer_delv_to = @customer_delv_to)
	AND (@customer_bill_to IS NULL)
	AND (detail_record.pickup_date < DATEADD(day, 1, @end_date))

	OR (detail_record.pickup_date >= @start_date)

	AND (detail_record.customer_delv_to = @customer_delv_to)
	AND (detail_record.customer_bill_to = @customer_bill_to)
	AND (detail_record.pickup_date < DATEADD(day, 1, @end_date))
GROUP BY 
	cmBillTo.customer_number
	, cmBillTo.customer_name
	, cmBillTo.city
	, cmDelvTo.customer_number
	, cmDelvTo.customer_name
	, cmDelvTo.city
	, detail_record.pickup_dt
	, detail_record.deliver_dt
	, detail_record.ticket_number
	, detail_record.hauler_number
	, detail_record.pickup_date
	, detail_record.division
	, detail_record.deliver_date
	, lpd.[Pickup_Date]
	, lpd.[DDP_Weight]
ORDER BY
	detail_record.pickup_dt

Note -- after formatting your code I'm not so sure that your where clause is working the way you want it to. There's no need for 3 copies of the OR condition they all do the same thing -- Do you need to add some more () ?

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

CoachBarker
Posting Yak Master

170 Posts

Posted - 2009-03-18 : 09:49:33

Thanks for the reply, As for the WHERE clause, everytime I paste the query into SSRS data field it changes the WHERE clause to multiple lines.

Error message

quote:
Msg 4104, Level 16, State 1, Procedure BQBO11MP_Report3, Line 13
The multi-part identifier "detail_record.ticket_number" could not be bound.

I've checked it over and over, outside of the capital N in number everything else seems alright.
Thanks for the help
CoachBarker

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-03-18 : 11:23:32

Hi Coach,

I see what's happened.

I think you posted a typo in your second query.

You Wrote:


SELECT     load_pound_detail.ticket_number, load_pound_detail.pickup_date, load_pound_detail.ddp_weight

FROM       load_pound_detail
			
WHERE      (load_pound_detail.pickup_date BETWEEN @start_date AND @end_date) AND
           (load_pound_detail.ticket_number = detail_record.ticket_number)
ORDER BY   pickup_date, ticket_number

And there's no detail_record linked in the FROM clause! I copied that. I don't think you need that line at all. Try replacing my derived table with this:


LEFT JOIN (
		SELECT
			load_pound_detail.ticket_number AS [Ticket_Number]
			, load_pound_detail.pickup_date AS [Pickup_Date]
			, load_pound_detail.ddp_weight AS [DDP_Weight]
		FROM
			load_pound_detail
		WHERE
			(load_pound_detail.pickup_date BETWEEN @start_date AND @end_date)
		)
		lpd ON lpd.[Ticket_Number] = detail_record.[ticket_Number]

And give that a whirl. -- that's what's causing that error anyway.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

CoachBarker
Posting Yak Master

170 Posts

Posted - 2009-03-18 : 12:06:38

I appreciate the help with the queries. Though I would still like to know if there is a way to set up some kind of global variables in SSRS.

Thanks for the help
CoachBarker

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-03-18 : 12:17:01

I'm sorry -- I really know next to nothing about SSRS. I think Visakh16's a dab hand at it though and I'm sure he'll be able to lend a hand.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

CoachBarker
Posting Yak Master

170 Posts

Posted - 2009-03-18 : 12:50:51

Unfortunately your query brings the same results as far as narrowing down the field load_pounds_detail.ddp_weight to 1 value. The darn Divisions field throws a monkey wrench into everything, and unfortunately I can not change the format of the report to account for that.

That is why I am trying to divide the Load Pounds by the Divisions in the Group to get the right weight.

Thanks for the help
CoachBarker

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-03-19 : 06:20:09

Sorry Coach This is where I get off -- I don't know the package you are using at all!

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

CoachBarker
Posting Yak Master

170 Posts

Posted - 2009-03-19 : 06:25:50

Thats ok, seems like neither do I, it is SSRS(SQL Server Reporting Services) and I am the lucky

guy at work that gets to learn it on the fly. See ya around.

Thanks for the help
CoachBarker

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-19 : 13:34:51

quote:
Originally posted by CoachBarker

Thats ok, seems like neither do I, it is SSRS(SQL Server Reporting Services) and I am the lucky guy at work that gets to learn it on the fly. See ya around.

Thanks for the help
CoachBarker

from your explanation, i think what you need is this
=SUM(Fields!LoadPounds.Value)/CountRows("TicketNumber")

CoachBarker
Posting Yak Master

170 Posts

Posted - 2009-03-19 : 13:54:44

That is what I have been trying to achieve, but Fields!LoadPounds.Value is in the PickupDate group and that is one group higher than CountRows("TicketNumber") And that has basically been the problem all along. But there a no way change the way the report is laid out.

Thanks for the help
CoachBarker

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-19 : 14:03:12

tried these too?
=SUM(Fields!LoadPounds.Value,"PickupDate")/CountRows("TicketNumber")

=SUM(Fields!LoadPounds.Value,"PickupDate")/CountDistinctRows("TicketNumber")

CoachBarker
Posting Yak Master

170 Posts

Posted - 2009-03-19 : 14:09:56

same old same old

quote:
The Value expression for the textbox ‘textbox16’ has a scope parameter that is not valid for an aggregate function. The scope parameter must be set to a string constant that is equal to either the name of a containing group, the name of a containing data region, or the name of a data set.

I think I have tried everything I can think of

Thanks for the help
CoachBarker

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-19 : 14:18:46

is pickupdate name of group or dataset?

CoachBarker
Posting Yak Master

170 Posts

Posted - 2009-03-19 : 14:24:47

The expression with CountDistinct returned this message:
The Value expression for the textbox ‘textbox16’ contains an error: [BC30451] Name 'CountDistinctRows' is not declared.

DataSet Name
NonFormEntry

Groups from the top going down
CustomerBillTo
CustomerDeliverTo
PickupDate
TicketNumber

Details Grouping
Divisions

To clarify it I changed the image of the layout view with laels to show the groups.

Thanks for the help
CoachBarker

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-19 : 14:42:36

quote:
Originally posted by CoachBarker

The expression with CountDistinct returned this message:
The Value expression for the textbox ‘textbox16’ contains an error: [BC30451] Name 'CountDistinctRows' is not declared.

DataSet Name
NonFormEntry

Groups from the top going down
CustomerBillTo
CustomerDeliverTo
PickupDate
TicketNumber

Details Grouping
Divisions

To clarify it I changed the image of the layout view with laels to show the groups.

Thanks for the help
CoachBarker

it should be CountDistinct()

CoachBarker
Posting Yak Master

170 Posts

Posted - 2009-03-19 : 15:01:55

It raised no errors, but the values in LoadPounds were not divided by the number of Divisions. If it helps I am also using my query for the data retrieval in the report that is in my first post I think.

Thanks for the help
CoachBarker

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-19 : 15:02:11

=SUM(Fields!LoadPounds.Value,"PickupDate")/CountDistinct(Fields!TicketNumber.value)

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources