Sum of one field minus sum of another SSRS Expression

1
votes

I have a SSRS report I'm working on. What I would like to do is get the value of one field from its own dataset and subtract the value of another field from a different dataset. I can do this; however, the values are grouped so rather than giving me an individual value it gives me: (sum of all completed) - (sum of all completed the previous year). enter image description here

Here is my expression I am using for the column "Compared to last year"

=SUM(Fields!Completed.Value, "MTDSales") - SUM(Fields!Completed.Value, "MTDminus1")

"MTDSales" and "MTDMinus1" are 2 seperate datasets. MTDSales Dataset is the current months sales outcomes grouped by company MTDMinus1 dataset is last years figure for this current month as i am comparing the 2 months separately.

1
sql-serverreporting-servicessumcalculated-columns
Any reason you can't get the previous year's data along with the current year's in your query? Are the names in quotes the names of column groups or the names of two different datasets? – Kidiskidvogingogin

1 Answers

2
votes

I had to do this in a report where I was pulling current data from one database and older data from a data warehouse and combining. You will need to do a few things:

1. Establish a match field

This can be as simple as a single column. If you need to match on multiple fields you will need to add a calculated field to each dataset that you can match on. Assuming you need to match on company and financial year and each dataset returns one year of data, this might look something like match_id (assuming numeric values - otherwise you might need to use | or something as a separator):

`="A" & Fields!fin_year.Value & "B" & Fields!cust_id.Value`

2. Retrieve the data to the source field.

In your tablix add a column as you have to hold the looked up value:

=Lookup(Fields!matchId.Value, Fields!matchId.Value, Fields!Completed.Value, "MTDminus1")

3. Use the data

Now you can aggregate the data or do whatever further calculations you wish as if the field was part of your original dataset.