Things To Consider While Using SQL Server Reporting (SSRS) With Silverlight (3 and 4)

Reporting is the heart of most business applications. And SQL Server Reporting Service (SSRS) is a preferred choice while MSSQL Server is the database back-end. Apart from ready to use services, SSRS provides different and useful scalability and extensibility options.

On the other end, lately, MS Silverlight has generated positive heat amongst varied business applications.

So you got the point. I’m talking about the marriage between SSRS and Sliverlight.

It started with challenges as the whole concept was an unknown devil which we were able to tame in the end.

The problem was simple – Microsoft doesn’t have any SSRS report-viewers for Silverlight – but its impact was not so. Initially, it was apparent that we cannot use SSRS with Silverlight.

So, I Googled (and then Bingged also – both the technologies are from Microsoft :) ) to find out the following two possibilities out of my one hour of investment:

  1. Use Third Party Report Viewer Controls
  2. Use ASP.NET application and invoke it from Silverlight application

Each option has its own distinctions. Here are those:

  1. Third Party controls: We can use third party report viewers which support SSRS reports. Right now there is only one such software provider Perpetuum Software is available. They provide SSRS report viewer for Silverlight which is also Silverlight 4 ready. There are other report viewers provided by other major component providers like Telerik however they do not support SSRS reports at present. Instead, they support their own reporting ways from within Silverlight application.
  2. ASP.NET Application: In this approach, we have to create an ASP.NET web application for hosting SSRS reports. The web application will use default report viewer control. To integrate this web application into the Silverlight application there are two possible options as below:
    1. Using Html Viewer: We can use HtmlViewer control to view reports hosted as an ASP.NET web application. Though, Silverlight 3 does not have in-built html viewer control, there are third party controls available like one from Telerik. The good news is that Silverlight 4 does have in-built html viewer control.
    2. Using JavaScript: We can use client side JavaScript functions to open new window or IFRAME from within Silverlight application which will show reports hosted as an ASP.NET web application.

I am sure Microsoft will come up with a better solution to this problem in the coming days. Until that any of the above options can be used. I used option #2 – ASP.NET application – considering the client needs of flexibility and it worked well.

Something about NULL in SQL Server

As developers, we all probably know what a NULL is in SQL Server. NULL is not a specific value but it stands for UNKNOWN value. In other words, NULL represents the ‘absence of data’.

The behavior and treatment of NULL in SQL server is very interesting in different context and part of T-SQL language. Let’s have a quick look on how it is treated in different ways by SQL Server.

When you use NULL (UNKNOWN value) in Arithmetic operations or in String Concatenation operations, the result is always UNKNOWN because the value which is being operated is not available or UNKNOWN. So, all the following statements yield the result of NULL:

SELECT 5 + NULL
SELECT 10 / NULL
SELECT 'Gateway' + ' Technolabs' + NULL

So beware when you are trying to do something similar as shown below:

DECLARE @TOP INT
DECLARE @SQL NVARCHAR(100)
SET @SQL = 'SELECT TOP ' + CAST(@TOP AS VARCHAR(10)) + '* FROM HumanResources.Employee'
EXECUTE SP_EXECUTESQL @SQL

The above query will never execute. Guess why?  Because you forgot to initialize @TOP variable and due to that it will contain NULL. So when you try to concatenate the value of @TOP with the value of @SQL, the resulting query is UNKNOWN.

Unlike other programming languages, SQL Server follows the rules of “Three-Valued Logic” which means that the result of a condition in T-SQL can have any of three possible values viz. TRUE, FALSE and UNKNOWN. If NULL is one of the operands in condition composed of comparison operators like =,>,<,>=,<=,<>, then the result of the expression is NULL or UNKNOWN.  For example, the result of all of the following conditions is UNKNOWN:

… WHERE 100 = NULL

… WHERE NULL > 5

… WHERE NULL < 10

Even following statement yields NULL as per SQL-92 Standards:

… WHERE NULL = NULL

Let’s create a simple table and insert some records in it.

CREATE TABLE tblPerson
(
PersonID INT
,PersonName VARCHAR(50)
,Age TINYINT
)
GO
INSERT INTO tblPerson
SELECT 1,'Person-1',25
UNION
SELECT 2,'Person-2',NULL
UNION
SELECT 3,'Person-3',35

We have a table which contains three rows.  The second row has NULL in Age for Person-2. Now, execute the following query:

SET ANSI_NULLS OFF
SELECT * FROM tblPerson WHERE Age = NULL

The above query will return 1 row for Person-2. Notice the use of SET ANSI_NULLS OFF which is not recommended way because it does not conform to SQL-92 standards. Microsoft encourages the usage of SET ANSI_NULLS ON which is considered best practice and which is also the default behavior of SQL Server. Now, consider following query:

SET ANSI_NULLS ON
SELECT * FROM tblPerson WHERE Age = NULL

The result of the condition in WHERE clause in above query will be UNKNOWN. It is neither TRUE nor FALSE. But, in this case SQL server treats NULL as if “NOT EQUAL TO” and evaluates the result of the condition to FALSE and will not return any rows. Note the use of SET ANSI_NULLS ON, which is SQL Server default.

If you want to fetch rows where Age is NULL, you should use ‘IS’ operator with ANSI_NULLS set to ON instead of using ‘=’ operator with ANSI_NULLS set to OFF. So, the preferred query should be as follows:

SET ANSI_NULLS ON
SELECT * FROM tblPerson WHERE Age IS NULL

As you can observe from above examples that using comparison operators (=,<,>,>=,<=,<>) in condition with NULL as one of its operands appearing in WHERE clause yields UNKNOWN result when you don’t specify ANSI_NULLS option or if it is set to ON.

But this is not the case when NULL appears in condition for a CHECK constraint. To see this, let’s add a CHECK constraint to our table so that age must be greater than zero when a record is inserted or updated.

ALTER TABLE tblPerson
ADD CONSTRAINT CK_tblPerson_Age CHECK (Age &gt; 0)

Now, add new row with NULL in Age as shown below:

INSERT INTO tblPerson VALUES (4,'Person-4',NULL)

The age for newly inserted row is NULL which is not greater than zero because it is UNKNOWN. But, SQL Server treats NULL differently in this case and evaluates the CHECK constraint condition as if it is TRUE and inserts new row to the table.

You saw that the condition WHERE NULL=NULL yields NULL because both the values are UNKNOWN and due to this comparison is not possible. Thus, the result will always be UNKNOWN. But again, this is not always the case. To prove my point, let’s add a UNIQUE constraint on PersonID column.

ALTER TABLE tblPerson
ADD CONSTRAINT UK_tblPerson_Age UNIQUE (PersonID)

Now insert a new row where PersonID is NULL as shown below:

INSERT INTO tblPerson VALUES (NULL,’Person-5′,30)

Because PersonID column allows NULL values, SQL Server will insert this new row. Now, try inserting one more row where PersonID is NULL:

INSERT INTO tblPerson VALUES (NULL,'Person-6',35)

As you may probably have guessed, because of UNIQUE constraint on PersonID SQL Server will not allow inserting another row with NULL for PersonID because tblPerson already contains one row with NULL in PersonID column. In this case, SQL server treats NULL even differently and considers all NULLs as if they are equal and does not allow a second NULL to be inserted. Even when SQL Server groups or orders rows by a column, it considers all the NULLs in one group as if they are all equal.

I hope this discussion will help many of you in understanding the behavior of NULL in SQL Server. Thank you for taking time to read this article. Readers’ valuable comments are always welcome!