Programming Reporting Services

Module 07: Programming Reporting Services Contents Overview

1

Lesson: Supporting User Access

2

Demonstration 1: Querying for Server Information

7

Demonstration 2: Rendering Reports via URL Access 13 Demonstration 3: Rendering Reports via Web Service 15 Lesson: Automating Report Management

18

Demonstration 4: Automating Report Management

20

Lesson: Advanced Programmability

25

Demonstration 5: Creating a Custom Assembly

31

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. 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.  2003 Microsoft Corporation. All rights reserved. Microsoft, MS-DOS, Windows, Windows NT, ActiveX, BackOffice, FoxPro, JScript, MS-DOS, PivotChart, PivotTable, PowerPoint, Visual Basic, Visual C++, and Visual Studio are either registered trademarks or trademarks 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.

Last Saved: 9/30/2003 9:19:00 PM Last Printed: 9/30/2003 9:58:00 PM

Module 07: Programming Reporting Services

1

Overview „

Lesson: Supporting User Access

„

Lesson: Automating Report Management

„

Lesson: Advanced Programmability

Microsoft Confidential

This module will introduce you to the basic concepts of controlling Reporting Services programmatically. By the end of this module you will be able to do the following: „

Create a simple Visual Basic .NET program to do the following: • Retrieve information about published reports and folders from the server • Render a specific report to a selected format • Publish a report to the server • Delete a report from the server

„

Understand how reporting services can be extended

„

Create a custom assembly that includes a function you can call from an expression in a report

Last Saved: 9/30/2003 9:19:00 PM Last Printed: 9/30/2003 9:58:00 PM

2

Module 07: Programming Reporting Services

Lesson: Supporting User Access „

Using Web Services

„

Discovering Server Information

„

Rendering Reports

Microsoft Confidential

In this lesson you will learn the basics for creating a custom interfaces or report browser for end users. Creating a custom browser typically requires two separate tasks: first, discovering information from the server, such as which reports are available for the current user; and second, actually rendering the report. You will create a very simple application that accomplishes both these tasks.

Last Saved: 9/30/2003 9:19:00 PM Last Printed: 9/30/2003 9:58:00 PM

Module 07: Programming Reporting Services

3

Using Web Services „

„

What Are Web Services? z

Platform independent applications based on established standards

z

XML, SOAP, HTTP

Why Does Reporting Services use Web Services? z

Allows interaction with Windows Applications, Web Applications, etc.

z

Allows for cross platform interoperability

Microsoft Confidential

What Are Web Services? Web Services are applications that communicate over open protocols using standard data formats. In order to create a platform independent set of application services, we need to agree on how to pass information around. Using established standards such as XML, SOAP, and HTTP Microsoft and other vendors are able to create platforms for truly distributed computing. „

XML – Gives us a standard data presentation.

„

SOAP – Gives us a standard format for packaging the data.

„

HTTP – Gives us a standard protocol for sending and receiving data packets.

All of these standards are set by the World Wide Web Consortium (www.w3c.org). The World Wide Web Consortium is an outside organization composed of around 450 members.

Why Does Reporting Services use Web Services? By using web services, developers can build numerous applications around a rich set of functionality. Developers can easily create Windows Applications, Web Applications and even Console Applications using the .NET Framework. But, developers are not tied to one platform. Any platform that can read/write XML and transport data over HTTP can use these Web Services. This opens the door to integration with most applications platforms.

Last Saved: 9/30/2003 9:19:00 PM Last Printed: 9/30/2003 9:58:00 PM

4

Module 07: Programming Reporting Services

Discovering Server Information Report Design Report Browser Browsing Information

Report Server

Data Sources

ManagementOutput Formats Information

Delivery Channels

Report Manager

Data Flow Control Flow Microsoft Confidential

Discovering server information is useful for two separate purposes. On the one hand it is often necessary when creating a custom browser for end users. On the other hand, it can be useful when creating a custom management tool.

Last Saved: 9/30/2003 9:19:00 PM Last Printed: 9/30/2003 9:58:00 PM

Module 07: Programming Reporting Services

5

Discovering Server Information - Purpose „

For creating custom applications

„

Browsing

„

„

z

What reports are available?

z

What parameters does the report require?

Managing z

What subscriptions are in place?

z

What are the security settings for this report?

Retrieve information from Web Service Microsoft Confidential

One of the major reasons for retrieving information about items on the server is to facilitate user access to appropriate reports. You can programmatically find which reports are available for a particular user, whether those reports require parameters, and other pertinent information about the reports. In addition, you can also discover report information as part of an application to manage reports. Before changing attributes of a report, your application may want to know what the current attributes are. The primary mechanism for getting information about Reporting Services items is by communicating with the Web Service, which is accessible via standard SOAP calls. Fortunately, the .NET infrastructure makes it easy to write applications that communicate with a Web Service.

Last Saved: 9/30/2003 9:19:00 PM Last Printed: 9/30/2003 9:58:00 PM

6

Module 07: Programming Reporting Services

Discovering Server Information - Logistics „

Create Web Reference (or proxy) to handle SOAP calls, and pass Authentication credentials to server z

http://«server»/reportserver/reportservice.asmx?wsdl

„

Information for all Server items (e.g., reports, folders)

„

Navigate Hierarchy: ListChildren

„

Search for Items: FindItems z

Use key properties such as Name, CreationDate

z

Automatically filter’s by user’s permissions Microsoft Confidential

The first step in communicating programmatically with the Report Server Web Service is to create a Web Reference to the service. The Web Reference translates normal object methods and properties into the appropriate SOAP calls. (It is also possible to create an explicit proxy class for translating to and from SOAP, but the Web Reference automatically creates the necessary class for you.) Visual Studio walks you through the process of creating a Web Reference. The URL that contains the definition of the web service is http://«server»/reportserver/reportservice.asmx?wsdl. Once you create a reference to the primary class—RSWebService—you must first establish credentials by passing an item from the local .NET Credentials Cache. When you retrieve information about reporting items, you are not limited to reports. You also retrieve information about other items on the server, most notably folders that organize reports. There are two primary mechanisms for finding information about reporting items, ListChildren—which allows you to navigate through the folder hierarchy—and FindItems—which allows you to filter all or part of the hierarchy by search conditions. When you create a search condition, you make comparison to certain key attributes of the reporting items, such as the name or create date or modify date. When you search for items, the list returned to you is automatically filtered by the authenticated user’s permissions.

Last Saved: 9/30/2003 9:19:00 PM Last Printed: 9/30/2003 9:58:00 PM

Module 07: Programming Reporting Services

7

Demonstration 1: Querying for Server Information In this demonstration, you will create a simple Windows Form with a textbox, a button and a list (combo) box. When you type a string in the textbox and click the button, the items on the server—folders and reports—that contain that text will appear in the list. In this and the following demonstrations, to facilitate focusing on the current task, you will create the simplest possible application necessary to accomplish the indicated tasks. Consequently, you will not give standardized names to controls or add error handling code.

Ç Launch Visual Studio and create a new Visual Basic Windows Form 1. Click Start, point to All Programs, point to Microsoft Visual Studio .NET 2003, and then click Microsoft Visual Studio .NET 2003. 2. On the File menu, point to New, and then click Project. 3. In the New Project dialog box, in the Project Types pane, click Visual Basic Projects. 4. In the Templates list, click the Windows Application icon. 5. In the Name textbox, replace the default name with Web Service, in the Location textbox, type C:\Reporting Services Training\Workspace\ and then click OK. The project contains a blank windows form, Form1.vb. 6. Resize the form to fill most of the available space. 7. On the Toolbox, double-click the TextBox control, the Button control, and the ComboBox control. Tip: By default, the Toolbox is located on the left side of the screen. If the Toolbox is not visible, on the View menu, click Toolbox. 8. Arrange the Button to the right of the TextBox, and the ComboBox below them both. 9. Select the TextBox control, and in the Properties window, clear the Text property value. 10. Select the Button control, in the Properties window, in the Text property, delete the default value, and then type Get Items 11. Select the ComboBox control, and in the Properties window, for the DropDownStyle property, select Simple.

Ç Add a Web Reference to the Reporting Services Web Service 1. In Solution Explorer, right-click the References folder, and click Add Web Reference. Tip: If the Solution Explorer pane is not visible, on the View menu, click Solution Explorer. 2. In the URL textbox, enter the following URL: http://localhost/ReportServer/ReportService.asmx?wsdl

3. Click Go. Last Saved: 9/30/2003 9:19:00 PM Last Printed: 9/30/2003 9:58:00 PM

8

Module 07: Programming Reporting Services

4. After the web service is located, in the Web reference name textbox, delete the default value, type ReportService and then click Add Reference.

Ç Add code to the Find Items button to search for matching items 1. On the form double-click the Get Items button. This opens the Visual Basic page that controls the form, and adds a new procedure called Button1_Click. This code will run whenever you click the Find Items button. The code to enter in this procedure is in a text file. 2. Click Start, point to All Programs, point to Accessories, and then click Notepad. 3. In Notepad, on the File menu, click Open. 4. Navigate to the C:\Reporting Services Training\M07\Demo\Step1.txt and then click Open. This file contains the following code: Dim myReportService As New ReportService.RSWebService Dim myCatalogItems As ReportService.CatalogItem() myReportService.Credentials = _ System.Net.CredentialCache.DefaultCredentials Dim mySearchConditions(0) As ReportService.SearchCondition mySearchConditions(0) = New ReportService.SearchCondition mySearchConditions(0).Name = "Name" mySearchConditions(0).Value = TextBox1.Text myCatalogItems = myReportService.FindItems("/", _ Nothing, mySearchConditions) Dim i As Integer ComboBox1.Items.Clear() For i = 0 To myCatalogItems.Length - 1 ComboBox1.Items.Add(myCatalogItems(i).Name) Next i

The first two lines respectively create a connection to the web service and prepare a place to store the list of returned items. These two lines will go at the top of the page. The first indented statement (split onto two lines for readability), tells the service to use the credentials of the currently logged in user. This line needs to execute once when you first start the program. The remaining statements execute each time you click the Find Items button. The next block of statements creates a list consisting of one search condition. This search condition tests the “Name” value of a report item with the text from the Textbox. The next statement uses the FindItems command to tell the report service to “find” the “items” that match the list of conditions, and put the information about the items into the myCatalogItems list. The final block of statements puts the name of each item in the retrieved list into the combo box, after first clearing the box of any previous items. Last Saved: 9/30/2003 9:19:00 PM Last Printed: 9/30/2003 9:58:00 PM

Module 07: Programming Reporting Services

9

5. Copy the first two lines and switch to Visual Studio, after the Inherits statement, press ENTER and then paste the two copied lines. 6. Switch back to Notepad and copy the first indented statement (the credentials statement). 7. Switch to Visual Studio, click the plus sign next to Windows Form Designer generated code. 8. After the InitializeComponents() statement, press ENTER, and then paste the copied line. 9. Click the minus sign next to #Region " Windows Form Designer generated code " 10. Switch back to Notepad, copy the remaining lines, and on the File menu, click Exit to close Notepad. 11. In Visual Studio, before the End Sub statement, paste the copied lines. Your form should now look like the following image:

12. On the Debug menu, click Start to run the form and wait for Form1 to appear. 13. In Form1, with the textbox empty, click the Get Items button and wait for the names to appear in the combo box. The names of all the items on the server—both folders and reports—appear. 14. In the textbox, type Sales, and click the Get Items button. The names of all items that contain the word Sales appear. 15. Click the Close button to close the form. Note Leave this Visual Basic project open, as you will continue the project in Demonstration 4.

Last Saved: 9/30/2003 9:19:00 PM Last Printed: 9/30/2003 9:58:00 PM

10

Module 07: Programming Reporting Services

Rendering Reports Report Design Report Browser

Data Sources

Report Server

Rendering Reports Output Formats

Delivery Channels

Report Manager Data Flow Control Flow Microsoft Confidential

The second part of facilitation user access to reports is to actually render the reports.

Last Saved: 9/30/2003 9:19:00 PM Last Printed: 9/30/2003 9:58:00 PM

Module 07: Programming Reporting Services

11

Rendering Reports - Purpose „

Web Pages – Links to Report Pages

„

Custom client applications

„

Dashboards

„

Extract report data

Microsoft Confidential

There are a number of reasons to programmatically control the rendering of a report, ranging from the straightforward objectives of inserting reports in a dashboard or creating links from a portal, to the more sophisticated applications such as creating completely custom front-end tools or extracting data from reports to pass to other applications.

Last Saved: 9/30/2003 9:19:00 PM Last Printed: 9/30/2003 9:58:00 PM

12

Module 07: Programming Reporting Services

Rendering Reports via URL Access „

For simple dashboard applications, web page links

„

Reference a report on the Server. For example: z

http://localhost/ReportServer/Sample%20Reports/ Company%20Sales?rs:Command=Render

„

Tip: Referencing a folder shows the children, and then clicking a report name gives a URL you can copy

„

Can include report parameters

„

Plain text – use encrypted internet (SSL) for security

„

Automatically detects browser and uses appropriate HTML level

Microsoft Confidential

The easiest way to programmatically access a report is by simply giving a URL to a browser. To render a report, you simply specify the server, the service, and the report name (including the folder hierarchy, as appropriate), and then add the special parameter rs:Command=Render. This tells the service to render a report. If you specify a folder rather than a report, the service displays a simple page with available subfolders and reports. Each subfolder or report is actually a hyperlink that contains the appropriate URL for that object. This makes it easy to determine the URL for rendering a specific report: Simply start by accessing the root level folder, and then navigate to the desired report. When you click the report, the desired URL will appear in the browser Address Bar. If a report takes parameters, you can insert parameter values in the URL directly. A report parameter is simply entered in the URL without the rs:command= prefix. Remember that URLs are sent to the server as plain text. If you include anything in the URL, including the parameters, that is confidential, be sure to connect to the server via a secure, encrypted protocol. When you use a URL to render a report, the web service automatically detects the browser you are using and issues the appropriate form of HTML for that browser.

Last Saved: 9/30/2003 9:19:00 PM Last Printed: 9/30/2003 9:58:00 PM

Module 07: Programming Reporting Services

13

Demonstration 2: Rendering Reports via URL Access In this demonstration, you will display a report in a browser simply by entering the URL. You can use this concept to add a link to a report anywhere you could put a URL—in a simple web page or a dashboard.

Ç Access a report via a URL link 1. Launch Internet Explorer. 2. In the Address Bar, type http://localhost/ReportServer/. This is simply the name of the root folder on the server. It is not a report, but a folder. When you use URL to access a folder, the contents of the folder are displayed, but as URL links to their respective folders or reports. 3. Click the Example Reports folder link. 4. Click a report link. The report appears in the browser. Note that the URL to render that report appears in the Address Bar. You could copy that URL and put it into any other hyperlink you want.

Last Saved: 9/30/2003 9:19:00 PM Last Printed: 9/30/2003 9:58:00 PM

14

Module 07: Programming Reporting Services

Rendering Reports via Web Service „

Create Web Reference (or proxy) to handle SOAP calls, and pass Authentication credentials to server

„

Specify Rendering Type, parameters, and options

„

Get back byte array as result

„

Forward byte array to appropriate target

„

Example: Extract CSV version of data to copy to Excel

Microsoft Confidential

Using a URL to render a report is clearly the simplest way to render a report, but there are times when you programmatically want more control. You can issue a Render command to the web service directly and programmatically, in essentially the same way that you get report item information from the service: create a Web Reference, pass authentication, and then execute the Render method. The Render method has many parameters, including the full path for the report and the rendering type (when you call the Render method to create HTML output, you must specify the HTML level you want). You also pass as arguments any report parameters or other options. Because the proxy is translating the request to SOAP to send to the service, you must include the keyword Nothing for any optional arguments you choose not to use. When you call the Render method, the report is not automatically rendered in the browser, which means that you can call the method from any application, not just a browser. The function returns the rendered report in the form of a byte array. You then programmatically do with that byte array whatever you want: for example, you can display an HTML rendered report in a literal Web Control, or you can save any report output to a file. If you want to dump the report results to a list in a spreadsheet—for sorting and other analysis—you may want to retrieve the data as comma separated values (CSV), which you can then copy into Excel.

Last Saved: 9/30/2003 9:19:00 PM Last Printed: 9/30/2003 9:58:00 PM

Module 07: Programming Reporting Services

15

Demonstration 3: Rendering Reports via Web Service In this demonstration, you will render a comma separated value (CSV) version of a report and display the CSV form of the report in a textbox. This type of data would be useful for transferring the data into a spreadsheet program.

Ç Open a saved project and report Important If you successfully completed Demonstration 2, then use the Web Service project that you already have open to complete the procedures in this demo. If you did not complete Demonstration 2 then you should complete this procedure to open a saved project. 1. Click Start, point to All Programs, point to Microsoft Visual Studio .NET 2003, and then click Microsoft Visual Studio .NET 2003. 2. On the File menu, point to Open, and then click Project. 3. In the Open Project dialog box, navigate to the C:\Reporting Services Training\M07\Demo\Answers\Demo 02\ folder containing Demonstration 2 answer files. 4. Click the Web Service.sln file and then click Open. 5. In Solution Explorer, double-click the Form1.vb form. Tip: If the Solution Explorer pane is not visible, on the View menu, click Solution Explorer.

Ç Insert a rich textbox for output 1. In the Toolbox window, double click the RichTextBox control. Tip: By default, the Toolbox is located on the left side of the screen. If the Toolbox is not visible, on the View menu, click Toolbox. You may need to scroll down the list of controls to see the RichTextBox control. 2. Position and resize the control to take up all available space on the right side of the form. 3. With the RichTextBox control selected, in the Properties window, clear the Text property value.

Ç Render a report when you change the selection in the list of items 1. On the form, double-click the ComboBox control. This displays the code page with a new procedure named ComboBox1_SelectedIndexChanged. This code will run each time you select a new item in the Combo Box. 2. Click Start, point to All Programs, point to Accessories, and then click Notepad. 3. In Notepad, on the File menu, click Open. 4. Navigate to the C:\Reporting Services Training\M07\Demo\Step2.txt and then click Open. Last Saved: 9/30/2003 9:19:00 PM Last Printed: 9/30/2003 9:58:00 PM

16

Module 07: Programming Reporting Services

This file contains the code necessary for rendering the report into the RichTextBox. Dim myDefinitionBytes As Byte() Dim myEncoding As System.Text.Encoding = _ System.Text.Encoding.Unicode Try myDefinitionBytes = myReportService.Render( _ myCatalogItems(ComboBox1.SelectedIndex).Path, "CSV", _ Nothing, Nothing, Nothing, Nothing, Nothing, _ Nothing, Nothing, Nothing, Nothing, Nothing) RichTextBox1.Text = _ myEncoding.GetString(myDefinitionBytes) Catch ex As Exception MsgBox(ex.ToString) End Try

The first statement creates a buffer—called a byte array—to store the details for the report. You retrieve a rendered report into a byte array regardless of the rendering type you use. The second statement creates an Encoding object that allows you to translate the byte array, which is stored in Unicode text (capable of handling exotic text such as Chinese) into a simple string that you can put into the RichTextBox. The Try, Catch, and End Try statements allow you to display an error message if the report does not render properly. For example, you will see an error if you select a folder from the list, rather than a report. The first statement in the Try section renders the report into the byte array. This Render command is passed to the server via the Web Reference that you created earlier. There are a lot of possible arguments to the Render command, and if you don’t need them, you must enter Nothing as a placeholder. Notice the “CSV” argument. This is what tells the Render command to create CSV output. If you use “HTML3.2” in the command, the report will come out with HTML formatting. You pass to the report server the complete path of the report you want to render. The path is one of the properties stored in the catalog list created by the FindItems command. This command gets the item from the list corresponding to the item selected in the combo box. Different report formats require different encoding. So, for the “CSV” format presented here use Unicode. To render a report in “HTML”, change the encoding to UTF8. The second (and final) statement in the Try section uses the Encoding object to translate the byte array into a string and put it into the Rich Text Box control. The Catch portion of the code displays an error message if something goes wrong with either Rendering the report, or translating the byte array. 5. In Notepad, select all by using Ctrl + A and then copy by using Ctrl + C. 6. On the File menu, click Exit to close Notepad. 7. In the Visual Basic code, paste all the statements before the End Sub statement of the ComboBox1_SelectedItemChanged procedure. Last Saved: 9/30/2003 9:19:00 PM Last Printed: 9/30/2003 9:58:00 PM

Module 07: Programming Reporting Services

17

8. On the Debug menu, click Start to run the form. 9. Wait for Form1 to appear. 10. In Form1, type Sales in the textbox and click Get Items. 11. Select the Product Profitability report. The CSV-rendered report appears in the textbox. If you wanted, you could copy the contents of the textbox, paste it into Excel, and parse the data into columns by using the Data to Text command. 12. Click the Close button to close the form. 13. On the File menu, click Close Solution.

Last Saved: 9/30/2003 9:19:00 PM Last Printed: 9/30/2003 9:58:00 PM

18

Module 07: Programming Reporting Services

Lesson: Automating Report Management Report Design Report Browser

Report Server

Data Sources

Managing Reports

Output Formats

Delivery Channels

Report Manager

Data Flow Control Flow Microsoft Confidential

In this lesson, you will learn about how to write your own custom tools for managing reports on the server, so that you could replace the functionality of the Report Manager application You will write a very simply application that publishes a report and deletes a report.

Last Saved: 9/30/2003 9:19:00 PM Last Printed: 9/30/2003 9:58:00 PM

Module 07: Programming Reporting Services

19

Automating Report Management „

„

Anything you can do with Report Manager z

Add, delete reports, folders

z

Administer security, schedules, etc

Example applications, z

Apply identical report settings to multiple servers

z

Apply identical security settings to multiple reports

„

Create Web Reference (or proxy) to handle SOAP calls, and pass Authentication credentials to server

„

Execute methods and properties of RSWebService Microsoft Confidential

When you automate report management via the web service, you can do anything that you can do in the Report Manager application. That’s because the Report Manager application uses the web service to carry out its tasks. You can write custom code to add or delete reports, administer security or schedules, or any of a large number of other tasks. Any report management task that you must execute multiple times becomes a candidate for a custom application. If you are running a report server farm, for example, you may want to apply identical settings to multiple servers. Or you may want to apply the same security or subscription settings to multiple reports. Or create an application that generates report RDL files and then automatically deploys the generated reports. As with other web service applications, you must create a Web Reference and pass credentials in order to communicate with the server. The RSWebService class then has methods and properties for manipulating reports.

Last Saved: 9/30/2003 9:19:00 PM Last Printed: 9/30/2003 9:58:00 PM

20

Module 07: Programming Reporting Services

Demonstration 4: Automating Report Management You can use the Reporting Services Web Service to manage the report server. Anything that you can do in the Report Manger interface, you can do via the Web Service—because that is what Report Manager uses. In this demonstration, you will deploy a new report (as defined in an RDL file) to the server. You will then delete a selected report from the server.

Ç Open a saved project and report Important If you successfully completed Demonstration 4, then use the Web Service project that you already have open to complete the procedures in this demo. If you did not complete Demonstration 4 then you should complete this procedure to open a saved project. 1. Click Start, point to All Programs, point to Microsoft Visual Studio .NET 2003, and then click Microsoft Visual Studio .NET 2003. 2. On the File menu, click Open Solution. 3. In the Open Solution dialog box, navigate to the C:\Reporting Services Training\M07\Demo\Answers\Demo 04\ folder containing Demonstration 4 answer files. 4. Click the Web Services.sln file and then click Open. 5. In Solution Explorer, double-click the Form1.vb form. Tip: If the Solution Explorer pane is not visible, on the View menu, click Solution Explorer.

Ç Add buttons for Adding and Deleting a report 1. With the design view of the form visible, in the Toolbox, double-click the Button control twice. Tip: By default, the Toolbox is located on the left side of the screen. If the Toolbox is not visible, on the View menu, click Toolbox. 2. Move the two new buttons below the ComboBox. 3. Select the first of the new buttons, Button2. 4. In the Properties window, in the Text property, type Add Report 5. Select the second of the new buttons, Button3. 6. In the Properties window, in the Text property, delete the default value and then type Delete Report Note You may need to resize the button to view all of the Delete Report text.

Ç Copy a report definition to an easy to type location 1. Click Start, point to All Programs, point to Accessories, and then click Windows Explorer.

Last Saved: 9/30/2003 9:19:00 PM Last Printed: 9/30/2003 9:58:00 PM

Module 07: Programming Reporting Services

21

2. Navigate to the C:\Reporting Services Training\M07\Demo\Answers\Demo 01\ folder. 3. Copy the Product Profitability.rdl report from C:\Reporting Services Training\M07\Demo\Answers\Demo 01\ to the C:\ folder. 4. Rename C:\Product Profitability.rdl to C:\Report.rdl. Important If you prefer not to copy and rename the report, you can edit the location of the report in the code file in the next step.

Ç Add code to add a new report 1. Double-click the Add Report button. This creates a new procedure named Button2_Click, which will run whenever you click the button. 2. Click Start, point to All Programs, point to Accessories, and then click Notepad. 3. In Notepad, on the File menu, click Open. 4. Navigate to the C:\Reporting Services Training\M07\Demo\Step3.txt and then click Open. This file contains the code necessary for copying the report definition to the server. Dim myDefinitionBytes As Byte() Dim fr As System.IO.FileStream = _ System.IO.File.OpenRead("C:\Report.rdl") ReDim myDefinitionBytes(fr.Length) fr.Read(myDefinitionBytes, 0, fr.Length) myReportService.CreateReport("New Report", "/", False, _ myDefinitionBytes, Nothing) Button1_Click(Nothing, Nothing)

In the first block, the first statement creates a place to store the report definition, a byte array. The second statement creates an object capable of reading a file, a FileStream object. It also sets up the object to read the sample report file. The third statement prepares the byte array for the number of characters that will be coming from the file. In the second block, the first statement reads the characters from the file into the byte array. The second statement passes that array to the Web Service, giving it a name (New Report) and a location (/—the root folder). The final block simply “clicks” the Get Items button so that the list will refresh. 5. In Notepad, select all by using Ctrl + A and then copy by using Ctrl + C, and then switch to Visual Studio. 6. In the Visual Basic code, paste all the statements before the End Sub statement of the Button2_Click procedure.

Ç Add code to delete a selected report 1. Click the Design tab and double-click the Delete Report button.

Last Saved: 9/30/2003 9:19:00 PM Last Printed: 9/30/2003 9:58:00 PM

22

Module 07: Programming Reporting Services

This creates a new procedure named Button3_Click, which will run whenever you click the button. 2. In Notepad, on the File menu, click Open. 3. Navigate to the C:\Reporting Services Training\M07\Demo\Step4.txt and then click Open. This file contains the code necessary for copying the report definition to the server. myReportService.DeleteItem(myCatalogItems( _ ComboBox1.SelectedIndex).Path) Button1_Click(Nothing, Nothing)

The first statement deletes the selected item. To delete an item, you simply pass its complete path. The path is stored in the collection of catalog information retrieved by FindItems. In this case, you pass the path from the item with the same number as the report currently selected in the combo box. The second statement “clicks” the Get Items button to refresh the list. 4. In Notepad, copy all the code, and then on the File menu, click Exit to close Notepad. 5. In the Visual Basic code, paste all the statements before the End Sub statement of the Button3_Click procedure.

Ç Test the Add and Delete buttons 1. On the Debug menu, click Start to display the form. 2. Wait for the form to appear. 3. With the textbox empty, click the Get Items button. 4. Click the Add Report button, and watch the New Report appear in the list. 5. Select New Report and watch the report render in the Rich Text Box. 6. Click the Delete Report button, and watch the New Report disappear from the list.

Last Saved: 9/30/2003 9:19:00 PM Last Printed: 9/30/2003 9:58:00 PM

Module 07: Programming Reporting Services

23

Automating Report Management via Scripts „

For batch automation without writing a full application

„

RS.EXE provides shell functionality z

Manages SOAP interfaces

z

Manages connection to Report Server Web Service

z

Manages authentication

„

Create Visual Basic .Net function (named Main)

„

Store in script file with extension .rss

„

Pass script file as command line argument to rs.exe Microsoft Confidential

Rather than write a complete executable application to carry out a management task, you can create a script file and then utilize the RS.EXE application to execute the script. The RS.EXE application manages the SOAP interface and the connection to the web service. It also manages the necessary authentication. In essence, a script file is the core of a Visual Basic application. It must have a function named Main, but it doesn’t need to create a proxy or connect to the server or pass authentication. You can create multiple script files—with the extension rss— and then execute them in various batch process as command line arguments to RS.EXE.

Last Saved: 9/30/2003 9:19:00 PM Last Printed: 9/30/2003 9:58:00 PM

24

Module 07: Programming Reporting Services

For More Detailed Examples „

„

Web Service and URL Access z

RS Explorer

z

Includes reusable URL Generator class for constructing an appropriate URL (with parameters, etc)

z

Shows ListChildren method of Report Service

RS.EXE Scripting z

PublishSampleReports.rss

z

Creates shared data source

z

Includes command-line argument for parent folder Microsoft Confidential

The demonstrations in this module are intended to give you the simplest possible code that can carry out the essential requirements. The samples that come with Reporting Services include more extensive applications that you can learn from. To learn more about communicating with the web service and using URLs, check out the RS Explorer application. In addition to basic navigation and rendering code, this sample application includes a URL Generator class that is more powerful than needed for the RS Explorer itself. This is a class that you can incorporate in your own applications that need to generate URL string with parameters and other sophisticated options. If you want to create scripts for managing reports, you should look at the PublishSampleReports sample script. This script shows how to create a shared data source, upload multiple reports, and pass a command-line argument via the RS.EXE application.

Last Saved: 9/30/2003 9:19:00 PM Last Printed: 9/30/2003 9:58:00 PM

Module 07: Programming Reporting Services

25

Lesson: Advanced Programmability „

Server Configuration

„

Types of Extensions

„

Custom Extensions

„

Custom Assemblies

Microsoft Confidential

In this lesson you will learn about some advanced programmability options available in Reporting Services. In addition to day-to-day operations such as creating or customizing reports, facilitating user access to reports, and managing reports, there are also some specialized ways in which you can automate Reporting Services, particularly to control server configuration and to add extensions to the server.

Last Saved: 9/30/2003 9:19:00 PM Last Printed: 9/30/2003 9:58:00 PM

26

Module 07: Programming Reporting Services

Server Configuration „

All management interface is through SOAP calls to Report Server Web Service

„

If service is not running, can’t use SOAP to communicate with it

„

Windows Management Interface (WMI) calls allow programmatic control whether server is running or not

Microsoft Confidential

Normal report management is all done through SOAP calls to the Report Server Web Service. In order for the server to respond to the SOAP calls, the service must be running. There are, however, certain server configuration tasks that you may want to be able to automate whether or not the server is actually running. In particular, options that allow you to configure the server in the first place must run before starting the service. For these configuration activities, Reporting Service supports a Windows Management Interface (API). This interface does not replace the normal Web Service activities, but provides a narrowly focused set of tools for those tasks that must be executable without requiring the service to be running.

Last Saved: 9/30/2003 9:19:00 PM Last Printed: 9/30/2003 9:58:00 PM

Module 07: Programming Reporting Services

27

Types of Extensions Report Design Report Browser

Rendering Extensions Data Processing Data Sources Extensions

Report Server

Output Formats Report Manager

Security Extensions

Delivery Extensions

Delivery Channels

Data Flow Control Flow Microsoft Confidential

Reporting Services was designed as an extensible architecture. All the major tasks of Reporting Service are implemented as extensions. For example, rendering a report as HTML is implemented as an HTML rendering extension. What this means is that you can, if necessary, add additional customized extensions that integrate seamlessly with the native functionality of the Report Server. Specifically, extensions come in four flavors: Data Processing, Rendering, Security, and Delivery.

Last Saved: 9/30/2003 9:19:00 PM Last Printed: 9/30/2003 9:58:00 PM

28

Module 07: Programming Reporting Services

Custom Extensions „

Create “add-ins” for Reporting Services

„

Particularly useful for 3rd party developers

„

z

Proprietary data source

z

Enhanced rendered output (3-D view, link to diagrams)

Advanced technique

Microsoft Confidential

Creating custom extensions allows you to create seamless “add-ins” to Reporting Services. These are particularly useful if you are a 3rd party developer using Reporting Services to create a specialized solution. For example, if you have specialize application with a proprietary data source, you can create a custom Data Processing extension to retrieve data from your application. Or you might want to create specialized reporting or visualization tools—for example reports that link to manufacturing diagrams or schematics, or specialized three-dimensional views of data. Your ability to extend Reporting Services functionality is limited only by your imagination. Of course, creating custom extensions is not for everyone. Most reporting needs can be met with the native extensions that ship with the product. But it’s nice to know that if you have a specialized need, you can extend Reporting Services to handle it.

Last Saved: 9/30/2003 9:19:00 PM Last Printed: 9/30/2003 9:58:00 PM

Module 07: Programming Reporting Services

29

Custom Assemblies: Purpose „

Create functions that you can call from within a report expression

„

Access the full capabilities of the .NET platform

„

Use strong keys to secure the assemblies

Microsoft Confidential

Custom assemblies accessible from a report serve several purposes. „

They allow you to create functions that you can use to extend the capabilities of expressions.

„

Custom functions can be used to encapsulate complex or detailed expressions. Rather than build all the logic of a complex statistical calculation in a text box expression, you can put the complexities into a custom function so that the text box expression is easier to understand. Custom functions in an assembly can make use of all the capabilities.NET platform For example, the code can be procedural, not just a single expression, allowing you to make use of sophisticated structures such as conditionals (IF or CASE), or loops (FOR or WHILE).

„

Putting custom functions in an assembly allows you to be confident that the code is secure. You must provide a strong key for the assembly, so that no one—either maliciously or inadvertently—can change the code in the function.

Last Saved: 9/30/2003 9:19:00 PM Last Printed: 9/30/2003 9:58:00 PM

30

Module 07: Programming Reporting Services

Custom Assemblies: Logistics „

Create a new VB .NET class library and add functions

„

Create a strong key for the assembly and add to the Global Assembly Cache (GAC)

„

Enable custom assemblies in Reporting Services

„

In the project, add a reference to the assembly

„

In an expression, reference a custom function

Microsoft Confidential

Creating and using a custom assembly from a report requires several steps. Some of these steps—such as writing the function code—must clearly be done for each function you create. Other steps—such as enabling custom assemblies in reports— need to be done only once. • You must create a new .NET class library and add functions to that library. • You must create a strong key to secure the assembly so that you can add it to the .NET Global Assembly Cache (GAC), from which it is accessible to your reporting services project. • You must edit two configuration files—one for the Report Designer, and one for the Report Server—to allow custom assemblies to be used. • You must add a reference within the report to any assemblies you wish to use. • Within any expression, you can then call the functions from the registered assembly.

Last Saved: 9/30/2003 9:19:00 PM Last Printed: 9/30/2003 9:58:00 PM

Module 07: Programming Reporting Services

31

Demonstration 5: Creating a Custom Assembly In this demonstration, you will create a very simple custom assembly that includes a function named GetColor. This function will return the colors Red, Yellow, or Green, suitable for adding as conditional format to a report. As arguments to the function, you will pass a test value. If the value is more than the first cut-off level, the function will return Green. Otherwise, if the value is more than a second cut-off level, the function will return Yellow. Otherwise, the function will return Red. You will then reference the function from within a report to apply conditional colors to the value of a cell.

Ç Create a new project 1. Click Start, point to All Programs, point to Microsoft Visual Studio .NET 2003, and then click Microsoft Visual Studio .NET 2003. 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 Visual Basic Projects folder is selected. 4. In the Templates pane, click the Class Library icon. 5. In the Name box, replace the default name with AdventureWorks.Extensions, in the Location box, type C:\Reporting Services Training\Workspace\ and then click OK. The Class1.vb tab should open automatically. 6. In the Solution Explorer window, right click Class1.vb, and then click Rename. 7. Type CommonFunctions.vb and press <ENTER>. 8. Keep Visual Studio .NET 2003 open.

Ç Copy a custom function 1. Click Start, point to All Programs, point to Accessories, and then click Notepad. 2. In Notepad, on the File menu, click Open. 3. Navigate to the C:\Reporting Services Training\M07\Demo\Custom Assembly.txt file and then click Open. This file contains the GetColor custom function. This is the contents of the file: Public Shared Function GetColor(ByVal Value As Decimal) As String Select Case Value Case Is > 0.15 Return “Green” Case Is > 0.1 Return “Yellow” Case Else Return “Red” End Select End Function

The function takes an input number and returns a color based on that information. Last Saved: 9/30/2003 9:19:00 PM Last Printed: 9/30/2003 9:58:00 PM

32

Module 07: Programming Reporting Services

4. In Notepad, select all by using Ctrl + A and then copy by using Ctrl + C, and then close the file.

Ç Insert a function into the class definition 1. Switch back to Visual Studio .NET 2003. 2. On the CommonFunction.vb tab, replace the class name from Class1 to CommonFunctions. You should now see the following: Public Class CommonFunctions End Class

3. Click immediately after CommonFunctions, press <ENTER>, and then click the Paste toolbar button to paste the function into the class definition. The complete code should look like the following: Public Class CommonFunctions Public Shared Function GetColor(ByVal Value As Decimal) As String Select Case Value Case Is > 0.15 Return "Green" Case Is > 0.1 Return "Yellow" Case Else Return "Red" End Select End Function

End Class

4. On the File menu, click Save All. 5. Keep Visual Studio .NET 2003 open.

Ç Create a strong name key for the assembly Now that you have compiled the assembly you will create a key pair that allows you to sign the assembly with a strong name. Creating a strong named assembly will allows you to install the assembly into the Global Assembly Cache. 1. Click Start, point to All Programs, point to Microsoft Visual Studio .NET 2003, point to Visual Studio .NET Tools, and then click Visual Studio .NET 2003 Command Prompt. The Visual Studio .NET 2003 Command Prompt window uses the same commands as the windows command prompt and adds additional functionality specific to .NET. 2. In the command prompt window, type cd C:\Reporting Services Training\Workspace\AdventureWorks.Extensions\ and then press <ENTER> to change directories. 3. In the command prompt window, type sn -k AdventureWorks.Extensions.snk and then press <ENTER> to create the Strong Name Key. Last Saved: 9/30/2003 9:19:00 PM Last Printed: 9/30/2003 9:58:00 PM

Module 07: Programming Reporting Services

33

You should now have a new file in the root of the project called AdventureWorks.snk. This file contains a public/private key pair that will allow you to sign the assembly. 4. Keep the command prompt window open.

Ç Associate the key file with the assembly To associate the key file with the assembly you need to edit the AssemblyInfo.vb file. 1. Switch back to Visual Studio .NET 2003. 2. In the Solution Explorer window, right click AssemblyInfo.vb, and then click Open. 3. Find the following metadata tag at the bottom of the code file:

4. Replace the 1.0.* version number to 1.0.0.0 The last line of code should look like the following:

5. Click after the version number line, press <ENTER>, and then type the following metadata tag to specify the key file:

6. On the Build menu, click Build Solution to build the solution. This will create a .dll file called AdventureWorks.Extensions.dll in C:\Reporting Services Training\Workspace\AdventureWorks.Extensions\bin. This is the dll we will reference in our report. 7. On the File menu, click Save All.

Ç Install the Custom Assembly in the Global Assembly Cache In this procedure you will take the custom assembly and install it in the Global Assembly Cache (GAC). Installing a .NET assembly in the GAC allows multiple applications to share the same .dll file. 1. Switch back to the Visual Studio .NET 2003 Command Prompt window. 2. In the command prompt window, type cd C:\Reporting Services Training\Workspace\AdventureWorks.Extensions\bin and then press <ENTER> to change directories to work with the .dll you created. 3. In the command prompt window, type gacutil -i AdventureWorks.Extensions.dll and then press <ENTER> to add the assembly to the GAC. You should receive a message stating that the assembly has been successfully added to the Global Assembly Cache. The assembly is now ready to be used in our report. 4. Close the command prompt window.

Ç Enable Custom Assemblies in Reports Last Saved: 9/30/2003 9:19:00 PM Last Printed: 9/30/2003 9:58:00 PM

34

Module 07: Programming Reporting Services

For security purposes, Reporting Services does not enable custom assemblies in a report until you explicitly enable them. To enable custom assemblies, you simply delete comment marks from two configuration files. 1. In Windows Explorer, navigate to the folder C:\Program Files\Microsoft SQL Server\80\Tools\Report Designer. 2. Make a copy of the RSReportDesigner.config file as a safety precaution. 3. Right-click the file RSReportDesigner.config, point at Open With, and click Choose Program. Then select Notepad, and click OK. 4. Click Edit, and click Find. Type Comment, and click OK. Delete the comment indicator before the tag, and the closing comment indicator after the tag. Then save the file and close Notepad. 5. Navigate to the folder C:\Program Files\Microsoft SQL Server\MSSQL\Reporting Services\ReportServer. 6. Make a copy of the RSReportServer.config file as a safety precaution. 7. Right-click the file RSReportServer.config, point at Open With, and click Choose Program. Then select Notepad, and click OK. 8. Click Edit, and click Find. Type Comment, and click OK. Delete the comment indicator before the tag, and the closing comment indicator after the tag. Then save the file and close Notepad.

Ç Preview the initial Product Profitability report 1. Switch back to Visual Studio .NET 2003. 2. On the File menu, click Close Solution. 3. On the File menu, click Open Solution. 4. In the Open Solution dialog box, navigate to the C:\Reporting Services Training\M07\Demo\Custom Assembly folder containing demonstration files. 5. Click the Custom Assembly.sln file and then click Open. 6. In Solution Explorer, expand the Custom Assembly project, and then expand Reports. Tip: If the Solution Explorer pane is not visible, on the View menu, click Solution Explorer. The project contains a Product Profitability report, which is a report that uses conditional formatting and grouping. 7. Double-click the Product Profitability.rdl report, and in the View pane click Preview. The Margin % value for Road Bike is colored red. This is the result of a conditional expression in the Color property. The condition uses a red font if the value is less than 15%. 8. Click the expand indicator (+) next to Road Bike. The Margin % for multiple road bikes are red, along with the bold subtotal for Road Bike. The conditional format was applied for detail rows (the individual products), for product footers (when products are expanded), and for product headers (when products are collapsed). Last Saved: 9/30/2003 9:19:00 PM Last Printed: 9/30/2003 9:58:00 PM

Module 07: Programming Reporting Services

35

Ç Reference the Custom Assembly 1. Click the Layout tab to view the design of the report 2. In the Properties window drop-down list, click Report to view the report properties. 3. In the Properties window, click the References property box, and then click the button. 4. In the Edit References dialog box, click the corner to add a reference.

button in the upper right hand

5. In the Add Reference dialog box, click the Browse button to navigate to the custom assembly. 6. In the Select Component dialog box, navigate to the C:\Reporting Services Training\Workspace\AdventureWorks.Extensions\bin folder. 7. Select the AdventureWorks.Extensions.dll file and then click Open. 8. Click OK twice.

Ç Use the Custom Assembly 1. In Solution Explorer, click the Product Profitability.rdl report, and then click the View Code button. This displays the XML representation of the report. Note the new tag towards the top of the report definition. This tag contains the assembly name, version and public key. This information is used to identify the assembly in the Global Assembly Cache. 2. Press + F to display the Find dialog box. 3. In the Find what textbox, type “Red” (including the quotation marks), and then click the Find Next button. Keep the Find dialog box open. This finds the first conditional format expression, the one for the detail line. It looks like this: =IIF(Me.Value<0.15,"Red","Black")

4. Replace IIF with AdventureWorks.Extensions.CommonFunctions.GetColor, and delete everything between Value and the closing parenthesis. The finished element should look like this: =AdventureWorks.Extensions.CommonFunctions. GetColor(Me.Value)

5. In the Find dialog box, click Find Next. Keep the Find dialog box open. 6. Again replace IIF with AdventureWorks.Extensions.CommonFunctions.GetColor and delete everything between the closing parentheses after Value and the final closing parentheses. The finished element should look like this: =AdventureWorks.Extensions.CommonFunctions. GetColor(Me.Value)

7. In the Find dialog box, click Find Next. Last Saved: 9/30/2003 9:19:00 PM Last Printed: 9/30/2003 9:58:00 PM

36

Module 07: Programming Reporting Services

8. In the Find dialog box, click Close. 9. Again replace IIF with GetColor and delete everything between the closing parentheses after Value and the final closing parentheses. The finished element should look like this: =AdventureWorks.Extensions.CommonFunctions. GetColor(Me.Value)

10. Activate the Design tab for the report by clicking the Product Profitability.rdl [Design] tab, and then click the Preview tab. Now some of the locations are yellow, because they are below the 15% cutoff value for green, but are above the 10% cut-off value for red.

Ç Reference a Report Parameter from Custom Assembly 1. In the View pane, click the Layout icon to return to layout view. 2. On the Report menu, click the Report Parameters. 3. Do the following steps to create a new parameter: a.

Click the Add button

b.

In the Name textbox, type High

c.

In Data type drop down list, click Float.

d.

Clear the Allow null value checkbox. Notice that the Available values option is Non-queried.

e.

For the Default values option, click the Non-queried radio button.

f.

In the Default values textbox, type 0.15

4. Repeat, adding a parameter named Low, with all the same options, except enter 0.1 in the Default values textbox, and then click OK to close the dialog box. You now need to modify the GetColor function to use the parameter values. Even though you created the Code tag at the top of the report, the Report Designer moved it down to a lower place in the XML structure. You can use Find to get to the Code section. 5. Click the Product Profitability.rdl [XML] tab. 6. Press Ctrl+F to display the Find dialog box. 7. In the Find what textbox, type Code and click Find Next, and then click Close. 8. In the GetColor function, replace 0.15 with Parameters!High.Value and replace 0.1 with Parameters!Low.Value. Using a custom function allows you to change stoplight values in one place, even though they are referenced three different times in the report. 9. Click the Design tab for the report and then in the View pane click the Preview icon. The report appears the same as before, because the default parameters are the same as the old default values. 10. In the View pane, click the Layout icon to return to layout view. 11. On the Report menu, click the Report Parameters. 12. In the Parameters pane, click the High parameter. 13. In the Default values textbox, type 0.29 Last Saved: 9/30/2003 9:19:00 PM Last Printed: 9/30/2003 9:58:00 PM

Module 07: Programming Reporting Services

37

14. In the Parameters pane, click the Low parameter. 15. In the Default values textbox, type 0.15 and then click OK. 16. In the View pane, click the Preview icon. Many more of the values change to Red or Yellow. If you want to make additional enhancements to the color calculation, you can do them in the one custom function, rather than the three expressions scattered about the report. 17. On the File menu, click Close Solution.

Last Saved: 9/30/2003 9:19:00 PM Last Printed: 9/30/2003 9:58:00 PM