Now that you have created a custom data extension, you are probably interested to learn how you can use it as well. Let’s create a report that is based on a custom Bugs SharePoint list. This list contains the bugs our testers have discovered and assigned to developers. Figure 3-14 shows our sample Bugs SharePoint list.
Figure 3-14. Bugs SharePoint list
We are going to use our new SharePoint list data extension to create a report via Reporting Services.
Open SQL Server Business Intelligence Development Studio by clicking Start ➤ All Programs ➤ Microsoft SQL Server 2005 and choose SQL Server Business Intelligence Develop- ment Studio.
Click File ➤ New Project and choose Business Intelligence Projects in the Project types list. In the Templates list choose Report Server Project Wizard, give your project a descriptive name, and click OK. We will call our report project BugsReportProject. Figure 3-15 shows the New Project dialog window.
Once you have clicked OK, a report project will be created and the Report Wizard will be started. On the first page of the Report Wizard, you have to select the data source you are going to use. In the Type drop-down list, choose our custom SharePoint data extension and specify the connection string. Figure 3-16 shows the Select the Data Source page from the Report Wizard.
128 C H A P T E R 3 ■ S Q L S E R V E R 2 0 0 5 R E P O R T I N G S E R V I C E S
Figure 3-15. New Project dialog window
Figure 3-16. Select the Data Source page in the Report Wizard
If you want to use a specific username and password, you have to click the Credentials button. This opens a Data Source Credentials dialog box that lets you specify whether you want to use Windows Authentication, no credentials, or a specific username and password, as
C H A P T E R 3 ■ S Q L S E R V E R 2 0 0 5 R E P O R T I N G S E R V I C E S 129
shown in Figure 3-17. Choose to specify a username and password, click OK, and click Next in the Report Wizard.
Figure 3-17. Data Source Credentials page in the Report Wizard
The next page of the Report Wizard is the Design the Query page. This page gives you the opportunity to use the query designer to build a query. You will not need a query designer for our extension, since all you have to do is add the name of the SharePoint list you want to use for the report. In our example, this will be the Bugs list. Figure 3-18 shows the Design the Query page of the Report Wizard. When you are done, click Next to go to the next page.
Figure 3-18. Design the Query page
130 C H A P T E R 3 ■ S Q L S E R V E R 2 0 0 5 R E P O R T I N G S E R V I C E S
The next page is the Select the Report Type page. This page will let you choose between a tabular report and a matrix report, as shown in Figure 3-19. We will choose the tabular report type and click Next.
Figure 3-19. Select the Report Type page
The next page is the Design the Table page. This page shows all available fields from the Bugs SharePoint list. Here you can choose which fields will be used for the report and how you want to use them. We want a report that groups information by the fields Developer and Priority and shows the details Title, Code, and Tester, as shown in Figure 3-20.
At this point we have already defined the greatest part of our report. There are only two things left to do: the table layout and the table style. In the next steps of the Report Wizard, you will get the chance to choose a table layout and style. In addition, you need to specify a report server URL and the name you would like to give to your freshly made report. These steps are all very simple; therefore, we will not show them. When you are done and have created a report, you can be proud of the result, which will resemble Figure 3-21.
C H A P T E R 3 ■ S Q L S E R V E R 2 0 0 5 R E P O R T I N G S E R V I C E S 131
Figure 3-20. Design the Table page
Figure 3-21. Bugs report made with the SharePoint list data extension
132 C H A P T E R 3 ■ S Q L S E R V E R 2 0 0 5 R E P O R T I N G S E R V I C E S