Ms Sql Reporting Services Final2

Astek Mauritius

Reporting Services Tutorial

An Introduction to Microsoft SQL Server Reporting Services

Table of Contents

1. Introduction

1

2. Architecture

2

3. Reporting Services System Requirements and Installation

6

4. Programming Reporting Services

7

5. Report Manager

20

6. Parameterized Reports

25

7. Calling Stored Procedures from Reports

28

8. The Report Viewer Control

39

9. Using Charts in Microsoft SQL Server 2000

45

Reporting Services 10. Conclusion

76

11. References and Further Readings

77

Astek Mauritius

Reporting Services Tutorial

1. Introduction Business Intelligence is a thriving discipline in the marvelous era of computing we live in. It's the process of analyzing and exploring the information, trends and patterns hidden in data. BI escalates the business roadmap and facilitates business users to deliver better strategic solutions. This process helps enterprise decision makers by providing data models, statistical analysis, forecasting for comprehensive data analysis. Microsoft provides the most comprehensive integrated business intelligence, data mining, analysis and reporting solution with its state of the art Microsoft SQL Server Analysis services and Microsoft SQL Server reporting services. With the modern architecture design based on XML web services, SQL Server Reporting Services provides highly flexible, scalable, customizable and high performance reporting solution in a cost effective way. This is a developer's introduction to SQL server reporting services, its functionality and how it can be used to provide state of the art reporting solutions. SQL Server Reporting Services, due to its robust but user friendly architecture, is an obvious choice for Enterprise or in-house reporting, for product management, sales, and human resource and finance departments. Its flexibility makes it an ideal for usage in applications as well. Reporting Services offer various delivery methods, from ftp to email and it provides various rendering formats therefore it makes adds easiness to business to business reporting. Similarly because of its flexibility and industry standard security model, it makes extranet and secure internet reporting easily achievable.

1

Astek Mauritius

Reporting Services Tutorial

2. Architecture The reporting system comprises of two main components namely: 1. Report Server & Report Server Database Report Server is an integrated web service which controls report generation and management. Report Server database is a SQL server database which is used as data dictionary about reports (catalogue, groups) and caching. SQL server agent is used for scheduling the reports. 2. Report Manager An ASP.NET based Web interface for managing the reports, setting security and user permissions.

Report Designer is considered as a part of reporting services in Microsoft documentation but because RDL (Report definition language) is an XML based open standard, any vendor can implement it and therefore a single tool can't be categorized as report designer. However, Microsoft provides a graphical report authoring tool with Visual Studio.NET 2003 for creating reports which automatically creates RDL markup at the backend. Reporting services were designed with concept of disparate data sources in mind. A single report can retrieve data from multiple heterogeneous databases and render it to make it look like it's from a single source. It provides built-in windows authentication security however one can write custom security wrapper to fit particular enterprise needs. With SQL Server reporting services, multiple delivery methods and formats work like a charm. You design a generic report and reporting server takes care of exporting it into HTML, Excel, PDF, war (web archive).

2

Astek Mauritius

Reporting Services Tutorial

Figure 2.1: Reporting Services delivery formats

Following diagram schematically explains Reporting services architecture

Figure 2.2: Reporting Services system architecture diagram Being a developer, Reporting Services API is another reason of making a move towards SQL server reporting Services. Beside the code segments which can be written within a report in VB.NET, these API makes it more programmable.

3

Astek Mauritius

Reporting Services Tutorial

The application program interface can be classified into five categories: · · · · · ·

Data processing extension application programming interface (API) Delivery extension API Rendering extension API Security extension API Web service API Windows Management Instrumentation (WMI) configuration API

Interactive interfaces are another salient feature of SQL Server Reporting Services. Reports designed in SQL server reporting services supports charts, document map, freeform, cross tab matrix, sub reports and tables. Reports can also be parameterized and event driven (supports actions). Management is one of the most important parts in any reporting system; SQL server reporting services has it all planned. It manages jobs from a user friendly console, provides personalization "my reports", tracks report history, manage shared data sources, provide search, subscription and snapshot features with shared subscription from one stop shop, the management console. Reporting Services supports report caching and stores reports execution data in execution logs. Following diagram explains the reporting services architecture in further technical detail.

Figure 2.3: Reporting Services architecture diagram

4

Astek Mauritius

Reporting Services Tutorial

As defined in the diagram above, the process of report generation and publishing consists of the following main steps. 1. Reporting server engine (Report Processor) receives the request for a particular report. A request includes parameters and formatting instructions. 2. Report Processor retrieves the report definition on the basis of request. 3. For the corresponding RDL, the report processor then retrieves the report data for specified data sources. 4. Report Processor performs transformation on reporting data and sends the document data along with schema to rendering engine (rendering extension). 5. The extension publishes the final rendered report.

5

Astek Mauritius

Reporting Services Tutorial

3. Reporting Services System Requirements and Installation The following steps are basics of how reporting services work. The extensions (data processing extensions, rendering extensions etc) can be custom built and wrap around the existing set of API to provide extended functionality. Reporting Services installation is fairly easy providing that you've all the required components installed. Along with a good system, you need to have a local or remote instance of SQL server database with SP3a or later. The SQL server reporting services works as a part of SQL server license. Also if you don't have updated service pack 3a, setup won't continue. Edition of SQL server should also correspond to that of reporting services i.e. Enterprise Edition reporting services along with SQL server Enterprise edition, professional along with professional and so forth. Report server can be installed on same as well as different SQL server machine. For heavy enterprise reporting processing demands, its most probably recommended to keep the machines apart. Reporting services need .NET framework 1.1, if it’s not installed, setup will install it for you. ASP.NET 1.1 is also required to be installed and registered with web server. ASPNET_Regiis utility is mostly a handy solution for registering ASP.NET with web server. Visual Studio.NET 2003 is additional software required for report designing. Any edition of visual Studio.NET or individual copies of Visual C#, VB.NET or Visual C++ can be used to install report designer. Once all the System Check requirements are fulfilled, Reporting Services installation wizard creates all the virtual directories, set the permissions and provide you the interface to start working with reporting services. To access Reporting services "Report Manager", you'd require Internet explorer 6.0 or above on your system. You may also want to install the Adventure Works Database to try and study the sample reports provided with reporting services.

6

Astek Mauritius

Reporting Services Tutorial

4. Programming Reporting Services It's time to create our first report in SQL Server Reporting Services, you'll require Northwind database to be able to continue with this exercise. This is a step by step introduction to create a report. Step 1: Start a Business Intelligence Project in Visual Studio.NET How to: 1. Select File à New from the main menu. 2. Click Project from the cascading menu(Figure 4.1)

Figure 4.1: Creating new project You can either use Wizard or Report Project to create a report. We will be using Report Wizard here.

Figure 4.2: Selecting project type 7

Astek Mauritius

Reporting Services Tutorial

Step 2: Create a data source connection string for Northwind database in SQL Server. Method 1: Individual data sources Create a data source for report to retrieve data from Northwind database. The database shown in the example is local but you can use a remote connection for it as well. How to: 1. 2. 3. 4.

Click Edit à Dialog box(Data Link Properties) is displayed Select Server name from list Choose authentication method(here Windows NT Integrated Security) Select database name(here Northwind)

Figure 4.3: Conection to data source Method 2: Shared data source In Solution Explorer, you should see that under the project are two folders: Shared Data Sources and Reports(). We will create a report using the Pubs database; right-click on the Shared Data Sources folder and select Add New Data Source.

8

Astek Mauritius

Reporting Services Tutorial

Figure 4.4: Adding a new data source In the Data Link Properties dialog, enter the name of your SQL Server. For my case, my SQL Server is on my local machine and hence I type "(local)" and select the Pubs database (see Figure 4.5). Click OK.

Figure 4.5: Selecting the database server

9

Astek Mauritius

Reporting Services Tutorial

Figure 4.6: Adding a new report You will now see the wizard. Select the shared data source you configured in the earlier step. Click Next to proceed.

Figure 4.7: Selecting the data source to use for the report

10

Astek Mauritius

Reporting Services Tutorial

Step 3: Design the query to display the data in report. Write or (design in the Query Builder) the SQL query to retrieve data from database into the report. I'm selecting all records from table "orders" for display in the report.

Figure 4.8: Designing the Query string of the report

11

Astek Mauritius

Reporting Services Tutorial

Step 4: Select Report Type & Style This step is to choose the report type (tabular or Matrix) and table style for the report.

Figure 4.9: Setting up the report type and style

Step 5: Select fields to be displayed in the report. This step is to choose the fields (from the list of Available Fields) we want to display in the report.

Figure 4.10: Selecting fields to be displayed 12

Astek Mauritius

Reporting Services Tutorial

Step 6: Select Deployment Location and Review the Report This step is to choose the deployment location i.e. the web server location (default is http://localhost/ReportServer) f o r report. Finally you can review all your selections and can go back and change them if needed. P.S: This step is not displayed if the Deployment Location has already been set by default.

Figure 4.11: Setting up the Deployment Location

13

Astek Mauritius

Reporting Services Tutorial

Figure 4.12: Completing the Report wizard And, finally here is our rendered "Orders report". Within five wizard steps, a formatted data report is created which can be exported in a wide variety of formats, scheduled to be emailed or put on ftp and can be accessed over HTTP like any other webpage and supports built-in security.

14

Astek Mauritius

Reporting Services Tutorial

Figure 4.13: Orders Report in Report Designer Preview mode Report designer has three different views for a report; Data view, Layout view and Preview. Data view provides the database selection and query writing support for reports. So if someone didn't want to use the wizard interface to create and publish reports, he can use the data view to create datasets and define custom queries.

Figure 4.14: Orders Report in Data view

15

Astek Mauritius

Reporting Services Tutorial

Similarly, layout view provides the interface to a set of tools for creating the elements on a report. Fields can be dragged and dropped from Fields toolbar in layout view and it will populate the table or matrix.

Figure 4.15: Report Designer Toolbox

Figure 4.16: Orders Report in Layout View Expression editor is another useful tool provided with Report Manager. As an enhancement to this basic Orders report, I'm adding a header to it which will show the report generation time, name and number of pages in this report. This metadata is provided as global constants which are added into text boxes in the header pane. Other useful global variables for instance Execution time, Report Server URL etc can be seen in the screenshot below.

16

Astek Mauritius

Reporting Services Tutorial

Figure 4.17: Report Designer Expression Editor. However we've seen the report in Preview mode, its time to execute and view it in browser. After pressing F5 (or directly accessing the URI of report server), you'll see an animated gif saying report is being generated. The following report shows the time generated and report name along with pages as defined above using global variables. As you can see in the query string below, it defines the set of parameters from the report. These parameters serve a useful purpose as you'll see further.

Figure 4.18: Order Report rendered in IE

17

Astek Mauritius

Reporting Services Tutorial

Figure 4.19: Choosing different formats to render the report

18

Astek Mauritius

Reporting Services Tutorial

5. Report Manager Report Manager is the interface provided by Reporting server to manage reports. Microsoft is promoting web based management consoles as you can see ASP.NET 2.0 is also equipped with a website administration and management console. Using the Report Manager interface, depending on their access privileges, one can upload report files (RDL), set subscriptions to receive reports, create a new data source and add / modify user report access roles.

Figure 5.1: Interface for Report Manager

As shown in Figure 5.1 above, OrdersReport is published by Report Designer in Visual Studio.NET for us. One can also manually upload an RDL file and it will be shown in Report Manager. Upon clicking the Orders Report report manager executes and show the output of report. It provides us four tabs with a report i.e. View, Properties, History and subscription. One can perform a wide variety of tasks with these reporting tabs.

19

Astek Mauritius

Reporting Services Tutorial

Figure 5.2: Interface for Report Manager with different tabs For instance you can setup a new role for an NT group of a single user. There are four roles in general; Browser, Content Manager, My Reports and Publisher as shown in Figure 5.3 below.

Figure 5.3: Setting up roles for a Report From the history tab, one can set the execution snap shots and further history settings.

20

Astek Mauritius

Reporting Services Tutorial

Figure 5.4: History and Snapshots Settings Probably the most important tab in report property is Data Source tag. It helps setting up the data source used by the report and impersonation settings as well. Connection type, string, credentials and further details required to execute a report are stored via this interface.

21

Astek Mauritius

Reporting Services Tutorial

Figure 5.5: Setting up Data Source RDL stands for Report Definition Language which is an XML based reporting definition initiative by Microsoft to share the different reporting formats. As defined by Microsoft: Report Definition Language (RDL) is an XML-based schema for defining reports. The goal of RDL is to promote the interoperability of commercial reporting products by defining a common schema that allows interchange of report definitions. To encourage interoperability, RDL includes the notion of compliance levels that products may choose to support. One can manually upload the RDL file using the report manager's general tab and then by update link in the properties as shown in the figure below.

22

Astek Mauritius

Reporting Services Tutorial

Figure 5.6: Manually uploading an RDL file Like code-behind files, the report manager in VS.NET also supports the editing of RDL file generated during the visual exercise performed above. In the figure below, you'd see the query, fields and data source selected in the XML file.

23

Astek Mauritius

Reporting Services Tutorial

6. Parameterized Reports Not all reports are static, most of the enterprise reports are data driven or parameterized. In the following section I'm adding two variables, startdate and enddate in the Orders Report. These arguments will be used to filter the data being queried from orders table. How to: 1. 2. 3. 4. 5.

Click on Report à Report Parameters Click Add button Set values for Name, Data type, Prompt Repeat Step1-3 for other parameters Click Ok

Figure 6.1: Adding Report Parameters The parameters are regarded as query parameters and as you can see in the query window below; these parameters are passed in the SQL query string.

24

Astek Mauritius

Reporting Services Tutorial

How to: 1. 2. 3. 4. 5.

Add new Report from Solution Explorer Add “Orders” table in Data view Create SQL query string – include parameters Run SQL Query Set Parameter Values when prompted(Fig Param)

Figure 6.2: Defining Query Parameters Upon execution, the Report Designer preview asks for parameters values. When executing from a browser, it shows the parameters toolbar with default values in it. You can change the values in the parameters toolbar. Parameters can be set to null, defaulted through report manager and can also be data driven.

25

Astek Mauritius

Reporting Services Tutorial

Figure 6.3: Result generated for Report Parameters

26

Astek Mauritius

Reporting Services Tutorial

7. Calling Stored Procedures from Reports If your enterprise reporting system policy dictates business logic to be encapsulated in stored procedures, reporting services are there to rescue you again. SQL server stored procedure provides an efficient way of processing compiled SQL statements and can be used by reporting services to build reports on top of their result set. In the following two examples, we'll be using stored procedures from northwind database and display their result in reports. The dataset creation process is similar to one specified above, however this time command type is selected as stored procedure. Example 1: Using Stored Procedure Only This first example is simpler; it uses the stored procedure "Ten Most Expensive Products" and use it as data source for its tabular data binding. The process of using stored procedure as data source can be seen below. How to: 1. 2. 3. 4. 5. 6. 7.

Add new Report(StoredProcedure.rdl) from Solution Explorer Create new dataset(Northwind) Click on “Edit Selected Dataset”(as shown in figure 7.1 below) Dataset Screen will popup Set Command type as “StoredProcedure” Insert your StoredProcedure in Query string Click Run(!) to test your store procedure

27

Astek Mauritius

Reporting Services Tutorial

Figure 7.1: Creating a data source from stored procedure via data tab in reporting services IDE for visual studio.NET This binding exposes collection of two fields (Ten Most Expensive Products, UnitPrice) which can be used as fields in the report table. After adding these fields in the recurring row, I've set the headings in header and we are all set to preview the report. How to: 1. 2. 3. 4.

Drag & Drop a table from toolbox into the layout window Drag & Drop required fields into the report table Headers and Expressions will be displayed automatically Click Preview to display the list

28

Astek Mauritius

Reporting Services Tutorial

Figure7.2: Adding the fields in report table. Upon execution, the report will look like the following.

Figure 7.3: Report preview which uses Ten Most Expensive Products as data source. Example 2: Using both Stored Procedure and SQL Second example is a bit complex as it uses stored procedure as well as SQL to retrieve the parameters for combo box. The intent of this report is to provide sale by the category of products which includes beverages, produce, seafood, etc. Every product is associated with a category and the stored procedure "SalesByCategory" takes category as input and provide the sales breakdown of products in that category. However, to achieve this, we would need to display the list of categories in the report. Here is the step by step diagrammatic flow of how to achieve this goal. 29

Astek Mauritius

Reporting Services Tutorial

How to: 1. Follow same steps as in Example 1 2. StoredProcedure à SalesByCategory

Figure 7.4: Creating a stored procedure based data set. First, as its name depicts the sales by category stored procedure provides the sales history on the basis of product category. To provide interactivity in the report, we need to acquire the data driven parameters i.e. list of categories from database. This can easily be achieved from report parameters window as shown in figure 7 . 5 . The report is reading category name, order year and their corresponding values from database as parameters. How to: 1. 2. 3. 4.

Create new dataset à Categories Follow same steps as in Example 1 Command type à Text Input Query string

30

Astek Mauritius

Reporting Services Tutorial

Figure 7.5: Report Parameters being retrieved from database (step 1) How to: 8. Click Report à Report Parameters from menu 8. Set the values(Available+Default) for each report parameter 8. Specify whether from query or non-queried a. If Non-queriedà Enter values manually(Fig 7.6) b. If From query à Select Dataset,Value field, Label field(Fig 7.7)

31

Astek Mauritius

Reporting Services Tutorial

Figure 7.6: Assigning default Report Parameters.

32

Astek Mauritius

Reporting Services Tutorial

Figure 7.7: Assigning the corresponding value fields for report parameters. Finally the report could be seen as follows. The Category name and order are drop down lists for selection and the report takes these parameters and pass them on the salesbycategory stored procedure for the required result set.

33

Astek Mauritius

Reporting Services Tutorial

Figure 7.8: Report preview; combo boxes for category details This whole procedure as demonstrated with GUI above can be represented in RDL as follows. 1a755f4d-006e-42ce-804b852dc13c6840 SQL initial catalog=Northwind Listing 7.1: The RDL segment for data source. Notice the data source id as guid to uniquely identify the data source 34

Astek Mauritius

Reporting Services Tutorial

ProductName System.String TotalPurchase System.Decimal Northwind StoredProcedure SalesByCategory =Parameters!CategoryName.Value =Parameters!OrdYear.Value true Listing 7.2: The RDL segment for dataset, query parameters are specified in this segment. CategoryID System.Int32 CategoryName System.String Description System.String Picture System.Byte[] 35

Astek Mauritius

Reporting Services Tutorial

Northwind SELECT * FROM CATEGORIES true Listing 7.3: The RDL segment for retrieving parameters. It includes the Command text and field definitions. String Categories CategoryName CategoryName Categories CategoryName CategoryName String 1998 OrdYear <ParameterValues> <ParameterValue> 1996 <ParameterValue> 1997 <ParameterValue> 1998 36

Astek Mauritius

Reporting Services Tutorial

Listing 7.4: The RDL segment for fixed Parameters. It specifies the label, data value data set reference along with parameter (Ordyear) values and default values. Reporting services opens up a wide arena for you for data binding. The above examples demonstrate how we can use stored procedures in reports; however it’s your choice to use dynamic SQL, business objects (custom assemblies), stored procedures or any other custom data source for your report.

37

Astek Mauritius

Reporting Services Tutorial

8. The Report Viewer Control The report viewer control is an excellent intrinsic tool provided to facilitate report viewing in hosted client web or desktop application. This control makes URL based access a breeze and reporting integration to any existing .NET app a charm. This control comes with code both in VB.NET and C#. This control is available %System Root%\Program Files\Microsoft SQL Server\MSSQL\Reporting Services\Samples\Applications\ReportViewer\cs.

You may want to build the solution to incorporate the assembly in your web / desktop application project. In rest of this section, I'll demonstrate how you can use this control to create a sample web application which supports reporting via URL access. How to: Part 1: Add new web form (Fig 8.1-8.2) 1. 2. 3. 4.

Add new project to existing solution Select Project Types à Visual C# Project Select Templates à ASP.NET Web Application Set the location à http://localhost/ReportViewer

Figure 8.1: Adding web project to existing solution 5. Add two calendar controls in the web form

38

Astek Mauritius

Reporting Services Tutorial

Figure 8.2: Adding the Calendar controls in the web form.

Part 2: Add Report Viewer Control in web form(Fig 8.3-8.7) 6. Go to tools à Add/Remove Toolbox Items 7. In the Customize Toolbar dialog box click the .NET Framework Components tab 8. Browse to the location specified above and select ReportViewer.dll 9. Report Viewer component would be added to VS.NET toolbox 10. Drag & Drop Report Viewer control into your web form. 11. Set the ReportPath to the required report

39

Astek Mauritius

Reporting Services Tutorial

Figure 8.3: Adding the Report Viewer component to VS.NET toolbox.

Figure 8.4: Adding the report viewer control in the web form. This is a simple web application which uses two calendar controls to select starting and ending date for orders report. The purpose of this example is to 40

Astek Mauritius

Reporting Services Tutorial

demonstrate how easily reports can be embedded in a web application. It also shows that reporting services functionality can easily be extended by traditional applications and their controls.

Figure 8.5: Showing the Code and Design Windows With couple of lines of code to pass the orders details parameters to the Report viewer control, the following report can be achieved.

41

Astek Mauritius

Reporting Services Tutorial

Figure 8.6: Report rendered by using report viewer control. With just modifying the rendering format to PDF, I can get the report in PDF within the report viewer control. This makes it a very handy choice for showing supporting reports and business intelligence associations within interactive web and desktop applications.

42

Astek Mauritius

Reporting Services Tutorial

Figure 8.7: Report rendered as PDF by using the URL access and report viewer control.

43

Astek Mauritius

Reporting Services Tutorial

9. Using Charts in Microsoft SQL Server 2000 Reporting Services A chart is a data region that displays a graphical representation of the data in a report. You can add charts of different types and specify values and category and series groups. You can also change the style of the chart to include different colors, symbols, and 3D effects. Chart Data Data for charts in Reporting Services is organized into three areas: values, category groups, and series groups. Understanding the following terms is a prerequisite for the creation of any type of charts. Values When you define a chart, you add at least one value series to the chart. Values determine the size of the chart element for each category group. For example, values determine the height of a column in a column chart and the size of a slice in a pie chart. Value series are static. If you define a single value series and no series groups, a single chart element is displayed for each category group. For example, a simple column chart with one value series displays a single column for each category group. If you define multiple values, the chart will display a chart element for each value series. If there are multiple value series, the chart legend displays the name of each value series. In most charts, you will group data by category. In this case, you must use an aggregate expression for the value expressions in the chart. You do not need an aggregate expression if you are not grouping data, that is, there is one category value for each value in the dataset. However, you do need an aggregate expression if you are using series groups. Category Groups Use category groups to group data. Categories provide the labels for chart elements. For example, in a column chart, category labels are placed on the xaxis of the chart, one for each set of columns. You can nest category groups. When you define multiple category groups, each category is nested within another category. For example, in a column chart that displays products by model, the first category group would be model, and the second category group would be product. The column chart would display groupings of products by model on the x-axis.

44

Astek Mauritius

Reporting Services Tutorial

Series Groups Series groups are optional. You can use series to add an additional dimension of data to a report. For example, in a column chart that displays sales by product, you can add a series group to display data by year for each. Series groups’ labels are placed in the legend of the chart. Series groups are dynamic. Using a series group results in one chart element for each value in the group. When combined with categories, this can result in a large number of chart elements. In a simple chart with a single value series and no series groups, the chart displays one chart element (for example, a column) for each category. In a complex chart that uses category groups with series groups, the chart displays a chart element for each series group for each category. Creating a Pie Chart From scratch We will begin our exploration of chart reports with an examination of the humble pie chart. While virtually all of us have interacted with these kinds of charts before, the pie chart is a good place to begin a review of the chart types, because it contains many of the basic features common to most chart types. In this session we will: · · · · · · · ·

Create a chart report in Report Designer; Create an underlying dataset; Locate a Chart Item on the new report; Populate the chart item with the required data. Practice the use of the Data Label property; Modify the palette for the chart report we create; Examine other properties we can select for the pie chart. Preview the report to verify its operation.

Objective and Business Scenario In the following sections, we will perform the steps required to create a pie chart report to meet a business need as expressed by a hypothetical group of organizational information consumers. We will base our report datasets on the AdventureWorks2000 sample OLTP database that accompanies the installation of Reporting Services. For purposes of our practice procedure, we will assume that information consumers within the Purchasing department of the AdventureWorks2000 organization have expressed the need for a pie chart report. The consumers have stated that they intend to use it within various other Microsoft Office applications, including PowerPoint presentations, Word documents, and, ultimately, within other reports. They need a report that presents the concentration of our organizational vendors, by state, for numerous analytical 45

Astek Mauritius

Reporting Services Tutorial

ventures. To begin, however, they are interested in only three states: Washington, California, and Oregon. For purposes of this exercise, we will create a Reporting Services project within the Visual Studio.Net 2003 Report Designer environment, within which we will work primarily with a Chart data region. Hands-On Procedure Step 1: 1. Select File à New from the main menu. 2. Click Project from the cascading menu, as shown in Figure 9.1

Figure 9.1: Creating new project

The New Project dialog appears. We note that Business Intelligence Projects appears in the Project Types tree, indicating an installation of Reporting Services (the folder was added by the installation of Reporting Services, as it established the Report Designer in Visual Studio .NET).

46

Astek Mauritius

Reporting Services Tutorial

Figure 9.2: Setting name and location of new report project 3. Click Business Intelligence Projects in the Project Types tree. 4. Click Report Project in the Templates list. 5. Navigate to a location in which to place the Report Project files. 6. Type the following into the Name box: AstekChart. Step 2: Create a Blank Report Let's begin by creating a blank report. 1. Right-click the Reports folder in Solutions Explorer. 2. Select Add from the context menu that appears. 3. Click Add New Item from the cascading menu, as shown in Figure 9.3

47

Astek Mauritius

Reporting Services Tutorial

Figure 9.3: Adding new item to our report project 4. Click Report in the Add New Item dialog. 5. Type the following into the Name box: ChartReport. The Add New Item dialog appears, as shown in Figure 9.3

48

Astek Mauritius

Reporting Services Tutorial

Figure 9.4: Setting name of the report 6. Click the Open button at the bottom of the Add New Item dialog. The design environment opens. We see the Data, Layout a n d Preview tabs appear in the Report Designer (I have docked many of my toolbars in places I find convenient, and so your environment may differ somewhat). The report has opened in Data View, as shown in Figure 9.5.

Figure 9.5: Setting name of the report

49

Astek Mauritius

Reporting Services Tutorial

Step 3: Set up a Data Connection and Create a Dataset Our next step is to set up a Data Connection. Reporting Services can connect with, and create the datasets it needs from, virtually any ODBC o r OLE DBcompliant data source. Let's set up a Connection, and create a Dataset within our practice example. 1. Select New Dataset in the Dataset selector at the top of the Data tab, as depicted in Figure 9.6

Figure 9.6: Creating a new Dataset

As soon as we click the New Dataset selection, the Data Link Properties dialog box appears, defaulted to the Connection tab. 2. Type the name of the computer housing the targeted OLTP database, AdventureWorks2000. 3. Select the radio button to the left of the authentication option that is appropriate for your environment. 4. Select AdventureWorks2000 within the Select the database on the server selector. The settings on the Connection tab of the Data Link Properties dialog should resemble those shown in Figure 9.7.

50

Astek Mauritius

Reporting Services Tutorial

Figure 9.7: Connecting to the database

5. Click the Test Connection button to verify connectivity to the data source. We receive a message box, indicating a successful test connection, as shown in Figure 9.8.

Figure 9.8: Message box indicating a successful test connection 6. Click OK to accept the settings we have made, and to close the Data Link Properties dialog. 51

Astek Mauritius

Reporting Services Tutorial

Report Designer next presents us with the dataset design tool, based upon our newly connected source. We are immediately positioned to design our query, which brings us to the next step. 7. Click the ellipses ("...") button to the right of the default dataset name of AdventureWorks2000, which appears in the Dataset selector, as shown in Figure 9.9.

Figure 9.9: Data tab of report selected 8. Enter VendorStateAllocation for the name of the Dataset dialog box, replacing the default name of AdventureWorks2000. (AdventureWorks2000 remains selected for the data source by default.) The Dataset dialog appears as shown in Figure 9.10.

Figure 9.10: Modification of the dataset name

52

Astek Mauritius

Reporting Services Tutorial

9. Click OK to accept the settings and return to the Data tab, which displays in the Generic Query Designer. 10. Type the following into the SQL pane: SELECT Vendor.VendorID AS VendorID, StateProvince.StateProvinceCode AS StateCode FROM Address INNER JOIN VendorAddress ON Address.AddressID = VendorAddress.AddressID INNER JOIN Vendor ON VendorAddress.VendorID = Vendor.VendorID INNER JOIN StateProvince ON Address.StateProvinceID = StateProvince.StateProvinceID WHERE (StateProvince.StateProvinceCode IN( 'WA', 'CA', 'OR')) Click the Run button (shown in Figure 9.11, at the top of the query we have constructed) to ascertain that the syntax is correct.

53

Astek Mauritius

Reporting Services Tutorial

Figure 9.11: Verifying the syntax of the query As we can see, the resulting dataset contains a simple list of Vendor IDs, together with the respective States in which they are located. We will rely upon this dataset to populate our pie chart in the next section. The VendorIDs will be counted to generate number of vendors in each State (or Category, in chart property parlance), as we shall see. Step 4: Locate the Pie Chart Item on the Blank Report The process of building a chart report consists of dragging the chart item onto the Layout tab, and adjusting it, while setting properties as appropriate to meet the report specifications. 1. Click the Layout tab to switch to the Layout view. 2. Drag the report edges to comfortably fill the screen area. 3. Select View --> Toolbox (as shown in Figure 9.12), from the main menu to place the Toolbox within easy reach (if it already appears, simply disregard this step).

54

Astek Mauritius

Reporting Services Tutorial

Figure 9.12: Selecting the toolbox The toolbox window should appear similar to that shown in Figure 9.13. Mine is pinned to the upper left corner of the design environment, where I find it most convenient. This is, of course, subject to your own choices.

55

Astek Mauritius

Reporting Services Tutorial

Figure 9.13: Snapshot of the toolbox 4. Click the Chart button (at the bottom of the Toolbox pane). 5. Place the mouse cursor over the upper left corner of the report layout. The cursor becomes a small chart icon in combination with crosshairs when held above the layout. This indicates that we can click to "anchor" the point, from which we wish to draw the box that the chart will inhabit. 6. Starting in the upper left corner of the layout body, click, and then, holding the mouse button down, drag to create a box that covers the report layout. The layout should have this box appearing at its perimeter. 7. Release the mouse to drop the chart item. The chart item appears, in its generic manifestation, as shown in Figure 9.14. Because the chart is the only item we intend to place in the report, it should extend almost completely over the report layout.

56

Astek Mauritius

Reporting Services Tutorial

Figure 9.14: Snapshot of the chart without data

Clicking again on the border will also allow you to expand / contract the chart shape. (The nuances are easy to learn with a little practice.) Double-clicking the chart item will make the "drop regions," seen above, appear. The chart item is now in place, and we are ready to specify its "pie" nature, as well as to populate it with the dataset that we have created. Step 5: Populate the Pie Chart item to meet the Business Requirements Our next steps focus upon simply dragging fields from the Fields window. Ensure that the Fields window appears either fixed in place or as a dynamic tab. 1. Drag the VendorID field from the Fields window, dropping it on Drop Data Fields Here section of the generic chart item on the Layout tab. 2. Drag the StateCode field and drop it on the area of the chart item marked Drop Category Fields Here.

57

Astek Mauritius

Reporting Services Tutorial

Figure 9.15: Dragging the required fields to populate the chart with data 3. Right-click the chart. 4. Select Properties from the context menu that appears. The Chart Properties dialog box appears defaulted to the General tab. 5. Type the following into the Title box on the General tab: Vendors by State 6. Click the Style button, which appears to the right of the Title box. The Style Properties dialog box appears. 7. Make the settings, listed in table below, within the Style Properties dialog box: Property Family Size Style Weight Color Decoration

Setting Verdana 14pt Normal Bold Dark Green None 58

Astek Mauritius

Reporting Services Tutorial

Figure 9.16: Style properties window 8. Click OK to accept changes and to exit the Style Properties dialog box. 9. using the Palette dropdown selector, choose Semi-Transparent. 10. Select Pie under Chart Type in the lower left corner of the General tab. 11. Ensure that the leftmost of the two Chart sub-types is selected. 12. Click the Data tab to select it. 13. Select VendorStateAllocation in the Dataset name box. 14. Highlighting [Value], click Edit, to the right of the Values box. The Edit Chart Value dialog box appears. 15. Click the Point Labels tab to select it. 16. Click, to place a checkmark, in the checkbox to the left of Show point labels. 17. Click the Expression to the immediate right of the Data label selector box. The Edit Expression dialog box appears. 18. Type the following into the Expression area: =Fields!StateCode.Value & vbcrlf & "(" & CSTR(Count(Fields!VendorID.Value)) & ")"

59

Astek Mauritius

Reporting Services Tutorial

TIP:

We used "&" in the expression to concatenate each State abbreviation (always separate the "&" character from others by a single space on both sides) with the respective count of the vendors within each, adding a line break (via the old carriage return-line feed keyword, vbcrlf,) to separate the two. (Using vbcrlf in this manner is a great way to make what would normally appear on a single line separate into two or more lines.

The Edit Expression dialog appears as shown in Figure 9.17 .

Figure 9.17: Edit expression dialog

By typing in the expression above, we are leveraging the data label to perform two functions: First, we are generating a State abbreviation (recall we are focusing on Washington, California, and Oregon) to label the sections of the chart. Second, we are adding a count of vendors within each respective section. The delivery of this data is, in effect, adding another conceptual dimension to the information we are imparting. 19. Click OK to accept input. The Edit Expression dialog box closes, returning us to the Point Labels tab. 20. Click the Label Style button in the lower left section of the Point Labels tab. The Style Properties dialog box appears. 60

Astek Mauritius

Reporting Services Tutorial

21. Make the settings, listed in the table below, within the Style Properties dialog box: Property Family Size Style Weight Color Decoration

Setting Verdana 11pt Normal Bold Black (Default) None

The Style Properties dialog appears, with our settings, as depicted in Figure 9.18.

Figure 9.18: Style properties window 22. Click OK to accept settings. The Style Properties box closes. We return to the Edit Chart Value dialog box, which now appears as shown in Figure 9.19.

61

Astek Mauritius

Reporting Services Tutorial

Figure 9.19: Edit chart value dialog box 23. Click OK to close the Edit Chart Value dialog. We return to the Chart Properties dialog box, Data tab. 24. Click the Legend tab to select it. 25. Uncheck Show Legend. As we shall see, our design will alleviate the need for a legend of the garden variety. Our expression in the Data Label above causes the placement of a label directly upon each section of the chart. The Legend tab appears, with our settings, as depicted in Figure 9.20

62

Astek Mauritius

Reporting Services Tutorial

Figure 9.20: Legend tab selected 26. Click the 3-D Effect tab to select it. The 3-D Effect tab appears, with our settings, as depicted in Figure 9.21.

Figure 9.21: 3D effect tab selected

27. Click the checkbox to the immediate left of Display Chart with 3-D Visual Effect, to place a checkmark there. 63

Astek Mauritius

Reporting Services Tutorial

28. Click the General tab to review settings. The General tab appears, at this stage in our process, as shown in Figure 9.22.

Figure 9.22: General tab selected 29. Click OK to accept all the settings we have made in the Chart Properties dialog box. The Chart Properties dialog closes, returning us to the generic chart item in Report Designer, Layout tab.

64

Astek Mauritius

Reporting Services Tutorial

Step 6: Verify Operation of the Chart Report 1. Click the Preview tab, to the right of the Layout tab atop the design surface. The new chart report generates, and appears as depicted in Figure 9.23.

Figure 9.23: snapshot of the report result 2. Click the Layout tab, once more. 3. Select File --> Save and then exit Visual Studio.net, when desired. Creating a complex bar chart using a sub report

In this session we will: · · · · · · · ·

Create a chart report in Report Designer; Create an underlying dataset from a view already present in the database Locate a Chart Item on the new report. Populate the chart item with the required data. Preview the report to verify its operation. Create another report (Main report). Place Sub report control inside the main report. Configure the sub report control to show the report where the chart is placed.

Hands-On Procedure Repeat Step 1 and Step 2 of the previous example to create a new project and create two blank reports namely MainReport.rdl and ChartReport.rdl.

65

Astek Mauritius

Reporting Services Tutorial

Step 3: Select the report “ChartReport.rdl” Set up a Data Connection and Create a Dataset 1. Select New Dataset in the Dataset selector at the top of the Data tab, as depicted in Figure 9.24

Figure 9.24: Creating a new Dataset

As soon as we click the New Dataset selection, the Data Link Properties dialog box appears, defaulted to the Connection tab. 2. Type the name of the computer housing the targeted OLTP database, AdventureWorks2000. 3. Select the radio button to the left of the authentication option that is appropriate for your environment. 4. Select AdventureWorks2000 within the Select the database on the server selector. The settings on the Connection tab of the Data Link Properties dialog should resemble those shown in Figure 9.25.

66

Astek Mauritius

Reporting Services Tutorial

Figure 9.25: Connecting to the database

5. Click the Test Connection button to verify connectivity to the data source. We receive a message box, indicating a successful test connection, as shown in Figure 9.26.

Figure 9.26: Message box indicating a successful test connection 6. Click OK to accept the settings we have made, and to close the Data Link Properties dialog. 67

Astek Mauritius

Reporting Services Tutorial

Report Designer next presents us with the dataset design tool, based upon our newly connected source. We are immediately positioned to design our query, which brings us to the next step. 7. Click the ellipses ("...") button to the right of the default dataset name of AdventureWorks2000, which appears in the Dataset selector, as shown in Figure 9.27.

Figure 9.27: Data tab of report selected 8. Enter for the name of the Dataset dialog box, replacing the default name of AdventureWorks2000 to BusinessFigures and also enter the following query string so as to fill the dataset. Query String: SELECT

vw_ProductInventory.*

FROM

vw_ProductInventory

WHERE

(ProductCategoryID = @PrdCatId) AND (LocationID = @LclId)

Note: A creation script of the view vw_ProductInventory is available in the sample code. The view takes two parameters namely @PrdCatId and @LclId which are the productCategoryId and LocationId respectively. These two parameters are used for displaying a bar chart with a particular ProductCategoryId and LocationID.

68

Astek Mauritius

Reporting Services Tutorial

The resulting dataset dialog appears as shown in Figure 9.28 below.

Figure 9.28: Modification of the dataset name and query string 9. Click OK to accept the settings and return to the Data tab Step 4: 1. Click the Layout tab to switch to the Layout view. 2. Drag the report edges to comfortably fill the screen area. 3. Select View --> Toolbox , from the main menu to place the Toolbox within easy reach (if it already appears, simply disregard this step).

69

Astek Mauritius

Reporting Services Tutorial

Figure 9.29: Snapshot of the toolbox 4. Click the Chart button. 5. Place the mouse cursor over the upper left corner of the report layout. The cursor becomes a small chart icon in combination with crosshairs when held above the layout. This indicates that we can click to "anchor" the point, from which we wish to draw the box that the chart will inhabit. 6. Starting in the upper left corner of the layout body, click, and then, holding the mouse button down, drag to create a box that covers the report layout. The layout should have this box appearing at its perimeter. 7. Release the mouse to drop the chart item. 8. Double-clicking the chart item will make the "drop regions" appear. 9. Drag and drop the Quantity field to the data fields’ area. 10. Drag and drop the Status field to the series fields’ area. 11. Drag and drop the Shelf field to the Category fields’ area.

70

Astek Mauritius

Reporting Services Tutorial

Figure 9.30: Snapshot of the chart with the fields dropped in the data area Step 5: 1. To View the resulting Bar chart for a specific location and product category enter values in the two text boxes as shown below (Figure 9.31). Here, values 6 and 2 are passed as parameters.

. Figure 9.31: Snapshot of the chart with the fields dropped in the data area 2. The Following Bar Chart is displayed upon pressing the view Report button.

Figure 9.32: Snapshot of the resulting chart 71

Astek Mauritius

Reporting Services Tutorial

Step 6: To display bar charts for all combination of location and Product category in a single report we make use of the subreport control in the MainReport.rdl. Adding a sub report In Layout view, in the Toolbox, double click Subreport as shown in Figure 9.33.

Figure 9.34: Selecting Subreport from the toolbox menu 1. On the design surface, drag a box to the size you want the sub report to be as shown in Figure 9.35.

Figure 9.35: Area where the subreport will appear 2. Go to the properties of the sub report by pressing F4 to set the name of the report to be displayed in the sub report area as shown in Figure 9.36.

72

Astek Mauritius

Reporting Services Tutorial

Figure 9.36: Choosing the report to be shown in the sub report area 3. Here, we are setting the report name as ChartReport which is the previous report where we created the bar chart.

4. We will now set the parameters of the subreport as shown in Figure 9.37.

Figure 9.37: Setting the parameters to be passed to the sub report Step 7: 1. Create a new dataset in the main report as in step 3 but with the following query string. SELECT

ProductCategoryID, LocationID

FROM

vw_ProductInventory

GROUP BY LocationID, ProductCategoryID ORDER BY LocationID

73

Astek Mauritius

Reporting Services Tutorial

The Dataset dialog should be as shown in the figure below:

Figure 9.38: Dataset Dialog showing the Name of the dataset and the Query String to be used to fill the dataset Step 8: 1. Now your report is ready to be previewed. 2. Click on the preview tab on top of the report and the following charts should be displayed if the steps have been followed correctly. 3. According to the data 7 charts should be displayed, only 2 charts have been shown in the figure below. As you can see both charts are being displayed on the same report.

74

Astek Mauritius

Reporting Services Tutorial

Figure 9.39: Several charts displayed on a single report

75

Astek Mauritius

10.

Reporting Services Tutorial

Conclusion

Business Intelligence helps building systems which enterprises use to evaluate and predict future with. Reporting systems are basic part of any corporate environment and with SQL server reporting services; this development can be done in a robust, scalable and cost effective way. Reporting Services integrated with Microsoft analysis services can provide excellent support for decision support systems and personals in fields of finance, medical research, market analysis, customer analysis, product management, customer profiling, product profitability, and inventory movement to name a few. I hope this tutorial was helpful in understanding reporting services. Links and resources section will be able to further help you in finding your way forward.

76

Astek Mauritius

Reporting Services Tutorial

References and Further Readings Resources ·

Microsoft SQL Server: SQL Server Reporting Services Home

·

InfoWorld: Microsoft launches SQL Server Reporting Services

·

SQL Server Reporting Services experiences

·

Reporting Services Datasheet

·

Reporting Services Features Comparison

·

Reporting Services Demo: How to Author, Manage, and Deliver Reports

·

Reporting Services case Studies

·

Latest News on SQL Server Reporting Services

·

Report Definition Language Specification

·

SQL Servertm 2000 Reporting Services Deployment Guide

·

Reporting Server Sample Walkthroughs

77