Sum(), Scope parameter error

[DJWheezyWeez]

Hi,

I'm new to sql and its syntax/functions but here goes. Basically, I have a query that is returning duplicate values because of the tables I've linked. When I do =Sum(Fields!estimatedvalue.Value), it sums for every value including the duplicating ones on the detail level. What I want it to do is only sum values at the group level. Using my little knowledge of sql, I tried =Sum(Fields!estimatedvalue.Value, "name"), 'name' being the name of the group I want it to sum, but I get the error:

"An error occurred during local report processing. The definition of the report '/SalesReport' is invalid. The Value expression for the text box 'Textbox51' 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, of the name of a dataset."

If my rambling made sense, does anyone have any suggestions? Any help is greatly appreciated.

-DJWW

 

[Qik3Coder]

What language/application is this in?
It looks like Excel from what you're describing.

Lod

You've got questions and source code. We want both!

 

[DJWheezyWeez]

I'm using MS Visual Studio 2008.

 

[Qik3Coder]

K, I need more code to even try to figure out what you are doing.
Are you using the Data Controls to connect to your SQL Database?

You "linked the tables" in what? is there a SQL Query that you are joining them on? Are they "linked" in your Linq to SQL .dbml file?

Lod

You've got questions and source code. We want both!

 

[DJWheezyWeez]

Sorry, again I'm new to this but I'll give you whatever you need to help. Here is my query:

SELECT     FilteredOpportunity.customeridname, FilteredOpportunity.createdon, FilteredOpportunity.description, FilteredOpportunity.estimatedclosedate, 
                      FilteredOpportunity.estimatedvalue, FilteredOpportunity.name, FilteredOpportunity.new_opportunitytypename, 
                      FilteredOpportunity.new_isbudgetedname, FilteredOpportunity.salesstagecodename, FilteredOpportunity.statecodename, 
                      FilteredOpportunity.actualclosedate, FilteredOpportunity.new_singlesalesactionname, FilteredOpportunity.new_singlesalesamount, 
                      FilteredOpportunity.new_singlesalesdate, FilteredAccount.customertypecodename, FilteredOpportunity.owneridname, FilteredOpportunity.ownerid, 
                      FilteredOpportunity.opportunityid, FilteredOpportunity.customerid, FilteredOpportunity.customeridtype, 
                      FilteredOpportunity.new_projectedcurrentyearvalue, FilteredActivityPointer.activitytypecodename, FilteredActivityPointer.scheduledend, 
                      FilteredActivityPointer.subject, FilteredActivityPointer.description AS description_activity, 
                      FilteredActivityPointer.statecodename AS statecodename_activity, FilteredPhoneCall.new_calldescription, FilteredActivityPointer.actualend
FROM         FilteredPhoneCall RIGHT OUTER JOIN
                      FilteredActivityPointer ON FilteredPhoneCall.regardingobjectid = FilteredActivityPointer.regardingobjectid AND 
                      FilteredPhoneCall.activityid = FilteredActivityPointer.activityid RIGHT OUTER JOIN
                      FilteredOpportunity AS FilteredOpportunity ON FilteredActivityPointer.regardingobjectid = FilteredOpportunity.opportunityid LEFT OUTER JOIN
                      FilteredAccount ON FilteredOpportunity.accountid = FilteredAccount.accountid
WHERE     (FilteredOpportunity.statecodename = N'Open')

 

[Qik3Coder]

Is this an SSRS report? Where does the error with the textbox come from?

Lod

You've got questions and source code. We want both!

 

[DJWheezyWeez]

Yes. The error comes from sums I've added in the report. The report is grouped by Stage > customeridname > opportunityid. One is below the header line as a grand total and the other is just below in the Stage group as a subtotal.

 

[Qik3Coder]

What type of data do you have in the VALUE field? Is it int/decimal data or is it varchar data that you are trying to treat as numbers?

Are you sure you have group name correct?


You've got questions and source code. We want both!

 

[DJWheezyWeez]

The data type is int and the group names are correct. I found that when I try to edit the sum in the Stage group, if I use =Sum(Fields!estimatedvalue.Value, "Stage"), the report works but if I use "customeridname" or "name" it doesn't. It seems like it will only run if I don't use a scope or use the same group the sum is on as the scope... Not sure why or how to get around it.

 

[Qik3Coder]

http://msdn.microsoft.com/en-us/library/ms159673(v=sql.100).aspx

You've got questions and source code. We want both!