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! ^^
Well coz if it doesn't have foams, it gives me this feelings that I didn't wash my hair thorough enough and still giving that dry dry de feelings wtf. Maybe others won't feel the same.
Been hanging out with Jann coz she'll be here for 2 weeks only! First blog of the year must be happy - ie, reunion ma right! ^^
Like I care. It's in my blog ady. 
