Search code examples
reporting-servicesssrs-2008ssrs-2008-r2ssrs-expression

SSRS Expression

I've been using Crystal Reports for about 2 years and now I'm trying to learn SSRS in order to convert some of our custom reports over to SSRS.

In the DB, I have a column called OpenToPublic which will either be 0 for No or 1 for Yes. In CR, I created a formula to display on the report either Y or N depending upon the value:

if {DriveMaster.OpenToPublic} then 'Y'
else 'N'

What would be the best way to accomplish the same thing in SSRS? Is it an expression?

Solution

  • It is generally faster to let the SQL Server handle the the calculations(by doing them inside the dataset) rather than the SSRS engine(using expressions). So, go for creating an additional field in your dataset if possible.

    Modifying your existing dataset to add lew column logic for the new column:

    SELECT CASE WHEN OpenToPublic = 1 THEN 'Y' ELSE 'N' END AS DisplayCol
FROM dbo.DriveMaster

    Then simply display the column like Fields!OpenToPublic.Value. You could even add further formatting to change the color of the cell based on the value(e.g. if Y, then green else red).

    To reiterate, go for expressions if absolutely required(e.g. formatting, visibility etc.)

    A slower method would be to go for expressions. Both IIF and Switch can do your work. Either of the below expressions would work.

    =IIF(Fields!OpenToPublic.Value = 1, "Y", "N")

    =Switch(Fields!OpenToPublic.Value, 1, "Y", Fields!OpenToPublic.Value, 0, "N")