Tip 1: Creating Reports
For experienced users, creating new reports may seem almost as mundane as creating a new Word document or a class file; however, new users usually need some sort of guide to create a basic report structure.
Creating New Reports in Crystal
You can create new Crystal files either in Visual Studio 2005 (by adding a new item and selecting Crystal Reports as the item type), or by using the standalone Crystal product. The Crystal designer in Visual Studio 2005 contains most of the functionality of the standalone product.
Crystal provides three options for creating reports:
- You can use the new report wizard that helps to define a data source and build the general report layout.
- You can start with a blank report.
- You can create a report from an existing report. This option is helpful if you have a template report that you wish to use as the basis for all future reports.
With the second or third option, you'll eventually need to establish a design-time data source for the report.
Generally, a new report will contain a report header and footer section, a page header and footer section, and a detail band. You can add sections and groups based on the report requirements, which I'll cover throughout this article. The Crystal designer contains a WYSIWYG previewer—and in many instances, you can actually modify report elements while in preview mode. A saved Crystal Report is a binary file in a propriety format. You can add reports as strongly typed items to your Visual Studio project, or you can keep them outside your .NET project and use them as untyped reports.
Creating New Reports in SSRS
To create SSRS reports, you must first create a Business Intelligence project in Visual Studio 2005, and select Report Server Project from the list of installed templates (see Figure 1
). You'll see the BI project type and the Report Server template if you have SQL Server 2005 and SSRS installed. After you create the new project, you can set target URL deployment and other options in the project properties dialog box (see Figure 2
). A Report Server project contains shared data sources and report files (see Figure 3
|Figure 1: Creating a New SSRS Reporting Project.||
SSRS provides two options: You can use the Add New Report wizard (again, to define a data source and general layout), or you can start with a blank report. If you want to create reports from a base template report, you can manually make a copy of the file outside of Visual Studio (i.e., in Windows Explorer), and then go back to Visual Studio and add the copy as an existing item.
|Do not confuse an ADO.NET DataSet with a SQL Server Reporting Services DataSet. An SSRS DataSet only holds one table.|
A blank SSRS report does not contain as many default sections as a Crystal report, but you can easily add sections (such as headers, a table layout, etc.). The design environment for SSRS contains three tabs: one for data definition, one for layout, and one for preview. Note that unlike Crystal, SSRS preview mode does not allow you to modify any formatting on the fly—you need to go back to the Design tab, make the change, and return to the Preview tab.
An SSRS report uses an extension of RDL (Report Description Language) and the file itself is an XML file. Some software companies have produced their own RDL designer. These companies include: Cizer, Panorama Software, Panscopic, and ProClarity. In some instances, these third-party RDL designers are part of the vendor's BI solution.
Tip 2: Report Data Sources
|Figure 4: Specifying a Data Source in Crystal.|
A report is essentially an analytical view of the data—an extension of the UI. Therefore, it stands to reason that the relationship between reports and data is similar to the relationship between data-bound controls and data. You design a report against some type of representation of report data, and then run the report against a populated instance of the original representation.
Creating a Data Source in Crystal—the Push and Pull Models
Crystal allows you to specify a design-time data source (see Figure 4
), which can be a typed DataSet, a .NET class object, an OLE DB source, etc. This generally reflects Crystal's support for the report models commonly known as the push model
and the pull model
. In the push model, developers will "push" data into an instance of the report, usually by means of typed DataSets or XML data, or by means of a custom collection (for example, a .NET List object). By contrast, the pull model describes a process where the report "pulls" data from an external data source (quite often, a stored procedure).
Creating a Data Source in SSRS
|Figure 5: Creating a Shared Data Source for SSRS.|
In SSRS, you can specify the data source for a report in one of two ways: You can add a shared data source to your report project, or you can add a specific data source that's scoped to a specific report. Either way, SSRS provides you with a dialog box (see Figure 5
) to define your data source. Note the Type pull-down in Figure 5
(defaulted to Microsoft SQL Server): SSRS allows you to specify Microsoft SQL Server, SQL Server Analysis Services, Oracle, XML, or any OLE DB source as the data source for the report. (Yes, that's right; you can specify an XML web service as the data source for an SSRS report! I'll cover that momentarily.)
Additionally, you can write a custom data extension to utilize your own .NET classes as a Data Source. Rod Paddock has written two excellent articles not only on implementing this, but also on integrating other .NET DLLs with SSRS. You can find his articles in the July/August 2004 and January/February 2007 issues of CoDe Magazine
|When using the Crystal Reports push model, make sure to account for EVERY report database object at runtime.|
Finally, in order to specify an XML web service for the data source for an SSRS report, you have to follow two steps.
- You define the data source in the connection string as follows:
Use the code below for the query syntax (in the report dataset query).
Linking Tables in a Report Data Source
// Syntax to query XML web service for an SSRS DataSet
<Method Namespace = "http://localhost/TestSSRSSite/"
Name = "GetData" >
<Parameter Name="EmployeeID" Type="int">
If you use stored procedures (or in-line SQL queries) directly as your data source, be aware both report products will only allow you to access the first table in the result set. So for both products, if you have stored procedures that return multiple result sets, the only way to access every table in the result set is to call the stored procedure once for every result set (usually not a very good approach).
In fact, the SSRS DataSet will only store one result set, period. If you need multiple result sets for your report, you either must create a Cartesian product in your SQL queries, or you must create multiple datasets for the report and use subreports to link the data from the datasets.
However, Crystal does allow multiple tables when you use the "push" model to push datasets/XML data into the report. (Crystal has a tables
collection in a report's database object.) So as long as you use the report push model, you can pass multiple tables into the report, and you can create linked relationships between the tables. Figure 6
shows the Links tab of the Database Expert dialog box, which allows you to define relationships between tables. Once you've done that, Crystal will allow you to place columns from multiple tables on the report body (respecting any relationships), without needing to use subreports.