CODEWITHSUNDEEP
sql-serverreporting-services
Kelly Hudson
Kelly Hudson

Reputation: 3

How do I filter and sum only negative or positive numbers in a field using an expression in SQL Reporting Services 2014

How do I filter and sum only negative numbers in a field using an expression in SQL Reporting Services 2014? The field Amt has both negatives (revenue) and positives (expenses) that I want to total in two different columns.

IIf(Fields!Amt.Value<0,SUM(Fields!Amt.Value<0),0) - Revenue


IIf(Fields!Amt.Value>0,(Fields!Amt.Value>0),0) - Expenses

This does not work, but you get the idea of what I'm trying to do. Sorry, I'm new at report writing. This is likely pretty simple for most of you.

Upvotes: 0

Views: 2597

Answers (1)

Alan Schofield
Alan Schofield

Reputation: 21683

You need something like this (and reversed for the other column)

=SUM(IIF(Fields!Amt.Value<0, Fields!Amt.Value, 0))

You should think of it as...

For each row, evaluate if it's < 0, if it is then return the value, else return 0. THEN we sum the results of all these returned values.

Often people think about it the wrong way round. You'll soon do it instinctively with a little practice.

Here's a complete example

I created a new report then.. I created a sample dataset as follows.

DECLARE @t TABLE (SomeText varchar(10), Amt float)

INSERT INTO @t VALUES
('A', -2.5),
('A', 2.5),
('A', 1),
('A', 0.5),
('B', -2.5),
('B', -0.5),
('B', 0.5),
('B', -0.1),
('C', -1),
('C', -2),
('C', 3),
('C', 4),
('C', -5)

SELECT * FROM @t

I then added a table to the report and added a row group, grouped by the SomeText field

The report design looks like this..

enter image description here The expressions in the Rev and Exp columns are as follows.

=SUM(IIF(Fields!Amt.Value<0, Fields!Amt.Value, 0))

and

=SUM(IIF(Fields!Amt.Value>0, Fields!Amt.Value, 0))

respectively.

The final output looks like this.

enter image description here

If you still get errors then check the datatypes in your recordset and post the output from it.

Upvotes: 4

Related Questions