Code Sample: SCRS_SWIApplicationsInventorySummary_CUSTOM SCRM Stored Procedure

System Center

This sample demonstrates the changes made to the SCRM 2006 stored procedure SCRS_SWIApplicationsInventorySummary in How to Add Drilldown Functionality to an SCRM Report. SCRS_SWIApplicationsInventorySummary is in the SCRM 2006 SystemCenterPresentation database.

Requirements

SCRM 2006

Description

SCRS_SWIApplicationsInventorySummary is changed to add the LanguageName variable to the procedure's result set.

Code

USE [SystemCenterPresentation]
GO
/****** Object:  StoredProcedure [dbo].[SCRS_SWIApplicationsInventorySummary]    Script Date: 04/06/2006 12:46:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE     PROCEDURE [dbo].[SCRS_SWIApplicationsInventorySummary_CUSTOM]      
(  
 --declaration and initialization of the input parameters for this stored procedure.      
 @Company  NVARCHAR(255)='<ALL>',
 @ProductName  NVARCHAR(255)='<ALL>',
 @Version  NVARCHAR(255)='<ALL>',
 @Domain  NVARCHAR(255)='<ALL>',
 @Location  NVARCHAR(32)='<ALL>',
 @LocationParameter NVARCHAR(128)='<ALL>',
 @CostCenter    NVARCHAR(16)='<ALL>',
 @Manager  NVARCHAR(64)='<ALL>'     
)      
AS       
BEGIN      
-- Prevent number of rows affected being returned as part of results.      
SET NOCOUNT ON      
      
--Declare and initialize all location variables.      
DECLARE           @Building NVARCHAR(15)      
SET  @Building = 'Building'      
DECLARE           @Campus NVARCHAR(15)      
SET  @Campus = 'Campus'      
DECLARE           @City  NVARCHAR(15)      
SET  @City  = 'City'      
DECLARE           @Country NVARCHAR(15)      
SET  @Country = 'Country'      
DECLARE           @Floor  NVARCHAR(15)      
SET  @Floor  = 'Floor'      
DECLARE           @Office  NVARCHAR(15)      
SET  @Office  = 'Office'      
DECLARE           @Rack  NVARCHAR(15)      
SET  @Rack  = 'Rack'      
DECLARE           @Region NVARCHAR(15)      
SET  @Region = 'Region'      
DECLARE           @StateProvince NVARCHAR(15)      
SET  @StateProvince = 'State/Province'      
DECLARE           @WorldRegion NVARCHAR(15)      
SET  @WorldRegion = 'World Region'      
DECLARE           @PostalCode NVARCHAR(15)      
SET  @PostalCode = 'Postal Code'      
       
DECLARE @VAR_ALL   NVARCHAR(10)      
SET @VAR_ALL = '<ALL>'      
  
DECLARE @LocationUserUDPPassed smallint    
Set @LocationUserUDPPassed=1    
IF (  @LocationParameter= '<ALL>' and @CostCenter ='<ALL>' and @Manager='<ALL>')    
 Set @LocationUserUDPPassed=0        
      
/*      
Selecting the CompanyName, ProductName, ProductVersion of all the products      
and count of virtual/physical computers on which they are installed.      
*/      
    
SELECT       
  ARPDIM.ARPUserPublisher AS CompanyName,       
  ARPDIM.ARPUserDisplayName AS ProductName,       
  ARPDIM.ARPUserVersion AS ProductVersion,      
  INDIM.ComputerFullNTName as ComputerFullNTName,  
  CASE WHEN (INDIM.Virtual= 0) THEN INDIM.ComputerFullNTName Else NULL END AS Physical,      
  CASE WHEN (INDIM.Virtual= 1) THEN INDIM.ComputerFullNTName ELSE NULL END AS Virtual,
  MNDIM.OSLanguage AS LanguageName
FROM      
 [dbo].[SCRS_AddRemoveProgram_Fact_View] AS ARPFCT    
 INNER JOIN [dbo].[SCRS_AddRemoveProgram_DIMENSION_View] AS ARPDIM ON (ARPDIM.ARPKey = ARPFCT.ARPKey)     
 INNER JOIN [dbo].[SCRS_ManagedNode_Dimension_View] AS MNDIM ON (MNDIM.NodeKey = ARPFCT.NodeKey)    
 INNER JOIN [dbo].[SCRS_ManagedNode_Dimension_Latest_View] INDIM ON (INDIM.ComputerFullNTName = MNDIM.ComputerFullNTName)    
WHERE  
 (@Company = @VAR_ALL  OR ARPDIM.ARPUserPublisher = @Company)   
 AND (@ProductName = @VAR_ALL  OR ARPDIM.ARPUserDisplayName = @ProductName)      
 AND (@Version = @VAR_ALL   OR ARPDIM.ARPUserVersion = @Version)      
 AND (@Domain = @VAR_ALL  OR INDIM.ComputerNTDomainname = @Domain)  
 AND ( @LocationUserUDPPassed =0 OR INDIM.ComputerFullNTName in  
 (  
 SELECT NLAFCT.ComputerFullNTName  
 FROM [dbo].[SCRS_NodeLocationAndAssociation_FACT_Latest_View] NLAFCT   
 WHERE (@CostCenter = @VAR_ALL OR NLAFCT.Costcenter = @CostCenter)  
 AND (@Manager = @VAR_ALL OR NLAFCT.Manager = @Manager)    
 AND ((@Location=@VAR_ALL) OR --When User does Not Select Location  
 --When User Selects Locations  
 ((((@Location=@Building)  AND (@LocationParameter = @VAR_ALL OR NLAFCT.LocationBuilding = @LocationParameter))  
 OR ((@Location=@Campus)  AND (@LocationParameter = @VAR_ALL OR NLAFCT.LocationCampus = @LocationParameter))  
 OR ((@Location=@City)  AND (@LocationParameter = @VAR_ALL OR NLAFCT.LocationCity = @LocationParameter))  
 OR ((@Location=@Country)  AND (@LocationParameter = @VAR_ALL OR NLAFCT.LocationCountry = @LocationParameter))  
 OR ((@Location=@Floor)  AND (@LocationParameter = @VAR_ALL OR NLAFCT.LocationFloor = @LocationParameter))  
 OR ((@Location=@Office)  AND (@LocationParameter = @VAR_ALL OR NLAFCT.LocationOffice = @LocationParameter))  
 OR ((@Location=@Rack)  AND (@LocationParameter = @VAR_ALL OR NLAFCT.LocationRack = @LocationParameter))  
 OR ((@Location=@Region)  AND (@LocationParameter = @VAR_ALL OR NLAFCT.LocationRegion = @LocationParameter))  
 OR ((@Location=@StateProvince) AND (@LocationParameter = @VAR_ALL OR NLAFCT.LocationStateProvince = @LocationParameter))  
 OR ((@Location=@WorldRegion) AND (@LocationParameter = @VAR_ALL OR NLAFCT.LocationWorldRegion = @LocationParameter))  
 OR ((@Location=@PostalCode) AND (@LocationParameter = @VAR_ALL OR NLAFCT.LocationPostalCode = @LocationParameter))  
 )))) )  
  AND ARPFCT.ARPEndDateKey IS NULL   
ORDER BY     
 ARPDIM.ARPUserPublisher,      
 ARPDIM.ARPUSerDisplayName,      
 ARPDIM.ARPUserVersion    

--Restore Database Settings      
SET NOCOUNT OFF    
END

See Also

Show: