Microsoft® SQL Server® 2012 Bible

1248

Part IX: Business Intelligence

Working with Parameters

Most report users want to interact with the data in a report. For instance, a user may

want to see the sales of a product for only one state, Pennsylvania, and then change the

state fi lter to Florida and peruse that data. To accomplish this, parameters are used in

Reporting Services. Not only do parameters help the user, they also aid in report ren-

dering, by limiting the amount of data returned by a dataset. Parameters are added to

the dataset through several methods. If the dataset connects to a SQL Server relational

source, you can add a parameter by adding @ParameterName in the WHERE clause of the

query text in the dataset, using the same structure as you would use to parameterize

Transact-SQL.

In the case of the fi rst query written in the ResellerContact report, if you want to add

a parameter to fi lter by country, you could accomplish this by adding a line to the end of

the query in the ResellerContact dataset, like this:

SELECT dsr.ResellerName , dsr.Phone

,dsr.AddressLine1, dsr.AddressLine2, dg.City,

dg.StateProvinceCode , dg.EnglishCountryRegionName ,

dsr.AnnualSales

FROM DimReseller dsr

JOIN DimGeography dg ON dsr.GeographyKey = dg.GeographyKey

where dg. EnglishCountryRegionName = @RegionCountryName

After the parameter has been added to the ResellerContact dataset, a parameter is

available in the Report Data pane under the parameters node. A parameter can be fur-

ther confi gured by right-clicking the parameter in the Report Data pane and selecting

Parameter Properties, which opens the Report Parameter Properties window, as shown

in Figure 55-5.

Located on the General tab of the Report Parameter Properties window, the Prompt box

enables changing the text beside the parameter. The Available Values tab is quite impor-

tant. The parameter as currently confi gured would take free form text and then enable the

user to submit the parameter and view the report. A better way to choose the value of a

parameter is to create another dataset of known available values and attach that dataset

to the parameter. For instance, the parameter you just confi gured would return data only

if one of the countries available in the table were typed into it, so wouldn’t it be more effi -

cient to confi gure a dataset of the distinct values available in the CountryRegionName

column? To accomplish this, you can create a dataset named CountryRegionName in the

report using the following query:

SELECT Distinct EnglishCountryRegionName FROM DimGeography

Then attach the dataset to the @RegionCountryName parameter by confi guring the values

in its Available Values tab, as shown in Figure 55-6:

c55.indd 1248c55.indd 1248 7/31/2012 10:28:06 AM7/31/2012 10:28:06 AM

http://www.it-ebooks.info