RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX


The Baker's Dozen: 13 Comparisons Between Crystal Reports and SQL Server Reporting Services : Page 5

Explore how the rich reporting functionality in Crystal Reports XI and SQL Server Reporting Services 2005 works with common reporting requirements.

Tip 5: Page Control Options
If you've ever built reports for insurance companies or financial institutions, you know how important it is to be able to manage page breaks. A client or employer might quickly lose confidence in a reporting solution (or the developer) if they hear a response of, "I don't know how to do that" or "The report writer doesn't give me the option of doing that."

I've covered part of this back in Tip 3: Sections and Groups, but this is such an important topic that it bears repeating. Crystal generally provides better support for page control through the Keep Together options for both individual report sections as well as entire report groups. While SSRS allows you to define a page break at the end of a group, it will not conditionally allow you to essentially implement "widow-orphan" protection to ensure that a group or section will fit on a page. SSRS does implement a Keep Together property for the rectangle control, as well as a certain level of page control through the use of subreports, though those who have used the Keep Together function in Crystal are not likely to consider this an elegant workaround.

Tip 6: Global Report Variables and Report Parameters
Most report writers track and expose system variables that reflect the report's run-time environment. Developers often need to annotate a report with such information as current database, page count, current user running the report, etc. Additionally, developers need the ability to define run-time parameters for the report. Both report products provide these capabilities, albeit in slightly different ways.

Figure 13: Special Fields in Crystal.
Global Variables and Parameters in Crystal
Crystal refers to these as Special Fields. Figure 13 shows all the Special Fields that Crystal provides. This includes common variables like "Page N of M" for page numbering along with other variables for general report annotation.

Global Variables and Parameters in SSRS
SSRS breaks apart all report variables into the following categories:

  • Fields. All the columns that are available for the current DataSet.
  • Globals. Page number variables, Execution Time, Report Name, etc. Note that you cannot place a page number variable anywhere other than a page header or page footer in SSRS.
  • User. The current User ID running the report.
  • Parameters. Any parameters you've defined for the report.
  • ReportItems. A collection of all the report objects in the report—this is very helpful if you want to annotate or reference something from a value of a particular object on the report.
Figure 14: Report Global Variables in SSRS.
Figure 14 shows the SSRS Edit Expression editor, along with an example that uses IntelliSense to display values from multiple report variable categories.

Tip 7: Subreports
As the name implies, a subreport is a report within a report. Typically you build a subreport when you need to display multiple levels of detail data, and/or when you need to display data from multiple data sources. In most instances you'll need to establish a link between the subreport and the parent report

Subreports in Crystal
Here are examples where you'd build subreports in Crystal:

  • If you build a report component (like a reusable header or footer) and wish to utilize the report as a subreport across many reports. In this case, you would build the report component, and then "insert" the report as a subreport in other reports, and set any necessary data subreport links.
  • If you are displaying multiple charts on a page. For example, you might generate a report that shows a line chart and a bar chart for each customer. In this case, your result set would likely contain three tables: one for the customer list, one for the details for the line chart, and one for the details for the bar chart. You would probably create a group on customer, build two subreports for the line chart and bar chart, and establish a subreport link between the customer in the customer list table and the customer in the two chart tables.
  • If you are displaying multiple levels of detail (perhaps to go along with charts).
In order to guarantee that subsequent changes to a subreport will automatically cascade to the parent report(s), make sure to check the Re-import When Opening option (in the Format Object/Format Editor/Subreports option dialog box).

Subreports in SSRS
At face value, you might think that subreports in SSRS are very similar to Crystal. However, there are some differences.

  • You can "nest" subreports in SSRS, which is something that you cannot do in Crystal.
  • In SSRS, you cannot insert a subreport into the Page Header or Page Footer area. This makes it difficult to build a common header/footer template.
  • In some instances you'll need subreports in SSRS for situations where subreports would not be necessary in Crystal. You'll see this most commonly when a stored procedure or web service returns multiple tables in the result set.
Tip 8: Charts
Both report writers come equipped with charting tools to incorporate business graphs into a report. While you can purchase third-party charting tools for more advanced capabilities, the charting tools in both Crystal and SSRS are often adequate for many business needs.

Charting Capabilities in Crystal
Crystal allows you to add the following chart types: Bar (horizontal or vertical), Line, Area, Pie, Doughnut, 3D Riser, 3D Surface, XY Scatter, Radar, Bubble, Stock, Numeric Axis, Gauge, Gantt, and Funnel.

As I've written in prior Crystal articles, building charts is about three things: data, data, and data. Charting engines use data definitions for chart rows, columns, X-axis, and Y-axis definitions. So building a complete result set for a chart is often more than half the battle.

Charting Capabilities in SSRS
SSRS supports the following chart types: Column (vertical bar chart), Bar (horizontal bar chart), Area, Line, Pie, Doughnut, Scatter, Bubble, and Stock.

Despite the greater number of chart types in the Crystal list, both tools are very similar in terms of overall charting functionality. Both tools contain chart options dialog screens that are quite similar to charting configuration options in Microsoft Excel and other popular charting tools.

The only notable omission is that SSRS does not support a Gantt chart type, which developers use to provide a pictorial timeline of data.

A final note on charting in SSRS: recently I had to develop a Pie chart where the pie slice labels indicated the % of the total. You can do this by going into "Chart Properties…Data…Edit…Point Labels" and then entering the following formula in the Data Label:

   -- Assuming a column called Monthly_Sales
   -- and assume a DataSet called DataSet1.
   = round((Sum(Fields!Monthly_Sales.Value)  /
      * 100,2)
   -- Note that if your report contained groups, 
   -- you would replace "DataSet1" with the name
   -- of the group.
This reflects SSRS' occasional lack of direct support for functionality but open architecture for implementing that function.

Author's Note: You can find the source code for this article on my site.

Close Icon
Thanks for your registration, follow us on our social networks to keep up-to-date