SSRS - Create Matrix Reports Using Report Wizard In SQL Server 2008

In this article I will demonstrate how to create a matrix report then build and execute the report using Business Intelligence Studio.  SQL Server Reporting Service is used to design, develop, deploy and manage reports.

Lets see step by step process to create a report in SSRS using Report Wizard Template of the Business Intelligence Development Studio (BIDS).

Steps

First we need to create new SSRS Project. To open it, Go to Programs- > Microsoft SQL Server 2008 R2 according to installed version and then select Business Intelligence Development Studio:

Create a new project shown in screen below. You can use shortcut Ctrl + Shift + N keys to open a new project.

To create a report select Report Server Project Wizard and provide a name to the project as shown below. Click on OK.

Now a wizard will open, just click Next. New window requesting for data source input details will open. We can either configure a shared data source or a new data source, according to our requirement. Suppose I want to create a New data source:

Click on Edit Button, Connection Properties window will open. Enter basic configuration and Test connection. Once Test connection is successful Click OK.

New window showing basis configuration will open as shown in screen below. Click Next.

Next window will show an option to build query based on which result set will displayed. Click on Query Builder...

Query Designer window will open. Write a query to pull up the records as shown below. Once you write the query, execute the query by clicking on Execute Button. Once done Click OK to close the window.

Select the Report Type i.e. in which format you want to display the data (Tabular and Matrix). Select Matrix and click Next button to proceed.

Once you click on Next Button, new window is opened to choose the option for grouping of data.

After clicking Next Button, choose the table layout Stepped or Block and Click Next Button. Then choose the table Style as shown in screen below and click Finish Button.

Clicking on Next Button, new window is opened. Here you can provide customized report name. And Click Finish. Clicking on Finish will open a window as shown in the screen below, which displays the structure of report as shown below.

Build the solution and execute the process by pressing F5. Generated report is displayed.