SQL SERVER 2005 REPORTING SERVICES in C#

Encoder Data Matrix ECC200 in C# SQL SERVER 2005 REPORTING SERVICES

12. Type the following query into the SQL pane that opened: SELECT C.CustomerFirstName, C.CustomerLastName, T.TitleDescription FROM CustomerDetails.Customers C JOIN CustomerDetails.Title T ON C.CustomerTitleId = T.CustomerTitleId 13. Click the Run (!) button on the Query Designer toolbar to see the results of the query.

14. To create a second dataset, expand the Dataset drop-down list, and select New Dataset. 15. In the Dataset dialog box, type TitleList in the Name textbox, leave ApressFinancial as the data source, make sure that in the Command type drop-down list Text is selected, and in the Query string textbox type the following:

SELECT TitleDescription FROM CustomerDetails.Title 16. Click OK to add the TitleList dataset. Optionally, you can click the Run (!) button on the Query Designer toolbar to see the results of the query.

In this section, we will add a table data region to the report layout. 17. Click the Layout tab to display the Layout view. 18. If the Toolbox window is not displayed, from the View menu click Toolbox. It may be just a small tab on the top left of your design area so it may not be obvious. 19. In the Toolbox window, click the Table control and then click the design surface. A table with three columns is generated. 20. In the Datasets explorer, probably on the left of the design pane, expand the ApressFinancial dataset to see the fields.

21. Drag the CustomerFirstName field to the detail row (the middle row) of the first column. 22. Drag the CustomerLastName field to the detail row (the middle row) of the second column. 23. Drag the TitleDescription field to the detail row (the middle row) of the third column. Your columns should now resemble those shown in Figure 14-7.

Figure 14-7. The design surface 24. Use the File menu and click Save All. Click the Preview tab to see the report.

In this section, we will change the design by adding a new column, and we will add some interactivity to our report using a parameter. 1. Click the Layout tab to display the Layout view. 2. Double-click the first column of the first row in the table that displays the text Customer First Name. Delete the word Customer. 3. Repeat the step for the second column.

4. Delete the Description word from the third column using the same method. 5. Click the table to display the column and row handles. Right-click the handle of the third column (Title) and select Insert Column To the Right, as shown in Figure 14-8.

Figure 14-8. Adding a new column 6. Double-click the detail row for the new column (the fourth). Enter the following expression that will calculate the initials: =Left(Fields!CustomerFirstName.Value,1) & " " & Left(Fields!CustomerLastName.Value,1) 7. Double-click the first row for the new column and type Initials. Click the Preview tab to see the results.

Try It Out: Deploying the Report (Optional)

In this section, we will deploy the report to the local report server and use Report Manager to view the report. 1. In the Solution Explorer, right-click Advanced Report solution, and select Properties. 2. In the TargetServerURL textbox, type http://localhost/ReportServer and click OK.

3. The Report Services service has to be running. This will only be started manually unless you have changed the default within your Services. From the Control Panel, select Administrative Tools, then select Services. Find SQL Server Reporting Services, right-click, and click Start as seen in the following illustration:

4. In the Solution Explorer, right-click Advanced Report solution, and select Deploy. 5. Open Microsoft Internet Explorer and in the address bar, type the Report Manager URL: http://localhost/ Reports/. This will then redirect you to the reports pages (see Figure 14-9).