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 –

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 will get the following result –



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

Monitor Running Processes in SQL Server 2005/2008

You often need to monitor the processes running on the server in order to improve the performance, by tuning them.  A common option is to make use of a profiler. But in SQL Server versions starting 2005, we can also use dynamic management views.

select
    source_code,stats.total_elapsed_time/1000000 as seconds,last_execution_time
from sys.dm_exec_query_stats as stats
    cross apply(SELECT text as source_code FROM sys.dm_exec_sql_text(sql_handle))AS query_text
order by total_elapsed_time desc

The above code will list out the queries based on the descending order of the time they take

The sys.dm_exec_query_stats is a Dynamic Management view that gives the statistical
information's about cached data. The sys.dm_exec_sql_text is the another view that
gives actual text of the sql_handle which is in binary format. It converts the binary
to actual characters

You can also make use of an article from the Microsoft available at
http://msdn.microsoft.com/en-us/library/ms191511.aspx

Further Reading:

Dynamic Management Views (DMV) for SQL Server Performance and Tuning
Important DMVs to monitor CPU – SQL Server

SQL Server: Increment an AlphaNumeric Number‏

Your columns may have a categorization scheme in the form of an alphanumeric number. and it may be needed to have a customized incremental value for this scheme. Consider that you want to have numbers in the series like ABC1, ABC2, ABC3  etc. In SQL Server, there can be many methods to do this.

In this post, I will show two methods to increment an Alphanumeric Number in SQL Server

Method 1 : Derive it in a SELECT statement



declare @t table(id int identity(1,1), names varchar(100))
insert into @t(names)
select 'test1' union all
select 'test2' union all
select 'test3' union all
select 'test4' union all
select 'test5'
select 'ABC'+CAST(id as varchar(10)) as id,names from @t

The above method concatenates the string with the identity column. This produces the following results:

Method 2 : Use Derived column in the table



declare @t table(id int identity(1,1),
    alpha_id as 'ABC'+CAST(id as varchar(10)), names varchar(100))
insert into @t(names)
select 'test1' union all
select 'test2' union all
select 'test3' union all
select 'test4' union all
select 'test5'
select * from @t

Using this method, the T-SQL code concatenates the string ABC with identity column as soon as data is added to the table.

OUTPUT

SQL Server: Monitor Long SQL Agent Jobs

In SQL Server, there are various jobs running on the server, each solving a different purpose. Sometimes these jobs run for a long time. The best way to identify and monitor these long running jobs is to make use of a profiler.

Let us assume, you have the following code:

While 1=1
print 1

When you schedule this as a job, the code never finishes it's execution, as the code does not have any condition to break the loop. So it would be a never ending job that may consume full memory to execute the endless loop.

Note: This is just an example to emulate a long running job.

When you run a profiler, you can identify jobs which have ‘Batch Starting’ class with no corresponding ‘Batch Ending’ Class. Also the columns CPU, Reads, Writes etc. will be empty. As you can see in the profiler report below, the row with textdata while 1=1 print 1 is a long running job. See the highlighted row below:

Also if the values of columns CPU, Reads, Writes, etc. are huge in numbers, there is a chance that a job is running for long time. The following is an example of a long running, but which finishes its execution after some time.



As you see in the profiler result, the values for the columns CPU, Reads, Writes etc. are high which means these are also long running jobs



Important Notes:

1. Do not run the above examples in a production server, as they will consume a lot of memory
2. After trying the above examples as a job, make sure to delete those jobs.

Transfer Logins from SQL Server 2005 to SQL Server 2008

When you want to move a database from one server to another, you can take a backup of the database and restore into the other server. However this will not transfers the logins from one SQL server to another, say SQL Server 2005 to SQL Server 2008. Instead users may receive the following message on SQL Server 2008, when they try to log in:

Login failed for user '<user name>'. (Microsoft SQL Server, Error: 18456)

To transfer the logins, the most reliable method in my opinion is to make use of the sp_help_revlogin stored procedure shown in this article http://support.microsoft.com/kb/918992

Assuming you have copied the script from the above link, just run the following code in SQL Server 2005

EXEC sp_help_revlogin

This command will generate the create logon script based on the logins available in the SQL Server 2005. Here’s a sample:

All you need to do now is go to the SQL Server 2008 instance, start SQL Server Management Studio, connect to the instance where you moved the database and execute the login script you generated in the previous step.

Note: If the logins should be transferred to a server with a different domain, you may need to specify the domain name in the script generated.

SQL Server: First and Last Day of Year, Quarter, Month and Week

With so many questions floating around calculating the first and last day of a given duration in SQL Server, here’s a post that explains the technique of calculating the first and last day of a given duration. We will cover the following:

- Calculate First and Last Day of the Year
- Calculate First and Last Day of the Quarter
- Calculate First and Last Day of a Month

First and Last Day of Year

To get the first day of the year, we are using the DATEDIFF function to determine the number of years from ‘1/1/1900’ to the current date (GETDATE). We then add this calculated number result (in this case 111) to ‘1/1/1900’, to obtain the first day of the year.

Similarly, a simple trick is adopted to get the last day of the year. We get the first date of the ‘next year’ and simply subtract one day from it. So the expression SELECT DATEADD(yy, DATEDIFF(yy,'',GETDATE()) + 1, 0) gets you the first day of the ‘next’ year. Subtracting 1 from it gives you the last day of the current year.

The same approach is adopted to find the last day of the quarter and the month.

First and Last Day of the Quarter



First and Last Day of a Month



Here are all the three queries for you to try out:

-- First and Last Day of Year
SELECT DATEADD(yy, DATEDIFF(yy, 0, GETDATE()),0)
as 'First Day of Year'
SELECT DATEADD(dd, -1, DATEADD(yy, DATEDIFF(yy,0,GETDATE()) + 1, 0)) as 'Last Day of the Year'

-- First and Last Day of Quarter
SELECT DATEADD(qq, DATEDIFF(qq, 0, GETDATE()), 0)
as 'First Day of Quarter'
SELECT DATEADD(dd, -1, DATEADD(qq, DATEDIFF(qq, 0, GETDATE()) + 1, 0))
as 'Last Day of the Quarter'

-- First and Last Day on Month
SELECT DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0)
as 'First Day of Month'
SELECT DATEADD(dd, -1, DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) + 1, 0))
as 'Last Day of Month'

OUTPUT

SQL Server: Disable Table Constraints (all or some)

Constraints let you define a way to automatically enforce the integrity of a database. A table constraint is declared independently from a column and can be applied to more than one column in a table. Sometimes you may need to disable one or all table constraints, in order to import data, truncate tables etc.

You can use the following methods:

Consider these tables:

Suppose you want to import employee details to the table emp_details without having any corresponding data in emp_master:

INSERT INTO emp_details(emp_id) SELECT 34

This statement will throw an error as the value 34 in not available in the table emp_master

Msg 547, Level 16, State 0, Line 10
The INSERT statement conflicted with the FOREIGN KEY constraint "FK__emp_detai__emp_i__1ED998B2". The conflict occurred in database "master", table "dbo.emp_master", column 'emp_id'. The statement has been terminated.

A Table constraint is in effect! Here to know the constraint name, run this code:

EXEC sp_help emp_details

and see the sixth resultset which shows the constraint name.



Using this name, you can now disable the constraint

ALTER TABLE emp_details
NOCHECK CONSTRAINT FK__emp_detai__emp_i__1ED998B2

Now if you re-run the code,

INSERT INTO emp_details(emp_id) SELECT 34

you will see that it gets executed successfully

To enable the constraint again, use the following code:

ALTER TABLE emp_details
CHECK CONSTRAINT FK__emp_detai__emp_i__1ED998B2

Disable All Constraints in a Table

To disable all constraints at a time, use the following code:

EXEC sp_msforeachtable 'alter table ? nocheck constraint all'

To enable all constraints at a time, use the following code

EXEC sp_msforeachtable 'alter table ? check constraint all'

Note that sp_msforeachtable is undocumented and alternatively you can use a cursor or a while loop to get the constraints and disable or enable them.

Some Points to be considered:

1. If you disable a constraint for some reasons, make sure to enable them later
2. Primary key constraint can not be disabled
3. Foreign key and Check constraint will be disabled using the nocheck command

SQL Server Denali Resources

Microsoft SQL Server Code-Named “Denali” is the next release of Microsoft SQL Server. With the CTP1 release, there has been a lot of buzz in the communities about it. If you are new to SQL Server Denali, here are some resources to get you started:

Download SQL Server Denali CTP1 Evaluation (iso)

Download SQL Server Denali (x86)

Download SQL Server Denali (x64)

What’s New in SQL Server Denali

SQL Server Denali Books Online/Documentation

Sample Database AdventureWorks for SQL Server Denali CTP1

SQL Server Denali System Requirements

I will update this post as and when I find more resources.