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.