Combining totals between tablix

[ncvandy]

I do not have a SQL background but I have been drafted into putting together a report using report builder 3 connected to a SQL 2008R2 database. What the report does is show the deductions from 2 different pay periods, employee amount, employer amount, and total of the two, grouped by deduction code and by then by employee. The pay periods are defined by a warrant number (payperiod plus year that runs sequentially, i.e. 032013 for the 3rd pay period of the year) that is used as parameters to filter the data. These warrant numbers are inputted by the user running the report. The report uses 2 tablix, tablix 1 shows the data from @warrant1 and tablix2 shows the data from @warrant2. Each tablix returns the data for their respective pay period where the result is listed by deduction code which can be expanded out to show the individual employees that have a deduction under that deduction code as shown below:
Deduction Code Employee# Employee Amt Employer Amt Total
1010 1000 (total all emp) 1000 (total all emp) 2000
100 50 50 100 etc…

This all works fine but what they also want and I cannot figure out is an additional variance column that shows the difference between Total Tablix1 and Total Tablix2 for each deduction code ie. in plain english
(Tablix1 Employee Amt + Employer Amount) - (Tablix2 Employee Amt + Employer Amount).
Nothing I have tried works. Can this be done, what am I missing.

Thanks,

Jim

Elegant solutions are nice, but right now I'll settle for whatever works.

 

[flapeyre]

Something like:

= "Difference: " & Sum(Fields!Amount.Value,[b]"Tablix1"[/b]) - Sum(Fields!Amount.Value,[b]"Tablix2"[/b])

The Tablix1 and Tablix2 parameters to the Sum expressions are the scope (in this case, your two tablixes). Scoping makes it possible to mix results from two separate data sources, among other things. If you leave out the scopr parameter, it assumes the current scope.

-- Francis
Aut viam inveniam aut faciam.