CHAPTER 6 BUILDING REPORTS
Listing 6-2. Parameter Value Queries--Query to Derive Year
SELECT
DISTINCT DATEPART(yy, ChargeServiceStartDate) AS Year
FROM
Trx
UNION
SELECT Null AS Year ORDER BY Year--Query to Derive Month
SELECT
DISTINCT DATEPART(mm, ChargeServiceStartDate) AS DateNum
, DATENAME(mm, ChargeServiceStartDate) AS Month
FROM
trx
UNION
SELECT Null AS DateNum, Null AS Month ORDER BY DATEPART(mm, ChargeServiceStartDate)To finish the report, add the Service Year field to the report by right clicking the BranchName field,
Insert Row and then Inside Group – Below. Select the Year field from field selector list by placing the
cursor over the top right corner of the blank space created underneath BranchName and clicking the
field list box. Change the aggregation to be FIRST rather than the default SUM. Next, format it with a
distinct color (in this case, dark salmon), set the alignment to be left justified and then resize the field to
12 points. Then, set the default value for all of the parameters with the exception of Service Year to be
(NULL). To do this, use the Default Values tab under the Report Parameter Properties. Then select
Specify values and click the Add button. Before you preview the report, it is important to set the default
value for the year so that a valid Service Month selection is not based on the default Service Year field of
NULL. This could potentially have undesired results; in other words, the user might select January and
assume that it means January for the current year, when in fact it would be all occurrences of January.
To make the Service Year parameter default to the current year, go to the Parameters dialog box and
set the Default Value option to the following expression:=CINT(DATEPART("yyyy", Now()))Also, in this report, we want to show the Service Year parameter before Service Month, so let’s move
the ServiceYear parameter above ServiceMonth parameter. Re-organizing the parameters like this
changes their order at runtime as well. Open up the Report Data pane and expand the Parameters folder.
Select the ServiceYear parameter and click the up arrow in the Report Data pane menu. You can preview
the report and provide parameter values (see Figure 6-29). Note Most of the data in the Pro_SSRS database is from 2009 and 2010. If the current year is defaulted to a
different year, the data you see may not be the same as in Figure 6-29.