USE [SoftwareReporting]
GO

/****** Object:  StoredProcedure [dbo].[sp_ReportBasic]    Script Date: 03/26/2010 09:10:23 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[sp_ReportBasic]
		@AppName VARCHAR(255) = NULL,
		@WkstnName VARCHAR(128) = NULL,
		@EmployeeID VARCHAR(255) = NULL,
		@PAYROLL_CO_num VARCHAR(4) = NULL,
		@PAYROLL_RC_num VARCHAR(7) = NULL,
		@MANAGER_EMPLOYEE_ID VARCHAR(255) = NULL
AS
BEGIN
SELECT	v_CommonApp.AppName
		, v_SMSAppData.[Version]
		, v_SMSAppData.WkstnName
		, SMS_Hardware.EmployeeID
		, ePeople_feed.NAME
		, ePeople_feed.PAYROLL_CO_num
		, ePeople_feed.PAYROLL_RC_num
		, ePeople_feed.EMAIL_ADDRESS
		, ePeople_feed.PHONE_NUMBER
		, ePeople_feed.MANAGER_EMPLOYEE_ID
		, ePeople_feed.MANAGER_NAME
		, ePeople_feed.MANAGER_PHONE
FROM    v_SMSAppData 
LEFT JOIN v_CommonApp ON v_SMSAppData.appID = v_CommonApp.AppID 
LEFT JOIN SMS_Hardware ON v_SMSAppData.WkstnName = SMS_Hardware.wksname 
LEFT JOIN ePeople_feed ON SMS_Hardware.EmployeeID = ePeople_feed.EMPLOYEE_ID
WHERE (@AppName IS NULL OR AppName LIKE '%' + @AppName + '%')
  AND (@WkstnName IS NULL OR WkstnName LIKE '%' + @WkstnName + '%')
  AND (@EmployeeID IS NULL OR EmployeeID = @EmployeeID)
  AND (@PAYROLL_CO_num IS NULL OR PAYROLL_CO_num = @PAYROLL_CO_num)
  AND (@PAYROLL_RC_num IS NULL OR PAYROLL_RC_num = @PAYROLL_RC_num)
  AND (@MANAGER_EMPLOYEE_ID IS NULL OR EmployeeID = @MANAGER_EMPLOYEE_ID)
ORDER BY v_CommonApp.AppName
END
GO

CREATE PROCEDURE sp_InstalledAppNamesAndCounts 
	-- Add the parameters for the stored procedure here
        -- These will need to be changed, I am sure.
	@p1 Varchar(255) = NULL, 
	@p2 int = 0
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
    -- These will need to be changed, I am sure.
    -- SELECT @P1, @P2
	SELECT v_CommonApp.AppName, COUNT(v_SMSAppdata.appID) AS CountOfApps
	FROM v_CommonApp
	LEFT JOIN v_SMSAppData ON v_SMSAppData.appID = v_CommonApp.AppID
	GROUP BY v_CommonApp.AppName
	HAVING COUNT(v_SMSAppData.AppID) > 0
	ORDER BY v_CommonApp.AppNameEND
GO

ALTER PROCEDURE [dbo].[sp_InstalledAppNameCount]
AS
BEGIN
	SELECT v_CommonApp.AppID
	, v_CommonApp.AppName
	, (COUNT(v_SMSAppdata.appID)) As AppCount
	FROM v_CommonApp
	LEFT JOIN v_SMSAppData ON v_SMSAppData.appID = v_CommonApp.AppID
	GROUP BY v_CommonApp.AppID, v_CommonApp.AppName
	HAVING COUNT(v_SMSAppData.AppID) > 0
	ORDER BY v_CommonApp.AppName
END

quote:

---

Originally posted by duanecwilson

I am not sure how to do what you're saying. I must have mental block on this one. I don't have to use output parameters. I guess I just don't understand how to do it either way.

Duane

---

quote:

---

Originally posted by duanecwilson

Yes, I am showing them. It is just the AppName and then I have the count(AppID) which is an aggregate sum of how many of each type of AppName is in the detail records. For example, there may be 100,000 users of Winzip, so there will be a row in the summary report with Winzip, 100,000. So the summary report shows just those 2 columns and if works when I run it. It has about 60 lines with the + mark below the AppName. But, depending on what I do, when I click the plus +, I either get an error right on the report saying the data cannot be displayed, or the whole thing hangs.

Duane

---