Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations IamaSherpa on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Report object documentation 2

Status
Not open for further replies.
dmcmunn

dmcmunn

Programmer
May 20, 2003
85
US
Here's a query I've used to get up to speed on a new project via SQL...You can cut and paste the results into your tool of choice and sort things out...

/* SQL Used to generate documentation
* Server: MYMETADATA
* Database: MSImd; Schema Owner: MSImd
* MSI Project Name: MYOLAPDEV
* Limitation: Only documents up to 10 levels of folders/objects
*/

select o.project_id
, case o.object_type
when 1 then 'filter (1)'
when 2 then 'template (2)'
when 3 then 'report (3)'
when 4 then 'metric (4)'
when 6 then 'autostyle (6)'
when 8 then 'folder (8)'
when 10 then 'prompt (10)'
when 11 then 'function (11)'
when 12 then 'attribute (12)'
when 13 then 'fact (13)'
when 14 then 'hierarchy (14)'
when 15 then 'table (15)'
when 21 then 'attribute id (21)'
when 22 then 'schema (22)'
when 24 then 'warehouse catalog (24)'
when 25 then 'warehouse catalog definition (25)'
when 26 then 'table column (26)'
when 28 then 'property sets (28)'
when 39 then 'search (39)'
when 42 then 'package (42)'
when 47 then 'consolidations (47)'
when 52 then 'link (52)'
when 53 then 'table (53)'
when 56 then 'drill map (56)'
when 58 then 'security filter (58)'
else '(?' + rtrim(cast(o.object_type as char(3))) + '?)'
end object_type,
case when o.object_type = 8 then
isnull(p10.object_name,'')
+ '/' + isnull(p9.object_name,'')
+ '/' + isnull(p8.object_name,'')
+ '/' + isnull(p7.object_name,'')
+ '/' + isnull(p6.object_name,'')
+ '/' + isnull(p5.object_name,'')
+ '/' + isnull(p4.object_name,'')
+ '/' + isnull(p3.object_name,'')
+ '/' + isnull(p2.object_name,'')
+ '/' + isnull(p1.object_name,'')
+ '/' + o.object_name
else
isnull(p10.object_name,'')
+ '/' + isnull(p9.object_name,'')
+ '/' + isnull(p8.object_name,'')
+ '/' + isnull(p7.object_name,'')
+ '/' + isnull(p6.object_name,'')
+ '/' + isnull(p5.object_name,'')
+ '/' + isnull(p4.object_name,'')
+ '/' + isnull(p3.object_name,'')
+ '/' + isnull(p2.object_name,'')
+ '/' + isnull(p1.object_name,'')
end folder
, o.object_name
, o.object_id
, dod.depn_object_type
, dod.depn_object_subtype
, dod.depn_object_name
, dod.depn_object_id
, df.folder

from MSImd.dssmdobjinfo o

inner join MSImd.dssmdobjinfo p1
on o.parent_id = p1.object_id
and o.project_id = p1.project_id

left outer join MSImd.dssmdobjinfo p2
on p1.parent_id = p2.object_id
and p1.project_id = p2.project_id

left outer join MSImd.dssmdobjinfo p3
on p2.parent_id = p3.object_id
and p2.project_id = p3.project_id

left outer join MSImd.dssmdobjinfo p4
on p3.parent_id = p4.object_id
and p3.project_id = p4.project_id

left outer join MSImd.dssmdobjinfo p5
on p4.parent_id = p5.object_id
and p4.project_id = p5.project_id

left outer join MSImd.dssmdobjinfo p6
on p5.parent_id = p6.object_id
and p5.project_id = p6.project_id

left outer join MSImd.dssmdobjinfo p7
on p6.parent_id = p7.object_id
and p6.project_id = p7.project_id

left outer join MSImd.dssmdobjinfo p8
on p7.parent_id = p8.object_id
and p7.project_id = p8.project_id

left outer join MSImd.dssmdobjinfo p9
on p8.parent_id = p9.object_id
and p8.project_id = p9.project_id

left outer join MSImd.dssmdobjinfo p10
on p9.parent_id = p10.object_id
and p9.project_id = p10.project_id

inner join
(
select obj.project_id
, case obj.object_type
when 1 then 'filter (1)'
when 2 then 'template (2)'
when 3 then 'report (3)'
when 4 then 'metric (4)'
when 6 then 'autostyle (6)'
when 8 then 'folder (8)'
when 10 then 'prompt (10)'
when 11 then 'function (11)'
when 12 then 'attribute (12)'
when 13 then 'fact (13)'
when 14 then 'hierarchy (14)'
when 15 then 'table (15)'
when 21 then 'attribute id (21)'
when 22 then 'schema (22)'
when 24 then 'warehouse catalog (24)'
when 25 then 'warehouse catalog definition (25)'
when 26 then 'table column (26)'
when 28 then 'property sets (28)'
when 39 then 'search (39)'
when 42 then 'package (42)'
when 47 then 'consolidations (47)'
when 52 then 'link (52)'
when 53 then 'table (53)'
when 56 then 'drill map (56)'
when 58 then 'security filter (58)'
else '(?' + rtrim(cast(dob.object_type as char(3))) + '?)'
end container_object_type
, dep.object_id container_object_id
, obj.object_name container_object_name
, case dob.object_type
when 1 then 'filter (1)'
when 2 then 'template (2)'
when 3 then 'report (3)'
when 4 then 'metric (4)'
when 6 then 'autostyle (6)'
when 8 then 'folder (8)'
when 10 then 'prompt (10)'
when 11 then 'function (11)'
when 12 then 'attribute (12)'
when 13 then 'fact (13)'
when 14 then 'hierarchy (14)'
when 15 then 'table (15)'
when 21 then 'attribute id (21)'
when 22 then 'schema (22)'
when 24 then 'warehouse catalog (24)'
when 25 then 'warehouse catalog definition (25)'
when 26 then 'table column (26)'
when 28 then 'property sets (28)'
when 39 then 'search (39)'
when 42 then 'package (42)'
when 47 then 'consolidations (47)'
when 52 then 'link (52)'
when 53 then 'table (53)'
when 56 then 'drill map (56)'
when 58 then 'security filter (58)'
else '(?' + rtrim(cast(dob.object_type as char(3))) + '?)'
end depn_object_type
, dob.subtype depn_object_subtype
, dep.depn_objid depn_object_id
, dob.object_name depn_object_name

from MSImd.dssmdobjdepn dep

inner join MSImd.dssmdobjinfo pro
on dep.project_id = pro.project_id
and pro.parent_id = '00000000000000000000000000000000'
and pro.object_name = 'MYOLAPDEV'
and pro.object_type = 8

inner join MSImd.dssmdobjinfo obj
on dep.project_id = obj.project_id
and dep.object_id = obj.object_id
and dep.object_type = obj.object_type

inner join MSImd.dssmdobjinfo dob
on dep.project_id = dob.project_id
and dep.depn_objid = dob.object_id
and dep.depnobj_type = dob.object_type

where obj.object_type in (1, 2, 3, 4, 8, 10, 12, 13, 56)
and dob.subtype not in (1026, 2816)
) dod
on o.project_id = dod.project_id
and o.object_id = dod.container_object_id

inner join
(
select o.project_id
, case when o.object_type = 8 then
isnull(p10.object_name,'')
+ '/' + isnull(p9.object_name,'')
+ '/' + isnull(p8.object_name,'')
+ '/' + isnull(p7.object_name,'')
+ '/' + isnull(p6.object_name,'')
+ '/' + isnull(p5.object_name,'')
+ '/' + isnull(p4.object_name,'')
+ '/' + isnull(p3.object_name,'')
+ '/' + isnull(p2.object_name,'')
+ '/' + isnull(p1.object_name,'')
+ '/' + o.object_name
else
isnull(p10.object_name,'')
+ '/' + isnull(p9.object_name,'')
+ '/' + isnull(p8.object_name,'')
+ '/' + isnull(p7.object_name,'')
+ '/' + isnull(p6.object_name,'')
+ '/' + isnull(p5.object_name,'')
+ '/' + isnull(p4.object_name,'')
+ '/' + isnull(p3.object_name,'')
+ '/' + isnull(p2.object_name,'')
+ '/' + isnull(p1.object_name,'')
end folder
, o.object_id

from MSImd.dssmdobjinfo o

inner join MSImd.dssmdobjinfo p1
on o.parent_id = p1.object_id
and o.project_id = p1.project_id

left outer join MSImd.dssmdobjinfo p2
on p1.parent_id = p2.object_id
and p1.project_id = p2.project_id

left outer join MSImd.dssmdobjinfo p3
on p2.parent_id = p3.object_id
and p2.project_id = p3.project_id

left outer join MSImd.dssmdobjinfo p4
on p3.parent_id = p4.object_id
and p3.project_id = p4.project_id

left outer join MSImd.dssmdobjinfo p5
on p4.parent_id = p5.object_id
and p4.project_id = p5.project_id

left outer join MSImd.dssmdobjinfo p6
on p5.parent_id = p6.object_id
and p5.project_id = p6.project_id

left outer join MSImd.dssmdobjinfo p7
on p6.parent_id = p7.object_id
and p6.project_id = p7.project_id

left outer join MSImd.dssmdobjinfo p8
on p7.parent_id = p8.object_id
and p7.project_id = p8.project_id

left outer join MSImd.dssmdobjinfo p9
on p8.parent_id = p9.object_id
and p8.project_id = p9.project_id

left outer join MSImd.dssmdobjinfo p10
on p9.parent_id = p10.object_id
and p9.project_id = p10.project_id

where o.project_id = (select project_id
from MSImd.dssmdobjinfo
where object_type = 8
and object_name = 'MYOLAPDEV')
) df
on dod.project_id = df.project_id
and dod.depn_object_id = df.object_id

where o.project_id = (select project_id
from MSImd.dssmdobjinfo
where object_type = 8
and object_name = 'MYOLAPDEV')
and o.object_type = 3
-- the following filters report selection to those found only these two folders with these GUIDs:
and ( 'FOLDER1GUIDGOESHERE' in (p1.parent_id, p2.parent_id, p3.parent_id, p4.parent_id, p5.parent_id, p6.parent_id, p7.parent_id, p8.parent_id, p9.parent_id, p10.parent_id)
or 'FOLDER2GUIDGOESHERE' in (p1.parent_id, p2.parent_id, p3.parent_id, p4.parent_id, p5.parent_id, p6.parent_id, p7.parent_id, p8.parent_id, p9.parent_id, p10.parent_id))

order by o.object_type
, p10.object_name
, p9.object_name
, p8.object_name
, p7.object_name
, p6.object_name
, p5.object_name
, p4.object_name
, p3.object_name
, p2.object_name
, p1.object_name
, o.object_name
, dod.depn_object_type
, dod.depn_object_name
, dod.depn_object_id

 
Sort by date Sort by votes
Here's some SQL Server 2000 SQL I've used to perform recursive dependency analysis of a given report...

/* SQL to rebuild the project object relationship table */
truncate table MSImd.dssmd_x

insert MSImd.dssmd_x
select o.project_id
, o.object_type object_type_no
, case o.object_type
when 1 then 'filter (1)'
when 2 then 'template (2)'
when 3 then 'report (3)'
when 4 then 'metric (4)'
when 6 then 'autostyle (6)'
when 8 then 'folder (8)'
when 10 then 'prompt (10)'
when 11 then 'function (11)'
when 12 then 'attribute (12)'
when 13 then 'fact (13)'
when 14 then 'hierarchy (14)'
when 15 then 'available table (15)'
when 21 then 'attribute id (21)'
when 22 then 'schema (22)'
when 24 then 'warehouse catalog (24)'
when 25 then 'warehouse catalog definition (25)'
when 26 then 'table column (26)'
when 28 then 'property sets (28)'
when 39 then 'search (39)'
when 42 then 'package (42)'
when 47 then 'consolidations (47)'
when 52 then 'link (52)'
when 53 then 'architected table (53)'
when 56 then 'drill map (56)'
when 58 then 'security filter (58)'
else '(?' + rtrim(cast(o.object_type as char(3))) + '?)'
end object_type,
case when o.object_type = 8 then
isnull(p10.object_name,'')
+ '/' + isnull(p9.object_name,'')
+ '/' + isnull(p8.object_name,'')
+ '/' + isnull(p7.object_name,'')
+ '/' + isnull(p6.object_name,'')
+ '/' + isnull(p5.object_name,'')
+ '/' + isnull(p4.object_name,'')
+ '/' + isnull(p3.object_name,'')
+ '/' + isnull(p2.object_name,'')
+ '/' + isnull(p1.object_name,'')
+ '/' + o.object_name
else
isnull(p10.object_name,'')
+ '/' + isnull(p9.object_name,'')
+ '/' + isnull(p8.object_name,'')
+ '/' + isnull(p7.object_name,'')
+ '/' + isnull(p6.object_name,'')
+ '/' + isnull(p5.object_name,'')
+ '/' + isnull(p4.object_name,'')
+ '/' + isnull(p3.object_name,'')
+ '/' + isnull(p2.object_name,'')
+ '/' + isnull(p1.object_name,'')
end object_folder
, o.object_name
, o.object_id
, dod.depn_object_type_no
, dod.depn_object_type
, dod.depn_object_subtype
, dod.depn_object_name
, dod.depn_object_id
, df.folder depn_object_folder

-- into MSImd.dssmd_x

from MSImd.dssmdobjinfo o

inner join MSImd.dssmdobjinfo p1
on o.parent_id = p1.object_id
and o.project_id = p1.project_id

left outer join MSImd.dssmdobjinfo p2
on p1.parent_id = p2.object_id
and p1.project_id = p2.project_id

left outer join MSImd.dssmdobjinfo p3
on p2.parent_id = p3.object_id
and p2.project_id = p3.project_id

left outer join MSImd.dssmdobjinfo p4
on p3.parent_id = p4.object_id
and p3.project_id = p4.project_id

left outer join MSImd.dssmdobjinfo p5
on p4.parent_id = p5.object_id
and p4.project_id = p5.project_id

left outer join MSImd.dssmdobjinfo p6
on p5.parent_id = p6.object_id
and p5.project_id = p6.project_id

left outer join MSImd.dssmdobjinfo p7
on p6.parent_id = p7.object_id
and p6.project_id = p7.project_id

left outer join MSImd.dssmdobjinfo p8
on p7.parent_id = p8.object_id
and p7.project_id = p8.project_id

left outer join MSImd.dssmdobjinfo p9
on p8.parent_id = p9.object_id
and p8.project_id = p9.project_id

left outer join MSImd.dssmdobjinfo p10
on p9.parent_id = p10.object_id
and p9.project_id = p10.project_id

inner join
(
select obj.project_id
, case obj.object_type
when 1 then 'filter (1)'
when 2 then 'template (2)'
when 3 then 'report (3)'
when 4 then 'metric (4)'
when 6 then 'autostyle (6)'
when 8 then 'folder (8)'
when 10 then 'prompt (10)'
when 11 then 'function (11)'
when 12 then 'attribute (12)'
when 13 then 'fact (13)'
when 14 then 'hierarchy (14)'
when 15 then 'available table (15)'
when 21 then 'attribute id (21)'
when 22 then 'schema (22)'
when 24 then 'warehouse catalog (24)'
when 25 then 'warehouse catalog definition (25)'
when 26 then 'table column (26)'
when 28 then 'property sets (28)'
when 39 then 'search (39)'
when 42 then 'package (42)'
when 47 then 'consolidations (47)'
when 52 then 'link (52)'
when 53 then 'architected table (53)'
when 56 then 'drill map (56)'
when 58 then 'security filter (58)'
else '(?' + rtrim(cast(dob.object_type as char(3))) + '?)'
end container_object_type
, dep.object_id container_object_id
, obj.object_name container_object_name
, dob.object_type depn_object_type_no
, case dob.object_type
when 1 then 'filter (1)'
when 2 then 'template (2)'
when 3 then 'report (3)'
when 4 then 'metric (4)'
when 6 then 'autostyle (6)'
when 8 then 'folder (8)'
when 10 then 'prompt (10)'
when 11 then 'function (11)'
when 12 then 'attribute (12)'
when 13 then 'fact (13)'
when 14 then 'hierarchy (14)'
when 15 then 'available table (15)'
when 21 then 'attribute id (21)'
when 22 then 'schema (22)'
when 24 then 'warehouse catalog (24)'
when 25 then 'warehouse catalog definition (25)'
when 26 then 'table column (26)'
when 28 then 'property sets (28)'
when 39 then 'search (39)'
when 42 then 'package (42)'
when 47 then 'consolidations (47)'
when 52 then 'link (52)'
when 53 then 'architected table (53)'
when 56 then 'drill map (56)'
when 58 then 'security filter (58)'
else '(?' + rtrim(cast(dob.object_type as char(3))) + '?)'
end depn_object_type
, dob.subtype depn_object_subtype
, dep.depn_objid depn_object_id
, dob.object_name depn_object_name

from MSImd.dssmdobjdepn dep

inner join MSImd.dssmdobjinfo pro
on dep.project_id = pro.project_id
and pro.parent_id = '00000000000000000000000000000000'
and pro.object_name = 'MYOLAPDEV'
and pro.object_type = 8

inner join MSImd.dssmdobjinfo obj
on dep.project_id = obj.project_id
and dep.object_id = obj.object_id
and dep.object_type = obj.object_type

inner join MSImd.dssmdobjinfo dob
on dep.project_id = dob.project_id
and dep.depn_objid = dob.object_id
and dep.depnobj_type = dob.object_type

where obj.object_type in (1, 2, 3, 4, 8, 10, 12, 13, 56)
and dob.subtype not in (1026, 2816)
) dod
on o.project_id = dod.project_id
and o.object_id = dod.container_object_id

inner join
(
select o.project_id
, case when o.object_type = 8 then
isnull(p10.object_name,'')
+ '/' + isnull(p9.object_name,'')
+ '/' + isnull(p8.object_name,'')
+ '/' + isnull(p7.object_name,'')
+ '/' + isnull(p6.object_name,'')
+ '/' + isnull(p5.object_name,'')
+ '/' + isnull(p4.object_name,'')
+ '/' + isnull(p3.object_name,'')
+ '/' + isnull(p2.object_name,'')
+ '/' + isnull(p1.object_name,'')
+ '/' + o.object_name
else
isnull(p10.object_name,'')
+ '/' + isnull(p9.object_name,'')
+ '/' + isnull(p8.object_name,'')
+ '/' + isnull(p7.object_name,'')
+ '/' + isnull(p6.object_name,'')
+ '/' + isnull(p5.object_name,'')
+ '/' + isnull(p4.object_name,'')
+ '/' + isnull(p3.object_name,'')
+ '/' + isnull(p2.object_name,'')
+ '/' + isnull(p1.object_name,'')
end folder
, o.object_id

from MSImd.dssmdobjinfo o

inner join MSImd.dssmdobjinfo p1
on o.parent_id = p1.object_id
and o.project_id = p1.project_id

left outer join MSImd.dssmdobjinfo p2
on p1.parent_id = p2.object_id
and p1.project_id = p2.project_id

left outer join MSImd.dssmdobjinfo p3
on p2.parent_id = p3.object_id
and p2.project_id = p3.project_id

left outer join MSImd.dssmdobjinfo p4
on p3.parent_id = p4.object_id
and p3.project_id = p4.project_id

left outer join MSImd.dssmdobjinfo p5
on p4.parent_id = p5.object_id
and p4.project_id = p5.project_id

left outer join MSImd.dssmdobjinfo p6
on p5.parent_id = p6.object_id
and p5.project_id = p6.project_id

left outer join MSImd.dssmdobjinfo p7
on p6.parent_id = p7.object_id
and p6.project_id = p7.project_id

left outer join MSImd.dssmdobjinfo p8
on p7.parent_id = p8.object_id
and p7.project_id = p8.project_id

left outer join MSImd.dssmdobjinfo p9
on p8.parent_id = p9.object_id
and p8.project_id = p9.project_id

left outer join MSImd.dssmdobjinfo p10
on p9.parent_id = p10.object_id
and p9.project_id = p10.project_id

where o.project_id = (select project_id
from MSImd.dssmdobjinfo
where object_type = 8
and object_name = 'MYOLAPDEV')
) df
on dod.project_id = df.project_id
and dod.depn_object_id = df.object_id

where o.project_id = (select project_id
from MSImd.dssmdobjinfo
where object_type = 8
and object_name = 'MYOLAPDEV')
--and o.object_type = 3
-- the following chooses reports found only these two folders represented by the GUIDs below
--and ( 'FOLDER1GUIDGOESHERE' in (p1.parent_id, p2.parent_id, p3.parent_id, p4.parent_id, p5.parent_id, p6.parent_id, p7.parent_id, p8.parent_id, p9.parent_id, p10.parent_id)
-- or 'FOLDER2GUIDGOESHERE' in (p1.parent_id, p2.parent_id, p3.parent_id, p4.parent_id, p5.parent_id, p6.parent_id, p7.parent_id, p8.parent_id, p9.parent_id, p10.parent_id))



--Step #1
truncate table MSImd.MSI_objx

--Step #2 plug in guid of report to document......................>
exec MSImd.asp_GenObjDependents @sParentGUID='REPORTGUIDGOESHERE', @iLevel=1

--Step #3 get unique list of objects dependent on the original parent
select
a11.depn_object_type_no
, a11.depn_object_type
, a11.depn_object_name
, a11.depn_object_folder
, a11.depn_object_id
--, a11.seq_no
from
(
select space((y.nesting_level - 1) * 2) + cast(y.nesting_level as char(6)) nesting_level
, obj.object_type_no
, obj.object_type
, obj.object_folder
, obj.object_name
, obj.object_id
, obj.depn_object_type_no
, obj.depn_object_type
, obj.depn_object_name
, obj.depn_object_id
, obj.depn_object_folder
, y.seq_no
from MSImd.dssmd_x obj
inner join MSImd.MSI_objx y
on obj.object_id = y.parent_object_id
and obj.depn_object_id = y.depn_object_id

where obj.object_id + obj.depn_object_id in
(select x.parent_object_id + x.depn_object_id
from MSImd.MSI_objx x
group by x.parent_object_id, x.depn_object_id)
and obj.depn_object_type_no in (12,1,10,15,13,4)
) a11
group by
a11.depn_object_type_no
, a11.depn_object_type
, a11.depn_object_name
, a11.depn_object_folder
, a11.depn_object_id
order by
a11.depn_object_type_no
, a11.depn_object_type
, a11.depn_object_name
, a11.depn_object_folder
, a11.depn_object_id









CREATE PROCEDURE MSImd.asp_GenObjDependents
(
@sParentGUID varchar(32)
, @iLevel int
)
AS

/* set an absolute maximum number of levels to drill down as a runaway protection */
if @iLevel > 16 return

if not exists (select * from dbo.sysobjects where id = object_id(N'[MSImd].[MSI_objx]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
create table MSImd.MSI_objx (
parent_object_id char(32) null,
depn_object_id char(32) null,
nesting_level int null,
seq_no int identity (1,1)
);
END

declare @pobj_id varchar(32)
declare @dobj_id varchar(32)
declare @dobj_type integer
declare @sSQL varchar(110)

declare curParentObjects CURSOR LOCAL FAST_FORWARD FOR
select object_id, depn_object_id, depn_object_type_no from MSImd.dssmd_x where object_id = @sParentGUID

OPEN curParentObjects

FETCH NEXT FROM curParentObjects
INTO @pobj_id, @dobj_id, @dobj_type

WHILE @@FETCH_STATUS = 0
BEGIN

insert MSImd.MSI_objx (parent_object_id, depn_object_id, nesting_level)
values (@pobj_id, @dobj_id, @iLevel);

/* only recurse through: filters(1), templates(2), reports(3), metrics(4), prompts(10), attributes(12), facts(13) and drill maps(56) */
if @dobj_type in (1,2,3,4,10,12,13,56)
BEGIN
set @sSQL = "MSImd.asp_GenObjDependents @sParentGUID='" + @dobj_id + "'" + ", @iLevel=" + ltrim(rtrim(cast(@iLevel + 1 as varchar(8))))
exec (@sSQL)
END

FETCH NEXT FROM curParentObjects
INTO @pobj_id, @dobj_id, @dobj_type
END
CLOSE curParentObjects
DEALLOCATE curParentObjects
GO

 
Upvote 0 Downvote
Here's a general recursive report object documentation "how-to" along with a query for returning the results indented based on level of recursion where the dependency was found...

--Step #1
truncate table MSImd.MSI_objx

--Step #2 plug in guid of report to document......................>
exec MSImd.asp_GenObjDependents @sParentGUID='REPORTGUIDGOESHERE', @iLevel=1

--Step #3
select space((y.nesting_level - 1) * 2) + cast(y.nesting_level as char(6)), obj.*, y.seq_no
from MSImd.dssmd_x obj
inner join MSImd.MSI_objx y
on obj.object_id = y.parent_object_id
and obj.depn_object_id = y.depn_object_id

where obj.object_id + obj.depn_object_id in
(select x.parent_object_id + x.depn_object_id
from MSImd.MSI_objx x
group by x.parent_object_id, x.depn_object_id)
and obj.depn_object_type in ('attribute (12)', 'filter (1)', 'prompt (10)', 'table (15)', 'fact (13)', 'metric (4)')
order by y.seq_no


 
Upvote 0 Downvote
hmmmm.... what tool did you use to generate these SQL? Looks pretty goood :D) I'll keep it as a reference - maybe usefully later. ty.
 
Upvote 0 Downvote
Thanks, dmcmunn. Submit it as a FAQ so it doesn't get lost in the shuffle.
 
Upvote 0 Downvote
Entaroadun,

I've submitted it for review in the FAQ section.

Best,
dmcmunn
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

BrunnoRJ
  • Locked
  • Question
Odbc error on report
Replies
0
Views
211
BrunnoRJ
BrunnoRJ
szeiss
  • Locked
  • Question
Display FY2011 instead of FY-1 in Report
Replies
0
Views
139
szeiss
szeiss
dinzana
  • Locked
  • Question
Comparing sysdate()-1 to another date in multiple datasets in a report document.
Replies
0
Views
153
dinzana
dinzana
oldcognos
  • Locked
  • Question
intranet web page value pass in url to prompted report
Replies
0
Views
131
oldcognos
oldcognos
pattypan
  • Locked
  • Question
Conditional Prompt Object
Replies
1
Views
119
Z3
Z3

Part and Inventory Search

Sponsor

Back
Top