troubleshooting Question

sql parameters

Avatar of aneilg
aneilgFlag for United Kingdom of Great Britain and Northern Ireland asked on
SSRSSQL
3 Comments1 Solution332 ViewsLast Modified:
I am using the following query but i cannot seem to pass in any parameters.
if i do exec ExecutionLogReport, the query just runs


USE ReportServer
GO
/****** Object:  StoredProcedure [ExecutionLogReport]    Script Date: 02/07/2012 11:14:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO

ALTER PROCEDURE [dbo].[ExecutionLogReport]

AS
BEGIN

      DECLARE @DateFrom Date
      DECLARE @DateTo Date
      
--SET @DateFrom = '2012-01-01'
--SET @DateTo = '2012-02-07'


-- End debugging script --

SELECT    

      DATEPART(Hour, TimeStart) AS ReportYear
    , DATEPART(Month, TimeStart) AS ReportMonth
    , DATEPART(Day, TimeStart) AS ReportDay
    , DATEPART(Hour, TimeStart) AS ReportHour
    , Type
    , COUNT(Name) AS ExecutionCount
    , SUM(TimeDataRetrieval) AS TimeDataRetrievalSum
    , SUM(TimeProcessing) AS TimeProcessingSum
    , SUM(TimeRendering) AS TimeRenderingSum
    , SUM(ByteCount) AS ByteCountSum
    , SUM([RowCount]) AS RowCountSum
    , TimeStart

FROM

(
    SELECT
            TimeStart,
            Catalog.Type,
            Catalog.Name,      
            TimeDataRetrieval,
            TimeProcessing,            
            TimeRendering,      
            ByteCount,      
            [RowCount]
            
    FROM
    Catalog
            INNER JOIN ExecutionLog4
                  ON Catalog.ItemID = ExecutionLog4.ReportID
            LEFT OUTER JOIN Users
                  ON Catalog.CreatedByID = Users.UserID

    WHERE ExecutionLog4.TimeStart BETWEEN @DateFrom AND @DateTo

) AS RE

GROUP BY

      DATEPART(Hour, TimeStart)
    , DATEPART(Month, TimeStart)
    , DATEPART(Day, TimeStart)
    , DATEPART(Hour, TimeStart)
    , Type
    , TimeStart

ORDER BY

      ReportYear
    , ReportMonth
    , ReportDay
    , ReportHour
    , Type
    , TimeStart

END
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 3 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 3 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros