CONTENTS

1. INTRODUCTION................................................................................................................................2
2. PREREQUISITES..............................................................................................................................2
3. INSTALLATION OVERVIEW...............................................................................................................2
4. REPORTING SERVICES ARCHITECTURE..........................................................................................2
5. REPORT DESIGNER.........................................................................................................................3

5.1. USING THE REPORT WIZARD................................................................................................3
5.2. MODIFYING A REPORT..........................................................................................................6
5.3. DESIGNING A REPORT FROM SCRATCH................................................................................7

6. MATRIX CONTROL VS TABLE CONTROL...........................................................................................9
7. ADD A GROUP TO A TABULAR REPORT...........................................................................................9
8. DRILL DOWN TECHNIQUES WITH THE VISIBILITY PROPERTY..........................................................11

8.1. THE STANDARD “DRILLDOWN...............................................................................................11
8.2. THE REVERSE “DRILLDOWN”................................................................................................12
8.3. CONDITIONAL INITIAL VISIBILITY............................................................................................13

9. PUBLISHING A REPORT...................................................................................................................14
10. REPORT MANAGER.......................................................................................................................16

 

 1. INTRODUCTION

SQL Server Reporting Services is a key component of SQL Server. It is the customizable reporting solution for report designers and programmers. This hands-on guide will get you up to speed quickly so you can design, deploy, manage, and even customize reporting solutions. You can create powerful reports without programming knowledge and extend reporting solutions using VB, C#, and ASP.NET.

2. PREREQUISITES

In order to use the provider, the following components must be installed:

• Microsoft SQL Server 2005 Reporting Services Service Pack 1 or later.
• Microsoft .NET Framework 2.0.
  

3. INSTALLATION OVERVIEW
The assemblies required to use this provider are installed together with Service Pack 1 (SP1) in two separate locations: one location for the design environment (Business Intelligence Development Studio), and another for the run-time environment (Report Server). Set up for SP1 copies the files to the correct locations for each installed environment. Set up will install to both locations if both the run-time and design environments are on the same computer. If only one of these environments is installed when you set up SP1, but subsequently install the other environment, you must rerun SP1 Setup to install the required assemblies for the newly added environment.

After the provider is installed, you must register the provider with each environment in which it will be used. For the design environment, you must modify the file RSReportDesigner.config, located in <drive>:\Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies. For the run-time environment, you must modify the file RSReportServer.config, located in <drive>:\Program Files\Microsoft SQL Server\MSSQL.n\Reporting Services\ReportServer.

4. REPORTING SERVICES ARCHITECTURE

For a relatively new product, Reporting Services has a quite complex architecture. The full Reporting Services architecture includes development tools, administration tools, and report viewers. There are a number of ways to get to Reporting Services programmatically, including SOAP and WMI interfaces.

Simplified diagram of the main Reporting Services components as below:-

 

• Report Server is the core engine that drives Reporting Services.

• Report Manager is a Web-based administrative interface for Reporting Services.

• Report Designer is a developer tool for building complex reports.

• Report Builder is a simplified end-user tool for building reports.

• The Report Server database stores report definitions. Reports themselves can make use of data from many different data sources.

5. REPORT DESIGNER

Reporting Services includes two tools for creating reports:

• Report Designer can create reports of any complexity that Reporting Services supports, but requires you to understand the structure of your data and to be able to navigate the Visual Studio user interface.

• Report Builder provides a simpler user interface for creating ad hoc reports, directed primarily at business users rather than developers. Report Builder requires a developer or administrator to set up a data model before end users can create reports.

We'll start our tour of Reporting Services with Report Designer. Report Designer runs inside the Business Intelligence Development Studio shell, and offers several ways to create reports. You can either use the Report Wizard to quickly create a report, or you can use a set of design tools to build a report from scratch. You can also use the design tools to modify a report created with the wizard.

5.1 Using the Report Wizard
The easiest way to create a report in Report Designer is to use the Report Wizard. Like all wizards, the Report Wizard walks you through the process in step-by-step fashion. You can make the following choices in the wizard:

• The data source to use.

• The query to use to retrieve data.

• Whether to use a tabular or matrix layout for the report.

• How to group the retrieved data.

• What visual style to use.

• Where to deploy the finished report.

To create a simple report using the Report Wizard, follow these steps:

1. Launch Business Intelligence Development Studio.
2. Select File > New >Project.
3. Select the Business Intelligence Projects project type.
4. Select the Report Server Project Wizard template.
5. Name the new report ProductReport1 and pick a convenient location to save it in.
6. Click OK.
7. Read the first page of the Report Wizard and click Next.
8. Name the new data source.
9. Click the Edit button.
10. Log on to your test server.
11. Select the database.
12. Click OK.
13. Click the Credentials button.
14. Select Use Windows Authentication.
15. Click OK.
16. Check the Make This a Shared Data Source checkbox. This will make this particular data source available to other Reporting Services applications in the future.
17. Click Next.
18. Click the Query Builder button.
19. If the full query designer interface does not display by default, click the query designer toolbar button at the far left end of the toolbar. Figure 5-1 shows the full query designer interface.
    
    
    

    Figure 5-1: Query Builder

20. Click the Add Table toolbar button.
21. Select the table and click Add.
22. Click Close.
23. Check the columns of table.
24. Click OK.
25. Click Next.
26. Select the Tabular layout and click next.
27. Move the Color column to the Group area and the other three columns to the Detail area, as shown in Figure 5-2.
    
    

Figure 5-2: Grouping columns in the report

 

28. Click Next.
29. Select the Stepped layout and click Next.
30. Select the Ocean style and click Next.
31. Accept the default deployment location and click Next.
32. Name the report ProductReport1.
33. Check the Preview Report checkbox.
34. Click Finish.

Figure 5-3 shows the finished report, open in Report Designer.

Figure 5-3: Report created by the Report Wizard

Figure 5-3 shows the main features of Report Designer:

• The Datasets window shows the data that is available to the report.

• The main design window lets you view the report itself. You can see a preview of the report, work with the report in a layout designer, or work with the query that returns the data for the report.

• The Solution Explorer, Output, and Properties windows are the standard Visual Studio windows.

5.2. Modifying a Report

Now that you've created a report with the Report Wizard, you can modify it with the Report Designer. If you've used any sort of visual report design tool in the past, you should have no problem making changes here. Among the possibilities here:

• You can change the available data or the sort order for the report by modifying the query on the Data tab.

• You can resize or rearrange controls on the Layout tab.

• You can use the Properties window to change properties of individual controls including their font, alignment, colors, and so on.

To modify the report that you just created, follow these steps:

1. Click the Data tab to view the query for the report.
2. Select a Descending sort type for the ListPrice column.
3. Click the Layout tab.
4. Click in the textbox at the top of the report, where the report name is displayed.
5. Use the Properties window to change the Value property of this control to Products By Color.
6. Click on the header for the Product column.
7. Place the cursor between the Name and Product Number columns to display a double-headed arrow. Hold down the mouse button and drag the cursor to the right to widen the Name column.
8. Place the cursor between the Product Number and ListPrice columns to display a double-headed arrow. Hold down the mouse button and drag the cursor to the right to widen the Product Number column.
9. Click on the Preview tab to view the modified report, as shown in Figure 5-4.

Figure 5-4: Modified product report

5.3. Designing a Report from Scratch

You can also use Report Designer to build your own reports starting from scratch. In general, you'll follow these steps to create a report:

1. Create a Report project in Business Intelligence Design Studio.
2. Associate one or more datasets with the report.
3. Create a query from the dataset.
4. Build the report layout.

To create a fresh report in Report Designer, follow these steps:

1. Select File > Close Solution to close the existing report.
2. Select File > New > Project.
3. Select the Business Intelligence Projects project type.
4. Select the Report Server Project template.
5. Name the new report ProductReport2 and pick a convenient location to save it in.
6. Right-click on the Reports node in Solution Explorer and select Add „ New Item.
7. Select the Report template.
8. Name the new report ProductReport2.rdl and click Add.
9. Select the Data tab in the Report Designer.
10. Click the Datasets dropdown and select <New Dataset> to open the Data Source dialog box.
11. Name the new Data Source.
12. Click the Edit button.
13. Connect to your test server and choose the database.
14. Click OK.
15. Click OK again to create the data source.
16. Click the Query Designer toolbar button inside the Data tab to display the full query designer user interface.
17. Click the Add Table button.
18. Select the Location table.
19. Click Add.
20. Click Close.
21. Check the boxes for the Name and CostRate columns.
22. Sort the dataset in ascending order by Name.
23. Click the Layout tab to show the blank report layout.
24. Hover your mouse over the toolbox tab to display the available controls for the report.
25. Double-click the List control.
26. Expand the List control to the width of the report.
27. Expand the dataset in the Datasets window to show the column names.
28. Drag the Name and CostRate columns from the Datasets window and drop them in the List control. Expand them horizontally until each takes up about half of the List control. Figure 5-5 shows the report in layout view.
    
    

Figure 5-5: Designing a report from scratch



29. Select the Preview tab to see the report with data.

6. MATRIX CONTROL VS TABLE CONTROL

SSRS provide table and matrix control to let user populate data in different scenario. Table control let user to populate simple format of data where fixed columns applied and rows grow dynamically. Matrix provide more powerful features where it columns and rows can be dynamically grow. In that case, writing a store procedure to support a matrix become easier in some scenario.

7. ADD A GROUP TO A TABULAR REPORT

1. In the Layout pane, click the table so that column and row handles appear above and next to the table
2. Right-click on the handle of any row and then click Insert Group.
3. On the General tab, for Group on, select =Fields!LastName.Value in the first row and =Fields!FirstName.Value in the second row. This will group the data by sales person name.
   
   
   
   
4. On the Sorting tab, for Sort on, select =Fields!LastName.Value in the first row and =Fields!FirstName.Value in the second row, each with a direction of Ascending. This will sort the groupings by sales person name.
   
   



5. Click OK. Two new rows, a group header and group footer, are added to the table.

 

8. DRILL DOWN TECHNIQUES WITH THE VISIBILITY PROPERTY

The techniques are the following:

• Technique #1 – The Standard “Drilldown”

• Technique #2 – The Reverse “Drilldown”

• Technique #3 - Conditional Initial Visibility

8.1. The Standard “Drilldown

Check the properties of the group header textbox. Note the "Name" property. It should be something like "SupplierRegion_1" (or something close to that).

Now we have to get to the Details Grouping properties. (There's something about that that doesn't make sense, but go with me here.) There are two ways (that I know of) to get there. 1) Right click on the details line and select Edit Group (even though it's a details line), or 2) select the entire table, right-click, select Properties, then the Groups tab, then Details Grouping. Both these methods bring you to the same place.

Then click on the Visibility tab. Click Hidden, check the Visibility can be toggled... checkbox and select the SupplierRegion_1 textbox.

When you are done your preview should look like this:

Expand some of the times:

8.2. The Reverse “Drilldown
But let's say this is a small table and you want display the records expanded to open by default. There are two additional things to add to the above technique to make this work.

1) On your Visibility tab (of the Details Grouping), change the Initial Visibility to Visible.

After this, try out a preview. You will notice that the +/- signs do not work the way you would expect or want.

2) To reverse this, right click on the SupplierRegion_1 (group header, not the group details anymore) textbox properties. Select the Visibility tab, then change the Initial appearance of the toggle... to Expanded (-).

This will correct the +/- sign and you are all set to provide all details by default with the ability to collapse groupings for readability.

8.3. Conditional Initial Visibility

Now let's say you want to open some groups but not others based on a certain condition. (How about by the number of records?). For the purposes of demonstration, I have added the following expression to the last cell in the group heading line.

=CountRows("table1_Group1")

This will display the number of rows for each group

For the sake of this discussion, let's say that we want to display the records of a group only if the number is 4 or less.

In the Visibility tab of the Group Details section, under Initial Visibility instead of selecting "Visible" or "Hidden", select Expression and enter the following in the Expression field (or use the Expression Editor button).

=IIF(CountRows("table1_Group1")<=4,False,True)

NOTE: Important - Make sure you use the "False" and "True" keywords. In many other cases, the IIF statement uses the keywords from the normal options (i.e. 'solid' & 'none' for BorderStyle). In this case, you cannot use "hidden" & "visible".

Once done, the report operates fine, but we have to enter a similar (but opposite) expression to fix the +/- signs again. To fix this, right click on the SupplierRegion_1 (group header, not the group details anymore) textbox properties. Select the Visibility tab, then change the Initial appearance of the toggle... to Expression and enter the following:

IIF(CountRows("table1_Group1")<=4,True,False)

You see, the table conditionally expands where row counts are <=4. You have to click the "+" sign to expand groups of 5 or more.

9. PUBLISHING A REPORT

Creating reports in Business Intelligence Development Studio is good for developers, but it doesn't help users at all. In order for the reports you build to be available to others, you must publish them to your Reporting Services server. To publish a report, you can use the Build and Deploy menu items in Business Intelligence Development Studio. Before you do this, you need to check the project's configuration to make sure that you've selected an appropriate server for the deployment.

To publish a report, follow these steps:

1. Close the ProductReport2 project.
2. Open the ProductReport1 project.
3. Select Project > ProductReport1 Properties.
4. Click the Configuration Manager button.
5. Set the Active Solution Configuration to Production.
6. Make sure the Configuration is set to Production and both the Build and Deploy checkboxes are checked. Figure 9-1 shows the Configuration Manager.
   
   
   
   Figure 9-1: Setting the active configuration



7. Click Close.
8. Fill in the URL for your Report Server. If you're developing on the same computer where Reporting Services is installed, and you installed in the default configuration, this will be http://localhost/ReportServer. Figure 9-2 shows the completed Property Pages.
   
   
   
   Figure 9-2: Setting report project properties
   
   
9. Click OK.
10. Select Build > Deploy ProductReport1. The Output Window will track the progress of BIDS in deploying your report, as shown in Figure 9-3. Depending on the speed of your computer, building the report may take some time.
    
    
    Figure 9-3: Deploying a report


11. Launch SQL Server Management Studio.
12. Click the Connect button in Object Explorer and select Reporting Services.
13. Connect to your test server.
14. Expand the Home node in the Object Explorer tree to find the ProductReport1 project.
15. Expand the ProductReport1 project to find the ProductReport1 report.
16. Right-click on the report and select View Report.
17. If prompted, supply your username and password. The report will open in your Web browser.

10. REPORT MANAGER

The Web home page for Reporting Services provides a complete interface for managing reports (as well as other objects such as data sources and models) after they are created. This interface, known as Report Manager, is intended primarily for database administrators, but as a developer you should know about its capabilities for managing and modifying reports.

When you click on a report in Report Manager, you'll see the report's data, as shown in Figure 9-4.

Figure 9-4: Report in Report Manager

Note that reports in Report Manager open in a tabbed interface. The four tabs allow you to perform various functions:

• View allows you to see the current data in the report.

• Properties lets you adjust such things as the report's name, data source, security credentials, caching, and end-user security.

• History shows you saved snapshots of the report.

• Subscriptions lets you create subscriptions to the report. Subscriptions allow you to set up periodic delivery of reports to end users by e-mail or file share.