Pro SQL Server 2012 Reporting Services

CHAPTER 13  CREATING REPORTS USING REPORT BUILDER 1.0, 2.0, AND 3.0

Figure 13-38. Chart report template

The chart has three areas to which you can drag and drop data: Series, Category, and Data. You want

to be able to show an active patient count, so you could drag the Pat ID field to the Data area; however,

because the Pat ID field is not seen as a field that can have an aggregated function applied to it, unlike

Avg Length of Stay, you cannot drag it directly to the Data area. The chart simply won’t accept it to be

dropped there. Since you know you will need both a count value and a filter to get only active patients,

you can take care of both at the same time by adding a new field to the field list. Click the New Field

button in the Explorer tab, and name the new field Patient Count, as shown in Figure 13-39. The formula

you can use for the Patient Count field is COUNTDISTINCT(Pat ID). Notice that in the Define Formula