Learning SQL Server 2008 Reporting Services — Save 50%
A step-by-step book and eBook to getting the most of Microsoft SQL Server Reporting Services 2008
Report Services, Analysis Services, and Integration Services are the three pillars of Business Intelligence in Microsoft's vision that continues to evolve. Reporting is a basic activity, albeit one of the most important activities of an organization because it provides a specialized and customized view of the data of various forms (relational, text, xml etc) that live in data stores. The report is useful in making business decisions, scheduling business campaigns, or assessing the competition. The report itself may be required in hard copy in several document formats such as DOC, HTML, PDF, etc. Many times it is also required to be retrieved in an interactive form from the data store and viewed on a suitable interface, including a web browser. The Microsoft SQL Server 2005 Reporting Services, popularly known by its acronym SSRS, provides all that is necessary to create and manage reports and deploy them on a report server with output available in several document formats. The reader will greatly benefit from reading the several articles detailed in the author's Hodentek Blog. The content for the articles were developed using VS 2003, VS 2005, SQL 2000 and SQL 2005.
(For more resources on Microsoft, see here.)
The content for the present tutorial uses a Visual Studio 2008 Professional and a Microsoft SQL Server Compact 3.5 embeddable database for its data. In Visual Studio a Report Design Wizard guides you through fashioning a report from your choices.
Create a Windows Project in VS2008
Create a new project from File | New | Project. Provide a name instead of the default name (WindowsApplicaiton1). This is changed to ReportDesign for this tutorial as shown in the next figure. VS 2008 supports multi-version targeting. In the top right of the New Project window you can see that this report is targeted for the NET 2.0 Framework Version and can be published to a Net 2.0 web site.
Slightly enlarge the Form1. Drag and drop the Microsoft Report Viewer control shown in the next figure on to the form from the Toolbox.
This has the same functionality as the ReportViewer control in VS 2005 as shown in the next figure.
The control will be housed on the form as shown in the next figure. You can display the tasks needed to configure the Report Viewer by clicking on the Smart Task as shown in the same figure. The report will have all the functionalities like print, save to different formats, navigating through pages, etc.
Working with the Report Wizard
Now click on the Design a new report task. The opens the Report Wizard window as shown in the figure.
Read the instructions on this page carefully. Click on the Next Button. This displays the Data Source Configuration Wizard shown in the next figure.
Choosing a Data Source
The application can obtain data from these different resources. Click on the Database icon and then click on the Next button.
This displays the window where you need to select a connection to the data source. If there are existing connections you should be able to see them in the drop-down list box.
Making a Connection to Get Data
Click on the New Connection button. This brings up the Add Connection window showing a default connection to a Microsoft SQL Server Compact 3.5.NET Framework Data Provider. It also shows the location to be My Computer.
This source can be changed by clicking on the Change... button. This will bring up the Change Data Source window where you can choose.
As found in this version you have the following options:
- Microsoft SQL Server option lets you connect to SQL 2000 or 2005 using the .NET Framework Data Provider for SQL Server.
- Microsoft SQL Server Compact 3.5 lets you connect to a database file.
- Microsoft SQL Server Database File lets you connect to a Local Microsoft SQL Server Instance including a SQL Express. Although it is not explicitly stated what these versions are.
For this tutorial the Compact 3.5 will be used (also uses a .NET Framework Data Provider of Compact 3.5).
Click on the OK button in the Change Data Source window.
VS 2008 installation also installs a database file on the computer for the SQL Server Compact 3.5.
Click on Browse button (you could also create one if you like, herein it will be browsed). This brings up the Select SQL Server Compact 3.5 Database File window with the default location where the database file is parked as shown in the next figure.
Click on the Northwind icon in the window and click on the Open button. This updates the Add Connection window with this information as shown in the next figure.
You may test the connection by hitting the Test Connection button which should display a successful outcome as shown in the next figure. There is no need for a password as you are the owner.
Click OK twice and this will take you back to the Data Source Configuration Wizard updating the connection information which you may review as shown in the next figure.
Click on the Next button. This brings up the Microsoft Visual Studio message window giving you the option to bring this data source to your project.
eBook Price: $29.99
Book Price: $49.99
(For more resources on Microsoft, see here.)
Click on the Yes button. This adds the Data Source to your project and it will now appear in the ReportDesign project folder as Northwind.sdf. The Save the Connection String to the Application Configuration File section of the wizard also pops-up as shown.
Accept the default and click on the Next button. The window retrieves the database objects as shown in the next figure where the Tables object is shown expanded.
To keep matters simple the Customers table data will be used in fashioning this report. Place a check mark for the Customers and expand the Customers table node as shown. Also choose the columns that should appear in the report by choosing them with check marks as shown.
Designing the Report
When you click on the Finish button you will add a NorthwindDataSet to the Project. Click on the Finish button. This adds a NorthwindDataSet.xsd file to the Project and a new wizard, Report Wizard pops-up requiring you to select the data source. It will display the columns you selected in the earlier step.
You get a chance to add another data source at this point should you choose to do so. Here only the data chosen earlier will be used. Click on the Next button.
Choosing a Report Type
This opens the window where you may choose the type of report. Tabular is appropriate for the selected data.
Grouping the Data
Click on the Next button. In the window that opens you may choose to group the data in any fashion you think appropriate. In this tutorial it will be grouped under 'City' field. For designing meaningful reports it will be necessary to know the data well. The grouping is as shown in the next figure although this may not be the best.
eBook Price: $32.99
Book Price: $54.99
(For more resources on Microsoft, see here.)
Choosing Layout of Data
Click on the Next button to display the window where you may choose a table layout. A stepped layout is chosen as shown.
Choosing a Report Style
Click on the Next button to display the style for the report one may choose. Here the 'Corporate' style is chosen as shown.
Clicking on the Next button brings up the summary page for the report as shown.
Click on the Finish button. After a while the the report design shows up as Report1.rdlc[Design] as shown in the next figure.
The Report title is Report1 which can be changed by clicking in this field and typing it over (in the last figure you see this has been changed). It may also be suitably positioned and formatted. The Page level is shown by =Fileds!Customer ID.Value and the grouping by city by =Fields!City.Value under each page.
Now the Project should contain the items shown in the next figure.
Further design changes can be made to the report as needed.
Displaying the Report
Build the project, ReportDesign and you can see the report by clicking on the green arrow in the toolbar menu. Presently you will see an empty report. The design of the report and the Data Source were independently designed although it appeared like a continuous process. You still need to add a data source to the report.
Adding the Data Source to the Report
Click on the ReportViewer's Smart task and choose the default displayed.
Click on Choose Data Sources which displays the next window shown.
Click the OK button on this screen. Now build the project and run the form with the green arrow menu item.
This brings up the form and after a little processing the report gets displayed.
This tutorial described designing a simple report using the VS 2008 IDE. The reader will benefit from reading the earlier referenced articles. Compared to VS2005, the options for data source connectivity appears to be limited. The publishing of the report to a web server will be described in a future article.
About the Author :
Jayaram Krishnaswamy studied at the Indian Institute of Science in Bangalore India and Madras University in India and taught at the Indian Institute of Technology in Madras. He went to Japan on a Japanese Ministry of Education Research scholarship to complete his PhD in Electrical Engineering from Nagoya University. He was a Post-Doctoral Fellow at Sydney University in Australia; a Government of India Senior Scientific Officer at the Indian Institute of Science in Bangalore and Indian Institute of Technology at Kanpur; a Visiting Scientist at the Eindhoven Institute of Technology in the Netherlands; a visiting Professor of Physics at the Federal University in Brazil; an Associate Research Scientist at a government laboratory in São Jose dos Campos in São Paulo, Brazil; a visiting scientist at the National Research Council in Ottawa, Canada before coming to USA in 1985. He has also taught and worked at the Colorado State University in Fort Collins and North Carolina State University in Raleigh, North Carolina. He worked with Northrop Grumman Corporation on a number of projects related to high energy electron accelerators and Free Electron Lasers. These projects were undertaken at the Brookhaven National Laboratory in Long Island and in the Physics Department at Princeton University. He has over 80 publications in refereed and non-refereed publications and 8 issued patents. He is fluent in Japanese and Portuguese and lives in Honolulu, Hawaii, USA.
He has been working in IT-related fields since 1997. He was once a Microsoft Certified Trainer in Networking and a Siebel Certified developer. He has worked with several IT related companies, such as the Butler International in their Siebel practice, with several IBM sub-contractors and smaller companies. Presently he is active in writing technical articles in the IT field to many online sites such as http://CodeProject.com, http://APSFree.com, http://DevShed.com, http://DevArticles.com, http://OfficeUsers.org, http://ASPAlliance.com, Egghead Café, http://SSWUG.org, Packt Article Network, http://databasedev.co.uk, http://cimaware.com, and many others. Between 2006 and 2010 he wrote more than 400 articles mostly related to database and web related technologies covering Microsoft, Oracle, Sybase, ColdFusion, Sun, and other vendor products.
He has written four books all published by Packt related to Microsoft Database and Application Development: SQL Server Integration Services Using Visual Studio 2005, Learning SQL Server Reporting Services 2008, Microsoft SQL Azure; Enterprise Application Development, and Microsoft Visual Studio Lightswitch Business Application Development. He regularly writes for his four blogs on Blogger; http://Hodentek.blogspot.com, http://HodentekHelp.blogspot.com, http://HodentekMobile.blogspot.com, and http://HodentekMSSS.blogspot.com. He received the 2011 Microsoft Community Contributor award.