MSSAP Why talking about reports? Reporting is one of the key elements of Business Intelligence technologies.

- The reason for this article is basically that there is a sort of dislike and panic involved in approaching a Reporting problem.

- Nobody (that I know of) likes working in reports projects.

- Reports are a different beast than OOP, with a complete different nature, programming experience and solution approach.

This is intended to illustrate and discuss the development of reports using Crystal Reports (CR) and SQL Server Reporting Services (SSRS).

CR and SSRS are 2 COMPLETELY DIFFERENT technologies that produce the same result: A Report.

Neither Crystal nor SSRS have a full object-oriented report writer. Both are object-based with a rich object model, and feature a certain amount of cascading. Additionally, Crystal supports CSS themes. However, OOP enthusiasts will need to lower their expectations and adjust to the fact that neither product supports true object-oriented features

In general we can say that SSRS contains roughly 70–75 percent of the functionality of Crystal Reports. Some companies have decided to switch from Crystal to SSRS, and others are contemplating the switch: therefore, it's very important to know what functionality may be lost during the process.

CRYSTAL REPORTS

As of today Crystal Reports is the most used general purpose reporting tool in the market (about 55%) Crystal reports is owned by SAP AG (a German Company), Business Objects was acquired by SAP.

It’s been in the market since 1980, when the first Crystal Report version I. Today the product has evolved and has had 11 versions spanning over 20 years.

REPORTING SERVICES

Reporting Services is much easier to use and has a more rich experience since it supports things like having a Chart AND a table in the same report section. Today it has about 20% of the reporting market share. SQL Server Reporting Services is owned by Microsoft, as part of subsystem that belongs to their SQL Server Enterprise Solutions branch.

It’s been in the market since 2004 and it has 3 versions so far (SRS2000, SRS2005 and SRS2008)

Comparison:

  • Report creation:

Both offer report wizard that helps to define a data source and build general report layout. They also support manual definition where you’ll start with an empty report and modify it from there.

  • Report data sources:
Crystal Reports SQL Reporting Services
Data Source from: DataSet, .Net Class, OLE DB, SQL Server, Oracle, etc.Pull Model: External Data Source like a store procedure. Data Source from: DataSet, .Net Class, OLE DB, SQL Server, Oracle, or XML.

You can specify an XML web service as the data source for an SSRS report!!! To do this you must follow the next steps:

1- You define the data source in the connection string like this:

       data source=http://localhost/test.aspx
    

2- Use the code below for the query syntax (in the report dataset query).

       // Syntax to query XML web service for an SSRS DataSet
       <Query>
         <SoapAction>
           http://localhost/TestSSRSSite/GetData
         </SoapAction>
         <Method Namespace = "http://localhost/TestSSRSSite/"
           Name = "GetData" >
           <Parameters>
            <Parameter>
             <DefaultValue>1</DefaultValue>
            </Parameter>
           </Parameters>
         </Method>
         <ElementPath IgnoreNamespaces="True">
           GetDataResponse{}/GetDataResult{}/
           diffgram{}/MyDataSet{}/
           MyDataTable{Firstname,LastName}
         </ElementPath>
       </Query>
    
    
  • Linking Tables:

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.

Crystal Reports SQL Reporting Services
Supports multiple tables when pushing data through datasets/XML into the report by using its tables collection database object. It allows put to place columns from != tables on the same report body respecting relationships. Supports only one result set. To use multiple result sets, multiple datasets must be created and use sub-reports to link the data from the datasets
  • Report sections and groups: Both offer similar approach to groups and report sections like header, footer, sections, groups and details area.
  • Formatting and formula options: Here Crystal offers a more powerful approach since it supports a larger variety of formatting options than SSRS.
Crystal Reports SQL Reporting Services
Supports:

  1. CSS
  2. Rotate Text
  3. Watermarks
  4. Tooltips
  5. Suppressing Duplicates
  6. Date formatting
  7. Currency formatting
  8. RTF/HTML Rendering (Rich Text Format)
  9. Datatype conversion.
  10. Expressions/Calculated fields
  11. Dynamic formatting
  12. Hyperlinks
Supports:

  1. Watermarks
  2. Tooltips
  3. Suppressing Duplicates (Hide)
  4. Date formatting
  5. Currency formatting
  6. Datatype conversion
  7. Expressions/Calculated fields
  8. Dynamic formatting (+ SWITCH statement for multiple condition evaluation)
  9. Hyperlinks

- No CSS, Text rotation or RTF (Rich Text Format) rendering

- Allows you to add custom code to reports, either by embedding Visual Basic code directly into reports, or by adding externally created and compiled .NET assemblies

  • Page control options:

Crystal generally provides better support for page control through the Keep Together options for both individual report sections as well as entire report groups.

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 that is very buggy at this moment and is consider an uncooked feature.

  • Report parameters and global report variables:

Both reporting products support parameters and report variables.

  • Sub-Reports:

Typically you build a sub-report 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 sub-report and the parent report.

Crystal Reports SQL Reporting Services
You can “Insert” sub-reports as reusable sections for parent reports (like a reusable header or footer)

Different charts for shared datasets can be linked to their own sub-report and be reuse across multiple parent reports.

You cannot insert a sub-report into sections of other reports. This makes difficult to build common header/footer templates.

Different charts for shared datasets MUST have their own sub-report since they cannot be used with the same result set, if the set is different than the one in the parent.

  • Charts:
Crystal Reports SQL Reporting Services
  1. Bars (horizontal & vertical)
  2. Lines
  3. Area
  4. Pie
  5. Doughnut
  6. 3D Raiser
  7. 3D Surface
  8. XY Scatter
  9. Radar
  10. Bubble
  11. Stock
  12. Numeric Axis
  13. Gauge
  14. Gantt (timeline)
  15. Funnel
  1. Horizontal Bar
  2. Vertical Column
  3. Area
  4. Line
  5. Pie
  6. Doughnut
  7. Scatter
  8. Bubble
  9. Stock
  • Drill-down capabilities:

Both reporting products support drill-down features. Crystal though its Hide property in the Section Expert and SSRS though its Hidden and ToggleItem properties.

  • Exporting reports:
Crystal Reports SQL Reporting Services
PDF, Excel, Word and RTF.

Note: No XML, TIFF or CSV support.

XML, CSV, TIFF, PDF, Web Archive (MHTML) and Excel.

Note: No Word support.

  • Programmatic access:
Crystal Reports SQL Reporting Services
  1. Extensive and complex API with a lot of 3rd party wrapper tools.
  2. Does not directly support web service report definition control (3rd party tools will)
  1. Great open architecture for implementing any function, but many CR features are in developing stage yet.
  2. Built in support to generate reports from a report URL or a Web Service.
  • Previewing in Windows Forms or in the browser:

Both reporting products support previewing reports in Windows Form or in the browser. Note that SSRS intrinsically support for the web is superior to Crystal because SSRS is essentially an ASP.NET application

  • Matrix and Cross-Tab objects:

Sometimes you may want to create reports with a variable number of columns based on the amount of data in a result set. For instance, you might have a result set of orders by year where the year is a row. You want to display a column for each year, but you may not know at design time how many years the result set will contain.

Both reporting products offer decent solutions for this situation through Crystal’s Cross-Tab Report and SSRS’s Matrix report.

UPCOMING VERSIONS

Crystal Reports (Beta Release Now Available) SQL Reporting Services (CTP Now Available “Katmai”)
  1. A reduced footprint for the report designer.
  2. Interactive sorting of report columns
  3. Support for web pagination and an improved web report viewer
  4. Cross-Tab enhancements (ability to add Cross-Tab custom summaries, either repeating or one-time)
  5. Built-in barcode font support (Crystal now includes Code39 barcode fonts)
  6. Interactive report parameters (customers can change report parameters on the fly, and the developer can decide which parameters can be modified)
  7. Ability to define XML export format options.
  8. Flash integration—you can embed a Shockwave Flash (SWF) object inside reports.
  9. Improvements to the report engine for performance and resource utilization
  1. A new report control called Tablix, which combines the flexibility of the report table with the cross-tab capabilities of the report matrix.
  2. Improvements to the report engine for performance and resource utilization.
  3. A new hosting model independent of IIS.
  4. New charting tool capabilities (Microsoft has purchased the rights to the Dundas charting capabilities)
  5. SharePoint integration to access and manage reports and data sources from a SharePoint site

Conclusions:

Crystal Reports has the benefit of being the veteran in the field and supports almost every conceivable reporting requirement there is. It has versions for developers as well as business users. SSRS is the new kid on the block and it is focused only on the developer market. SSRS is a 1.0 release and still has some growing pains to go through

Comment