CODEWITHSUNDEEP
reporting-servicesssrs-2008reportingservices-2005
Shiva
Shiva

Reputation: 83

How can we add new field in the column contain Expression related to different fields?

Below are sample table data in ssrs report,

ValueFor                  FirstHr    SecondHr    ThirdHr

Total Contacts                128      101         79

Abandons                       8       10           4

ValueFor,1Hrs,2Hrs and 3Hrs are columns Above,I need to add "Contacts Handled" filed under the "ValueFor" Column,its values depend on the same column in the different fields?

Contacts Handled=Total Contacts/Abandons

My Result is , 

      ValueFor                  FirstHr    SecondHr    ThirdHr

     Total Contacts                128      101         79

     Abandons                       8       10           4

    Contacts Handled                 16     10.1       19.75

How can I write Expression in FirstHr,SecondHr and ThirdHr columns?

Upvotes: 0

Views: 219

Answers (1)

Ian Preston
Ian Preston

Reputation: 39566

You can use an expression like:

=Sum(IIf(Fields!ValueFor.Value = "Total Contacts", Fields!MyValue.Value, Nothing))
  / Sum(IIf(Fields!ValueFor.Value = "Abandons", Fields!MyValue.Value, Nothing))

Using a conditional Sum as above you can look at all records in a particular Scope but only consider the ones that fulfill that particular criterion - by combining two you can get your required result.

Edit after comment

With your data:

enter image description here

I have a simple table:

enter image description here

Note that the expressions are all in the table footer row.

The expressions are:

FirstHr

=Sum(IIf(Fields!ValueFor.Value = "Total Contacts", Fields!FirstHr.Value, Nothing))
  / Sum(IIf(Fields!ValueFor.Value = "Abandons", Fields!FirstHr.Value, Nothing))

SecondHr

=Sum(IIf(Fields!ValueFor.Value = "Total Contacts", Fields!SecondHr.Value, Nothing))
  / Sum(IIf(Fields!ValueFor.Value = "Abandons", Fields!SecondHr.Value, Nothing))

ThirdHr

=Sum(IIf(Fields!ValueFor.Value = "Total Contacts", Fields!ThirdHr.Value, Nothing))
  / Sum(IIf(Fields!ValueFor.Value = "Abandons", Fields!ThirdHr.Value, Nothing))

Works fine for that dataset:

enter image description here

Upvotes: 1

Related Questions