Reporting to 500

expert's view The second of a three-part guide on corporate reporting. This week: how to create and distribute a report to 500 sales reps using SQL Reporting Services.
Written by Vivek Bhatnagar, Contributor

Editor's note: This is the second in a series of three features on corporate reporting. Check out the first instalment here. Next week's article is about how to create a robust reporting process.

Imagine your company launches a new product. Great!
Now imagine you, as your company's IT manager, suddenly gets called to create new sales reports that inform its users--your company's sales staff--on sales updates and data that helps marketing. And imagine you have to distribute those reports, weekly, to a sales force of 500.
Unfortunately, if you manage your company's database server, these kinds of tasks may land on your shoulders. If you are using Microsoft's SQL Server 2000, however, there is a free bundle that lets you do reporting.
This tutorial provides step-by-step instructions for creating a report using SQL Server 2000 Reporting Services. In this tutorial, we reveal how simple it is to create a basic report based on data stored in the Northwind database and walk through the web interface of the Report Manager. We also show previous reports generated and deployed and how to manage access to this server by creating a new role and assigning the role to a user. We then show the different properties of a report and how to create a historical snapshot of a report. Finally, we show how to set up a subscription to a report and then view the archive after it is sent.

​Microsoft SQL Server Reporting Services: A recap
Recently released by Microsoft as a free bundle to SQL Server 2000, Reporting Services is a centrally managed, server-based reporting platform adaptable to both for desktop and Web-based applications. In other words, reports can be both paper-oriented reports, or as interactive, Web-based forms.
Reporting Services includes:
  • A complete set of tools for creating, managing, and viewing reports
  • An engine for hosting and processing reports
  • An extensible architecture and open interfaces for embedding reports or integrating the solution in diverse IT environments. Specifically, you can extend the product's native capabilities for data sources, rendering, or report delivery.
Further, the use of application programming interfaces (APIs) lets software developers integrate reporting with legacy systems or third-party applications.

The overview provided below is sufficient to install, configure, and start working with Reporting Services.
At the end of this tutorial, we will have covered:
  • Report Project Wizard
  • Report Customization and Importing
  • Report Manager.
Tutorial Setup
This session requires one machine equipped with the following:
  • One server with the following minimum hardware specs: 3650 Mhz Pentium processor, 512MB memory, Windows Server 2003 operating system
  • Visual Studio.NET 2003
  • SQL Server 2000
  • SQL Server 2000 Reporting Services
  • Office 2003
Here are the installation instructions:
  1. Create a basic install of Windows Server 2003 with computer name SERVER1.
  2. Install Visual Studio.NET 2003 with all default settings.
  3. Install SQL Server 2000 Enterprise Edition, and note to accept all the defaults, but set the authentication mode to "mixed mode" with a blank password.
  4. Install SQL Server 2000 Service Pack 3. Download sql2ksp3.exe and install with default options.
  5. Set SQLServerAgent to AutoStart and Start the Service. Navigate to Start | Control Panel | Administrative Tools | Services
  6. Disable Enhanced Internet Security. Navigate to Start | Control Panel | Add or Remove Programs. Then click Add/Remove Windows Components. And lastly, uncheck Internet Explorer Enhanced Security Configuration and then click next and finish.
  7. Install SQL Server 2000 Reporting Services. Here are the detailed steps:
    • Double click Setup.exe.
    • Accept the licensing agreement and click next 6 times.
    • Click to deselect the Use SSL connections when retrieving data on these virtual directories check box.
    • Click Next.
    • In the Report Server Database screen, select SQL Login Account in the Credentials Type drop down.
    • Enter SA in the Username box.
    • Leave the Password box blank.
    • Click Next.
    • Click Next.
    • Choose Per Processor License.
    • Enter 1 for the number of processors.
    • Click Next.
    • Click Install.
  8. Install Microsoft Office System with default option.
    Creating a new report
    Before getting into the authoring process, it is useful to understand the reporting services architecture, as shown below:

    Here are the steps to create a new report and authoring via the Reporting Services interface:
    1. Click Start | All Programs | Microsoft Visual Studio .NET 2003 | Microsoft Visual Studio .NET 2003.
    2. Click File | New | Project.
    3. Select Business Intelligence Projects in the Project Types pane and select Report Project Wizard in the Templates pane.
    4. Name as CustomReport.
    5. Click Browse.
    6. Navigate to C:\tutorial
    7. Click Open.
    8. Click OK.
    9. Click Next.
    10. Click Edit on the Select the Data Source page.
    11. Type SERVER1 in the Select or enter a server name box.
    12. Click to select the Use Windows NT Integrated Security radio button.
    13. Click the Select the database on the server drop-down and select Northwind.
    14. Click Test Connection.
    15. Click OK.
    16. Click OK.
    17. Click Next.
    18. On the Design the Query page, click Edit.
    19. Double-click the Query Builder title bar.
    20. Right-click in the Query Builder, and then click Add Table.
    21. Click Orders, and then click Add.
    22. Click Order Details, and then click Add.
    23. Click Customers, and then click Add.
    24. Click Close.
    25. In the Orders table, click to select the OrderDate checkbox.
    26. In the OrderDetails table, click to select the UnitPrice checkbox.
    27. In the OrderDetails table, click to select the Quantity checkbox.
    28. In the Customers table, click to select the CompanyName checkbox.
    29. Click OK.
    30. Click Next.
    31. Click Next.
    32. In the Available fields pane, click CompanyName.
    33. Click Group.
    34. In the Available fields pane, click OrderDate.
    35. Click Details.
    36. In the Available fields pane, click UnitPrice.
    37. Click Details.
    38. In the Available fields pane, click Quantity.
    39. Click Details.
    40. Click Next.
    41. Ensure the Stepped radio button is selected.
    42. Click to select the Include subtotals checkbox.
    43. Click to select the Enable drilldown checkbox.
    44. Click Next.
    45. Click Corporate.
    46. Click Next.
    47. Click Next.
    48. Click Finish.
    49. In the Report1.rdl [Design] page, click the Preview tab.
    50. Expand Company Name next to Alfreds Futterkiste.
    51. Click the down arrow next to the Export the report to other format button the Preview pane.
    52. Click Web Archive.
    53. Navigate to C:\tutorial
    54. Click Save.
    55. In the Solution Explorer, right-click Report1.rdl, and then click View Code.
    56. In the Solution Explorer, click Report Project1.
    57. Click Build | Deploy Report Project1.
    58. Click File | Close Solution.
    Customizing existing reports
    Here are the steps for editing a report:
    1. Click File | Open | Project.
    2. Navigate to C:\tutorial
    3. Click on customReport.sln.
    4. Click Open.
    5. In the Solution Explorer, double-click on Report1.rdl.
    6. In the Report1.rdl[Design], click the Preview tab.
    7. Click the Data tab.
    8. In the Customers table, click to select the ContactName checkbox.
    9. Click the Layout tab.
    10. In the Body, click Quantity.
    11. Right-click the Column Header above Quantity, and then click Insert Column to the Right.
    12. From the Fields pane, drag ContactName onto the first row of the new column textbox.
    13. Click Report | Page Header.
    14. Click Report | Page Footer.
    15. In the Body, click and drag the Customer Report title onto the Page Header.
    16. Click View | Toolbox.
    17. Drag a TextBox control onto the Page Footer.
    18. Right-click the TextBox, and then click Expression.
    19. Expand Globals.
    20. Click PageNumber, and then click Insert.
    21. Click OK.
    22. Click the Preview tab, and then click the Next Page button.
    23. In the Solution Explorer, click CustomReport.
    24. Click Build | Deploy CustomReport.
    25. Click File | Close Solution.
    Using the report manager
    In this last section, you see how to use the Report Manager to gain access to reports and to schedule delivery of updated reports.
  • Click Start | All Programs | Internet Explorer.
  • Navigate to http://server1/reports/.
  • Click the Properties tab.
  • Click New Role Assignment.
  • Click New Role.
  • For Name, type Readers.
  • For Description, type Read-only access to reports.
  • Click to select the View reports checkbox.
  • Click OK.
  • For Group or username, type administrator.
  • Click to select the Readers checkbox.
  • Click OK.
  • Point to the new user and role.
  • Click Contents.
  • Click customReport
  • Click Report1.
  • View the report.
  • Click the Home link under the SQL Server Reporting Services title.
  • Click customReport
  • Click Report1.
  • Click the Properties tab.
  • Point to the General properties.
  • Click Data Sources.
  • Under Connect Using, click to select the Credentials stored securely in report server radio button.
  • For username, type sa.
  • For password, leave blank.
  • Click Apply.
  • Click Execution.
  • Click History.
  • Click Security.
  • Click the History tab.
  • Click New Snapshot.
  • Point to the new snapshot link.
  • Click the Subscriptions tab.
  • Click New Subscription.
  • In the Delivered by drop-down, select Report Server File Share.
  • In the File Name textbox, type SubscriptionReport.
  • In the Path textbox, type \\server1\c$\tutorial
  • In the Render Format drop-down, select Web Archive.
  • In the UserName text box, type Administrator.
  • In the Password text box, type password.
  • Click to select the Increment file names as newer versions are added radio button.
  • Click Select Schedule.
  • Click to select the Once radio button.
  • Under One-time Schedule, for the Start time, type the Current Time + 2 minutes. Make sure you check either AM or PM for the appropriate time.
  • Click OK.
  • In the Password textbox, type password.
  • Click OK.
  • Point to the new subscription.
  • Click New Data-driven Subscription.
  • Click Cancel.
  • Navigate to Start | All Programs | Accessories | Windows Explorer.
  • Navigate to C:\tutorial
  • Double-click on SubscriptionReport.mhtml. (Wait until the two minutes have passed.)
  • Close all windows.

    Vivek Bhatnagar is a Solution Architect at Microsoft Corporation. Prior to Microsoft, he worked as an Evangelist and a consultant at IBM, Oracle Consulting and Sun Microsystems.
  • Editorial standards