Hi
That makes me feel better.
However in srs using the old way I had things,
I'm getting appropriate results if I select 1 User at a time,
When I select "All" or more than 1, I get no results which is wrong
----------------------------------------------------------------
procedure to display data
DECLARE @CurrentUserName varchar(200);
SET @CurrentUserName =
(
SELECT fullname
FROM dbo.FilteredSystemUser
WHERE systemuserid = dbo.fn_FindUserGuid()
)
DECLARE @CurrentUserID uniqueidentifier;
SET @CurrentUserID =
(
SELECT systemuserid
FROM dbo.FilteredSystemUser
WHERE systemuserid = dbo.fn_FindUserGuid()
)
DECLARE @BusinessUnitID uniqueidentifier;
SET @BusinessUnitID =
(
SELECT businessunitid
FROM dbo.FilteredSystemUser
WHERE systemuserid = dbo.fn_FindUserGuid()
)
SELECT DISTINCT
OPP.customeridnameAS 'Customer Name',
OPP.contactidnameAS 'Contact Name',
OPP.actualclosedateAS 'Actual Close Date',
OPP.actualvalueAS 'Actual Value',
OPP.estimatedvalueAS 'Estimated Value',
OPP.createdbynameAS 'Created By Name',
OPP.createdbyAS 'Created By ID',
OPP.statuscodeAS 'Status Code',
OPP.statuscodenameAS 'Status Code Name',
OPP.owningbusinessunitAS 'Owning Bus Unit',
OPP.owneridnameAS 'Owner Name',
OPP.descriptionAS 'Description',
TBU.businessunitidnameAS 'TBU Business Unit Name',
TBU.businessunitidAS 'TBUBusiness Unit ID'
FROM dbo.FilteredOpportunity AS OPP
INNER JOIN dbo.FilteredUserSettings AS TBU ON @BusinessUnitID = TBU.businessunitid
WHERE @BusinessUnitID= TBU.businessunitid
AND OPP.actualclosedateBETWEEN @DateStart AND @DateEnd
AND @TheUser= OPP.owneridname
AND OPP.statuscode= 3
---------------------------------------------------------------
CREATE PROCEDURE atmWonOpportunities_Users
AS
DECLARE @CurrentUserID uniqueidentifier;
SET @CurrentUserID =
(
SELECT systemuserid
FROM dbo.FilteredSystemUser
WHERE systemuserid = dbo.fn_FindUserGuid()
)
DECLARE @CurrentUserName varchar(200);
SET @CurrentUserName =
(
SELECT fullname
FROM dbo.FilteredSystemUser
WHERE systemuserid = dbo.fn_FindUserGuid()
)
DECLARE @BusinessUnitID uniqueidentifier;
SET @BusinessUnitID =
(
SELECT businessunitid
FROM dbo.FilteredSystemUser
WHERE systemuserid = dbo.fn_FindUserGuid()
)
DECLARE @BusinessUnitIDName varchar(200);
SET @BusinessUnitIDName =
(
SELECT businessunitidname
FROM dbo.FilteredSystemUser
WHERE systemuserid = dbo.fn_FindUserGuid()
)
IF OBJECT_ID('#TEMP_TABLE', 'U') IS NOT NULL
DROP TABLE #TEMP_TABLE;
CREATE TABLE #TEMP_TABLE
(
useriduniqueidentifier,
usernamevarchar(200),
unitiduniqueidentifier,
unitnamevarchar(200)
)
INSERT INTO #TEMP_TABLE (userid, username, unitid, unitname)
SELECT USERS.systemuserid, USERS.fullname, USERS.businessunitid, USERS.businessunitidname
FROM dbo.FilteredSystemUser AS USERS
WHERE USERS.businessunitid = @BusinessUnitID
AND USERS.fullname <> 'SYSTEM'
AND USERS.fullname <> 'INTEGRATION'
SELECT * FROM #TEMP_TABLE
DROP TABLE #TEMP_TABLE