Introduction
Because the AdventureWorks database is used within an SQL Server, the available choices are MSOLEDBSQL, SQLOLEDB, ODBC or SQL Server Native Client. It should be noted that the SQLOLEDB and the Native Clients providers have been deprecated and shouldn't be used for new developments! In the project was used the Microsoft OLE DB Provider for SQL Server (MSOLEDBSQL) provider.
The Country parameter will be handled in the WHERE constraint - it's the role of the question mark. When an empty string was selected, the equivalent for ignoring the parameter, the formula used will result to an identity, which however makes sense only if there's always a value for the Country Code in the Person.StateProvince table. Otherwise the same line needs to be written as follows:
This might look strange, though it does the trick of handling the nulls. According to the rules of defensive programming, one should treat the eventuality of nulls. However, this depends also on application's design. If one develops reports with this supposition, then the supposition should be made explicit in the documentation!
Once the query provided, SSRS recognizes that a parameter was provided and requires to map the question mark to the respective parameter. Within dataset's properties click on the "Parameter" tab and selected @Country as Parameter Value:
A question mark needs to be considered in the query for each parameter, the parameters from the Dataset Properties being displayed in the order in which they appear in the query.
Once the dataset defined, one can go on and define the StateProvince parameter similarly to the Country parameter and consider the StateProvinces dataset as source. The StateProvince parameter needs to be added also in report's header:
= "State Province: " & Parameters!StateProvince.Label
Within the Addresses main dataset there needs to be added the declaration for the StateProvince parameter, while in where the corresponding clause:
With these changes the report should be ready to run.
Notes:
The above query for the StateProvince dataset can be used inside of a formula with a few small changes. This should work independently of the provider used. Just replace the query in the formula and delete the parameters previously created for the data source via the Dataset Properties:
Second Level of Dependency
The second dependency is based on the Territory Group formed of North America, Pacific and Europe. There is however no Territory Group table, the relations between Countries and Territory Groups being stored into the Sales.SalesTerritory table. Therefore, the query for the corresponding dropdown can be written via a DISTINCT:
When the user selects a Territory, in the Countries and State Province dropdowns are supposed to be shown only the corresponding values for the selected value. We need to modify thus both queries via formulas.
The query for the Country dataset includes the Territory parameter and the corresponding constraint via an EXIST:
In exchange the StateProvince dataset is slightly more complex:
Once the datasets changed, is still needed to modify the order of the parameters to reflect the order in which they are called within dependency, respectively Territory, State Province, Country. In the end the report looks like this:
Notes:
(1) If the order of the parameters doesn't reflect the otder in which they are called, you'll get a generic error similar with the following one (the error appears when there are problems with the query):
An error occurred during local report processing. The definition of the report '/Customer Addresses' is invalid. The CommandText expression for the query 'Countries' contains an error: [BC30277] Type character '8e does not match declared data type 'String'.
For exemplification will be used the reports build previously. The hierarchy will be built based on the State and Territory, a Country belonging to a Territory, while a State to a Country. For the beginning we will build the dependency existing between the Country and State.
First Level of Dependency
The text parameter for StateCountry dataset will be defined similarly to the one consider for the Country, and have as source the following query:
-- State Provinces SELECT '' Id , '(All)' Name UNION ALL SELECT StateProvinceCode , StateProvinceCode + ' ' + Name FROM [Person].[StateProvince] WHERE CountryRegionCode = ISNULL(NullIf(?, ''), CountryRegionCode) ORDER BY Id
The Country parameter will be handled in the WHERE constraint - it's the role of the question mark. When an empty string was selected, the equivalent for ignoring the parameter, the formula used will result to an identity, which however makes sense only if there's always a value for the Country Code in the Person.StateProvince table. Otherwise the same line needs to be written as follows:
WHERE IsNull(CountryRegionCode, '') = COALESCE(NullIf(?, ''), CountryRegionCode, '')
This might look strange, though it does the trick of handling the nulls. According to the rules of defensive programming, one should treat the eventuality of nulls. However, this depends also on application's design. If one develops reports with this supposition, then the supposition should be made explicit in the documentation!
Once the query provided, SSRS recognizes that a parameter was provided and requires to map the question mark to the respective parameter. Within dataset's properties click on the "Parameter" tab and selected @Country as Parameter Value:
Parameters in Dataset Properties
A question mark needs to be considered in the query for each parameter, the parameters from the Dataset Properties being displayed in the order in which they appear in the query.
Once the dataset defined, one can go on and define the StateProvince parameter similarly to the Country parameter and consider the StateProvinces dataset as source. The StateProvince parameter needs to be added also in report's header:
= "State Province: " & Parameters!StateProvince.Label
Within the Addresses main dataset there needs to be added the declaration for the StateProvince parameter, while in where the corresponding clause:
-- SQL Parameter & " DECLARE @StateProvince as nvarchar(3) = '" & Parameters!StateProvince.Value & "'" & vbcrlf -- constraint in WHERE clause & IIf(Parameters!StateProvince.Value<> "", " AND SIC.StateProvinceCode = @StateProvince", "") & vbcrlf W
With these changes the report should be ready to run.
Notes:
The above query for the StateProvince dataset can be used inside of a formula with a few small changes. This should work independently of the provider used. Just replace the query in the formula and delete the parameters previously created for the data source via the Dataset Properties:
= " -- State Provinces " & vbCrLf & " DECLARE @Country as nvarchar(3) = '" & Parameters!Country.Value & "'" & vbcrlf & " SELECT '' Id " & vbCrLf & " , '(All)' Name " & vbCrLf & " UNION ALL " & vbCrLf & " SELECT StateProvinceCode " & vbCrLf & " , StateProvinceCode + ' ' + Name " & vbCrLf & " FROM [Person].[StateProvince] " & vbCrLf & IIf(Parameters!Country.Value<>"", " WHERE CountryRegionCode = @Country", "") & vbcrlf & " ORDER BY Id " & vbCrLf
Second Level of Dependency
The second dependency is based on the Territory Group formed of North America, Pacific and Europe. There is however no Territory Group table, the relations between Countries and Territory Groups being stored into the Sales.SalesTerritory table. Therefore, the query for the corresponding dropdown can be written via a DISTINCT:
-- Territories SELECT '' Id , '(All)' Name UNION ALL SELECT DISTINCT [Group] Id , [Group] Name FROM Sales.SalesTerritory ORDER BY Id
When the user selects a Territory, in the Countries and State Province dropdowns are supposed to be shown only the corresponding values for the selected value. We need to modify thus both queries via formulas.
The query for the Country dataset includes the Territory parameter and the corresponding constraint via an EXIST:
= " -- Countries " & vbCrLf & " DECLARE @Territory as nvarchar(50) = '" & Parameters!Territory.Value & "'" & vbcrlf & " SELECT '' Id " & vbCrLf & " , '(All)' Name " & vbCrLf & " UNION ALL " & vbCrLf & " SELECT SSP.CountryRegionCode Id " & vbCrLf & " , SSP.Name " & vbCrLf & " FROM [Person].[CountryRegion] SSP " & vbCrLf & IIf(Parameters!Territory.Value<> "", " WHERE EXISTS (SELECT * FROM [Sales].[SalesTerritory] SST WHERE SSP.CountryRegionCode = SST.CountryRegionCode AND SST.[Group] = @Territory)", "") & vbcrlf & " ORDER BY Id " & vbCrLf
In exchange the StateProvince dataset is slightly more complex:
= " -- State Provinces " & vbCrLf & " DECLARE @Country as nvarchar(3) = '" & Parameters!Country.Value & "'" & vbcrlf & " DECLARE @Territory as nvarchar(50) = '" & Parameters!Territory.Value & "'" & vbcrlf & " SELECT '' Id " & vbCrLf & " , '(All)' Name " & vbCrLf & " UNION ALL " & vbCrLf & " SELECT PSP.StateProvinceCode " & vbCrLf & " , PSP.StateProvinceCode + ' ' + PSP.Name " & vbCrLf & " FROM [Person].[StateProvince] PSP" & vbCrLf & " JOIN [Sales].[SalesTerritory] PST" & vbCrLf & " ON PSP.TerritoryId = PST.TerritoryId " & vbCrLf & " WHERE 0=0 " & vbCrLf & IIf(Parameters!Country.Value<> "", " AND PSP.CountryRegionCode = @Country", "") & vbcrlf & IIf(Parameters!Territory.Value<> "", " AND PST.[Group] = @Territory", "") & vbcrlf & " ORDER BY Id " & vbCrLf
Once the datasets changed, is still needed to modify the order of the parameters to reflect the order in which they are called within dependency, respectively Territory, State Province, Country. In the end the report looks like this:
Notes:
(1) If the order of the parameters doesn't reflect the otder in which they are called, you'll get a generic error similar with the following one (the error appears when there are problems with the query):
An error occurred during local report processing. The definition of the report '/Customer Addresses' is invalid. The CommandText expression for the query 'Countries' contains an error: [BC30277] Type character '8e does not match declared data type 'String'.
(2) When designing parameters one should consider also the best practices for avoiding the poor design of parameters.
No comments:
Post a Comment