Calculations in SSRS

I have an SSRS Report. In which I have a table that has some rows. I want to calculate some things on the basis of these rows. See the sample table and things to be calculated:

Employment Period is calculated using if开发者_运维问答( DayEmployed>730) then add 1 to count. and count is the count of Employment Periods greater than 2 Years. How can I calculate this in SSRS?

In summary:

• an employment period is 365 days, or 1 year.
• you want the employment period count to start incrementing after they have clocked up 2 years

Assumptions:

• If the employee has worked 730 days, the employment period is still 0
• If the employee has worked 731 days, the employment period is 1
• the employment period remains at 1 until the employee clocks up 1096 days, at which point employment period becomes 2

Probably the easiest way to do it is:

= IIf(DaysEmployed > 730, CEILING(DaysEmployed/365) - 1, 0)

I did up a quick test of this in Excel, and excusing any syntactical errors* in my statement it does what you want.

Edit:

Okay, what you need is a conditional aggregate:

=Sum( IIF(DaysEmployed > 730, 1, 0), "DSEmploymentSummary")

This will do a sum over the DaysEmployed field in the scope of the object called DSEmploymentSummary (which is your data bound to the table/tablix).

Alternatively, you could have a hidden calculated column which simply contains a 1 or 0 for each row depending on the DaysEmployed being over the threshold, and then do a normal SUM on that column.

_{* My reporting is rusty, and i didn't want to crank up a new dummy report to test it.}

I've used this formula for this purpose:

=FormatNumber((iif(Fields!DaysEmployed.Value > 730, Count(Fields!DaysEmployed.Value, "DSEmploymentSummary"), 0))/CountRows("DSEmploymentSummary"),2,0,-1,-1)