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