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?
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")
There are Expressions in SSRS to set the calculation and set values to your fields. Here is more info.
Now for the your expression you can do as below (I am assuming the data type of your OpenToPublic is Bool),
= IIF(Fields!OpenToPublic.Value,"Y","N")
Set this expression on the cell in which you want to show the value. And if you want to add the calculated field in dataset then that can also be done Look here. And set the same expression for that field.
