Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
This sample demonstrates the changes made to the SCRM 2006 stored procedure SCRS_GetCompany in the task How to Switch the Filter Order of an SCRM Report. SCRS_GetCompany is in the SCRM 2006 SystemCenterPresentation database.
Requirements
SCRM 2006
Example
SCRS_GetCompany is changed so that it no longer returns the full list of company names. Instead, it requires a program name used to provide a list of companies associated with a specific program.
USE [SystemCenterPresentation]
GO
/****** Object: StoredProcedure [dbo].[SCRS_GetCompany_CUSTOM] Script Date: 04/06/2006 12:06:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[SCRS_GetCompany_CUSTOM] ( @LocaleID int , @ProductName NVARCHAR(255) = '<ALL>')
AS
-- Prevent number of rows affected being returned as part of results.
SET NOCOUNT ON
/*Select Company Name */
IF @ProductName = '<ALL>'
BEGIN
SELECT 1 AS Sequence,'<ALL>' AS ProductCompanyName, dbo.fn_getLocalizedString('<ALL>','Misc',@LocaleID) AS ProductCompanyNameLabel
UNION
SELECT DISTINCT 2 AS Sequence,ARPUserPublisher AS ProductCompanyName , ARPUserPublisher AS ProductCompanyNameLabel
FROM [dbo].[SCRS_AddRemoveProgram_DIMENSION_View]
WHERE ARPUserPublisher IS NOT NULL
ORDER BY Sequence, ProductCompanyNameLabel
END
ELSE
BEGIN
SELECT 1 AS Sequence,'<ALL>' AS ProductCompanyName, dbo.fn_getLocalizedString('<ALL>','Misc',@LocaleID) AS ProductCompanyNameLabel
UNION
SELECT DISTINCT 2 AS Sequence,ARPUserPublisher AS ProductCompanyName , ARPUserPublisher AS ProductCompanyNameLabel
FROM [dbo].[SCRS_AddRemoveProgram_DIMENSION_View]
WHERE ARPUserPublisher IS NOT NULL
AND ARPUserDisplayName = @ProductName
ORDER BY Sequence, ProductCompanyNameLabel
END
--To Restore Database Settings
SET NOCOUNT OFF
See Also
Tasks
How to Create an SCRM Stored Procedure
How to Switch the Filter Order of an SCRM Report
Reference
SCRM 2006 SCRS_AddRemoveProgram_DIMENSION_View
SCRS_GetCompany Stored Procedure