CODEWITHSUNDEEP
sqlreporting-servicesfielddistinct
agrk12
agrk12

Reputation: 43

Sum of distinct values in field SSRS 2005

I'm working on SSRS report builder that is using a dataset calling a SQL Server 2000 database.

The query is getting sums of a few different fields and is also pulling out all records that have to do with that client number. I want to get the sum of the sum but it is way over because of the detail rows. Basically what I want is the sum of the distinct sum column values.

=Sum(Fields!tot.Value, "table1_Group3")

I saw that you can get sums by the groups and I tried the expression above but it comes back with an error:

The Value expression for the textbox 'tot' has a scope parameter that is not 
    valid for an aggregate function...

table1_Group3 is the name of the group that holds the sum value in the report.

Any suggestions on how to get the distinct values to sum them in this report.

Upvotes: 3

Views: 9973

Answers (3)

user978324
user978324

Reputation: 59

change the query/ dataset to sum(distinct tot) using the temp table on the sql server

Upvotes: 0

Jamie F
Jamie F

Reputation: 23789

=Sum(Fields!tot.Value, "table1_Group3")

The code above will give you the sum of "tot" for all rows in the current "table1_Group3." This means that this expression only makes sense somewhere within table1_Group3. Otherwise, SSRS doesn't know which is the current instance of that group.

Sounds like you would like to sum this value across multiple groups, but only take one "tot" from each instance of the group. (Are you sure that all rows in that group will have the same "Tot?")

If tot is the total of other fields in your returned data, then simply add those up in your formula. This may have the added benefit of simplifying your SQL query as well.

Some other options that could work: - Change your SQL query so that only one row per group gets the Tot field set. - Use Embedded code in the report to keep a running total which is added to only once per group, such as in the group header.

(If upgrading to 2008R2 SSRS is an option, then the Lookup function could be used here, maybe even to look back at the same dataset.)

Upvotes: 0

narek.gevorgyan
narek.gevorgyan

Reputation: 4185

I suppose you need to write sum(distinct columnName).

Upvotes: -1

Related Questions