Report Builder is a new tool built into SQL Server Reporting services 2005 that allows easy report building using Report Manager. Before you can use Report Builder to build reports, you must first create a report model for Report Builder to use. Report models can be created using Microsoft Office SharePoint Server 2007, Report Manager or SQL Server Management Studio. I will explain how to create a report model using Report Manager.
The first step is creating a data source to use for your report model. The second step is generating the report model. Once that is done you can use the report model to create reports.
Creating a data source:
1. In your Web browser, type the URL for your report server in the address bar.
2. Click “New Data Source.”
3. In the “Name” box, enter a name for the data source.
4. Optionally, enter a brief description of the mode in the “Description” text box.
5. Verify that the “Enable this data source” check box is selected.
6. In the Connection type list, select the data source type to which you want to connect. The connection type must be one of the following: Oracle, Microsoft SQL Server or Microsoft SQL Server Analysis Services.
7. In the Connection string box, enter the connection string that points to the database.
8. Select the connection method that Report Builder users will need to use to connect to the database.
9. Windows Authentication: Select this option when you want the operating system to authenticate SQL Server users. This option allows SQL Server to use Windows security features, such as password encryption, to authenticate users. It is strongly recommended that you select this option.
10. SQL Server Authentication: Select this option when you want users to use a SQL Server login account that you created. Users must supply a valid SQL Server login name and password.
11. Click “OK.”
Creating a report model from the data source:
1. In Report Manager, select the data source that you want to use for your model.
2. The “Properties” page is displayed.
3. Verify that you want to use the options specified for the data source.
4. Click “Generate Model.”
5. In the “Name” box, enter a name for the report model.
6. In the “Description” box, enter a brief description of the model.
7. To specify a new location to save the report model to, click “Change Location.”
8. By default, the report model is saved to Report Manager Home.
9. Click “OK.”
At this point you are ready to create a report using Report Builder. Click the “Report Builder” button on the toolbar within Report Manager. This will launch Report Builder and display report models that can be used to build the report. Once the report is completed, save it and then review it in Report Manager.