Code Sample: SCRS_SWIApplicationsInventorySummary_CUSTOM SCRM Stored Procedure

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

Tasks

How to Create an SCRM Stored Procedure
How to Add Drilldown Functionality to an SCRM Report

Other Resources

SCRM 2006 SMS Views
SCRM 2006 Stored Procedures