SSRS NOT LIKE expression

I had the recent request to provide the ability to filter data in a report that was not like specific pattern.  This requirement is not uncommon or difficult when placed on the dataset using a parameter:

SELECT FirstName

FROM Person.Person

WHERE FirstName NOT LIKE @filter

The difficulty was that the report was being presented from a snapshot so the filter needed to be placed in the form of an expression.  Again, this does not seem overly difficult except that there is no NOT LIKE comparison operator in SSRS, but fortunately we can embed VB.NET in reports. 

In this example I have a single report using my local instance of SQL 2008 and the AdventureWorks2008 as the data source and a dataset populated using the below query:

SELECT FirstName

FROM Person.Person

From the Report menu select Report Properties and enter the below VB.NET code in the Custom Code box:

   
Public Function NotLike(ByVal val As String, ByVal filter As String)As Boolean

If val.Contains(filter) Then

   Return False

Else

   Return True

End If

End Function

So you should end up with something like this

Now let's get an idea of how this works by displaying the results in a table side by side with the FirstName column from our dataset.

While in the Design tab of BIDS drag and drop a table from the toolbox on the design work surface.  In the first details field of the table drag and drop your FirstName column from your dataset from the Report Data tab.  Right click the second details field and choose Expression and enter the following expression:

=code.NotLike(Fields!FirstName.Value, "ld")

Now preview the report and take a look at what our embedded code is providing:

We can see that all first names that do not contain "ld" returns True, so they are NOT LIKE the filter text we entered, while names that do contain the filter, like Donald, returns False. 

We need to create a parameter that will hold the user provided provided  filter value.  Go back to the Design tab and from the Report Data tab right click the Parameters folder and choose Add New Parameter.  The parameter will be called Comparison and the prompt will be Comparison string and will be a text data type:

From the Report Data tab right click the dataset and select Dataset Properties:

Go to the Filters tab and add a filter.  In the Expression open the expression builder and enter the below:


=code.NotLike(Fields!FirstName.Value, Parameters!Comparison.Value)
In the Value text box enter =True:

Now click the preview tab again and in the Comparison string parameter prompt enter ld and click View Report.  The data set is now filtered on the expression utilizing our embedded code only where a value of True is returned representing values that are not like the provided string comparison.

This is a rather simplistic example of utilizing embedded code to filter a report, but should provide a good starting point.  If you are interested in reviewing more in depth information on embedded code within SSRS or creating and using custom code references in expressions then please take a look at the documentation on MSDN:

Custom code references:
http://msdn.microsoft.com/en-us/library/ms155798.aspx

Adding code to a report:
http://msdn.microsoft.com/en-us/library/ms156028.aspx

SSRS Formatting

This post is found at: http://www.sqlsafety.com/?p=231

All formatting options described will be available for download on a .pdf in the next few weeks at the new site.

After working for years with other reporting products I made the move to SSRS and found that one of the more difficult learning curves was to find formatting codes I began with what I thought was a rather simplistic report that contained a simple tabular report with only a few columns. I had no problem with grouping my data, creating the expressions for the footers and headers, or even creating default values for parameters, but hit a wall when I began trying to format my dates and numbers. After finally finding the Format property I was at a loss of what to put in it. The below are the formatting codes that I have compiled since that day:

Date/Time Formatting Codes

Date Format Codes	Description	Example
d	Short date	3/12/2006
D	Long date	Monday, June 1, 2006
t	Short time	7:12 PM
T	Long time	6:15:55 AM
f	Long date/short time	Monday, June 1, 2006 7:12 PM
F	Long date/long time
g	Short date/short time	3/12/2006 19:12
G	Short date/long time	3/12/2006 19:12
M or m	Month and day only	5-Nov
Y or y	Month and year only	October, 2002
Yyyy	Four digit year	2008
yy	Two digit year	8
MMMM	Full month name	April
MMM	Three character month	Jan
MM	One or two digit month	4
Dddd	Full day name	Friday
Ddd	Three character abbrev.	Sat
dd	Two digit day always	4
d	One or two digit day	4
hh	Two digit hour always 12 hr clk	8
h	One digit hour 12 hr clk	8
HH	Two digit hour always 24 hr clk	20
H	One or two digit hour 24 hr clk	8
Mm	Two digit minutes	24
ss	Two digit seconds	24
tt	Two character AM or PM	AM
t	One character AM or PM 24 hr clk	A

NUMERIC FORMAT CODES

Numeric Format Codes	Description	Example
#	Optional place holder
0	Required number placehoolder
%	Percentage .95 becomes 95%
C	Two digit numbers & local currency char	$12,345.95 (For US currency)
D or D2	Decimal value, optionally can specify precision	56.95
E or E12	Specific notation, optionally can specify precision	1.23E+08
P or P1	Percentage, optionally can specify precision	87.4
N or N#	Numeric, including thousands comma separator prec. Optional	1,000

Now that you have these formatting codes where exactly do they go?  From within Business Intelligence Development Studio you can right click on the textbox or group and choose Properties and from the properties window select the Format tab :

or click on the object and browse to the format property in the Properties pane

and enter the formatting code.

The results for numeric formatting are:

Format	Value	Formatted Value
N0	10000.00	10,000
N1	10000.00	10,000.0
C	10000.00	$10,000.00
D2	10000.00	10000
P1	10000.00	1,000,000.0%
E	10000.00	1.000000E+004

The results for date/time formatting are:

Format	Value	Formatted Value
d	2/3/2010 12:30.000 AM	2/3/2010 12:30:00 AM
D	2/3/2010 12:30.000 AM	Wednesday, February 03, 2010
t	2/3/2010 12:30.000 AM	12:30 AM
T	2/3/2010 12:30.000 AM	12:30:00 AM
f	2/3/2010 12:30.000 AM	Wednesday, February 03, 2010 12:30:00 AM
F	2/3/2010 12:30.000 AM	Wednesday, February 03, 2010 12:30 AM
g	2/3/2010 12:30.000 AM	2/3/2010 12:30 AM
G	2/3/2010 12:30.000 AM	2/3/2010 12:30:00 AM
M or m	2/3/2010 12:30.000 AM	February 03
Y or y	2/3/2010 12:30.000 AM	February, 2010
Yyyy	2/3/2010 12:30.000 AM	2010

This post covered simplistic out of the box formatting and
will be followed by more complex formatting of numeric, data/time, and string types, until then you can also refer to MSDN on line documentation:

http://msdn.microsoft.com/en-us/library/ms157406.aspx

T-SQL or SSMS... Same Thing Right??

 
While teaching a T-SQL class a few years ago I was fortunate to have our training center's Office applications instructor, Johnnie Lewis, in attendance. In the first lab, within an hour of the class starting, Johnnie discovered the query designer in SQL Server Management Studio, SSMS, and announced to the entire class, "Hey David!! Look at this! SQL has a query builder just like Access!!" In sheer panic I announced, "Well, it's a little different. We really don't cover that in this class though", but the damage was already done and the bell could not be un-rung. The class immediately converged and surrounded Johnnie's work area to find where the query designer could be found and the remainder of the three day class was spent answering, "How do you do that query in the query designer". After joking with Johnnie about his discovery and his class room announcement and informing him any such future declarations could leave me unemployed, he asked the sincere question, "Why should I learn and use T-SQL when I can do the same thing in management studio?". This question was posed many times before Johnnie's earnest inquiry and, although I knew that there distinct differences and advantages to using t-sql I failed to educate myself as to the when and why. I was taught that using t-sql over a graphical user interface assured more control and efficiency, but I had had not made an effort to verify what, if any, difference there was between my t-sql code and what SSMS was doing in the background.  After I began testing the statements passed by SSMS I discovered that the disparity between efficiently written t-sql and what SSMS passed lay mostly in manipulating objects from the object explorer so that is what I will focus on in this post.

I originally ran the below examples on enterprise manager so when I began to write this I found some subtle differences between SSMS 2005 and 2008, so I will demonstrate the behavior of both. The examples require running a default trace from SQL Server profiler, either 2005 or 2008, to capture the commands that SSMS are passing to the database engine, I would also recommend filtering on the database id or name to keep overhead to a minimum. Once the trace is running create a table using t-sql that contains a single column with a data type of integer:

USE AdventureWorks
CREATE TABLE test(
col1   INT NULL);
GO

A typical database maintenance or development task is to change the data type of a column. To do so in t-sql is easy enough:

ALTER TABLE test

   ALTER COLUMN col1 FLOAT;
GO

but for those not familiar  or used to altering tables then why not use SSMS table designer?  From SSMS 2005 I can easily browse object explorer for the newly created test table, right click it and choose Design to open the table designer. In the design tab select the float data type from the drop down and choose to save the table and voila the change has been made. It would appear that SSMS passed the ALTER TABLE command, but in fact the DDL statement used is quite different when examining the profiler trace. The first action is SSMS creates a table called Tmp_test that is our test table with col1 as a float data type:

CREATE
TABLE dbo.Tmp_test(
col1 float(53) NULL
) ON [PRIMARY]

Next dynamic sql is used to insert all data from our test table into the new
Tmp_test table:

IF
EXISTS(SELECT
* FROM dbo.test)

     EXEC('INSERT
INTO dbo.Tmp_test (col1)

       SELECT CONVERT(float(53), col1) FROM dbo.test WITH (HOLDLOCK TABLOCKX)')

Then our original test table is dropped and the Tmp_test table is renamed to
test:

DROP
TABLE dbo.test

EXECUTE sp_rename N'dbo.Tmp_test',
N'test',
'OBJECT'

The difference is quite obvious and the overhead that this simple alter can incur is staggering.  I intimated that SSMS 2005 and 2008 differ in their behavior, so let’s look at SSMS 2008 now.  Follow the same steps to the table designer, change col1’s data type and choose to save your changes and you are immediately greeted with this:

The difference is not in SSMS writing a more efficient query, but instead telling you that it will have to drop and recreate the object and not letting you proceed.  This is the default behavior, but can be changed within SSMS from Tools>Options>Designers>Table and Database Designers and de-selecting Prevent saving changes that require table re-creation:

So why doesn’t SSMS 2005 provide such due diligence? It actually will, but as the table has no data in it, the creating, moving of data, dropping, and renaming provides substantially less overhead than a table that contains data.  From within SSMS 2005 we can populate the test table with data to exhibit the warning
posted for such actions:

DECLARE @int INT

SET @int = 1

WHILE @int <= 250000

BEGIN

   IF @int%10 <> 0

   BEGIN

     INSERT test

     VALUES(NULL)

  END

  ELSE

  INSERT test

  VALUES(@int)

  SET @int = @int + 1

END;
GO

Now again browse the object explorer and attempt to change the data type of col1 and SSMS 2005 displays this warning:

You are told that the action may take considerable time and given the option to proceed with the action or exit without your changes. Consider how much time that this may take, not to mention the resources, if the table contained numerous columns, constraints, a clustered index, several non-clustered indexes, and a couple million rows. As I mentioned earlier I had originally explored the behavior of enterprise manager to find out what was occurring in the background and required using profiler.  Had I waited till 2005 and SSMS I could have saved myself some time and effort. Management studio provides us with a handy Generate Change Script that will script out exactly the action(s) that SSMS would take:

I can now defend my position that Microsoft does in fact provides awesome graphical tools, but the use and knowledge of t-sql is, at times, far more efficient.