Dynamic Connection String in SQL Server Reporting Services 2005/2008

In this post we will see how to generate a report using dynamic connection string in SQL Server Reporting Services 2005/2008. The scenario is if we have different data with the same schemas, spread across different database and different SQL servers and people would like to see the data as per their preferences (like area wise), we can use a Dynamic Connection string.

So let’s create a report using SQL Server Business Intelligence Studio 2008 with the name ‘DynamicConnectionReports’ as shown below –

Now follow the wizard to create a simple Customer Records report from the database ‘Northwind’ with the query – ‘Select * from Customer’. I have already demonstrated creating report using wizard in my previous article ‘Designing Basic Report using SSRS 2008’. If you have not read it yet, I will highly recommend you to go through the steps.

Once your report is ready, let’s preview it and the output should look like below –

Now once your report is ready, go to the ‘Report Data’ window and add a parameter with the name ‘Databases’ as shown below –

Click on ‘OK’ button. Let’s create a Dataset which will fetch all ‘Databases’ from our SQL Server instance. To add a new dataset, right click on the data source (I have named it ‘CommonDS’) and click on ‘Add Dataset’. Now let’s configure the Dataset as shown below –

Make sure you will not use the existing Data Source. Create a new connection. Now let’s map the Parameter

‘Databases’ to take the data from ‘AddDatabases’ dataset. Right click on the ‘Databases’ parameter and configure the parameter as shown below –

Click on the ‘OK’ button. Now right click ‘CommonDS’ data source of the report and click on ‘Data Source Properties’ option. It will display the properties window. Remove the existing connection string and click on the ‘Expression’ button –

Now let’s define the connection string as shown below –

="Data Source=Localhost;Database=" & Parameters!Databases.Value

Click on the ‘OK’ button and now ‘Preview’ the report. You will see all the databases in a dropdown Listbox. Choose ‘Northwind’ database and click on the ‘View Report’ button. The report will look like the one shown below –

Now you can use this report from Web Form or Windows Form by passing the parameter value from the application. You can hide the parameter from the report by setting ‘Parameter visibility property’ ‘hidden’. Now the most important part is this dynamic connection string demo will not work with ‘Shared Data Source’.

Also make a note that in this demonstration, we are using one instance of SQL Server and fetching all the databases. You can even make your SQL Server parameter dynamic.

The reports will work fine as long as you are using the same schema for generating the report.

T-SQL, SQL Administration and SQL Server BI Articles Link List – May 2011

Here’s a quick wrap up of the articles published on SQLServerCurry.com in the month of May 2011

SQL Server Administration Articles

FileStream in SQL Server 2008 - In this article, we will see how to work with FileStream in SQL Server 2008 for storing unstructured data like documents and images on file system

SQL Server Monitoring Management Pack - Microsoft has released the SQL Server Management Pack for for Operations Manager 2007 SP1 and R2. This pack is used to discover SQL Server 2005, 2008, and 2008 R2 and monitors SQL Server components such as database engine instances, databases, SQL Server agents, jobs and other roles such as Reporting Services, Analysis Services, Integrations Services

Compressed Tables and Indexes in SQL Server 2008 - In this article, we will see how to use the new feature of compressing Tables and Indexes in SQL Server 2008 with some examples

SQL Server Migration Assistant v5.0 - Microsoft recently announced the release of SQL Server Migration Assistant (SSMA) v5.0, a tool to further simplify the user experience in automating the migration of Oracle, Sybase, MySQL and Microsoft Access databases to SQL server or SQL Azure

Troubleshoot Deadlocks using SQL Server Profiler 2005/2008 - In this article, we will see how to capture deadlocks while modifying data using SQL Server Profiler in SQL Server 2005/2008

SQL Server: Calculate tempdb Space using DMV - I am a huge fan of Dynamic Management Views (DMV) as they provide me the ability to look into the internals of SQL Server. I have been writing regularly about the same. Here are some of the articles

DDL Triggers in SQL Server 2005 and 2008 - In this post, we will see how to implement DDL triggers in SQL Server 2005 and 2008. 

T-SQL Articles

FileStream in SQL Server 2008 - In this article, we will see how to work with FileStream in SQL Server 2008 for storing unstructured data like documents and images on file system

SQL Server: XML FLOWR Expression in SQL Server 2005 and 2008 - In this article, we will see

how to use FLWOR Expression designed for querying XML data in SQL Server 2005 and 2008.

SQL Queries – beyond TRUE and FALSE - Most of the SQL novices are more accustomed to thinking in terms of two-valued logic (TRUE, FALSE) in SQL. But SQL uses three-valued logic – TRUE, FALSE and UNKNOWN. It means that the value of an expression may be TRUE, FALSE or UNKNOWN. Confused?

SQL CLR Stored Procedure using Visual Studio 2010 - In this post, we will see how to use SQL CLR functionality for defining Stored Procedure using C# in VS 2010. We will also see how to enable CLR under SQL Server

Troubleshoot Deadlocks using SQL Server Profiler 2005/2008 - In this article, we will see how to capture deadlocks while modifying data using SQL Server Profiler in SQL Server 2005/2008

SQL Server–Error Handling using Try Catch Block - In this post, we will see how to handle errors in SQL Server 2005 and 2008. In SQL Server 2005, Microsoft has introduced a new construct to handle errors in SQL Server that is ‘TRY – CATCH’. It is similar to .NET ‘Try – Catch’ block for handling the exceptions

SQL Server CLR User Defined Function using Visual Studio 2010 - In this post, we will see how to create a User Defined Function using Visual Studio 2010

SQL BI Articles

SSRS 2008: Cascading Parameterized Report - In this article, we will see how to create a SSRS 2008 report with cascading parameters. We will create cascading parameters and add those to our report. For this demonstration, we will use SQL Server 2008 database ‘AdventureWorks’ for designing the report as well as parameters.

SSRS 2008: Cascading Parameterized Report

In this article, we will see how to create a SSRS 2008 report with cascading parameters. We will create cascading parameters and add those to our report. For this demonstration, we will use SQL Server 2008 database ‘AdventureWorks’ for designing the report as well as parameters.

Note: In this article, I will be using the same demo which I have demonstrated in my previous article Designing Tablix Report in SSRS 2008.

Open SQL Server Reporting Services project ‘AdventureWorksTablixReport’. And go to ‘Preview’ tab to view the report –

Now go to ‘Report Data’ window and right click the ‘YearlySalesDS’ dataset and click on ‘Dataset Properties’. This will show you a Dataset Properties window as shown below –

Now replace the existing query with the below query –



Now if you observe the above query, we have added two parameters as shown below –
1) CategoryName
2) SubCategoryName

Preview the report and add ‘Bikes’ in Category Name Textbox and ‘Road Bikes’ in Sub Category Name and click on ‘View Report’ button. Your report will be similar to the following –


But that’s not what we are looking for. We want all the available Categories listed in a dropdown box and when we choose any of the category, the related sub categories should get loaded into the sub category dropdown box.

So let’s add a new dataset which will select all the distinct categories from the ProductCategory table. To add a new dataset, go to ‘Report Data’ window. Right click the data source and click ‘Add Dataset..’ as shown below –



This will bring up the ‘Dataset Properties’ window. Name the dataset as ‘CategoriesDS’. Click on the ‘New’ button in front of the ‘Data Source’ dropdown list and choose ‘Use Shared Data Source Reference’. From the dropdown list, choose ‘AdventureWorksDS’ data source and click ‘OK’ button.

Now paste the following query in the dataset properties box –

SELECT DISTINCT Name AS Category FROM Production.ProductCategory

Now your dataset properties window will look like the one shown below –



Click the ‘OK’ button. Now repeat the above steps (which we implemented for creating a dataset ‘CategoriesDS’) for creating ‘SubCategoriesDS’ with the following query –

SELECT DISTINCT PSC.Name AS Subcategory FROM Production.ProductSubcategory AS PSC INNER JOIN Production.ProductCategory AS PC ON PC.ProductCategoryID = PSC.ProductCategoryID WHERE PC.Name = @CategoryName

Now your Report Data window is having three datasets as shown below –



In the ‘Report Data’ window, expand the ‘Parameters’ section. You will see two parameters. Right click the ‘CategoryName’ parameter and go to ‘Parameter Properties’. From the left section make a choice of ‘Available Values’. Then in the right hand section, choose ‘Get Values from a Query’ option. This will show you the dataset dropdown box. Choose the ‘CategoriesDS’ dataset. Now choose ‘Category’ in the value and label field as shown below and click the ‘OK’ button –



Repeat the same steps for getting the value from a query for a SubCagetoryName parameter. Now preview your report –



If you observe, when you select ‘Categories’, the subcategories get selected/changed accordingly. This is how you implement Cascading effects on the Parameters in SQL Server Reporting Services.

Download the source code

T-SQL, SQL Administration and SSRS Articles Link List – April 2011

Here’s a quick wrap up of the articles published on SQLServerCurry.com in the month of April 2011

SQL Server Administration Articles

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.

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.

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.

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.

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

T-SQL Articles

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

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.

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.

SQL Server Management Objects 2008 (SMO) New Features - In this article we will practically explore some features of SQL Server Management Objects

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

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.

SQL Server Reporting Services (SSRS) Articles

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

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.

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.

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