[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/

• Brandie Tarvin

  SSC Guru

  Points: 173080

  More actions

  April 13, 2012 at 4:32 am

  #1473124

  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

  April 13, 2012 at 4:49 am

  #1473133

  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

  April 13, 2012 at 4:57 am

  #1473134

  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: 173080

  More actions

  April 13, 2012 at 5:02 am

  #1473138

  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.