hierarchy in SSRS 2005?

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.)