Sql Server Reporting Services

1、dat-hol03: sql server reporting services 27sql server reporting serviceshands-on labdat-hol03 introduction1exercise 1 building your first report2exercise 2 modifying report layout9exercise 3 parameters and interactivity16exercise 4 managing reports21exercise 5 managing security25lab summary28informa

2、tion in this document is subject to change without notice. the example companies, organizations, products, people, and events depicted herein are fictitious. no association with any real company, organization, product, person or event is intended or should be inferred. complying with all applicable

3、copyright laws is the responsibility of the user. without limiting the rights under copyright, no part of this document may be reproduced, stored in or introduced into a retrieval system, or transmitted in any form or by any means (electronic, mechanical, photocopying, recording, or otherwise), or f

4、or any purpose, without the express written permission of microsoft corporation.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

5、 microsoft, the furnishing of this document does not give you any license to these patents, trademarks, copyrights, or other intellectual property.2021 microsoft corporation. all rights reserved.microsoft, ms-dos, ms, windows, windows nt, msdn, active directory, biztalk, sql server, sharepoint, outl

6、ook, 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 t

7、rademarks of their respective owners.introductionmicrosoft sql server reporting services provides an enterprise-ready information delivery platform for building, managing, and delivering data from operational and business intelligence systems. reports can include data from a variety of microsoft and

8、 3rd party data sources and can be displayed in a variety of formats including freeform, tabular, crosstabs, and charts. users may browse or search for specific reports and view a report using a browser or office applications as well as subscribe to received reports on a scheduled or event-driven ba

9、sis.this lab presents an overview of reporting services, including basics of report authoring, management, and delivery. after completing the lab, you should have a basic understanding of how to: build and deploy a simple report using the report design wizard. use the report designer to edit the rep

10、ort layout. add advanced report design features such as parameters and interactivity. perform management tasks such as setting up a subscription, defining a shared schedule, and creating report history. modify system-wide and item-specific security settings for the report server.exercise 1building y

11、our first report in this exercise, you will build and deploy a simple report to gain an overall understanding of the report design and browsing features of reporting services. to create the simple report, you will:n create a visual studio solution.n create a simple report using the report wizard.n d

12、eploy the finished report to the report server.n view sample reports.estimated time: 15 minutesthe sql server reporting services report designer is hosted in the visual studio .net development environment. it provides you with an integrated developer environment for creating, designing, testing, and

13、 deploying reports. log on to windows xpif the session is not logged on, log on to windows xp by clicking on the user name teched user. copy the report querybefore creating a report, edit and copy a query using notepad. this query will be used to create your first report.1. click start, point to pro

14、grams, point to accessories, and then click notepad.2. on the file menu, click open, navigate to the c:labex1 directory, click 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

15、paste the sql into this interface.if the file is not available, the query is:select salesterritory.name as region, productcategory.name as category, productsubcategory.name as subcategory, sum(product.listprice * salesorderdetail.orderqty) as sales, sum(product.standardcost * salesorderdetail.orderq

16、ty) as costfrom salesorderheader inner join salesorderdetail on salesorderheader.salesorderid = salesorderdetail.salesorderid inner join product on salesorderdetail.productid = product.productid inner join productsubcategory on product.productsubcategoryid = productsubcategory.productsubcategoryid i

17、nner join productcategory on productsubcategory.productcategoryid = productcategory.productcategoryid inner join salesperson on salesperson.salespersonid = salesorderheader.salespersonid inner join salesterritory on salesperson.territoryid = salesterritory.territoryid inner join customer on salesord

18、erheader.customerid = customer.customeridwhere salesterritory.group = north america and customer.customertype = sgroup by salesterritory.name, productcategory.name, productsubcategory.namecaution: carefully enter the sql phrase in this step. the adventureworks database is case sensitive.3. in notepa

19、d, select all by using ctrl + a and then copy by using ctrl + c.you will paste the query to its destination later in the exercise. launch the report wizard 1. click start and click microsoft visual studio .net 2003.visual studio .net provides the environment in which reporting services reports are d

20、esigned.2. on the file menu, point to new, and then click project.3. in the new project dialog box, in the project types pane, verify that the business intelligence projects folder is selected.many project category options exist, including code development options like visual basic, c#, and c+, as w

21、ell as the project options in the business intelligence category such as the report project wizard.4. in the templates pane, click the report project wizard icon.5. in the name box, replace the default name with sales reports, and then click ok.the report wizard welcome screen will appear. create a

22、new report using the report wizard1. in the welcome to the report wizard page, click next.2. in the select the data source page, select new data source and click the button.3. in the data link properties dialog box, on the connection tab, in the select or enter a server name text box, type localhost

23、.caution: do not click the drop-down for the server name list.4. under enter information to log on to the server, click the use windows nt integrated security radio button.5. in the select the database on the server drop-down list, click adventureworks, and then click ok.6. in the select the data so

24、urce page, verify that the local server and adventureworks database are listed in the server and database properties. click next.7. in the query string text box of the design the query page, paste the text from the notepad query by clicking inside of the query string box, pressing ctrl + v, and then

25、 clicking next.note: if you do not have anything in your clipboard to paste, repeat the copy the report query procedure, the first procedure in this exercise.8. in the select the report type page, select the tabular option, and then click next.9. in the design the table page, in the fields list:a. s

26、elect the region field and click to the page button.b. select the category field and click the group button.c. select the subcategory field and click the details button.d. select the sales field and click the details button.e. click next.note: if make a mistake, you can remove a field from the data

27、area by selecting the field and pressing the delete key.10. in the choose the table layout page, select stepped, check include subtotals, and click next.11. in the choose the table style page, select corporate and click next.12. in the completing the report wizard page, replace the default report na

28、me with sales by region and click finish. save and preview the report1. on the file menu, click save all.2. in the view pane, click preview.a list of product categories and subcategories with sales amounts for each territory will be displayed. deploy report to the report serveronce the development o

29、f the report or project is completed, it can be deployed from the .net development environment to the report server where it will be available to end users.1. in the solution explorer, right-click the sales reports project and then click properties.2. in the sales reports property pages dialog box,

30、under deployment, in the targetserverurl text box, type http:/localhost/reportserver3. click ok.4. on the build menu, click deploy solution.the deployment is complete when you see that build and deploy are successful. view the report1. on the windows desktop, click start, point to programs, and then

31、 click internet explorer.sql services reporting services includes report manager, a web application for viewing and administering reports.this is the home page for report manager. this page has two tabs, contents and properties. the default page is contents.2. click the sales reports link.3. click t

32、he sales by region link.4. view the report that you just deployed to the server. use the scroll bar to view sales for all product categories across the various sales territories. view sample reportsin addition to viewing your previously published report, you can view some sample reports that have be

33、en pre-formatted and deployed to the report server.1. to view the sample reports, on the upper-right hand corner of the screen, click the home link.2. click adventure works. in the folder, you see sample reports with descriptions of each reports content. 3. to browse each sample, click a report titl

34、e to launch the browser.4. to navigate between reports, click the adventure works link on the top left of the page under sql server reporting services.5. when you are finished browsing reports, on file menu, point to close to close internet explorer.at the end of the exercise, if you would like to l

35、ook at how these sample reports are defined, you can open the visual studio project in the c:labadventure works reports folder.exercise 2modifying report layoutin this exercise, you will use the report designer to update the layout of the tabular report that you created in the first exercise using t

36、he report designer. to modify the report you will:n manipulate the report layoutn format fieldsn add fields to a tablen add a calculated field to the data setestimated time: 10 minutes opening the report projectif you did not complete the previous exercise, you will need to start visual studio .net

37、and open the results of the previous step.1. click start and click microsoft visual studio .net 2003.visual studio .net provides the environment in which reporting services reports are designed.2. on the file menu, click open project, navigate to the c:labsex2sales reports directory, select the sale

38、s reports solution (the file without the .rptproj extension), and then click open. increase the size of the region text box1. select the text box below the report title.the properties window in the lower right corner of the screen will show that textbox2 is selected.2. expand the width of the select

39、ed text box to match the size of the title area by dragging the middle right resize handle. add a column to the tableyou will now add an additional column to the table.1. click on the any cell in the table. the row and column selectors will appear.2. right-click the gray header area at the top of th

40、e sales column header and click insert column to the right.at this point, you will have four columns in the table.3. select the cost field in the fields window in the lower left corner.4. drag the cost field into the bottom cell of the newly created column.the field will be added to the table detail

41、s as well as the name of the column will be added to the table header.5. select the cost field again. this time, drag the field into the empty middle cell of the newly created column.because this is a summary (group header) row, the sum() aggregate function will automatically be added to the field n

42、ame. add a calculated field now we would like to add a field to the report that does not come from the query. rather, it is calculated within the report. reporting services allows you to use the visual basic .net expression language to define calculated values within the report.1. right-click in the

43、 fields window and select add.2. in the add new field dialog, type margin into the alias text box.3. select the calculated field radio button.4. click the expression button.5. in the edit expression dialog, type =fields!sales.value- fields!cost.value in the expression text box.note: you can also use

44、 the fields tree on the left side of the dialog to access the names of query fields as well as global report properties.6. click ok.7. click ok in the add new field dialog. add the new calculated field to the tableusing the same method that you previously used to add a column, add a new column to th

45、e table to the right of the cost column. drag the margin field from the fields window into the detail row as well as the group header. format the currency fieldsyou can use .net formatting expressions to format text box values before they are displayed. 1. click on the middle cell in the sales colum

46、n of the table.2. hold down the shift key on the keyboard and click on the cell in the bottom right of the table. there will be a selection rectangle around the text boxes.note: you can also select multiple table cells by clicking and dragging to increase the selection area.3. the properties window

47、allows you to edit common properties of the selected items. in the properties window, find the format property. type c in the value column. this will use the default currency format. format the group header1. click on the any cell in the table. the row and column selectors will appear.2. left-click

48、the selector to the left of the group header row. this will select the header row.3. click on the background color button in the formatting toolbar. 4. select lightgrey in web tab of the choose color dialog. click ok.the row will now be displayed with a light grey background color.note: in addition

49、to the report formatting toolbar, you can also use the properties window to set style attributes. save and preview the report1. on the file menu, click save all.2. in the view pane, click preview.a list of product categories and subcategories with sales, cost, and margin amounts for each territory w

50、ill be displayed.you can also use the and buttons to page through the report to see other sales regions.exercise 3parameters and interactivityin this exercise, you will continue to update the tabular report that you created in the previous exercise to include more advanced report design features suc

51、h as parameters and interactivity.to modify the report you will:n add report parameters.n create and configure a data set.n add show / hide interactivity.estimated time: 15 minutes opening the report projectif you did not complete the previous exercise, you will need to start visual studio .net and

52、open the results of the previous step.1. click start and click microsoft visual studio .net 2003.visual studio .net provides the environment in which reporting services reports are designed.2. on the file menu, click open project, navigate to the c:labsex3sales reports directory, select the sales re

53、ports solution (the file without the .rptproj extension), and then click open. parameterize the queryparameters can be used to further restrict the reports data. for example, the sales managers want to view the sales for their specific territory. to meet each sales managers reporting needs a territo

54、ry will be specified at run time by using parameters. this will allow the same report to be customized for each sales manager.1. in the view pane, click the data icon.the data view provides the tools to set up the data set and create the query information you wish to use in the report. the view will

55、 contain the query that was previously specified in the report wizard.2. locate the grid pane in the middle of the data view. find the row with the column labeled group. select the string =north america in the criteria column.3. replace the string =north america with =territory.the report designer a

56、utomatically detects the new parameter in the query and creates a report parameter that will be supplied by the user and bound into the query. save and preview the report1. on the file menu, click save all.2. in the view pane, click the preview icon.3. you will now be prompted to type a value for th

57、e territory parameter. uncheck the null check box. type europe in the area text box and click the view report button.4. the report is executed using europe as the value of the parameter. create a new data seta report may contain multiple data sets (queries), each against a potentially different data source. in this step, you will create a data set that will provide the names of different geographical territories so a report viewer can select one instead of typing a value.1. in the view pane, clic