Getting Started with SSRS - Part 3 - Designing a report using Report Designer

Getting Started with SSRS - Part 3 - Designing a report using Report Designer

Introduction
This article would cover how to design a report using the Report Designer. In our previous article, we saw the option on designing a report using the Report Wizard with just the configurations used to design and develop the report with some simple steps. However, in this article we will design every component on our own with the help of the Report Designer available with the SQL Server Business Intelligence Studio templates. Let us see the step-by-step process on how to design the report with the Report Designer.
Steps
The first step towards our task is to open Business Intelligence Studio. To open it, we would need to navigate through Programs >> Microsoft SQL Server Denali / Microsoft SQL Server 2008 as per the installed version and select Business Intelligence Development Studio as shown in the screen below.

This will open Visual Studio. It is the Business Intelligence Development Studio for SQL Server as shown in the screen below.

Now select File >> New >> Project to create a new project. We can directly use the shortcut Control + Shift + N to open a new project as shown in the screen below.

Once we have selected the New Project, a list of templates will be available as shown in the screen below.

Select Report Server Project template from the list of templates available and provide a valid name for the project as shown in the screen below.

Clicking on OK will create a project and open the project in the SQL Server Business Intelligence Studio as shown in the screen below.

Initially, there are no files created and only two folders are available. The next step is manually adding a report to the project. To add the report, click on the Reports folder in the solution explorer and select ADD >> New Item as shown in the screen below. Selecting Add New Report will go directly to the Report Wizard that we have already covered in the previous article.

Now, a window will be displayed with a list of available items that can be added to the project as shown in the screen below. Select Report from the list, provide a valid name and click on Add button to add a report to the project.

This will add the report to the project and open the Report Designer to allow designing the report as per the requirement as shown in the screen below.

Next step is to go to the Tool Box. Then drag and drop a table to design the report as shown in the screen below. Now we need to configure the data set first in order to design our report table.

To do the configuration we would first need to create a data source. This can be achieved by clicking on the New button. It will open a window where we would need to supply the details of the connection string as shown in the screen below. Configure it based on the database from which the data for the report needs to be collected.

Clicking on OK will go back to the old window with the data source available for pulling the data for the report. Now click on the Query Designer button to build the query required to pull the data as shown in the screen below.

Clicking on Query Designer will open a new window as shown in screen below. Write the query required to fetch the data. In this example, we are going to use the Authors table from the Pubs database. Once we write our query, click on the Execute button to get the complete details of the data for our reference to finalize the query. We can perform as many executes as required to get a final data as per the business requirement as shown in the screen below. Once we are done, click on OK to close this window and navigate back to the wizard.

Clicking on OK button will go back to the previous window and we can see the selected query available in the list as shown in the screen below.

Click on the OK button to complete the process. With this, we can see the table created in the designer view as shown in the screen below. We can customize the table as per our requirement.

Now we need to customize the report by dragging the outline of the table to the required length and width as shown in the screen below.

Next, we need to go to the data source we have created and select which columns should appear in which part of the report. To do this, first select the cell and click at the right side corner of the cell to get the list of columns as shown in the screen below:

Select the columns based on the requirement and edit the headers to the required customized format as shown in the screen below.

Click on the Preview tab to view the report’s preview as shown in the screen below.

Now we can see the report output in the preview mode. We can build and execute the project if we are satisfied with the report output. Else, we can go ahead and change the report design based on the business requirement. Since we have not used any inbuilt wizard, we need to manually do all the customization (Selecting Styles formats etc.). Now we have made some customization as shown in the screen below.

Press F5 or hit the Play button from the tool bar to build and execute the project to get the report output. However, we will get an error message that the start-up report project is not specified as shown in the screen below.

Go to the properties of the project and select the report as shown in the screen below. Since we have not used any wizard to build the report generation, these steps needs to be performed manually.

Once again, press F5 or Play button from the tool bar to build and execute the project. This will present the result as shown in the screen below.

Summary
Thus, in this article we have seen how to design and generate report from scratch without using the Report Wizard with the help of SQL Server Business Intelligence Studio.

 original Post by

Nov 12 2011 12:00AM by Karthikeyan Anbarasan   

 

Getting Started with SSRS - Part 2 - Designing a report using Report Wizard

Getting Started with SSRS - Part 2 - Designing a report using Report Wizard

 

Introduction
This article frames a guideline on how to make the use of the Report Wizard and create a simple report using the Business Intelligence Studio. A Report Wizard is the simplest form for creating a readymade report by simply selecting the data that should be displayed and by selecting the inbuilt templates available as per the requirement. Let us now see how to create the reports using the Report Wizard Template of the Business Intelligence Studio.
Steps
The first step towards our task is to open Business Intelligence Studio. To open it, navigate to Programs >> Microsoft SQL Server Denali / Microsoft SQL Server 2008 as per the installed version and then select Business Intelligence Development Studio as shown in the screen below.

This will open Visual Studio. Visual Studio is a Business Intelligence Development Studio for SQL Server and looks as shown in the screen below.

Next, select File >> New >> Project to create a new project. We can even use the shortcut Control + Shift + N directly as shown in the screen below.

Once a new project is selected, a list of templates will be available as shown in the screen below.

As we are in Business Intelligence Studio, the list of template will not have the normal templates (C#, VB.Net, Office etc).
Now the next step is to create a report using the Report Wizard. To do this, select the Report Server Project Wizard as shown in the screen below.

Once we select the Report Wizard and provide a valid name to the project as shown in the screen above, click on OK to create the project. Once the project is created, we can see a screen as shown below.

This is the start-up screen. To stave off the start up screen in the future, we can select the option “Don’t show this page again”. Once we are done with the above changes, click on the Next button to proceed further.
Now we can see a new window requesting for the data source input details. In this input request, we can either configure a shared data source or create a new data source as per the requirement. In our example, we will create a new data source as shown in the screen below.

Next, click on the Edit button and edit the connection string to connect to the database from which the data has to be retrieved to present in the reports. On clicking Edit button a window will open as shown in the screen below.

Once we have provided the above details (It may change as per your system database), click on Test Connection to test if the connection is successful. Once the connection is successful, click on OK and we can see the window as shown in the screen below.

Clicking on Next button will move to the next step in the wizard, which is the Query Builder. With this Query builder, we can build our query based on the requirement of the report. We can use this screen in the same manner as we write a query in a Query Analyzer as shown in the screen below.

To make use of the query builder click on the Query Builder button. It will open a new window as shown in the screen below.

Now, a query has to be written to pull up the record as shown in the screen below. Once the query is written, click on the Execute button. This will give complete details of the data for our reference to finalize the query. We can do as many executes as required to get a final data as per the business requirement. This is shown in the screen below. Once done, click on OK to close this window and navigate back to the wizard.

This will load the query that we selected back in the wizard as shown in the screen below. Now click on Next to proceed further.

Now we can see an option to select the format in which the data can be displayed. The Wizard has two options (Tabular and Matrix) that can be used based on the requirement.

Once we select the required format and click on the Next button, we will get a window to select fields that has to be displayed. Here we have more control over the data display, such as; we can select the data that should be displayed at the header or to group the data in a particular format as shown in the screen below.

Once selected, click on the Next button to move to the next screen. The next screen allows selecting the layout of the report as shown in the screen below.

Selecting Stepped option and clicking on the Next button will open a new window to select the style. This will allow us to display the report with a good look and feel. The report wizard displays a list of styles available as shown in the screen below. Select the required style and click on Finish button to complete the wizard.

A summary of the options selected will be presented with the data source as shown in the screen below. Click on Finish button to complete the process.

Clicking on Finish button will open the report in the designer view of the Business Intelligence Development Studio as shown in the screen below.

Now Press F5 or hit the Play button from the tool bar to start building and executing the report. Once the process is started and we can see the report generation in progress as shown in the screen above.

Once the report generation is completed, we can see report in a nice format as shown in the screen below, which is also our expected output.

Summary
Therefore, in this article we have seen how to create a report in SQL Server Reporting Services using Business Intelligence Development Studio Report Wizard template.

Original post by ===>

Nov 8 2011 12:00AM by Karthikeyan Anbarasan