Login | Register   
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 4

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

Tip 4: Formatting and Formula Options and Expressions
Rarely will you ever drag and drop data elements on a report and generate the report "as is." Most of the time, you'll need to format and decorate the content of the report. From items such as formatting dates and currency to dynamically highlighting content based on a condition, Crystal and SSRS offer many formatting options.

Formatting Options in Crystal
You can right-click a report object to bring up the Format Editor dialog box, which provides a number of formatting options. I'll describe the most common options:

  • CSS (Cascading Style Sheet). Specify a CSS class name to achieve a consistent appearance for report objects.
  • Rotate Text. Rotate text at 90- and 270-degree angles.
  • Watermarks. You can create a watermark image effect by inserting the image in the page header, and then setting "Underlay following sections" in the Section Expert for the page header.
  • Tooltips. Crystal allows you to set tooltip text for a specific report item, which might be helpful if you have a complex report calculation that you want to explain to the user. The Reportviewer control also has a property called EnableToolTips, so that you can programmatically turn tooltips on or off.
  • Suppressing Duplicates. Sometimes you may want to create the effect of a report group header without actually using a group. In these instances the data may contain duplicate information (such as a name, or account, etc.) where you only want to display the first occurrence of the data. This option will suppress all subsequent instances of duplicate values.
  • Date Formatting and Numeric/Currency Formatting. Crystal provides a rich set of options to format date and numeric/currency data. Crystal provides two separate dialog forms for these two data types, with many checkbox options to format dates and numbers in many different ways.
  • RTF/HTML Rendering. By default, Crystal renders field data as text; however, you may need to display content stored as rich text or HTML. If you right-click a text object, go to the Format Object Editor, and navigate to the Paragraph tab, you'll see a pulldown option for Text Interpretation with options for RTF and HTML.
  • Conversions. Sometimes you may need to convert from one datatype to another. For instance, you may want to display a numeric field as text, but without any decimal positions. You can create a Formula Field that uses the Crystal function ToText() to represent the data, as follows:
  • ' Returns a string value of a numeric column ' with zero decimals ToText(MyTable.MyNumericVal,0)

  • Expressions/Calculated Fields. I generally discourage calculations in report writers and recommend instead that developers perform calculations in the process that creates the report result set (either in a stored procedure or in a business layer). Having said that, sometimes you may need to add a calculated element to a report. To do so, add a Formula Field into the report, enter the calculation in the Formula Field Editor, and drag the Formula Field onto the appropriate area of the report.
  • Dynamic Formatting. If you need to show an area of the report in a different attribute based on a value, you can enter a formula expression for that attribute. For instance, if you want to set the color of a field object based on a value, you can enter the following formula:
  • if {MyTable.MyTotalCount} > 1 then crred else crblack

  • If you want to conditionally bold a field object, you can do the following:

  • if {MyTable.MyTotalCount} > 1 then crBold else crRegular

Formatting Options in SSRS
For the items above, SSRS does not currently support CSS, text rotation, or RTF rendering. Here are the equivalent steps you would take in SSRS for the remainder of the items above:

  • Watermarks. SSRS doesn't have direct support for a watermark. However, you can find a workaround on the SearchSQLServer site by searching on "watermark."
  • Tooltips. On the General Properties tab of a textbox, you can specify tooltip text.
  • Hide Duplicates. On the General Properties tab of a textbox, you can hide duplicates.
  • Date Formatting and Numeric/Currency Formatting. SSRS does not provide full-blown dialog boxes like Crystal to format dates and numeric data. Instead, you'll need to enter a short formula or expression to format this type of data. For instance, you can use the built-in functions FormatCurrency and FormatDateTime as follows:
  • =FormatCurrency(Fields!Freight.Value ,2) =FormatDateTime(Fields!ShippedDate.Value)

  • Alternatively, you can enter format codes in the Format tab of the Properties window. The format codes for currency or short date are C2 and D0, respectively.
  • Conversions. Just like Crystal, SSRS offers functions to convert from one data type to another. For example, you can use the STR function in the SSRS Expression Editor to convert from a numeric to a string.
  • Expressions/Calculated Fields. Again, I discourage this, but if necessary, you can specify a calculation for a column in the column expression area. In addition, SSRS also offers a function for maintaining running totals, RunningValue, which you can optionally reset based on an existing report group. Finally, you can add a calculation that uses a value from a previously rendered textbox (Tip 6 will cover this in a little more detail):
  • =SUM(Fields!AmountDue) / ReportItems!OtherTextBox.Value

  • Dynamic Formatting. SSRS provides the same general support for dynamic formatting. In the two examples for setting color and font state, you can enter the following expressions in the Properties window:
  • =IIF( Fields!StandardPrice.Value<45, "Red", "Black" ) =IIF( Fields!StandardPrice.Value<45, "Bold", "Normal" )

  • Note that SSRS also offers a SWITCH statement if you need to evaluate more than one condition.
  • Defining a Hyperlink. If you want to define a textbox as a hyperlink, you can go to the Navigation tab of the Properties window, and define a hyperlink that will direct the end user to another web page or even an email address (by using mailto). This functionality exists for Crystal as well. An impressive feature is that both products will retain this hyperlink even when you export the report to a PDF!
Additionally, SSRS contains a formatting option that Crystal does not have—the ability to define a detail column so that the user can interactively sort on it. In reality, many reports have multiple levels of grouping and predefined sort orders that make the need for interactive sorting to be marginal at best; however, other types of detailed listings will benefit from this type of option.

Finally, SSRS 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. As I stated earlier, Rod Paddock wrote an excellent article on this topic in the January/February 2007 issue of CoDe Magazine.

Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.