is it possible to use a hierarchy (e.g: CountryCode, PostCode, CityName) as basis for a report parameter? I would like the user to have the possibility to drill down from e.g.: "Country = 'Austria' - PostCode '1010' - CityName 'Vienna'" and after that view the report with the selected parameters.
possible?
yes - no - maybe?
regards
pamike
Don't think so... At least using the standart issues!|||is it hten possible to build the hierarchie in an Analysis Services Olap Cube and use parameters to pass througt to a predefined report? I also tried this but without any success;-(
thx
pamike
|||Can't answer that... i'm not very experienced in Analisys Services...
Sorry...
|||Are you looking to do this with one parameter or can it use multiple parameters? You can set up cascading parameters to do something like this. For example,
Parameter1 Datasource => SELECT countryid, country FROMCountryTable;
Parameter2 Datasource => SELECT stateid, state FROM StateTable WHERE countryid = Paramenters!Parameter1.Value;
Parameter3 Datasource => SELECT cityid, city FROM CityTable WHERE stateid = Paramenters!Parameter2.Value;
I am not sure if you can change the visibilty of each parameter after you select it (or if you need to).
R
|||Small error. Those should read
Parameter1 Datasource => SELECT countryid, country FROM CountryTable;
Parameter2 Datasource => SELECT stateid, state FROM StateTable WHERE countryid = @.Parameter1 ;
Parameter3 Datasource => SELECT cityid, city FROM CityTable WHERE stateid = @.Parameter2;
R
|||This is very possible using cascading parameters...
Parameter 1 (country code):
SELECT CountryCode FROM AccountGeography
Use something like the above as the dataset for the first parameter.
Multi value Parameter 2 (postal code):
SELECT PostalCode FROM AccountGeography WHERE CountryCode = @.CountryCode
Assuming you named the first parameter "CountryCode", the above statement will generate a list of postal codes that correspond to the CountryCode value selected in the first parameter.If the first parameter is a multi-valued parameter where you can select more than one CountryCode at a time then it gets a little trickier.To avoid brain numbing dynamic SQL you can use a user defined function to pass the comma delimited list of values from the CountryCode parameter to an IN statement in the child parameters.Example:
SELECT PostalCode FROM AccountGeography WHERE CountryCode IN (SELECT Param FROM ufn_MVParam(@.CountryCode,','))
Multi value Parameter 3 (city name):
SELECT CityName FROM AccountGeography WHERE PostalCode IN (SELECT Param FROM ufn_MVParam(@.PostalCode,','))
The third parameter is the same as the second except for that where parameter 2 uses the value of parameter 1 to filter its values, parameter 3 uses the value of parameter 2.
...Don't forget that when you're using reporting services parameters in your data set queries, the parameter name is case sensitive! WHERE CountryCode = @.CountryCode is not the same as WHERE CountryCode = @.Countrycode.
...SSRS will take care of generating the dynamic SQL for you!I'm not sure if that statement is technically correct, but it's at least operationally true.You will understand how wonderful this is if you have used SSRS 2000 x_x
...Here is the code to create the function which pareses the comma delimited string.I got this on the web somewhere but can't remember where:
CREATE FUNCTION [dbo].[ufn_MVParam](@.RepParam nvarchar(4000), @.Delim char(1)= ',')
RETURNS @.VALUES TABLE (Param nvarchar(4000))AS
BEGIN
DECLARE @.chrind INT
DECLARE @.Piece nvarchar(4000)
SELECT @.chrind = 1
WHILE @.chrind > 0
BEGIN
SELECT @.chrind = CHARINDEX(@.Delim,@.RepParam)
IF @.chrind > 0
SELECT @.Piece = LEFT(@.RepParam,@.chrind - 1)
ELSE
SELECT @.Piece = @.RepParam
INSERT @.VALUES(Param) VALUES(@.Piece)
SELECT @.RepParam = RIGHT(@.RepParam,LEN(@.RepParam) - @.chrind)
IF LEN(@.RepParam) = 0 BREAK
END
RETURN
END
Good luck!!
|||My bad... you only need that crazy text parsing function when trying to pass multi value parameters to a stored procedure. If your result set is not generated using a stored procedure, all you need is the SSRS parameter name like this:
SELECT CityName FROM AccountGeography WHERE PostalCode IN (@.PostalCode)
with SSRS 2005 you would only need the additional user function when using a stored procedure as the data set in your report... and in that situation you wouldn't call the function from within SSRS, you would call it from within the sproc itself because SSRS passes the multi value parameter to SQL server as a comma delimted value string ("CountryCode1,CountryCode2,CountryCode3" etc.)
