Monday 26 December 2011

Dynamic Sorting in SSRS

Introduction

Many times while developing any report we come across a situation where we need to SORT the TABLE columns or the columns in the GROUP dynamically. There are many well known ways to achieve this. However, here I will demonstrate a not-so-known way -

 Implementation

Assuming that the sorting is to be done on 3 columns – Col1,Col2 & Col3. But the ORDER is undecided until runtime. Now, what we can do is -

  1. Define 3 Report parameters. Say - @SortCol1Name,@SortCol2Name & @SortCol3Name of STRING type.
  2. Pass the name of the columns which need to sorted in the required order. Say -
    • @SortCol1Name = “ColumnX”,@SortCol2Name = “ColumnY” and @SortCol3Name = “ColumnZ”
    • OR @SortCol1Name = “ColumnY”,@SortCol2Name = “ColumnZ” and @SortCol3Name = “ColumnX”
    • OR any preferred order
  3. In the Sorting Option of the Properties dialog box of Table or Group, set the sort expression as under -
    • =Fields(Parameters!SortCol1Name.Value).Value
    • =Fields(Parameters!SortCol2Name.Value).Value
    • =Fields(Parameters!SortCol3Name.Value).Value

1

This finally gets converted to –Fields!ColumnX.Value,Fields!ColumnY.Value & Fields!ColumnZ.Value if we have the passed the following values for the parameters - @SortCol1Name = “ColumnX”,@SortCol2Name = “ColumnY” and @SortCol3Name = “ColumnZ”

Conclusion

With this trick, we can easily set the sort expression dynamically. However, there is a limitation that we can not set the Sort Direction dynamically. I could not find out a way for it. If you have any trick to get it done with this, please leave your suggestion as a comment to this post.

Thursday 15 December 2011

Tips and Tricks: Clipboard Ring and Toolbox Snippets


Introduction

Today I show you my favorite future in visual studio which is Clipboard Rind and Toolbox Snippets.

Implementation

1.    Clipboard Ring

Many times I wish I could go back to something I copied & pasted just a few minutes ago, then to have to go and copy the same text all over again. Turns out that Visual Studio already has this “hidden” feature.  It’s called the Clipboard Ring

If you copy a number of items to the clipboard, pressing [Ctrl] + [Shift] + [V] will paste the last text that was copied, but it will also highlight the pasted phrase.

Continue holding down [Ctrl] + [Shift] and press [V] again.  This will cause that highlighted section to cycle through the other items on the clipboard.  This is extremely useful when you have to copy multiple lines of separate text to a new document. 

You can also use [Ctrl] + [Shift] + [Insert] keys instead of [Ctrl] + [Shift] + [V]

2.     Toolbox Snippets

Now, you might be saying, “Great, but what if I want to save a piece of code that I use frequently in multiple places? It means same pieces of code change in multiple files. I don’t want to have to cycle through the clipboard ring every time.”  Well, there’s also a feature for that

If you highlight a section of code (or text) and drag it to the Toolbox, VS will create a "snippet” (not to be confused with code snippets). When u want back that code then you dragged into the VS editor or double click on that snippet it will paste in VS editor.

And last but not least these snippets also available in next day.

Screen Sot


Wednesday 7 December 2011

How many times the T-SQL inside the CTE is executed?

Introduction

Common Table Expression (CTE) has become very popular these days. However, many users of CTE still have a myth that the t-sql written inside the CTE will be executed only once irrespective of the number of times it is referred in the subsequent CTEs or the related query.

However, this is not correct. It is a pure misconception. And let’s try to prove this with a simple example.

 Proof

The Query 

;WITH CTE1 AS (
SELECT 
  * 
FROM 
  dbo.Employees
),CTE2 AS (
SELECT 
  * 
FROM 
  CTE1
)
SELECT
  C1.EmployeeID,
  C1.Name,
  C2.EmployeeID,
  C2.Name,
  C3.EmployeeID,
  C3.Name
FROM
  CTE1 C1
INNER JOIN CTE2 C2
  ON C2.EmployeeID = C1.EmployeeID
INNER JOIN CTE1 C3
  ON C3.EmployeeID = C1.EmployeeID

 


Execution Plan


CTE


 


Please have a look at the execution plan. It clearly shows that even though the t-sql to fetch the data from the table is written only once inside CTE1, the Scan is done thrice. This is because CTE1 is referred thrice in the final query -



  1. Two times CTE1 is directly used.
  2. Once CTE1 is used indirectly via CTE2.

 Conclusion


The CTE should be thought of a view that is defined for the current query only. At the time of execution, the query optimizer will replace all the direct/indirect use of CTE with the actual query in the same way as it does for Views.

Friday 2 December 2011

Workarounds for SSRS 2008 Font rendering issue

 

Problem

A report developed using SSRS 2008 either through Visual Studio 2010 or BIDS 2008 does not renders properly when viewed in the Report Viewer 10.0 via Remote Desktop or any 3rd party applications using Terminal Services in the background.

Remote Rendering

vs2010remote

Local Rendering

vs2010local

Workarounds

  1. Change the Screen Resolution to 1024 X 768 or any 4:3 aspect ratio for both the Remote machine as well as the local machine.
  2. Export the report as PDF and then take the print outs if required.
  3. If the above two solutions does not work for you, unfortunately you will have to look back to SSRS 2005.

Hope, this saves you some time of searching.