Convert a SQL statement to a SRSS statement

Question

Hi ,

Can anyone let me know how can I convert the below SQL statement into a SRSS expression . I am using SRSS 2008

SQL Exression  :(CASE  WHEN 1 THEN [Ytd] - [Target] ELSE [Target] - Ytd  END)

I want to convert the above statement in to a SRSS expression .Pls help.

thnx

Answer 1

Check IIF function in SSRS.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

My TechNet articles

Answer 2

Hi pepcoder,

Just as Naomi said, we can use IIF function or Switch function to achieve your requirement. For example, you are using the following T-SQL query:
CASE  WHEN [Ytd] >1 THEN [Ytd] - [Target] ELSE [Target] - [Ytd]  END

Then we can use the expression below to achieve the same goal in Reporting Services:
=iif(Fields!Ytd.Value>1, Fields!Ytd.Value- Fields!Target.Value, Fields!Target.Value- Fields!Ytd.Value)
Or
=Switch(Fields!Ytd.Value>1, Fields!Ytd.Value- Fields!Target.Value, Fields!Ytd.Value<=1, Fields!Target.Value- Fields!Ytd.Value)

The following article contains IIF function and Switch function is for your reference:
http://msdn.microsoft.com/en-us/library/ms157328.aspx

If there are any other questions, please feel free to ask.

Thanks,
Katherine Xiong

---

Katherine Xiong
TechNet Community Support

Answer 3

The posted CASE expression doesnt make any sense. I think its missing the condition to check

---

Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

Answer 4

Thanks Katherine. Expression mentioned got worked.However, I have a small query. What if the below expression returns 0 then I don't want to show any value in the report.How can I change the expression.

ie: =iif(Fields!Ytd.Value >1, Fields!Ytd.Value- Fields!Target.Value, Fields!Target.Value- Fields!Ytd.Value)

If the above expression returns a value of 0 then no need to show any values in the report. Column can be empty 

Answer 5

Visakh.

Your link is not opening. "Sorry, the page you were looking for in this blog does not exist."

 I have a small query. What if the below expression returns 0 then I don't want to show any value in the report.How can I change the expression.

ie: =iif(Fields!Ytd.Value >1, Fields!Ytd.Value- Fields!Target.Value, Fields!Target.Value- Fields!Ytd.Value)

If the above expression returns a value of 0 then no need to show any values in the report. Column can be empty 

Answer 6

Hi pepcoder,

To achieve the requirement, we can refer to the following expression:
=iif(iif(Fields!Ytd.Value >1, Fields!Ytd.Value- Fields!Target.Value, Fields!Target.Value- Fields!Ytd.Value)=0,nothing,iif(Fields!Ytd.Value >1, Fields!Ytd.Value- Fields!Target.Value, Fields!Target.Value- Fields!Ytd.Value))

Thanks,
Katherine Xiong

---

Katherine Xiong
TechNet Community Support