Does SQL Server Report (SSRS 2008) have event procedure?

Question

I am new to SSRS reports. Before this I have used similar kind of technologies like Oracle Developer Reports or MS Access Reports. My question here is: does SQL Reports have event procedures? By this I mean if we want to do some processing on every data row, or on before/after each group, can we write event procedures/functions at these events?

The scenario is this; In a typical Employees report, I want to define a variable that categorizes employees based on their salary, and for each row we compute that variable as per following logic;

IF sal BETWEEN 1000 AND 1999 THEN
   Emp_Tag = 'Lower Middle'
   Count_low_mid += 1
ELSIF sal BETWEEN 2000 AND 2999 THEN
   Emp_Tag = 'Average'
   Count_average += 1
ELSIF sal BETWEEN 3000 AND 3999 THEN
   Emp_Tag = 'Above Average'
   Count_abv_avg += 1
.... and so on ...
END IF

And then at the end we can calculate the counts of each category of employees (Count_low_mid, Count_average, Count_abv_avg). Can anybody please tell me how can I implement this kind of logic in sql server reports (SSRS)?

Thanks in advance.

Answer 1

You can do this by apply a conditional aggregate in a table header row. Say I have some sample data like this:

And a table with header rows only:

The expressions for the respective groups are:

Lower Middle

=Sum(IIf(Fields!sal.Value >= 1000 and Fields!sal.Value < 2000, 1, 0))

Average

=Sum(IIf(Fields!sal.Value >= 2000 and Fields!sal.Value < 3000, 1, 0))

Above Average

=Sum(IIf(Fields!sal.Value >= 3000 and Fields!sal.Value < 4000, 1, 0))

These expressions simply count all rows that fulfill certain criteria, giving the required results:

Alternatively, set up a Calculated Field with an expression like:

=Switch(Fields!sal.Value >= 1000 and Fields!sal.Value < 2000, "Lower Middle"
  , Fields!sal.Value >= 2000 and Fields!sal.Value < 3000, "Average"
  , Fields!sal.Value >= 3000 and Fields!sal.Value < 4000, "Above Average")

And set up a group based on this, which will give the same result: