[SSRS][Formula] Expression for subtracting values to get total

Question

Post reply

[SSRS][Formula] Expression for subtracting values to get total

taybre

SSC Eights!

Points: 916
More actions
April 13, 2012 at 3:22 am

#259958

Hi all,
I have generated a report in SSRS 2008 and I am experiencing a difficulty in generating totals.
This is a picture of the report in design view:
This is a picture of the report at run time:

As you can see that each location has 2 markers; "Imp" and "Exp" as well as an imbalance total.
SSRS automatically adds the 2 figures but I need for the Imbalance total to equal "Imp" minus "Exp".
This will result in location 1 having an imbalance of 0
Location 2 having an imbalance of -10 not 14
Location 3 having an imbalance of 1 not 11
Location 4 having an imbalance of 22 not 34
The expression in the cell where is the imbalance total is displayed is: =Sum(Fields!Count.Value)
What would the expression be to subtract "exp" from "imp" for each location?
Thank you in advance 🙂
HTH :-):-)
taybre
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Brandie Tarvin SSC Guru Points: 173061 More actions · Answer 1

You have to pull the EXP and the IMP fields into the expression for Imbalance.

=SUM(Fields!IMP.Value) - SUM(Fields!EXP.Value)

I probably have the field names wrong, but it's really not that hard. Make Imbalance an expression. Once you have the expression box open, open up field list of your report (upper left hand side, I think), drag the two fields into the expression box below, and add a minus sign. Then test the expression.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

taybre SSC Eights! Points: 916 More actions · Answer 2

The problem with that is there is no fields!imp.value and fields!exp.value to use.

This is what my source table looks like

create table sourcetable

(

[Marker] varchar(3),

[Country] varchar(2),

[Location] varchar(2),

[LocationName] varchar(10)

)

insert into sourcetable

select 'IMP', 'C1','L1', 'Name1'

union all

select 'IMP', 'C1','L2', 'Name2'

union all

select 'IMP', 'C1','L2', 'Name2'

union all

select 'IMP', 'C1','L2', 'Name2'

union all

select 'IMP', 'C1','L3', 'Name3'

union all

select 'IMP', 'C1','L3', 'Name3'

union all

select 'EXP', 'C1','L3', 'Name3'

union all

select 'EXP', 'C1','L2', 'Name2'

union all

select 'EXP', 'C1','L2', 'Name2'

union all

select 'EXP', 'C1','L1', 'Name1'

select [Country], [Location], [LocationName], [Marker], COUNT(*) [Count]

from sourcetable

group by [Marker], [Country], [Location], [LocationName]

Could I maybe work out the imbalance in the select statement by subtracting the "count" value of marker "exp"

from the "count" value of marker "imp" for each location?

HTH :-):-)
taybre

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

taybre SSC Eights! Points: 916 More actions · Answer 3

My apologises, I forgot to include "size"

New source data =

create table sourcetable

(

[Marker] varchar(3),

[Country] varchar(2),

[Location] varchar(2),

[LocationName] varchar(10),

[Size] varchar(2)

)

insert into sourcetable

select 'IMP', 'C1','L1', 'Name1', 'S1'

union all

select 'IMP', 'C1','L2', 'Name2', 'S1'

union all

select 'IMP', 'C1','L2', 'Name2', 'S2'

union all

select 'IMP', 'C1','L2', 'Name2', 'S2'

union all

select 'IMP', 'C1','L2', 'Name2', 'S2'

union all

select 'IMP', 'C1','L3', 'Name3', 'S2'

union all

select 'IMP', 'C1','L3', 'Name3', 'S2'

union all

select 'EXP', 'C1','L3', 'Name3', 'S2'

union all

select 'EXP', 'C1','L2', 'Name2', 'S2'

union all

select 'EXP', 'C1','L2', 'Name2', 'S2'

union all

select 'EXP', 'C1','L2', 'Name2', 'S1'

union all

select 'EXP', 'C1','L2', 'Name2', 'S1'

union all

select 'EXP', 'C1','L1', 'Name1', 'S1'

union all

select 'EXP', 'C1','L1', 'Name1', 'S1'

select [Country], [Location], [Size], [Marker], COUNT(*) [Count]

from sourcetable

group by [Country], [Location], [Size], [Marker]

Sorry about that.

HTH :-):-)
taybre

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Brandie Tarvin SSC Guru Points: 173061 More actions · Answer 4

What you do then is to create two report variables, one for EXP and one for IMP. Then have the variables subtract from each other.

FYI: It's been a while since I used SSRS. I might be getting this mixed up with Crystal Reports, where I know for a fact you can create report variables.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.