Tuesday, 14 January 2014

SSRS 2012: Decision Function (IIF & SWITCH)

Scribbled by Teddie Debbie at 12:06
Note: Cuppycakes that came to this entry because it's really urgent to know how, kindly Ctrl+F "Begin:" to get to the point.

* * * * * * * * * * * * * * * * * * * *

YORRR suddenly Serena shared my blog post I very de paiseh leh
Likes collected up to this time this moment, ie 13/1/2014 7.14pm. LOL (macam pathetic saja XD)

It was published 2 months ago and no one seems interested in it leh wtf.

Anyway the page record shoot up to 39 views today! Whoa! The power of social media and influence of Serena! I kowtow to both of you and fly my kisses to you readers! Both in reality and those imagine by me! Nah catch it!

Hahaha paiseh seems like I was a bit overexcited with it and keep crapping LOL OK OK lets begin ba!

So I was assigned (I think I still am wtf) to solve the report performance at work and have, of course, consulted Serena for the solution. (Yeah babe, it's SOLUTION, not WORKAROUND yo!) OK *ahem* nevermind that was an internal joke.

ANYWAY! Back to the assignment. Apparently, one of the way to improve the performance is to allow some tasks to be performed by the .rdl file instead of letting the SQL query in the stored procedure to render. So for this entry, I'm going to talk about using decision function with "IIF" and/or "SWITCH" in the .rdl UI to replace the "CASE WHEN... THEN" in the SQL statement.

To show as example, I've created a sample data as below:
Sample data
Where:
Subjects:
1 = History, 2 = Maths, 3 = Science, 4 = English, 5 = Psychology, 6 = P.E.

Normally, this is what we'll do:
a. Write the stored procedure this way (Query type: Text)
Declare @SQL nvarchar(max);

Begin
-- Select statements for procedure here
Set @SQL = 'SELECT Participants,
CASE WHEN Subjects = 1 THEN ''History'' WHEN Subjects = 2 THEN ''Maths'' WHEN Subjects = 3 THEN ''Science'' WHEN Subjects = 4 THEN ''English'' WHEN Subjects = 5 THEN ''Psychology'' WHEN Subjects = 6 THEN ''P.E.'' ELSE ''OTHERS'' END AS Subjects,
CASE WHEN Grades = ''A+'' THEN ''Excellent!'' WHEN Grades = ''A-'' THEN ''Well Done!'' WHEN Grades = ''B+'' THEN ''Good!'' WHEN Grades = ''B'' THEN ''Could Do Better!'' WHEN Grades = ''B-'' THEN ''Need a Lil Push!''
WHEN Grades = ''C+'' THEN ''Gotta Work Harder!'' ELSE ''Hopeless!'' END AS Grades
FROM dbo.SampleTable (nolock) a'
End

EXEC sp_executesql @SQL

b. Create UI like this
Sample .rdl UI

So the outcome will be like this:
Sample outcome
Click to see enlarged image
Note: Please ownself do necessary grouping in Step b. to get the above outcome ha.

ps: Don't nitpick over the description 'kay, it's just a sample.

* * * * * * * * * * * * * * * * * * * *

Begin:

So to let the UI take over the grouping task, here's what you can do:
a. Change all the "CASE WHEN... THEN" in the SQL statement as per below snippet
From
CASE WHEN Subjects = 1 THEN ''History'' WHEN Subjects = 2 THEN ''Maths'' WHEN Subjects = 3 THEN ''Science'' WHEN Subjects = 4 THEN ''English'' WHEN Subjects = 5 THEN ''Psychology'' WHEN Subjects = 6 THEN ''P.E.'' ELSE ''OTHERS'' END AS Subjects

To
Subjects

AND

From
CASE WHEN Grades = ''A+'' THEN ''Excellent!'' WHEN Grades = ''A-'' THEN ''Well Done!'' WHEN Grades = ''B+'' THEN ''Good!'' WHEN Grades = ''B'' THEN ''Could Do Better!'' WHEN Grades = ''B-'' THEN ''Need a Lil Push!'' WHEN Grades = ''C+'' THEN ''Gotta Work Harder!'' ELSE ''Hopeless!'' END AS Grades

To
Grades

As referring to sample above.

b. Change the .rdl file UI as per instruction below
SWITCH
Step 1: Right click on the cell you want to do the decision function as per shown in the image above. Click on "Expression..." to write the custom statement.
Step 2: When the pop up windows appear, write your own custom statement for the decision function.
Above image shown is an example of statement using SWITCH.
Syntax: =SWITCH(Cond1, Expr1, Cond2, Expr2, ...)

Very useful when you know what are your choices. But even if you don't - and you even need an "else" - you can always add in "1=1" as your condition followed by your "else" statement. Also used when there are more than 2 choices.

For example,
=SWITCH(..., Fields!Subjects.Value = 6, "P.E.", 1=1, "Others")

IIF
Step 3: Do the same to other cells that you want to change
Step 4: Write custom statements when pop up windows appear.
This one is using IIF statement.
Syntax: =IIF(Cond, true, false)

Usually IIF is used for 2 choices. But just like Excel, it can be used for multiple selections by just nesting them as shown in the example image!

The outcome
Click to enlarge
(Source: MSDN)

TADAH! There, you're good to go! ^^
Categories ,
Share/Like to turn a rhinoceros into a unicorn!

0 scream(s):

Post a Comment

 

Teddie • Debbie Template by Ipietoon Blogger Template | Gadget Review