January 21, 2010 at 4:45 pm
I need to be able to get the full classification hierarchy of a particular asset.
If I was to pull say a MONITOR (classstructureid = 13997), I need to see that this item belongs under this tree...
3 \ 2 \ 1 \ 0
IT\Computer Equipment\Computer Displays\Liquid crystal display LCD panels or monitors
Right now if I was to filter it for '13997' - I get 4 rows when I just need one record that pulls those 4 records into one.
On another note if I was to pull the path for Computer Displays with classstructureid = '13992' I shld get this: IT\Computer Equipment\Computer Displays
SELECT dbo.classancestor.classstructureid
, dbo.classancestor.ancestor
, dbo.classstructure.parent
, dbo.classancestor.hierarchylevels
, dbo.classstructure.description
FROM dbo.classancestor
INNER JOIN dbo.classstructure
ON dbo.classancestor.ancestor = dbo.classstructure.classstructureid
WHERE (dbo.classancestor.classstructureid = '13997')
classstructureid ancestorancestorclassid Leveldescription
1399713833NULL3IT
1399713887138332Computer equipment
1399713992138871Computer displays
1399713997139920Liquid crystal display LCD panels or monitors
Result should be:
3\2\1\0
OR
IT\Computer Equipment\Computer Displays\Liquid crystal display LCD panels or monitors
January 21, 2010 at 5:00 pm
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)
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);January 21, 2010 at 5:03 pm
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
January 21, 2010 at 5:21 pm
I have no idea creating a CTE recursive query in SSRS. 🙁
January 21, 2010 at 5:27 pm
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
January 21, 2010 at 5:29 pm
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
January 21, 2010 at 5:38 pm
SQL 2005 SSRS
Sorry I am still learning.
January 21, 2010 at 5:42 pm
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
January 21, 2010 at 6:05 pm
thanks
January 21, 2010 at 6:10 pm
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
January 22, 2010 at 12:13 am
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);Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply