Basic Report Design - Professional SQL Server 2005 Reporting Services - page 112

Aggregate expressions may be entered in different ways. The expression may be typed directly into the
text box or into the value property using the property sheet window or customer property page window.
Next to each applicable property, a button can be used to open the Expression Builder dialog box, which
can be used to assemble the expression. This is discussed in detail in Chapter 6.

Here is a condensed view of the same report shown in the print preview. Since you are using the Count
function on the Color field value, rows that don't have a value in this column (the value is Null ) have a
count of 0 . First you see four sections with subtotals for the Sub Category field, and then, further down
the page, you see rollups for the Category and then for the entire report, as shown in Figure 4-57.

Formatting
Many data values need to be formatted appropriately because the default formats are usually not accept-
able. The following table shows common SQL Server data types and their unformatted defaults.

Data Type Default Display Example

Float Large number of decimal 123456789.123456

positions with no rounding 1.23456789012346E+19

r truncation. Large numbers
with no thousand separators
or scientific notation.

Decimal Large numbers with no thousand 123456789.1234

separators. The number of decimal
positions is defined by column's
scale attribute.

Int, Large numbers with no thousand 123456789

SmallInt, separators.

BigInt

Money Up to four decimal positions. Large 123456789.1234

numbers with no thousand
separators.

Date Always displays date and time. 11/1/2003 3:34:26 PM

Seconds included.

Bit Displays the words True or False. True

False

If these values are not what you want to see in your reports, you will need to use the Format property of
each control to change them. The formatting capabilities of Reporting Services controls are based on the
formatting mechanics in the .NET Framework and use a form of regular expressions . Regular expressions
are very powerful and can be used to format values in just about any way imaginable. Expression strings
can range from simple to extremely complex. If you need to learn more about the advanced use of regu-
lar expressions, search the Visual Studio online help or the MSDN library for Regular Expression Language
Elements . For most of your needs, however, we'll show you how to use the basics.