SQL Nested Case Statement Example

2 Jul

As a workaround to the limitation of MS Reporting Services 2005 to do something like this

The value of Reportitems!txt_sales_p.Value >= 100.0
use this expression =code.sales_A(sum(fields!P_SALES_CURR.Value))
ELSE
=code.sales_B(sum(fields!P_SALES_CURR.Value))

Workaround in MS SQL Model

SELECT
STAFF_ACT_P1,
STAFF_ACT_P2,
STAFF_ACT_P3,
CASE WHEN STAFF_ACT_P1 >= ’24’ THEN
CASE WHEN STAFF_ACT_P1 <= ’25’THEN ‘A’
WHEN STAFF_ACT_P1 <= ’28’ THEN ‘B’
WHEN STAFF_ACT_P1 <= ’30’ THEN ‘C’
WHEN STAFF_ACT_P1 >= ’31’ THEN ‘HIGH’ END
ELSE
CASE WHEN STAFF_ACT_P1 <= ’23’ THEN
CASE WHEN STAFF_ACT_P1 >= ’22’ THEN ‘E’
WHEN STAFF_ACT_P1 >= ’21’ THEN ‘F’
WHEN STAFF_ACT_P1 >= ’20’ THEN ‘G’
WHEN STAFF_ACT_P1 >= ’19’ THEN ‘H’
WHEN STAFF_ACT_P1 <= ’18’ THEN ‘LOW’ END
END END
FROM bsc_rpt_model

I hope save some time to others creating reports in MS Reporting Services

–evick

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: