Abstract This document provides step-by-step instructions for building several types of reports using sql server 2008 Reporting Services applications, and sample data. The document focuses on the new sql server 2008 Report Builder 0 application


Download 207.93 Kb.
NameAbstract This document provides step-by-step instructions for building several types of reports using sql server 2008 Reporting Services applications, and sample data. The document focuses on the new sql server 2008 Report Builder 0 application
page1/9
A typeAbstract
  1   2   3   4   5   6   7   8   9
sql server brand.jpg

Authoring Reports Using Microsoft SQL Server 2008 Reporting Services Report Builder 2.0


Quickstart guide
Published: February 2009


Abstract

This document provides step-by-step instructions for building several types of reports using SQL Server 2008 Reporting Services applications, and sample data. The document focuses on the new SQL Server 2008 Report Builder 2.0 application.




Contents


Authoring Reports Using Microsoft SQL Server 2008 Reporting Services Report Builder 2.0 1

Introduction 4

Before You Begin 4

Lab Scenario 5

Exercise 1: Getting Started and Getting Familiar with Report Builder 5

1.On the Start menu, click Programs, then click Microsoft SQL Server 2008 Report Builder 2.0. 5

2.Click Report Builder 2.0. 5

Exercise 2: Pre-Requisite Exercise—Creating a Shared Data Source 9

1.Open Internet Explorer and type in the URL of the report manager of http://localhost/Reports (or your server URL) and press ENTER. 9

2.Click New Data Source in the Contents page of the Report Manager, as shown in the Figure 2. 9

9

3.On the New Data Source page, perform the steps: 9

In the Name field, enter LocalAdventureWorks. 9

For Data Source Type, select Microsoft SQL Server. 9

Enter the following in the Connection String. 9

Data Source=localhost;Initial Catalog=AdventureWorks2008 9

Near the bottom, click to select Windows Integrated Security. 9

4.Click Apply. 9

5.Minimize Internet Explorer to return to it later to preview reports from the server. 9

Exercise 3: Table and Matrix Wizard—Data Sources, Data Sets, and Building a Matrix 10

1.Open the Report Builder 2.0. 10

2.In the new blank report, click the Table or Matrix wizard link in the center of the screen. 10

10

3.At this point, you may briefly see the server dialog informing you the application is connecting to the server. The new Table or Matrix Wizard dialog appears. Select the existing data source connection LocalAdventureWorks, and click Next. 10

11

Figure 3: New Table or Matrix Wizard 11

1.Double-click the server name: http://localhost/reportserver 11

12

Figure 4: Browse Server to Data Source 12

2.Double-click the LocalAdventureWorks shared data source you previously created. 12

12

Figure 5: Select Your Data Source 12

3.Click Next to move to the Query Design step of the wizard. 12

Here, the data set is generated from a custom query that returns sales data, including ProductCategory, SubCategory, Product, and Year. 12

It follows that the next step in the wizard is to create a query (or a data set) for the matrix. 12

To streamline the process, perform the following procedure: 12

1.Copy and paste the query instead of building a query manually. 12

13

Figure 6: Query Designer 13

2.Click Edit as Text to switch the query designer to text mode. 13

3.Select all the query text below, copy it, and paste it into the query editor. 13

Figure 7: Text-based Query Design in the Wizard 14

4.Click Next to continue to arrange the fields. 14

In continuation with the Report Builder wizard, specify the fields and groupings in the following steps: 14

1.To arrange the fields (Column Groups, Row Groups, and Values), do the following: 14

a.Drag SalesAmount to the Values definition box. 14

b.Drag ProductCategory and SubCategory to the Row groups definition box. 15

c.Drag Region and then OrderYear to the Column groups definition box. 15

Figure 8: Arrange fields in the Wizard to Specify Groups and Values 15

2. Click Next to continue. 15

1.Select the visual layout. 15

Figure 9: Select the Visual Layout Type. 16

2.Click Next to proceed to the Style selection. 16

3.Select the style. 16

Figure 10: Select a Color Style. 17

4.Click Finish to complete the wizard and view your new report. 17

Figure 11: Completed Wizard 18

To run the report, perform the following procedure: 18

1.Click the Home tab, and click Run button or press F5. 19

2.Use the Expand and Collapse (+/-) functionality in the Accessories group to explore the report. 19

3.To switch back to design mode, Click Design or press F8. 20

4.To save your report to the server, do the following: 20

a.Click the Report Builder icon , then click Save As. 20

b.Double-click My Reports in the folder list. 20

c.Enter the file name SalesbyRegion_ex3.rdl. 20

d.Click Save. 20

20

a.Click Recent Sites and Servers in the left panel. 20

b.Double click http://localhost/reportserver. 20

c.Double-click My Reports. 21

If My Reports was not enabled on the server, you can save to the root of your report server. 21

d.Enter the file name SalesbyRegion.rdl. 21

e.Click Save. 21

21

Exercise 4: Enhancing Your Report with Richly-Formatted Text 22

1.Open the SalesbyRegion_ex3.rdl report created in the previous exercises, if it is not already open. 22

2.To set the sum fields to show currency format, do the following: 22

a.Click the upper-left sum field, the Sum(SalesAmount) under [OrderYear], 22

b.Hold down the SHIFT key, and click Sum(SalesAmount) in the lower-right corner to select all of the sum fields. 22

c.Click the currency format button on the ribbon. 22

22

You can also click and format each field individually. When you run the report, you will see the currency format. When in design mode, you can resize your columns wider so numbers do not wrap. 22

3.Click in the textbox at the top of the report showing Click to Add Title, and enter a title Sales by region. 23

4.Select the word Sales you just typed in, and use the Ribbon to format the font color to red. 23

5.Select the words by region and use the Ribbon to change the font size to 10. 23

6.Click between the words Sales and by and press ENTER. 23

23

To use placeholders, perform the following steps: 23

1.Click in the textbox in the lower-right corner of the page footer showing [&ExecutionTime]. 23

2.Type Executed: before the [&ExecutionTime] so the whole string is Executed: [&ExecutionTime]. 23

3.Expand the Built-in fields list in the Report Data window, and drag Page Number to the lower left corner of the report footer. 23

24

4.Click the right edge of the new textbox and resize the box to the right to make the textbox wider. You can also use SHIFT+ Right Arrow to resize the box. 24

24

5.In the text box, type of after the [&PageNumber] place holder, and from the Report data window, drag Total Pages to the end. 24

6.Select the text and make it bold. You can make the selected text made bold by pressing CTRL+B, similar to Microsoft Office Applications. 24

24

7.To run the report, click the Home tab, and click Run or press F5. 24

By default, the report layout appears in a small/collapsed size. If you expand several product categories (+), and switch to the layout mode, the page total reflects the increased number of pages. To change the layout, click the Print Layout button in the ribbon. 24

24

Click Design or press F8 to return to Design view. 24

Save your report to the server: To save your report on the server, perform the following steps: 25

1.Click the Report Builder icon, then select Save As. 25

2.Select the My Reports folder, if present, on your local server. 25

3.Enter the file name ‘SalesbyRegion_ex4.rdl’. 25

Exercise 5: View Your Report from the Server 26

1.Open Internet Explorer and type in the URL to the report manger of http://localhost/Reports, then press ENTER. 26

2.Click My Reports to open the folder. 26

3.Click the SalesbyRegion_ex4 report (or the name you saved it as) . 26

26

27

Exercise 6: Enrich Your Report Using Charts 28

1.Open the previously created report, SalesbyRegion4_ex.rdl. 28

2.To add a chart report item to the design surface below your existing Tablix, do the following: 28

a.Create some space at the bottom of your report by dragging the report body sizing handle down in the vertical ruler. This will create a blank space below your Tablix, and will be used to accommodate the chart. 28

28

3.In the ribbon, click the Insert tab, then select Insert chart under Chart. The mouse cursor is now in Insert mode. 28

4.Place the pointer to upper-left corner of the new chart, or drag to place and size the new chart. 28

5.Click immediately beneath the lower-left edge of the Tablix in the Report body. 29

29

Figure 17: Selecting the Chart Type 29

6.Select the default column chart in the upper left corner, then click OK. 30

30

Figure 18: Chart Report Item is Created 30

7.To put the chart into edit mode, double-click the chart. 31

Figure 19: Chart Showing Field Areas Where You Drag From the Report Data Window 31

8.Drag and drop fields from the Report Data window as follows: 31

9.To change the chart title, double-click the title and enter Yearly Sales by Region. 31

10.To change the vertical axis title, double-click the text box and enter Sales (1k). 31

11.To remove the horizontal axis title, right-click the axis and clear the Show Axis Title check box. 31

31

Figure 20: Turn Off the Horizontal Axis Title 31

32

Figure 21: Chart Design 32

12.To run your report, click the Home tab and click Run or press F5. 32

32

Figure 22: Previewing the chart 32

The result should resemble the chart above. Let's modify this chart further, and format the sales numbers on the left X-Axis for currency. 32

To format the Sales numbers in the X Axis for currency, perform the following steps: 32

1.Switch back to design mode by clicking Design or press F8. 33

2.Right-click the numbers of the sales at the X- Axis, then select Axis Properties…. 33

33

Figure 23: Value Axis properties Dialog 33

3.In the Value Axis Properties dialog box, select the Number pane and select the Currency category. 34

4.Set Decimal places to 0. 34

5.Select the Use 1000 separator check box. 34

6.Select the Show values in check box, then select Thousands from the drop-down list. 34

7.To run your report, click the Home tab, then click Run or press F5. 34

Figure 24: Previewing the Completed Chart 34

Save your report to the server: To save your report to the server, perform the following steps: 34

1.Click the Report Builder icon, then select Save As. 34

2.Select the My Reports folder, if present, on your local server. 34

3.Enter the file name ‘SalesbyRegion_ex4.rdl’. 34

Chart Wizard and Styles 34

The Chart Wizard enables you to easily create charts. The Chart wizard includes a list of pre-defined chart styles to select from, and it also guides you through creating data sets. 34

Figure 25: Sample of the Chart Styles Found in the Chart Wizard 35

Exercise 7: Enrich Your Data Visualization Using a Gauge 36

1.Open the report SalesbyRegion_Ex6.rdl created previously. 36

2.To add a new column to the right edge of the Tablix, right-click the header and select Insert Column and then click Right. 36

36

Figure 26: Insert a New Column 36

3.In the Insert ribbon, click Gauge and then click in the last cell of the newly added Total row of the ProductCategory group. 37

37

Figure 27: Insert a Gauge into the new column on the Total row 37

The Select Gauge Type dialog appears. 37

4.Select the first Linear type, Horizontal, then click OK. 37

Figure 28: Select Gauge Type 37

5.To adjust the size of the Tablix and Gauge, drag the row and column wider to view and to click on individual elements within the gauge, such as the rectangular pointer and the scale of numbers. 38

The Tablix looks as below. 38

Figure 29: Resize Gauge 38

6.To add the Data field in the Guage, click the Field Selector inside the Gauge and select SalesAmount. 38

7.Click the Gauge so field area is visible, then click the LinearPointer area to see the field selector. 38

8.To set the Scale Properties, right-click the scale and select Scale Properties…. 38

9.In the Linear Scale Properties dialog box, select the General category. 39

a.Set the Minimum scale to 1000000 (one million). 39

b.Set the Maximum scale to 80000000 (eighty million). 39

Figure 30: Linear Scale Properties—Setting the Min/Max 39

10.Select the Number category, then do the following: 40

a.In the Category list, select Currency. 40

b.Set Decimal places to 0. 40

c.Select the Show values in check box, then select Millions from the drop down list. 40

Figure 31: Currency Settings 40

11.Click OK. 41

12.To run your report, click the Run tab or press F5. 41

The rendered report will look similar to the second figure. 41

Figure 32: Previewing with the Gauge 41

1.Right-click the pointer, then select Pointer Properties. 42

2.In the Linear Pointer Properties dialog box, select the Pointer Options category. To enlarge the pointer for easy viewing, do the following: 42

b.Set the Length of the pointer to 30. 42

Figure 33: Resizing the Pointer 42

3.Select the Pointer Fill category and do the following: 43

a.Select the Solid option for Fill Style. 43

b.To select the color, click the function button at the Color drop-down list. 43

Figure 34: Pointer Fill Style and Color 43

To relate the color with the position of the guess, perform the following steps: 44

1.In the Expression dialog, enter a formula that supplies the following logic for pointer color: 44

a.Delete the default ‘White’ entry. 44

b.Copy and paste the following formula into the expression editor. 44

2.Click OK. 44

Figure 35: Expression Dialog 44

3.Run your report: Go to the ‘Home’ tab, then click the ‘Run’ button or press F5. 45

Figure 36: Previewing the Completed Gauge with Colored Pointers 45

Save your report to the server: To save your report on the server, perform the following steps: 45

1.Click the Report Builder icon and then select Save As. 45

2.Select the My Reports folder, if present, on your local server. 45

3.Enter the file name ‘SalesbyRegion_Ex7.rdl’. 45

Exercise 8: Modify the Tablix to Show Two Parallel Dynamic Groups 46

1.Open the report SalesbyRegion_Ex7.rdl you previously created. 46

2.Delete the OrderYear sub group. 46

Delete this group to add it as a second grouping at the end of the matrix, and not as a sub-grouping within the matrix. 46

3.To do this, select the drop-down menu to the right of the OrderYear sub-group name in the Grouping window, thenselect Delete Group. 46

47

Figure 37: Delete OrderYear group 47

4.Select Delete group and related rows and columns in the Delete Group dialog box. 47

47

Your Tablix will now resemble the following figure. 47

47

Figure 38: OrderYear Group Deleted 47

5.To add an adjacent group after Region, right-click the grouping window, select Add Group, then select Adjacent After. 47

47

Figure 39: Add Adjacent Group 47

6.Click OK. 48

7.Select Group By [OrderYear] in the Tablix group dialog box. 48

8.Right-click in the new [OrderYear] column and add SalesAmount. 48

48

Your Tablix will now resemble the following figure. 48

48

Figure 40: New Adjacent group 48

Note. 49

Changed from 49

49

TO 49

49

9.Set all three [Sum (SalesAmount)] cells in the new [OrderYear] column to currency format by clicking each cell and using the currency button on the Ribbon. 49

10.To add total columns for each of the two column groups using the grouping window, select Add Total in the drop-down for Region, then select After. Repeat this step for the new group. 49

49

Your Tablix will now resemble the following figure. 49

49

Figure 41: Design View of the Completed Tablix, with Parallel Dynamic Groups 49

11.To run your report, click the Home tab and click Run or press F5. 49

50

Figure 42: Previewing the Completed Tablix with Parallel Dynamic Groups 50

Save your report to the server: To save your report on the server, perform the following steps: 50

1.Click the Report Builder icon, then select Save As. 50

2.Select the My Reports folder, if present, on your local server. 50

3.Enter the file name SalesbyRegion_Ex8.rdl. 50

Exercise 9: Manual Creation of a Tablix to Match Exercise 3 (Wizard Tablix) 51

1.Insert a new tablix below the existing one. 51

2.Create some blank space below the existing Tablix. 51

3.From the Insert tab of the Ribbon, select Matrix, then select Insert Matrix. 51

4.Resize the new matrix below the existing one. 51

Your report in design mode will look similar to the following figure. 51

Figure 43: Empty New Tablix 51

5.From the Report Data window, do the following: 51

a.Drag the Region field to the Column Groups area of the Grouping window. 51

b.Drag the OrderYear to the Column Groups area of the Grouping window below Region. 52

6.From the Report Data window, do the following: 52

a.Drag the ProductCategory field to the Row Groups area of the Grouping window. 52

b.Drag the SubCategory field to the Row Groups area of the Grouping window, below ProductCategory. 52

7.From the Report Data window, drag SalesAmount to the Data cell at lower right of the Tablix. The new Tablix will look similar to the following figure. 52

8.To add the totals after all of the groups in the grouping window, click each of the four groups, select Add total, then select After. 52

9.To run your report, click the Home tab and click Run or press F5. 53

10.To hide the subgroups but allow the user to toggle them on, select Group Properties for Subcategory1 from the Grouping Window. 53

11.In the Group Properties dialog box, select the Visibility category. 53

a.Set When the report is initially run to Hide option. 53

b.Select the Display can be toggled by this report item check box, then select ProductCategory1. 53

c.Click OK. 53

Figure 44: Group Properties setting the toggle item. 54

12.Repeat the same changes to the OrderYear1 subgroup: 54

a.From the Grouping Window, select Group Properties for OrderYear1. 54

b.From the Group Properties dialog, select the ‘Visibility’ page 54

c.Set When the report is initially run to Hide. 54

d.Select the Display can be toggled by this report item check box and select Region1 from the drop-down list 54

e.Click OK 54

13.To run your report, click the Home tab and click the Run or press F5. 54

Figure 45: Completed Tablix. 55

Save your report to the server: To save your report on the server, perform the following steps: 55

1.Click the Report Builder icon, then select Save As. 55

2.Select the My Reports folder, if present, on your local server. 55

3.Enter the file name SalesbyRegion_ex8.rdl. 55

Exercise 10: Use Query, Parameter, and Filter to Limit the Tablix Data 56

1.Open the report “SalesbyRegion_Ex3.rdl” previously created in Exercise3. If you do not have it, rerun the wizard (Exercise3). 56

2.In the Report Data window, right-click the Data Source and select Add Dataset. 57

Figure 46: Adding a Dataset 57

3.In the Dataset Properties dialog box, select the Query category, and do the following: 57

a.Click Add to add a filter. 57

b.Enter Name as regionlist. 57

c.Set Data Source to LocalAdventureWorks (or whatever you have named your datasource) 57

d.Select the Text option for Query Type and enter the following query string in the Query field 57

Select Distinct Sales.SalesTerritory.Group AS Region from sales.SalesTerritory 57

57

Figure 47: Create New Query To Use Source The Parameter List 57

4.Click OK 57

1.In the Report Data window, right-click Parameter, then select Add Parameter. 58

58

Figure 48: Create a New Parameter 58

2.In the Report Parameters dialog box, select the General category, then do the following: 58

a.Click Add to add a filter. 58

b.Enter Name as whatregions. 58

c.Enter Prompt as whatregions. 58

d.Select Data type as Text, then select the Allow Multiple Values check box. 58

e.Select the Visible option for Select parameter visibility. 58

58

Figure 49: Parameter General Settings 58

3.Select the Available Values category, then do the following: 59

a.Select Get Values from query option. 59

b.Select the Dataset as regionlist. 59

c.Select Value field as Region. 59

d.Select Label Field to Regionv 59

4.Select Allow Multiple Values category. 59

59

Figure 50: Parameter Available Values Settings. 59

You should now see your parameter, as well as the new dataset listed in the Report Data window. 59

60

Figure 51: New Parameter and Data Set Listed in Report Data Window 60

1.Select the top edge of the Tablix, right-click it, and select Tablix Properties. 60

60

Figure 52: Open Tablix Properties 60

2.In the Tablix Properties dialog box, select the Filters category, then do the following: 61

a.Click Add to add a filter. 61

b.Set Expression to [Region] . 61

c.Set Operator to IN. 61

d.Set Value to [@whatregions] . 61

61

Figure 53: Tablix Filter Settings 61

3.Click OK. 61

1.To run the report, click the Home tab, then click the Run button or press F5. 61

2.Select Europe, then click the View Report on the right. 61

62

Figure 54: Selecting Parameter Values 62

Your report will look similar to the following figure, which only shows the Europe group. 62

3.Switch back to the Design view. 62

62

Figure 55: Tablix with the Filter Applied 62

4.Change your filtering by simply selecting different parameters and clicking View Report. Repeat this a few times and view the results. 62

Save your report to the server: To save your report on the server, perform the following steps: 62

1.Click the Report Builder icon , then select Save As. 62

2.Select the My Reports folder, if present, on your local server. 62

3.Enter the file name SalesbyRegion_ex9.rdl. 62

References and Where to Get Help 63

Information in this document, including URL and other Internet Web site references, is subject to change without notice. Unless otherwise noted, the example companies, organizations, products, domain names, e-mail addresses, logos, people, places, and events depicted herein are fictitious, and no association with any real company, organization, product, domain name, e-mail address, logo, person, place, or event is intended or should be inferred. Complying with all applicable 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 for any purpose, without the express written permission of Microsoft Corporation.

The names of manufacturers, products, or URLs are provided for informational purposes only and Microsoft makes no representations and warranties, either expressed, implied, or statutory, regarding these manufacturers or the use of the products with any Microsoft technologies. The inclusion of a manufacturer or product does not imply endorsement of Microsoft of the manufacturer or product. Links are provided to third party sites. Such sites are not under the control of Microsoft and Microsoft is not responsible for the contents of any linked site or any link contained in a linked site, or any changes or updates to such sites. Microsoft is not responsible for webcasting or any other form of transmission received from any linked site. Microsoft is providing these links to you only as a convenience, and the inclusion of any link does not imply endorsement of Microsoft of the site or the products contained therein.

Microsoft may have patents, patent applications, trademarks, 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.

Copyright © 2007 Microsoft Corporation. All rights reserved.

Microsoft registered trademarks or trademark(s) of Microsoft Corporation in the United States and/or other countries.

The names of actual companies and products mentioned herein may be the trademarks of their respective owners.

V1

1

Version 1.2
  1   2   3   4   5   6   7   8   9

Share in:

Related:

Abstract This document provides step-by-step instructions for building several types of reports using sql server 2008 Reporting Services applications, and sample data. The document focuses on the new sql server 2008 Report Builder 0 application iconMicrosoft sql server 2008 R2 Reporting Services Report Builder 0 Readme File

Abstract This document provides step-by-step instructions for building several types of reports using sql server 2008 Reporting Services applications, and sample data. The document focuses on the new sql server 2008 Report Builder 0 application iconSql server Technical Article Partitioned Table and Index Strategies Using sql server 2008

Abstract This document provides step-by-step instructions for building several types of reports using sql server 2008 Reporting Services applications, and sample data. The document focuses on the new sql server 2008 Report Builder 0 application iconHigh Availability and Disaster Recovery for Microsoft’s sap data...

Abstract This document provides step-by-step instructions for building several types of reports using sql server 2008 Reporting Services applications, and sample data. The document focuses on the new sql server 2008 Report Builder 0 application iconConsolidation Using sql server 2008

Abstract This document provides step-by-step instructions for building several types of reports using sql server 2008 Reporting Services applications, and sample data. The document focuses on the new sql server 2008 Report Builder 0 application iconSiebel Architecture 5 sql server 2008 Features 7

Abstract This document provides step-by-step instructions for building several types of reports using sql server 2008 Reporting Services applications, and sample data. The document focuses on the new sql server 2008 Report Builder 0 application iconGuide to Migrating from Oracle to sql server 2008

Abstract This document provides step-by-step instructions for building several types of reports using sql server 2008 Reporting Services applications, and sample data. The document focuses on the new sql server 2008 Report Builder 0 application iconDatabase Upgrade to sql server 2008, Tools and Approaches

Abstract This document provides step-by-step instructions for building several types of reports using sql server 2008 Reporting Services applications, and sample data. The document focuses on the new sql server 2008 Report Builder 0 application iconOptimal and Enterprise Ready: sql server 2008 with jd edwards, PeopleSoft, and Siebel

Abstract This document provides step-by-step instructions for building several types of reports using sql server 2008 Reporting Services applications, and sample data. The document focuses on the new sql server 2008 Report Builder 0 application iconInstalling BizTalk Server 2010 on Windows Server 2008 R2 and 2008...

Abstract This document provides step-by-step instructions for building several types of reports using sql server 2008 Reporting Services applications, and sample data. The document focuses on the new sql server 2008 Report Builder 0 application iconAnalysis Services rolap for sql server Data Warehouses




forms and shapes


When copying material provide a link © 2017
contacts
filling-form.info
search