CODEWITHSUNDEEP
reporting-servicesssrs-2008
Torben Junker Kjær
Torben Junker Kjær

Reputation: 4072

How to avoid summing of numbers in report model in SQL Server Reporting Services

In a report model I have some entities which have attributes which are integers (set to integer datatype) but should not be summed or aggregated in any way. For examples ID's. But when I create reports with the wizard, sometimes the report builder will try to sum the values even though it doesn't make sense to sum ID's. For example let's say I have a list of cars sold in a month. In january I've sold 2 cars, one with the ID 101 and one with the ID 210. In report builder I will then - when using the wizard - get the number 311 for the summed values. I can remove it afterwards, but I would like this to not happen at all (since the end-users will be confused)

I need a way to say to the report model: This is an integer, but it is not really a number you should sum up

Upvotes: 2

Views: 4692

Answers (3)

Jason Us
Jason Us

Reputation: 1

This is a bit of a kludge, but it works for me. I used my text editor to edit the RDL (XML) file and replace "Sum(" with "", then searched (carefully) for the ")" and replaced with "". I only replaced this inside of the ... Reopen in report builder without issues.

Upvotes: 0

Anthony
Anthony

Reputation: 5226

If you drag an integer field onto the table it generates a sum by default. This is not always what you want - e.g. when the field is an Id or a status code that is the same for all rows shown.

Right-click the "<<Expr>>" and bring up the "Expression..." dialog. Replace "Sum" with "First" - e.g. =Sum(carID.value, "datasetname") becomes =First(carID.value, "datasetname") If the values are the same in all rows, then the first value will do. If not, there are also other functions like Last, Min, Max.

Upvotes: 1

jstell
jstell

Reputation: 160

Well the wizard isn't perfect as you've found out. It does it's best in trying to figure out what to do. All that you can really do is remove =Sum(carID.value, "datasetname") when it automatically puts it there. Or if you don't need to sum anything at all delete the footer of the table. A workaround perhaps would be to do a Convert in your SQL to make your ID's a varchar.

Upvotes: 2

Related Questions