You might want to look into doing a recursive CTE
http://msdn.microsoft.com/en-us/library/ms186243.aspx
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
I have no idea creating a CTE recursive query in SSRS. 
canoedoceanprince (1/21/2010)
I have no idea creating a CTE recursive query in SSRS.
I apologize - I did not check the forum heading. Are you running SQL 2005 or SQL 2000?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Here is a SQL 2000 friendly suggestion:
http://www.sqlteam.com/article/more-trees-hierarchies-in-sql
As for building the queries, I would create a stored procedure and then have your report use the stored proc.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
SQL 2005 SSRS
Sorry I am still learning.
If you follow the link in my first reply it will give you an overview of how to write a Recursive CTE. This is used to produce Hierarchies.
Now if you are expecting SSRS to handle the Hierarchy, then that would be a different story. You could build a matrix that collapses down and populates your grid based on the query you provide. The query would not necessarily have to present the data to SSRS in hierarchical form.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
thanks
mister.magoo (1/21/2010)
try something like this
SELECT STUFF(a.[description],1,1,'')
FROM (
SELECT '/'+dbo.classstructure.description
FROM dbo.classancestor
INNER JOIN dbo.classstructure
ON dbo.classancestor.ancestor = dbo.classstructure.classstructureid
WHERE (dbo.classancestor.classstructureid = '13997')
FOR XML PATH('')
) a(Swap the / for the backslash - this forum doesn't like them in posts)
THANKS!! 
The sub query worked however when I add STUFF it throws an error:
TITLE: Microsoft Report Designer
------------------------------
An error occurred while executing the query.
No column was specified for column 1 of 'a'.
Invalid column name 'description'.
------------------------------
ADDITIONAL INFORMATION:
No column was specified for column 1 of 'a'.
Invalid column name 'description'. (Microsoft SQL Server, Error: 8155)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.4035&EvtSrc=MSSQLServer&EvtID=8155&LinkId=20476
SELECT STUFF(a.[description],1,1,'')
FROM (
SELECT '/'+dbo.classstructure.description AS [description]
FROM dbo.classancestor
INNER JOIN dbo.classstructure
ON dbo.classancestor.ancestor = dbo.classstructure.classstructureid
WHERE (dbo.classancestor.classstructureid = '13997')
FOR XML PATH('')
) a
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);