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. 
  • 3549

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:

CreateReport1.jpg

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

CreateReport2.jpg

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

CreateReport3.jpg

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:

CreateReport4.jpg

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

CreateReport5.jpg

CreateReport6.jpg

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

CreateReport7.jpg

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

CreateReport8.jpg

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.

CreateReport9.jpg

CreateReport10.jpg

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.

CreateReport11.jpg

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

CreateReport12.jpg

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.

CreateReport13.jpg

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.

CreateReport14.jpg

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

© 2020 DotNetHeaven. All rights reserved.