MS SQL server 2005 report select statement switch case

microsoft-sql-server-2005-report-select-statement-switch-case

To have one variable with many different cases (and many different results) use the SELECT command

In Microsoft SQL Server 2005 reports, THE KEY is Project -> Report Parameters -> "Code Tab"

Then you can enter VB visual basic (or .NET) code as a function and call it in the report...

do a select on a table? SELECT currency_codes

CASE currency
     WHEN 'USD' THEN '$' 
     WHEN 'GBP' THEN '?'

     [ ELSE else_result_expression ] 
END

Formatting the date interval as YYYY-MM can be moved to a method in the Code window,

which makes that method available everywhere within the report.

To format the date interval, I could create the following function and put it in the Code window:

Function FormatDateInterval(ByVal value As DateTime) As String
    Return Year(value) & "-" & Month(value)
End Function


=Code.FormatDateInterval(Fields!OrderDate.Value)


//ByVal as String also works for a parameter