Microsoft may have patents, patent applications, trademarked, copyrights, or other intellectual property rights covering subject matter in this document. Except as expressly provided in any written license agreement from Microsoft, the furnishing of this document does not give you any license to these patents, trademarks, copyrights, or other intellectual property.
2007 Microsoft Corporation. All rights reserved.
Microsoft, MS-DOS, MS, Windows, Windows NT, MSDN, Active Directory, BizTalk, SQL Server, SharePoint, Outlook, PowerPoint, FrontPage, Visual Basic, Visual C++, Visual J++, Visual InterDev, Visual SourceSafe, Visual C#, Visual J#, and Visual Studio are either registered trademarks or trademarks of Microsoft Corporation in the U.S.A. and/or other countries.
Other product and company names herein may be the trademarks of their respective owners.
SQL Server 2005 Reporting Services
Objectives
After completing this lab, you will be able to:
n Create a Report Using the Wizard
n Create a List Report
n Work with Reports
n Manage Security
Note
This lab focuses on the concepts in this module and as a result may not comply with Microsoft security recommendations.
Note
The SQL Server 2005 labs are based on beta builds of the product. The intent of these labs is to provide you with a general feel of some of the planned features for the next release of SQL Server. As with all software development projects, the final version may differ from beta builds in both features and user interface. For the latest details on SQL Server 2005, please visit http://www.microsoft.com/sql/2005/.
Estimated time to complete this lab: 75 minutes
Exercise 0 Lab Setup
n Log in using the Administrator user account. The password is pass@word1.
n The first time you log in to the Virtual PC 2004 image you will need to run a command to reinitialize Reporting Services. Note this is only necessary if you are using the Virtual PC 2004 image provided with the SQL Server 2005 Beta 2 CDs. To reinitialize, do the following:
1. Click Start, then Run and type cmd and then click OK.
2. Type the following text as one complete line as it appears and press Enter (including the quotes):cd "C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services\ReportManager\Bin"
3. Type RSKeyMgmt.exe -d and press Enter.
4. Exit the command prompt by typing Exit and pressing Enter.
5. Verify the command was successfully by starting Internet Explorer. At the home page, click the link for Reporting Services. Note it my take a moment for the service to initialize.
Exercise 1: Creating a Report Using the Wizard
In this exercise, you will create a simple report using the Business Intelligence Development Studio.
To create the report, you will:
n Create a Business Intelligence Development Studio solution and add a Report Project.
n Create a tabular report using the Report Wizard.
SQL Reporting Services report designer is hosted in the Business Intelligence Development Studio environment. It provides you with an integrated developer environment for designing, creating, testing, and debugging reports.
å Task 1: Copy the report query
Before creating a report, edit and copy a query using Notepad. This query will be used to create a simple report.
1. Click Start Run, and then type Notepad.
2. Select File Open, navigate to the C:\SQL Labs\Lab Projects\Reporting Services Lab directory, select Query.txt, and then click Open.
Using previously developed queries can save time when creating data sets. It also allows users to develop queries using other tools, and then paste the SQL into this interface.
3. In Notepad, select all the text by typing Ctrl+A and then copy by using Ctrl+C. Select File Exit to exit Notepad without saving the document.
å Task 2: Launch the report design environment
1. From the Windows task bar, select Start All Programs Microsoft SQL Server 2005 Business Intelligence Development Studio.
Business Intelligence Development Studio provides the environment in which Reporting Services reports are designed.
2. Select File New Project.
3. In the New Project dialog box, in the Project Types pane, verify that the Business Intelligence Projects folder is selected.
Business Intelligence Development Studio allows you to create a blank Visual Studio 2005 solution or to select one of the Business Intelligence templates such as Analysis Services Project, Data Transformation Project, or Report Project.
4. In the Templates pane, click the Report Project icon.
5. In the Location text box, enter C:\SQL Labs\User Projects.
6. In the Name box, accept the default name (that is, Report Project1), and then click OK.
Tip
If the Solution Explorer pane is not visible, select View Solution Explorer.
å Task 3: Create a new report using the report wizard
A new report can be created using the report wizard.
1. Select Project Add New Item.
2. In the Add New Item dialog box, from the Templates pane, select Report Wizard and then click Add.
3. On the Welcome to the Report Wizard page, click Next.
4. On the Select the Data Source page, select New data source, make sure that Microsoft SQL Server is selected in the Type drop-down list, and then click the Edit button.
5. In the Data Link Properties dialog box, on the Connection tab, in the Data Source text box, type localhost.
6. Under Enter information to log on to the server, select Use Windows NT Integrated security.
7. In the Enter the initial catalog to use drop-down list, select AdventureWorksDW, and then click OK.
8. Verify that the local server and AdventureWorksDW database are both listed in the connection string. Click Next.
9. In the Design the Query page, paste the text from the Notepad query in the Query String text box by clicking the Query String box and pressing Ctrl+V.
10. At the end of the existing query, insert a new line, and then type:
WHERE EnglishProductCategoryName = 'Bikes'
Caution
Enter the SQL clause in step 10 carefully. The AdventureWorksDW database you’ll be using is case sensitive.
11. Click Next to move to the next page.
12. In the Select the Report Type page, verify that Tabular is selected, and then click Next.
13. In the Design the Table page, in the Available Fields list, select EnglishProductName, and then click Details. Repeat for SalesAmount. Click Next.
The previous step adds the selected fields to the Details section of the report.
Note
Fields can be added to the other sections of the report either by using the other two buttons (Page and Group) or by dragging and dropping the fields into the appropriate boxes.
14. In the Choose the Table Style page, click Next.
15. In the Completing the Wizard page, leave Report1 as the report name and click Finish.
å Task 4: Modify the Report Layout
1. In the Layout view, in the Body section of the report, click inside the table (for example, on the EnglishProductName label).
2. Move the cursor over the table column divider (in the grey bar above EnglishProductName and SalesAmount) until it changes to a double arrow. Click and drag the column width for the EnglishProductName column until it is approximately two inches wide.
3. Click on the Report1 title, and replace it with “Bike Price Report.” Adjust the field width so that it is wide enough to display the whole title. When you’re done, the report should look like Figure 1.
Figure 1: The completed report design should look like this.
4. Select File Save All.
5. In the View pane, click Preview.
A list of bikes with sales amounts will be displayed.
Note
Reporting Services may take a few moments to preview the report.
å Task 5: Close the Business Intelligence Development Studio environment.
n Select File Exit.
Exercise 2:Creating a List Report
In this exercise, you will create a simple report using Business Intelligence Development Studio and deploy that report to the SQL Server Reporting Services report server.
To create the report, you will:
n Create a Business Intelligence Development Studio solution and add a Report Project.
n Create a Data Set.
n Set up a query from a Data Set.
n Design the layout of a report.
n Deploy the finished report to the Report Server.
SQL Reporting Services report designer is hosted in the Business Intelligence Development Studio environment. It provides you with an integrated development environment for designing, creating, testing, and debugging reports.
å Task 1: Launch the report design environment
1. Click Start All Programs Microsoft SQL Server 2005 Business Intelligence Development Studio.
Business Intelligence Development Studio provides the environment in which Reporting Services reports are designed.
2. Choose File New Project.
3. In the New Project dialog box, in the Project Types pane, verify that the Business Intelligence Projects folder is selected.
Business Intelligence Development Studio allows you to create a blank Visual Studio 2005 solution or to select one of the Business Intelligence templates such as Analysis Services Project, Data Transformation Project, or Report Project.
4. In the Templates pane, select Report Project.
5. In the Name box, type AWReports.
6. In the Location box, enter C:\SQL Labs\User Projects and then click OK.
Tip
If the Solution Explorer pane is not visible, click View Solution Explorer.
7. In Solution Explorer, right-click AWReports, and select Add New Item from the context menu.
8. In the Add New Item – AWReports dialog box, in the Templates pane, select Report (not Report Wizard).
9. In the Name text box, enter AWSales.rdl, and then click Add.
å Task 2: Create a new Dataset
All reports need to have associated data. This source data information is created in a Dataset.
1. At the top of the report designer, click the Data tab if it's not already selected.
The data view provides the tools to set up the Dataset and create the query information you wish to report.
2. In the Dataset drop-down list, click
A dataset represents the specific fields queried from a data source. Multiple datasets can be created using a single source.
3. In the Data Link Properties dialog box, on the Connection tab, in the Data Source text box, type localhost.
4. Under Enter information to log on to the server, select Use Windows NT integrated security.
5. In the Enter the initial catalog to use drop-down list, select AdventureWorksDW, and then click OK.
6. Click the ellipsis (…) button to the right of the Dataset drop-down list.
7. In the Dataset dialog box, in the Name text box, replace the existing text with “SalesData”. (Do not type the quotes.)
Warning
Be sure there are no spaces in your DataSet name.
å Task 3: Create a query for the Data Set
1. From the Windows task bar, click Start Run and type NotePad.
2. Choose File Open and navigate to the C:\SQL Labs\Lab Projects\Reporting Services Lab directory. Select Query.txt, and then click Open.
Using previously developed queries can save time when creating data sets. This also allows users to develop queries using other tools, and then paste the SQL into this interface.
3. In Notepad, press Ctrl+A to select all the text, press Ctrl+C to copy the text to the clipboard, and then close Notepad.
4. Switch back to Business Intelligence Development Studio and paste the text in the Query String text box of the DataSet dialog box by clicking the Query String box, and typing Ctrl+V. Click OK to close the Dataset dialog box.
å Task 4: Create the First List Object
1. In the View pane, click the Layout icon.
2. In the Layout pane, move the mouse pointer to the bottom edge of the report grid. When the mouse pointer changes into a vertical double-arrow icon, click and drag downward to enlarge the report grid.
3. In the Toolbox window, double-click the List item in the Report Items group. This will place an instance of the List control on the report design surface.
Tip
By default, the Toolbox is located on the left side of the screen. If the Toolbox window is not visible, select View Toolbox.
4. With the new List control still selected, click and drag the lower-right corner of the control so that the control fills the width of the entire design surface and is about one inch tall.
Important
If the Fields window is not already open, select View Fields.
5. Drag EnglishProductCategoryName from the Fields window to the upper left corner of the List area in the report grid. Click and drag the right edge of the field’s text box to make it approximately two inches wide.
6. With the EnglishProductCategoryName text box selected, press F4 to display the Properties window with properties for this control.
7. In the Properties window, click the Font property. Expand the Font property to display the FontWeight property. Change the FontWeight property from Normal to Bold.
8. Click inside the List area.
9. In the Properties window, select the Grouping property, and then click the ellipsis button to the right of the property value.
10. In the Details Grouping dialog box, in the Expression list, click on the first row and then expand the drop-down list. Select =Fields!EnglishProductCategoryName.Value, and then click OK.
11. In the Fields window, click SalesAmount and then drag from the Fields window to the upper right corner of the List area.
12. Expand the two text boxes vertically so that the expressions are visible, and verify that they match Figure 2.
Figure 2: The completed layout.
13. Click on the SalesAmount text box to select it, and in the Properties window, in the Format property text box, type 0,0.00
14. Select File Save All.
15. In the View pane, click the Preview icon. The results should look like Figure 3.
16. The report will generate and then show the three categories and a sales amount total for each category.
Figure 3: The report in its current state.
å Task 5: Create the second List object
1. In the View pane, click Layout.
2. Expand the List control to fill the entire design surface.
3. Click the Toolbox tab at the bottom left corner of the main window. After the Toolbox appears, click List. Use the mouse to drag a rectangle that covers the area inside the first List area and under the text boxes just added to the first List area. Make sure that you leave some space between the text boxes and the new List area. Use Figure 4 as your guide.
Figure 4: Lay out the second List control like this.
Nesting list objects facilitates grouping and sorting operations.
4. Select View Fields. From the Fields window, drag EnglishProductSubCategoryName to the upper left corner of the second (inner) list area. Click and drag the right edge of the text box to make it approximately two inches wide.
5. Click inside the second list area. In the Properties window, click the Grouping property, and click the ellipsis button to the right of the property value (…).
6. In the Expression list, click on the first row, expand the drop-down list, and then select =Fields!EnglishProductSubCategoryName.Value. Click OK.
7. In the first (outer) List area, right-click the SalesAmount text box, click Copy, right-click inside the second (inner) List area, and then click Paste. Drag the SalesAmount textbox to the upper right corner of the inner List area.
8. Select File Save All.
9. In the View pane, click Preview.
Both a category and a list of subcategories with corresponding sales amounts will be displayed.
Note
Reporting Services may take a few moments to preview the report.
å Task 6: Create the third List object
1. In the View pane, click Layout.
2. Click the Toolbox. In the Toolbox window, click List. Drag a rectangle that covers the area inside the second List area under the text boxes just added to the second List area.
3. Select View Fields. From the Fields list, drag EnglishProductName to the upper left corner of the third List area. Drag the right edge of the text box to make it approximately two inches wide.
4. From the Fields list, drag the SalesAmount field to the upper-right corner of the third List control.
5. Click inside the third List area. In the Properties window, click Grouping and then click the ellipsis (…) button.
6. In the Details Grouping dialog box, in the Expression list, click on the first row, expand the drop-down list, and select =Fields!EnglishProductName.Value. Click OK.
7. Reduce the size of the third List area to cover just around the text boxes, reduce the size of the second List area to cover just around the third List area, and reduce the size of the first List area to cover just around the second List area. The finished report should look like Figure 5.
Figure 5: The finished report contains three list areas.
8. Select File Save All.
9. In the View pane, click Preview.
All the detail for the category, subcategories, and products will be displayed.
Note
Depending on your individual report design, you may need to adjust your spacing in the Layout pane to achieve optimal report layout. A good rule of thumb is to make sure that the list areas are just big enough to house the text boxes without overlapping.
å Task 7: Add Page Breaks to the Report
This report contains many lines of data and may be easier to read if it is broken into pages.
1, In the View pane, click Layout.
2. Click inside the first (outermost) List area. In the Properties window, select the Grouping property, and then click the ellipsis (…) button.
3. In the Details Grouping dialog box, select Page break at end, and then click OK.
4. Select File Save All.
5. In the View pane, click Preview.
Your report will begin each category listing with a fresh page. You can use the page arrows in the Preview pane to navigate between pages of the report.
å Task 8: Create Parameters for a Report
Parameters can be used to further control the report’s data. For example, imagine that the product category managers want to view the sales for their specific category. In order to meet each manager’s reporting needs, a product category will be specified at run time by specifying a parameter value. This will allow the same report to be customized for each product category manager.
1. In the View pane, click on the Data icon.
2. In the SQL pane, move the cursor to the end of the SQL query, add a new line, and then type:
WHERE EnglishProductCategoryName = @Category
Caution
Enter the SQL phrase carefully in Step 2. The AdventureWorksDW database is case sensitive.
3. In the View pane, click on the Preview icon.
4. At the top of the Preview designer, in the Category text box, type Bikes, and then click View Report.
Tip
If the report does not return any records, retry Step 4, ensuring that you type Bikes in the Category text box. The AdventureWorksDW database is case sensitive.
The category parameter will limit the report to just the Bikes category.
5. Select File Save All.
å Task 9: Deploy a report to the Report Server
Once the development of the report or project is completed, it can be deployed from the Business Intelligence Development Studio environment to the Report Server Web application where it will be available to end users.
1. Select Build Configuration Manager.
2. In the Configuration Manager dialog box, set the Active Solution Cofiguration to Production.
3. Click Close to dismiss the dialog box.
4. In the Solution Explorer, right-click the AWReports project and then select Properties from the context menu.
5. In the AWReports Property Pages dialog box, under Deployment, in the TargetServerURL text box, type http://localhost/reportserver and then click OK.
6. Select the Build Deploy Solution menu item. Watch the Output window for status on the deployment.
Once the deployment has completed, you can view the report in a Web browser. In the next exercise, you will browse deployed reports.
å Task 10: Close the Business Intelligence Development Studio Environment
1. Select File Exit.
Exercise 3:Working with Reports
In this exercise, you will set up a subscription, define a shared schedule, and create a report snapshot by using Microsoft SQL Server Reporting Services report server.
To create this subscription you will:
n Navigate in the Report Server interface.
n Create a report subscription.
n View the subscription output.
n Create a shared schedule.
n Create a Report Snapshot.
å Task 1: Navigate to a deployed Report
1. From the Windows task bar, click Start All Programs Internet Explorer.
SQL Services Reporting Services is a Web application and will be administered by using a browser.
2. Within the browser, navigate to http://localhost/Reports/
This address is the home page for SQL Server Reporting Services, which is the default page for the Report Server. This page has two tabs, Contents and Properties. The default page is Contents. The Contents page has links to deployed report projects. Links to pages containing additional links are preceded by a folder icon.
3. Click the AWReports link.
4. Click the AWSales link.
Notice that the path of links you take through the Report Server pages is shown above the bold title on each page. Each section of the path can be accessed with a click.
5. If an authentication alert appears, enter Administrator for the user name and enter password for the Password, and then click OK.
6. In the Category text box, type Bikes, and then click View Report.
The default page for a report is the View tab. This page is very similar to the Preview pane in Business Intelligence Development Studio in that it has navigation arrows and other controls to manipulate the preview. There are three other tabs that link to other pages: Properties, History, and Subscriptions.
å Task 2: Set up the data source
1. Above the report, click the Properties tab.
2. In the left hand side of the screen, click the Data Sources link.
3. In the Connect Using section, click Credentials stored securely in the report server.
4. For User name, type “sa”. For Password, type “pass@word1”.
5. Click Apply.
å Task 3: Create a Subscription
Subscriptions are requests to be notified of an event, such as a repeating scheduled event, or updates to a report’s data (called snapshots). All messages contain a subject line note with a date/time stamp, and they can also include a URL link to the report or a copy of the report.
1. Click the Subscriptions tab.
Subscriptions can be data driven or they can be set up manually. A data-driven subscription uses a command or query that returns a list of recipients and optionally returns fields used to vary delivery settings and report parameter values for each recipient.
2. Click New Subscription.
3. In the Delivered by drop-down list, verify that Report Server Email is selected.
4. In the To box, verify that Administrator is the email address of the subscription recipient.
5. In the Scheduled Processing Options section, click When the scheduled report run is complete, and then click Select Schedule.
6. In the Schedule details section, click Once. Enter a time at which to run the report, and click OK.
7. In the Report Parameter Values section, in the Category text box, type Bikes, and then click OK. The completed page should look like Figure 6.
Figure 6: The completed subscription page.
å Task 4: Create a Shared Schedule
Schedules can be used to trigger updates to a report’s data and to store snapshot reports on the Report Server. These schedules can be customized for each report or a shared schedule can be used to trigger multiple events.
1. Make sure you're displaying the AWSales Subscriptions page. (This should be the current page, if you've been following the steps.)
2. In the upper right corner of the browser screen, click the Site Settings link.
The Site Settings page allows administrators to access properties that apply to the entire Reporting Services Server interface. Security settings are accessed here through links to Roles and Policies as well as Shared Schedules.
3. In the Other section at the bottom of the page, click the Manage shared schedules link.
The Shared Schedules page lists all of the shared schedules on the server. The schedule can be changed by clicking its link. There are Delete, Pause and Resume buttons for managing the schedules.
4. Click New Schedule.
5. In the Schedule Name textbox, enter Bikes.
6. Under Schedule details, click Once.
7. Inside the Scheduled once box, change the Start Time to be the current time plus five minutes by modifying the Hours and Minutes boxes as well as by choosing AM or PM, and then clicking OK.
Important
In the Start and end dates section, check that the date in the Begin running this schedule on text box is the current date. If the date is not correct, click the Calendar button on the right to open the calendar, and click on the correct date.
å Task 5: Create a Report Snapshot
1. On the upper-right hand corner of the screen, click the Home link.
2. Click the AWReports link.
3. Click the AWSales link.
4. Click the Properties tab.
5. On the left hand side of the screen, click the Parameters link.
6. Check the Has Default check box, and enter Bikes into the Default Value text box.
7. Uncheck the Prompt User checkbox to the right of the Default Value text box.
8. Click Apply.
9. On the left hand side of the screen, click the History link.
10. Click Use the following schedule to add snapshots to report history.
11. Select Shared schedule.
12. Next to Shared Schedule, verify the shared schedule created earlier is selected.
13. Click Apply.
At the scheduled time, the Report Server will generate a copy of the report with a time and date stamp. Storage and expiration of these snapshot reports can be customized for each report.
8. After the scheduled time, click the History tab to view the report snapshot. In the meantime, move on to the next exercise, but don’t forget to go back and check on the snapshot you created, after the scheduled time.
Exercise 4:Managing Security
In this exercise, you will modify security settings for both the Report Server application and for a specific report by using the Microsoft SQL Server Reporting Services Web application interface.
In this exercise, you will:
n Create a system-level security role.
n Apply a new system role to a group.
n Create an item-level security role.
n Apply a new item-level role to a report.
The security roles used by the Report Server Application use the groups and users set up in the Windows operating system.
å Task 1: Create a new system role
1. From the Windows task bar, click Start All Programs Internet Explorer.
SQL Server Reporting Services is a Web application and will be administered by using a browser.
2. In the address bar, type “http://localhost/Reports”.
3. In the upper-right corner of the browser screen, click the link for Site Settings.
4. In the Security section near the bottom of the page, click Configure system-level role definitions.
System Roles define what system level actions and permissions a group or user is allowed. This includes permission to manage security, roles, shared schedules, and server properties. These are inherited from their parent folder by default, but they can be changed on an item-by-item basis.
5. Click the New Role button near the top of the page.
6. In the Name text box, type “Report Analyst”
7. In the Description text box, type “May view server properties and manage shared schedules”.
8. In the Select one or more tasks to assign to the role section, select Manage shared schedules, View report server properties, and View shared schedules. Figure 1 shows the completed page
Figure 1: Select tasks for the new role.
9. Click OK to save the changes.
å Task 2: Create a new Item-level security role
Access to a report can be fine-tuned for specific groups or users by creating item-level roles that grant different permissions from the default permissions created when a report is first deployed.
1. Click Site Settings.
2. Under Security, click the Configure item-level role definitions link.
Roles define what item-level actions and permissions a group or user is allowed. This includes permissions to manage a report’s history, subscriptions, data sources, folders, and security.
3. Click the New Role button.
4. In the Name text box, type ReadOnly.
5. In the Description text box type “May only view server objects”.
6. Select the check boxes for View data sources, View folders, View reports, and View resources, and then click OK. Figure 3 shows the completed page.
Figure 3: Select tasks for the ReadOnly role.
7. Click the Home link, click AWReports, and then click AWSales.
8. Click the Properties tab. On the left-hand side of the screen, click the Security link, and then click the Edit Item Security button at the top of the window.
Permissions are inherited from their parent folder by default. Changing the default permissions opens an information dialog box asking the user if they want to break the inheritance of these permissions.
9. In the Microsoft Internet Explorer message box, click OK.
10. Click the Edit link to the left of the BuiltIn\Administrators label. Uncheck all selected items, check the ReadOnly box, and then click Apply, as shown in Figure 4.
Figure 4: Set assignments for the ReadOnly role
All users in the Administrators group set up in Windows will now only have the ReadOnly permissions created above for the report.
The original report permissions can be restored by clicking Revert to Parent Security.
