Tip 9: Drill-Down Capabilities
A common request from business analysts is the ability to drill down from summary level data to more detail-supporting data. When end users see drill-down capabilities in other OLAP tools, they assume that they should be able to do the same thing in any reporting tool. Both reporting products support drill-down features.
Drill-Down Capabilities in Crystal
Crystal supports drill-down capabilities through the Hide
property in the Section Expert (refer back to Tip 3 for more details). You can turn this option ON
for each child level in the hierarchy where you want to implement a drill-down feature.
Suppose you have a hierarchy of region/market/account and you just want to display the region data. You would simply check the Hide property in the Section Expert for market and account.
Drill-down Capabilities in SSRS
SSRS supports drill down through the use of two properties: Hidden and ToggleItem. You set the hidden property to TRUE for each child level in the hierarchy, and you set the ToggleItem property to each child's parent level.
So again, suppose you have a hierarchy of region/market/account. You would do the following:
Tip 10: Exporting Reports
- Create groups on region, and market.
- Click the Row Selector for the Market group tablerow, and set the Hidden property to TRUE and the ToggleItem property to Region (one level up).
- Click the Row selector for the detail (account) row, and set the Hidden property to TRUE and the ToggleItem property to Market.
It's become very common for reporting requirements to include export capability, with support for PDFs at the top of the list.
Exporting Capabilities in Crystal
Crystal exports to PDF, Excel, Word, and RTF. Crystal also allows you to export a page range, which is particularly helpful if you need to export a subset of a full report.
Exporting Capabilities in SSRS
SSRS exports to the following formats: XML, CSV, TIFF, PDF, web archive (MHTML), and Excel.
So as you can see, both support PDF export (arguably the most common export). SSRS does not currently support export to MS Word, though companies such as Aspose provide rendering extensions for MS Word and other formats. Crystal does not support export to TIFF, though there are several ways to programmatically incorporate this (such as implementing a custom toolbar option that takes a snapshot of the preview window area to the system clipboard).
Tip 11: Integrating Reports into Applications Programmatically
Eventually, developers will want to integrate reports into applications (either web-based or Windows Forms-based). Both reporting tools allow you to do so—once again, in different ways.
Programmatic Access in Crystal
Crystal provides a rich API for launching reports in both a browser and Windows Forms environment. I cover the Crystal API heavily in both my book and the January/February 2007 issue of CoDe Magazine. The API is complex enough that I wrote a set of reusable wrapper classes to easily set report data sources and then launch reports (see the sidebar "Additional Crystal Reports Information").
|When moving from Crystal Reports to SQL Reporting Services be aware that there are some restrictions, and some tasks may require more work.|
As I mentioned at the beginning of the article, the download contains reusable code for setting report data sources, launching a report viewer, printing and exporting, as well as modifying report object attributes on the fly. Again, Listing 1
shows an example of these tasks.
Programmatic Access in SSRS
The methodology for launching SSRS reports within an application is quite different from Crystal. You can programmatically generate reports in SSRS in one of two ways, either by URL or by the SSRS web service.
Tip 12: Previewing in Windows Forms or in the Browser
- You can specify the URL for the report, including any report parameters. Listing 2 shows some examples of this. The URL string must include the name of the Report Server to which you deployed the report, and then a parameter for the name of the report, and then any additional parameters.
- You can use the SSRS web service (ReportService2005.asmx) for greater control. You'll need to add this file as a web reference to your web application. Listing 3 shows some examples of using the web service to retrieve the list of all reports for a specific report folder.
Even with the increasing trend of exporting reports to PDF, many users still wish to view reports on the screen prior to printing or exporting. Crystal Reports provides two report viewers, one for Windows-based applications and one for web-based applications. The code for previewing reports in Listing 1
applies to Windows-based applications (through the remaining code for setting data sources, printing, and exporting).
As SSRS is essentially an ASP.NET application, users preview SSRS reports in a web browser, using the SSRS report previewer. Listing 2 and Listing 3 show how to launch SSRS reports.
Additionally, Microsoft supplies a ReportViewer control for Windows Forms applications so that users can view SSRS reports in a Windows environment. The ReportViewer control is a new UI class control in Visual Studio 2005 that renders SSRS reports. In addition to SQL Server stored procedures, you can bind an ADO.NET DataTable (typed or untyped) or custom collection to the report and then preview it using the ReportViewer.
You may find the ReportViewer control to be a godsend if you are trying to deploy SSRS reports in a Windows Forms environment. However, some developers have reported issues with this approach. For example, if you have an SSRS report that uses subreports, you have to write code to hook up an event. Others have observed that the entire process is slower than developing for browser-based applications. You will need to determine if these factors are relevant for your situation.
Tip 13: Cross-Tab and Matrix 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 provide functionality to handle this.
Let me walk through a simple example of a result set that contains three columns: Customer, Year, and Sales.
Creating a Cross-Tab Report in Crystal
You can create a Cross-Tab report in Crystal by taking the following steps:
Creating a Matrix Report in SSRS
- In the report area where you wish to insert a Cross-Tab, right-click and select "Insert
Cross-Tab" from the shortcut menu. This will launch the Cross-Tab Expert.
- Select Customer for the Rows area.
- Select Year for the columns.
- Select Sales for the summarized fields.
- If you have a subgroup (i.e., you want to break out data further underneath either customer and/or year) you can add multiple row/column definitions
- Click OK when you are finished.
SSRS uses the term matrix
instead of Cross-Tab, though they both refer to the same concept. The steps to create a matrix report in SSRS are very similar to creating a Cross-Tab report in Crystal. Essentially, you must define a minimum of three pieces of information: the data element that represents the variable number of columns for the matrix (e.g. Year), the data element that represents the rows down the left for the matrix (e.g. Customer), and the data element that contains the actual data (e.g. Sales).
Sneak Preview—Upcoming Versions
- In the report area where you wish to insert a matrix report, drag an instance of the matrix object onto the report area.
- Drag the Customer column from the DataSet list into the matrix row area.
- Drag the Year column from the DataSet list into the matrix column area.
- Drag the Sales column from the DataSet list into the matrix data area.
- Again, you can add multiple row/column groups if necessary.
- Finally, you can click the Preview tab to see the results of the matrix.
As promised at the beginning, here's a sneak peek at the next versions of these two products.
As I write this article, Business Objects has released a beta version of the next version of Crystal Reports. Here is a bullet list of some of the major enhancements in this version:
SQL Server Reporting Services 2008
- A reduced footprint for the report designer.
- Interactive sorting of report columns.
- Support for web pagination and an improved web report viewer.
- Cross-Tab enhancements (ability to add Cross-Tab custom summaries, either repeating or one-time).
- Built-in barcode font support (Crystal now includes Code39 barcode fonts).
- Interactive report parameters (customers can change report parameters on the fly, and the developer can decide which parameters can be modified).
- Ability to define XML export format options.
- Flash integration—you can embed a Shockwave Flash (SWF) object inside reports.
- Improvements to the report engine for performance and resource utilization.
As I write this article, Microsoft has released the August 2007 Community Technology Preview (CTP) for SQL Server 2008 (code named "Katmai"). Here is a bullet list of some of the major enhancements in this version:
- A new report control called Tablix, which combines the flexibility of the report table with the cross-tab capabilities of the report matrix.
- Improvements to the report engine for performance and resource utilization.
- A new hosting model independent of IIS.
- New charting tool capabilities (Microsoft has purchased the rights to the Dundas charting capabilities).
- SharePoint integration to access and manage reports and data sources from a SharePoint site.