April 2011

SQL Server: Search Similar String in a Table

There are may ways to look for similar strings in a SQL Server column. The most common method is to make use of LIKE operator. Let us see the different ways to look for similar string in a table.

Consider the following data:

declare @test table(data varchar(100))
insert into @test
select 'this is for testing' union all
select 'test entry' union all
select 'no way for this' union all
select 'nothing to be tested' union all
select 'welcome'

Suppose you want to find out data with the word ‘test’

Method 1 : Use LIKE operator

select data from @test
where data like '%test%'

Method 2 : Use CHARINDEX function

select data from @test
where charindex('test',data)>0

Method 3 : Use PATINDEX function

select data from @test
where patindex('%test%',data)>0

Method 4 : Use Regular expression

select data from @test
where data like '%[t][e][s][t]%'

Both charindex and patindex look for the match and return its position in the string. All the above four methods would return the same result as shown below:

SQL Server: String operations in XML document‏

SQL Server versions starting from 2005 and onwards supports storing and retrieving data as XML format. Using the XML datatype, you can do lot of things such as splitting string, string concatenation etc. In this post, we will see how we can use XML datatype to split strings

Consider the following example:

Declare @string Varchar(100),@delimiter CHAR(1)
Set @string = 'See if you can you split this'
set @delimiter = ' '
Declare @Xml Xml
Select @Xml = Cast('<d>' +
Replace(@string, @Delimiter,'</d><d>') + '</d>' As Xml )
SELECT T.split.value('.', 'nvarchar(max)') AS data
FROM @XML.nodes('/d') T (split)

In the above example the variable @Xml is of type xml. It concatenates the
original string by replacing a space with </d><d> so that each word will be surrounded
by <d> and </d>. The string is split by identifying the elements within <d> and </d>.

OUTPUT

XML Basics in SQL Server 2005 and 2008

In this article, we will see how to write basic queries to insert, query and generate XML data in SQL Server 2005/2008. For this demonstration, I am using the ‘Northwind’ database.

The first step is to start ‘SQL Server Management Studio’ and start a new query window. Then use the following clauses

1. FOR XML AUTO – This clause returns a simple, nested XML tree result.

SELECT * FROM Customers FOR XML AUTO

2. FOR XML RAW – This clause returns a simple, nested XML tree result by transforming each row in an <ROW/> Element.

SELECT * FROM Customers FOR XML RAW

3. FOR XML AUTO, ELEMENTS – This clause returns a XML result by specifying columns as sub elements.

SELECT * FROM Customers FOR XML AUTO,ELEMENTS

4. FOR XML AUTO, ELEMENTS, TYPE – This clause returns a XML result by specifying columns as sub elements and ‘TYPE’ specifies that it returns result as XML type which we can store in XML data type in SQL Server.

SELECT * FROM Customers FOR XML AUTO, ELEMENTS, TYPE

In SQL Server 2005, Microsoft has introduced a new data type ‘XML’. We can use this data type to store well formed as well as valid xml in our tables. Let’s see a few examples of the same –

Let’s first create a table which will use XML data type as shown in the following script –

Now let’s insert few rows in our table. For inserting the data in the above table, we will define a variable with ‘XML Data Type’ as shown below –

Now let’s write a select statement which will fetch the inserted data –

SELECT * FROM CustomerProducts

Now if you check the result, it should look like the following –

Now let’s write some queries which will test the XML data type. Let’s insert a record into our ‘CustomerProducts’ table which is not well formed, as shown below –

If you check the above XML, it does not having closing tag for <ProductID>. So the result will be as shown below –

Now let’s see how to validate the XML data using XML Schemas. Let’s drop the existing table we created above.

DROP TABLE CustomerProducts

Now create a schema for XML data validation as shown below –

CREATE XML SCHEMA COLLECTION ProductSchema AS'
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"
targetNamespace="http://www.microsoft.com/schemas/northwind/products"
xmlns:prod="http://www.microsoft.com/schemas/northwind/products">
<xs:element name="Product">
<xs:complexType>
<xs:sequence>
<xs:element ref="prod:ProductID" />
<xs:element ref="prod:ProductName" />
<xs:element ref="prod:SupplierID" />
<xs:element ref="prod:CategoryID" />
<xs:element ref="prod:QuantityPerUnit" />
<xs:element ref="prod:UnitPrice" />
<xs:element ref="prod:UnitsInStock" />
<xs:element ref="prod:UnitsOnOrder" />
<xs:element ref="prod:ReorderLevel" />
<xs:element ref="prod:Discontinued" />
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="ProductID" type="xs:integer" />
<xs:element name="ProductName" type="xs:string" />
<xs:element name="SupplierID" type="xs:integer" />
<xs:element name="CategoryID" type="xs:integer" />
<xs:element name="QuantityPerUnit" type="xs:string" />
<xs:element name="UnitPrice" type="xs:double" />
<xs:element name="UnitsInStock" type="xs:integer" />
<xs:element name="UnitsOnOrder" type="xs:integer" />
<xs:element name="ReorderLevel" type="xs:integer" />
<xs:element name="Discontinued" type="xs:boolean" />
</xs:schema>'

To see all the available XML schemas execute below query –

SELECT * FROM sys.xml_schema_collections

The result is as shown below –

Now let’s create the CustomerProduct table once again with the XML data type which will take the address of the above schema, as shown below –

Now try to insert a record and you will get an exception –

The exception is thrown because we are not passing the ProductName. Now let’s insert the following record which matches our schema validations –

You can now query the data and you should see valid XML.

Summary – In this article, we have seen how to use XML for querying and inserting the data in the table. We saw some basic queries of XML while working with SQL Server.

SQL Server: Transfer Objects From One Schema to Another

This post describes how to transfer objects from one schema to another. A simple method is to make use of the ALTER Schema statement as shown below.

Consider that there are two schemas named test1 and test2 in the database and you want
to move tables from schema test1 to test2.

Let’s create a table in schema test1.

In the above query, we have also run a query to know which schema the table test belongs to.

create table test1.test(id int, names varchar(100))
select * from information_schema.tables where TABLE_NAME='test'

As you can see, the schema of the table is test1

Now run the following query to move table test from schema test1 to test2

Again run the following statement to know which schema the table test now belongs to

SELECT * from information_schema.tables where TABLE_NAME='test'

As you see from the result, the schema of table has now changed from test1 to test2

SQL Server: Designing Chart Report in SSRS 2008

In this article, we will see how to design a Chart report in SQL Server Reporting Services 2008. For this demonstration we will use AdventureWorks sample database provided by Microsoft.

You can also check my previous article on SQL Server: Designing Tablix Report in SSRS 2008

To design this report, let’s open Visual studio 2008 Business Intelligence Studio and create a new Report Server project as shown below –

Once your project is ready, let’s create a data source which will fetch the data from AdventureWorks database. To fetch the data let’s write the following query –

SELECT YEAR(SOH.OrderDate) OrderYear,PC.NAME AS CategoryName,PSC.NAME as SubCategoryName,SUM(UnitPrice) TotalPrice, SUM(OrderQty) TotalOrders
FROM Production.ProductCategory PC INNER JOIN Production.ProductSubcategory PSC ON PC.ProductCategoryID=PSC.ProductCategoryID
INNER JOIN Production.Product P ON PSC.ProductSubcategoryID=P.ProductSubcategoryID INNER JOIN Sales.SalesOrderDetail SOD ON P.ProductID=SOD.ProductID
INNER JOIN Sales.SalesOrderHeader SOH ON SOH.SalesOrderID=SOD.SalesOrderID
GROUP BY YEAR(SOH.OrderDate), PC.Name,PSC.Name
ORDER BY OrderYear,CategoryName,SubCategoryName

If you execute this query, you should get a similar the result as shown below –

To add a new data source to our report, go to Solution Explorer. Right click ‘Shared Data Sources’ folder and add a ‘New Data Source’. This will show you a shared data source property window. Name the data source as ‘AdventureWorksDS’ and configure the connection to the AdventureWorks database by clicking the ‘Edit’ button as shown below –

Now let’s add a blank report using our solution explorer. To add a blank report, right click the Report and click on ‘Add New Item’ and choose ‘Report’. Name the report as ‘AdventureWorksYearlySalesChartReport’ as shown below –

Now go to ‘View’ menu and click on ‘Report Data’. In the ‘Report Data’ window, add a new dataset as shown below –

This will show you the ‘Dataset Properties’ window. Name the dataset as ‘YearlySalesDS’ and choose the existing data source by clicking a ‘New’ button from ‘Data Source’ option. This displays a ‘Data Source Properties’ window. Choose ‘Use Shared Data Source Reference’ radio button and choose our data source ‘AdventureWorksDS’ which we have created in the above steps. Then paste the query shown above in a ‘Query’ box as shown below –

Now we are ready to design the report. Drag and drop a ‘Chart’ control from tool box on our report. As soon as you drop the chart control on the report, it will ask you to ‘Select Chart Type’. Make a choice of Column chart and click ‘OK’ as shown below –

Now click on the Chart and you will see the view of the chart with different axis, as shown below –

Now from the ‘Report Data’ Window drag and drop ‘TotalPrice’ on ‘Drop data fields here’ and drag and drop OrderYear on ‘Drop category fields here’. Now your report will look like this –

Now click on the preview report tab and see your report. It will look similar to the following –

Now let’s modify the report. First of all change the title of the report to ‘Adventure Works Yearly Sales’. Change the ‘Y’ axis title to ‘Total Sales’ and change ‘X’ axis to ‘Order Year’. Now preview your report and it will look like the one shown below –

Now right click the ‘Total Price’ and click on ‘Add Calculated Series’ as shown below –

You will see the ‘Calculated Series Properties’ window. From here, choose ‘General’ section. Under formula, choose ‘Exponential Moving Average’ as shown below –

Now preview your report and it will look like the one shown below –

Now right click the Chart column and choose ‘3D Effects’. This will show you ‘Chart Area Properties’ window. Check the check box ‘Enable 3D’ and click ‘OK’ button by keeping the other properties default. Preview your chart and this is what you see –

Summary – In this article, we have seen how to design a Chart report using SQL Server Reporting Services 2008.

SQL Server: Designing Tablix Report in SSRS 2008

In this article we will see how to design a Tablix (Table + Matrix) report in SQL Server Reporting Services (SSRS) 2008. For this demonstration, we will use the AdventureWorks sample database provided by Microsoft.

In SQL Server Reporting Services 2008, Microsoft has introduced a new reporting model called Tablix. Tablix is a combination of ‘Table + Matrix’ report. To design this report, open Visual studio 2008 Business Intelligence Studio and create a new Report Server project as shown below –

Once your project is ready, let’s create a data source which will fetch the data from the AdventureWorks database. To fetch the data, write the following query –

To add a new data source to our report, go to Solution Explorer. Right click ‘Shared Data Sources’ folder and add a ‘New Data Source’. It will show you shared data source property window. Name the data source as ‘AdventureWorksDS’ and configure the connection to the AdventureWorks database by clicking the ‘Edit’ button as shown below –

Now let’s add a blank report using the solution explorer. To add a blank report, right click the ‘Report and click on add ‘New Item’ and make a choice of ‘Report’. Name the report as ‘AdventureWorksYearlySalesReport’ as shown below –

Now go to ‘View’ menu and click on ‘Report Data’. In the Report Data window, add a new dataset as shown below –

It will show you a Dataset Properties window. Name the dataset as ‘YearlySalesDS’ and choose the existing data source by clicking a ‘New’ button from ‘Data Source’ option. It will show you a Data Source Properties window. Make a choice of ‘Use Shared Data Source Reference’ radio button and choose our data source ‘AdventureWorksDS’ which we have created in the above steps. Then paste the query shown above in a ‘Query’ box -

Now we are ready to design the report. Drag and drop ‘Matrix’ control from tool box on our report as shown below –

Now if you check the properties window, you will see the name ‘Tablix1’. Align the report to the left side of the report designer.

Now from the ‘Report Data’ Window drag and drop ‘OrderYear’ on columns group section and drag and drop ‘CategoryName’ on Rows group section. Then drag and drop ‘TotalPrice’ on Data section. Now your report will look like this –

Now click on the preview report tab and see your report. It will look like below –

Now let’s modify the report little bit. Let’s first format the report. Make all columns bold. Now let’s add a ‘Total’ column before Columns group and add ‘Total’ column after Rows group.

To add a total column, right click to ‘[OrderYear]’ column header and click on ‘Add Total’ ‘Before’. Repeat the same step by right clicking the Category name column and ‘Add total’ ‘After’. Now preview your report and it should look like below –

Now let’s add a ‘SubCategory’ section in the Rows group. Drag and drop ‘SubCategoryName’ field below the ‘CategoryName’ field as shown below –

Now preview your report. It will look like this –

If you observe this report, it is similar to reporting services 2005. Let’s modify this report to display it in ‘Tablix’ format. Go to Subcategory column and ‘Add Total’ as shown below –

Now click on the total column and choose a field ‘Category Name’ and delete ‘Category Name’ column as shown below –

Preview your report and it will look similar to the following –

Summary – In this article we have seen how to design a basic ‘Tablix’ report using a ‘Tablix’ control in SQL Server Reporting Services (2008).

Download the source code

SQL Server: Designing Key Performance Indicator Report in SSRS 2008

In this article, we will see how to display ‘Key Performance Indicators’ based on the different conditions in SQL Server Reports. For this demonstration we will use the ‘Northwind’ database.

Embedding ‘Key Performance Indicators’ in a report is a common requirement which is requested by every CFO, CEO and stakeholders of the business, in each and every industry.

SQL Server provides different ways to embed the indicators in a Report. We will use one of them while designing our report. So, let’s start by creating a SQL Server Report using Visual Studio 2008. The steps to create a report using Visual studio 2008 are available in my previous article SQL Server Reporting Services Tutorial: Designing a Basic Report using SSRS 2008 and VS 2008 BI Studio

I am using the Northwind database tables to fetch the data. Shown below is a query which I will be using for designing the report –

The above query selects the employee city, order required date, total orders taken by an employee and the total sales grouped by City, Product name and order required date. This query fetches the data only for the ‘LONDON’ city. The result is as shown below –

Once you create a project using ‘Report Server Project Wizard’ template, it will display a wizard. Let’s configure the report using the wizard as below –

Name the data source as ‘NorthwindDataSource’ and configure the connection to Northwind database by clicking ‘Edit’ button as shown below –

Click on the ‘Next’ button. This step will ask you to write a query which will fetch data from the Northwind database. Copy the above query. Now let’s make a small change in our query. Instead of making ‘City’ value fixed, let us allow the user to enter the value.

So replace E.City=’LONDON’ with E.City=@EmployeeCity as shown below –

Now click the ‘Next’ button. In this step we will select the report type. So let’s select ‘Tabular’ report and click on the ‘Next’ button.

In this step, we will design the table. So, select ‘Employee_City’ from available field section and click on ‘Page >’ button. Now let’s select ‘Order_Year’ field and click on ‘Group >’ button. Finally add ‘Total_Orders’ and ‘Total_Sales’ in details section by clicking ‘Details >’ button. Your ‘Design Table Window’ should look like below –

Click on the ‘Next’ button. In this step, we will choose the layout of the table. Make a choice of ‘Stepped report’. Check the check box ‘Include Subtotal’ and ‘Enable Drilldown’ as shown below –

Now click on the ‘Next’ button. Choose the table style as per your requirement. Click on ‘Next’ button.
In this step we will provide the deployment location as shown below –

Now let’s click the ‘Next’ button. In this step, we will give a title to our report ‘City wise Yearly Sales’. Click the finish button. Now your report should look like below –

Now click on ‘Preview’ tab and view the report. Enter city name ‘LONDON’ and click on ‘View Report’ button. You will see the yearly total sales and total orders for all the products.

But now I don’t want user to enter the city everytime. So instead let’s give a choice to them to choose the city using a dropdown box. For this demonstration, you will have to read my previous article – Design Parameterized Report using SSRS 2008.

For getting all distinct employees cities, I am using the following query –

SELECT DISTINCT City FROM Employees

After adding a parameter in your report, it should look like below –

Now let’s add the indicators to our report. You can add indicators as ‘Colors’ or ‘Images’. If you want, you can even make use of gadget images for showing indicators. For this demonstration, we will use different colors to show the yearly total sales. The conditions are mentioned below –

1) If the yearly total sale is more than or equal to 1, 75,000 then it should highlight the cell with green color.
2) If the yearly total sale is less than 1, 75,000 and greater than 85,000 then it should highlight the cell with yellow color.
3) And less than 85,000 then it should highlight the cell with red color.

For this, we will have to write the condition as shown below –

=IIF(Sum(Fields!Total_Sales.Value) >= 175000, "Green", IIF(Sum(Fields!Total_Sales.Value) < 85000, "Red", "Yellow"))

To add this expression, go back to the design mode of our report. Then right click on ‘Total sales’ textbox and go to ‘Textbox Properties’ as shown below –

From the ‘Textbox Properties’ window select ‘Fill’ section from the left side and click on fill color expression button as shown below –

Now copy the above expression in the set expression box and click ‘OK’ button as shown below –

Click ‘OK’ button to close the ‘Textbox Properties’ window. Go to ‘Preview’ tab and choose the city. Now click ‘View Report’ button and your report should look similar to the following –

Summary – In this article we have seen how to add Key Performance Indicators to our report based on our business conditions. Download the source code