Using Report Builder to build reports

• Download source code - 7.38 KB

Introduction

Report Builder is a strong tool provided in SQL Server 2005 Reporting Services. You can just go ahead and make reports of your own choice, with your desired columns and calculations. Once built, you can go ahead and save it on a Report Server to access anytime in the future, too. How can we setup a Report Builder environment on a 'Server' such that we can access the Report Builder on our local machines. This article will show how you can setup the environment for Report Builder and then build reports using the tool provided.

Background

Some time back, I was involved in getting reports built from the Report Builder provided in SQL Server 2005 Reporting Services, along with making reports and deploying them on a Report Server. I had a hard time finding how exactly to make reports from the Report Builder. It was new, and there was hardly any information on the Internet either. In fact, I have not found any article on how to build reports using the Report Builder even today. So, I thought of writing one, to save some time for the first time users of the Report Builder.

Using the code

The first thing required is to create a Data Source, a Data Source View, and a Report Model.

Create a New Project, and select Report Model Project:

Create a new Data Source, a new Data Source View, and a new Report Model. For creating a new Data Source, provide the connection details and select the database on which the reports need to work on. Select this newly created Data Source while creating the new Data Source View, and then select the created Data Source View for creating the Report Model.

We can select Tables and Views out here that will be displayed in the Report Builder as available options to drag-drop fields to view in the report.

Click Finish to create the Report Model. After this, build it and deploy it.

Once this is successfully done, the whole setup to work with the Report Builder is done. Now, log into http://localhost/Reports (the 'localhost' here will be the server machine name, such that we can access the Report Builder from our machines.)

We can check if the newly deployed Report Model can be found in the Models folder above and if the Data Source is defined in the Data Sources folder. Now, click on the Report Builder. When we click the Report Builder, it will be downloaded on the local system to work with:

The user selects the Data Source and clicks OK. The UI appears like:

Now, we can drag-drop the fields on the right side. All the relations are maintained, and they can be traversed in the left side tree above. Below, in the left side, are the fields available to be dragged-dropped.

Once done, we can run the report using "Run report" at the top, in the toolbar.

How simple, isn't it? Now, what if we need to filter out some results based on certain conditions? The Report Builder has that facility too. We can put a filter on it using the "filter" at the top, in the toolbar.

Once we set a filter and re-run the report, we have:

What about saving the built report now? We can just click the Save button at the top, and a prompt appears with the Report Server folder structure.

We can go ahead and save the report in our personal folder specified on the Report Server. And now, it can be accessed any number of times in the future.

You can edit and save it the way you prefer. Filters, sorting, design, everything is in the user's hands. A report can also be viewed through the Report Server.

Points of interest

As I told you earlier, it was a good experience learning how to implement the Report Builder feature for my project. This gave me a lot of flexibility and options for quite a few reports that were needed. Adding to it, the user's choice and preferences are maintained using it too.