Microsoft KB Archive/886011

= How to create a report that has a site hierarchy view in Systems Management Server 2003 =

Article ID: 886011

Article Last Modified on 10/27/2006

-

APPLIES TO


 * Microsoft Systems Management Server 2003

-





INTRODUCTION
This article describes how to create a report that has a site hierarchy view in Microsoft Systems Management Server (SMS) 2003.



MORE INFORMATION
You can use a customized SQL statement to create a report that has a site hierarchy view that includes the site code, level, site name, and server name. To do this, follow these steps:  Open the SMS Administrator console. Expand Site Database ( -  ). Expand Reporting. Right-click Reports, point to New, and then click Report. In the Report Properties dialog box, enter a name in the Name box, select SMS Site – General in the Category list, and then click Edit SQL Statement.  Enter the following code into the SQL statement box, and then click OK.

Note When you add the code to the SQL statement box, you must remove the code comments. /* Sites in an SMS hierarchy can be listed in hierarchy order with indentation to show */ /* the site's depth in the hierarchy by using the following code sample in the Web    */ /* report definition. If a hierarchy has 7 sites that include AAA as a central site, */ /* BBB and CCC as primary sites under the central site, with DDD and EEE as secondary */ /* sites of BBB and FFF and GGG as secondary sites of CCC, the report will print the  */ /* following:                                                                         */ /*                                                                                    */ /* SiteCode   Level  SiteName   ServerName                                             */ /* - -     --                                             */ /* AAA        1      Site A     SERVER_A                                               */ /*   BBB     2      Site B     SERVER_B                                               */ /*      DDD  3      Site D     SERVER_D                                               */ /*      EEE  3      Site E     SERVER_E                                               */ /*   CCC     2      Site C     SERVER_C                                               */ /*      FFF  3      Site F     SERVER_F                                               */ /*      GGG  3      Site G     SERVER_G                                               */ /*                                                                                    */ /* You can see a demonstration of this in the SMS 2003 report                          */ /*  &quot;Site by hierarchy with time of last site status update.&quot;                         */

set nocount on

/* create a temporary table to hold the ordering information */

create table #SiteOrder ( SiteCode char(3),  Level    int, /* depth in hierarchy, 1 = centeral site */  PreOrder int identity, /* ordering for hierarchy order */  PRIMARY KEY (Level,SiteCode) )

declare @NextS char(3) declare @Level int

set @Level=1 set @NextS=''

/* do a depth first walk of the hierarchy tree, listing the sites in pre-order */

while @NextS is not null begin select @NextS=MIN(SiteCode) from v_Site where ReportingSiteCode=@NextS and SiteCode not in (select SiteCode from #SiteOrder where Level=@Level)

if @NextS IS NULL begin set @Level=@Level-1 select @NextS = MAX(SiteCode) from #SiteOrder where Level=@Level-1 end else begin insert into #SiteOrder(SiteCode,Level) values(@NextS,@Level) set @Level = @Level + 1 end end /* This is a sample query using the #SiteOrder table to list the sites in the v_Site view */ /* in hierarchy order. Other queries can be listed in hierarchy order by joining to the  */ /* #SiteOrder table by SiteCode and ordering by PreOrder. Use the Level column with the  */ /* SPACE function to provide indentation. */

select SPACE(3*(so.Level-1))+so.SiteCode as SiteCode, so.Level, s.SiteName, s.ServerName from #SiteOrder so join v_Site s on so.SiteCode=s.SiteCode order by so.PreOrder

/* drop the temporary table at the end */

drop table #SiteOrder

set nocount off  Right-click Dashboards, point to New, and then click Dashboard.</li> On the General tab of the Dashboard Properties dialog box, type a name for the dashboard in the Name box.</li> In the Dashboard Properties dialog box, click the Reports tab, right-click a row in Dashboard reports, and then click Properties.</li> In the Select Report dialog box, click to select the report that you created in step 9, and then click OK two times.</li> Right-click the dashboard that you created in step 7, point to All Tasks, point to Run, and then click the server name. You can open a dashboard that has a site hierarchy view that includes the site code, level, site name, and server name.</li></ol>

Additional query words: site control file data

Keywords: kbsmsreporting kbsmsadmin kbcode kbhowto KB886011

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.