Problem with Scope parameter for SUM function

Is it possible to use a string expression for the scope parameter of the SUM
function?
I am using conditional grouping in my report and do not know the name of the
group I need to SUM for until the report is run.
I need to use an expression like this: = SUM(Fields!Field1.Value,
Code.GetMaxGroupNameUsed()), where I am determining the name of the
containing group in custom code.
Unfortunately I get this error:
The value expression for the textbox 'textbox18' 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.
Any ideas?
Thanks,
TessaScope names have to be string constants.
I'm not sure why you need dynamic scopes, but you can simulate them.
Assuming you have a function that returns a number between 1 and N, use an
expression similar to this:
=Choose( Code.DetermineScope(), Sum(Fields!F1.Value), Sum(Fields!F1.Value,
"Group1"), Sum(Fields!F1.Value, "Group2"), ......, Sum(Fields!F1.Value,
"DataRegionName"), Sum(Fields!F1.Value, "DataSetName") )
Notes:
* you can only use scope names of containing groups, and data regions (e.g.
table, list, matrix)
* for huge reports (i.e. with many data rows in the dataset) this approach
of simulating dynamic scopes will degrade performance
* documentation on the Choose function is available at:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vblr7/html/vafctchoose.asp
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tessa" <nospam@.thanks> wrote in message
news:eXcayiYWEHA.2840@.TK2MSFTNGP11.phx.gbl...
> Is it possible to use a string expression for the scope parameter of the
SUM
> function?
> I am using conditional grouping in my report and do not know the name of
the
> group I need to SUM for until the report is run.
> I need to use an expression like this: = SUM(Fields!Field1.Value,
> Code.GetMaxGroupNameUsed()), where I am determining the name of the
> containing group in custom code.
> Unfortunately I get this error:
> The value expression for the textbox 'textbox18' 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.
> Any ideas?
> Thanks,
> Tessa
>

problem with output parameter stored procedure

My stored procedure below compiled - not sure if it is even correct though.
I have to get the sum of a totalpaid column from one table and get the sum o
f
a totalpaid column from a second table. I need to return the difference of
these sums.
---
CREATE PROCEDURE [stp_SumDiffTotalPaid]
@.SumDiff decimal output
AS
declare @.a decimal, @.b decimal
Select @.a = sum(pd_totl_amt) from tblncanschd
Select @.b = sum(totalpaid) from tblncalnonschedemipaid
Set @.SumDiff = @.a - @.b
Return
GO
----
-
Here is how I call my sp from query analyzer:
declare @.a decimal
stp_sumdiffTotalpaid, @.sumdiff = @.a output
This is not working. Any suggestions appreciated how to get this to work or
if there is a simpler way to do this.
Thanks,
RichI figured it out
Declare @.a
Excec stp_sumdiffTotalpaid @.a output
or
Excec stp_sumdiffTotalpaid @.sumdiff = @.a output
print @.a
I was missing Execute
I guess I don't need the comma after the sp either.
"Rich" wrote:

> My stored procedure below compiled - not sure if it is even correct though
.
> I have to get the sum of a totalpaid column from one table and get the sum
of
> a totalpaid column from a second table. I need to return the difference o
f
> these sums.
> ---
> CREATE PROCEDURE [stp_SumDiffTotalPaid]
> @.SumDiff decimal output
> AS
> declare @.a decimal, @.b decimal
> Select @.a = sum(pd_totl_amt) from tblncanschd
> Select @.b = sum(totalpaid) from tblncalnonschedemipaid
> Set @.SumDiff = @.a - @.b
> Return
> GO
> ----
--
> Here is how I call my sp from query analyzer:
> declare @.a decimal
> stp_sumdiffTotalpaid, @.sumdiff = @.a output
> This is not working. Any suggestions appreciated how to get this to work
or
> if there is a simpler way to do this.
> Thanks,
> Rich