Scope Parameters? - 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)

Scope Parameters?

Author

Topic

CoachBarker
Posting Yak Master

170 Posts

Posted - 2009-02-25 : 14:55:56

quote:
The Value expression for the textbox ‘tbxLoadPounds’ 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.

How do you go about getting the results of an aggravating

function that is one grouping up or down from where you need the information.


In this order I have:
1.Table Properties
   table1
2.Groups
   CustomerBillTo
   CustomerDeliverTo
   PickupDate
3.Detail
   TicketNumber

I have a SUM in PickupDate that I need to access from CustomerDeliverTo, can I do that?

=SUM(Fields!LoadPound.Value,"CustomerDeliverTo", is there anything I can add here?to get what I need?)

Thanks for the help
CoachBarker

Cody
Starting Member

24 Posts

Posted - 2009-02-25 : 20:42:11

You don't need the 3rd field, but I think you know that.

I've had some success using that SUM(Fields, GroupName) in my reports, but I don't remember if they are parents or children, and I'm not sure if it matters.

If this is a child group, try putting in a parent group name just to see if it compiles. If it does then either the group name is misspelt or child groups can't be used ... I didn't see anything in the docs about not being able to do child groups ...

Looking over this might help: http://msdn.microsoft.com/en-us/library/bb630415.aspx

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-02 : 13:06:21

quote:
Originally posted by CoachBarker

quote:
The Value expression for the textbox ‘tbxLoadPounds’ 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.

How do you go about getting the results of an aggravating function that is one grouping up or down from where you need the information.

In this order I have:
1.Table Properties
table1
2.Groups
CustomerBillTo
CustomerDeliverTo
PickupDate
3.Detail
TicketNumber

I have a SUM in PickupDate that I need to access from CustomerDeliverTo, can I do that?

=SUM(Fields!LoadPound.Value,"CustomerDeliverTo", is there anything I can add here?to get what I need?)

Thanks for the help
CoachBarker

your CustomerDeliverTo & PickupDate seems to be at same grouping level. then whats the need of using scope here?

CoachBarker
Posting Yak Master

170 Posts

Posted - 2009-03-02 : 15:58:40

Sorry for the confusion, PickupDate is GROUP inside of CustomerDeliverTo. The problem is like this post here:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=119643

where LoadPounds is the SUM of PickupPounds. Right now I am using this query, in this example I want the sum of one of each, one 40575,one 12952, and one 61867

Load Pounds
40575
40575
40575
40575
40575
40575
40575
40575
40575
40575
40575
12952
12952
12952
12952
12952
12952
12952
12952
12952
61867
61867
61867

LoadPounds
40575
12952
61867


SELECT     detail_record.ticket_number 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, SUM(DISTINCT detail_record.ddp_weight) 
                      AS LoadPounds, 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 BETWEEN @start_date AND @end_date) AND (detail_record.customer_delv_to = @customer_number) OR
                      (detail_record.pickup_date BETWEEN @start_date AND @end_date) AND (detail_record.customer_bill_to = @customer_number)
GROUP BY cmBillTo.customer_number, cmBillTo.customer_name, cmBillTo.city, cmDelvTo.customer_number, cmDelvTo.customer_name, cmDelvTo.city, 
                      detail_record.pickup_dt, detail_record.ticket_number, detail_record.deliver_dt, detail_record.hauler_number, detail_record.deliver_date, 
                      detail_record.pickup_date, detail_record.division
ORDER BY detail_record.pickup_date

I would like do do away with the grouping in the query and do it in the report, the PickupPOunds is easy enough, but the Load Pounds is driving me nutz.

Thanks for the help
CoachBarker

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-03 : 11:00:32

for that you need to use a case expression like

=SUM(IIF(RowNumber("GroupName")=1 ,Fields!LoadPounds.Name,0))

CoachBarker
Posting Yak Master

170 Posts

Posted - 2009-03-03 : 12:17:57

I've found that if I do the grouping for the report in the query, I can not get the Order By correct in the report. My original query was like this:


SELECT     detail_record.ticket_number 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, detail_record.pickup_weight AS PickupWeight, 
	detail_record.hauler_number AS Hauler, detail_record.ddp_weight AS LoadPounds
 
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 BETWEEN @start_date AND @end_date) AND (detail_record.customer_delv_to = @customer_number)	
	 OR (detail_record.pickup_date BETWEEN @start_date AND @end_date) AND (detail_record.customer_bill_to = @customer_number)

Order By 	TicketNumber

I can do all I want except for getting the SUM of the LoadPounds to calculate correctly.

If I use this query:


SELECT     detail_record.ticket_number 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, 
                      detail_record.pickup_weight AS PickupPounds, detail_record.hauler_number AS HaulerNumber, SUM(DISTINCT detail_record.ddp_weight) 
                      AS LoadPounds, 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 BETWEEN @start_date AND @end_date) AND (detail_record.customer_delv_to = @customer_number) OR
                      (detail_record.pickup_date BETWEEN @start_date AND @end_date) AND (detail_record.customer_bill_to = @customer_number)
GROUP BY cmBillTo.customer_number, cmBillTo.customer_name, cmBillTo.city, cmDelvTo.customer_number, cmDelvTo.customer_name, cmDelvTo.city, 
                      detail_record.pickup_dt, detail_record.ticket_number, detail_record.deliver_dt, detail_record.hauler_number, detail_record.deliver_date, 
                      detail_record.pickup_date, detail_record.division, detail_record.pickup_weight
ORDER BY detail_record.pickup_date

I get the correct LoadPounds but I can not get the Order By in the report right, even after checking all the correct settings in the properties.

There are 2 versions of each report I am creating, one Ordered By TicketNumber the other by PickupDate.

Thanks for the help
CoachBarker

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-03 : 12:21:13

try replacing ORDER BY detail_record.pickup_date
by

ORDER BY CAST(detail_record.pickup_dt AS datetime)

CoachBarker
Posting Yak Master

170 Posts

Posted - 2009-03-03 : 12:58:28

It is not the date order by but the ticketNumber that won't work

Thanks for the help
CoachBarker

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-03 : 13:15:41

why? whats the datatype of ticketNumber ?

CoachBarker
Posting Yak Master

170 Posts

Posted - 2009-03-03 : 13:39:07

TicketNumber varchar(10)

Is there no way, to use this query and modify it


SELECT     detail_record.ticket_number 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, 
                      detail_record.pickup_weight AS PickupPounds, detail_record.hauler_number AS HaulerNumber, detail_record.ddp_weight AS LoadPounds
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 BETWEEN @start_date AND @end_date) AND (detail_record.customer_delv_to = @customer_number) OR
                      (detail_record.pickup_date BETWEEN @start_date AND @end_date) AND (detail_record.customer_bill_to = @customer_number)
ORDER BY detail_record.pickup_date

and get the SUM of LoadPounds without having to use grouping in the query. For PickupPounds I can use SUM(Fields!PickupPounds.Value)in the report, but LoadPounds is the SUM of repeating Values, I used DISTINCT and that gets the correct LoadPounds but I had to group in the sql.

example
LoadPounds
1
1
1
1
1
2
2
2
2
3
3
equals
19 but I want only

LoadPounds
1
2
3
equals
6

Thanks for the help
CoachBarker

CoachBarker
Posting Yak Master

170 Posts

Posted - 2009-03-03 : 13:46:10

Something like create table, select distinct LoadPounds, ticket number where ticketNumber = ticketnumber

Thanks for the help
CoachBarker

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-04 : 09:56:28

cast or convert ticket number to integer before using in order by else it will give you results sorted as string value not by its numeric value

CoachBarker
Posting Yak Master

170 Posts

Posted - 2009-03-04 : 12:29:57

Ah okay then it was trying to Order By a string instead of a number. Now it is sorting correct in the query but it is still the same in the report.

Thanks for the help
CoachBarker

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-04 : 12:59:34

what do you mean its still same in report? try sorting it in report then

CoachBarker
Posting Yak Master

170 Posts

Posted - 2009-03-04 : 13:57:46

QUERY


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, detail_record.pickup_weight AS PickupWeight
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 BETWEEN @start_date AND @end_date) AND (detail_record.customer_delv_to = @customer_number) OR
                      (detail_record.pickup_date BETWEEN @start_date AND @end_date) AND (detail_record.customer_bill_to = @customer_number)
ORDER BY detail_record.ticket_number

But in the report it still sorts by pickupDate. In the table1 property dialog I have set the sorting to =Fields!TicketNumber.Value.

Thanks for the help
CoachBarker

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources