Filtering on a Calculated Field

• konrad.blocher

  Right there with Babe

  Points: 762

  More actions

  December 17, 2009 at 10:46 am

  #214844

  Hi,

  How do I filter a querry using a parameter based on a calculated field. Normally, in SQL I would say

  'WHERE db.table.field=@Parameter'

  but the field is not in the database - its calculated

  the calculated field is actually quite easy:

  =iif(Fields!V_CLASS_NAME.Value="Engine",Fields!V_CLASS_NAME.Value,Fields!V_ASSET_FAMILY.Value)

  so i was trying sth like

  WHERE (iif(dbo.VARIANTS.V_CLASS_NAME LIKE 'Engine', dbo.VARIANTS.V_CLASS_NAME, dbo.VARIANTS.V_ASSET_FAMILY) = @Asset)

  or

  WHERE (iif(dbo.VARIANTS.V_CLASS_NAME = 'Engine', dbo.VARIANTS.V_CLASS_NAME, dbo.VARIANTS.V_ASSET_FAMILY) = @Asset)

  But this seems to be producing an error

  Any Ideas?

• konrad.blocher

  Right there with Babe

  Points: 762

  More actions

  December 17, 2009 at 11:13 am

  #1093642

  OK

  got the answer

  (CASE WHEN dbo.VARIANTS.V_CLASS_NAME = 'Engine' THEN dbo.VARIANTS.V_CLASS_NAME ELSE dbo.VARIANTS.V_ASSET_FAMILY END = @Parameter)