Dataset works graph does not display

  • Hi there

    I've developed a report who's dataset is from a stored procedure which has three parameters: server name, start date, and end date.

    The dataset designer works as expected when I manually input the three parameters:

    http://i50.tinypic.com/6ntik8.jpg

    My report layout is:

    http://i47.tinypic.com/27wsnth.jpg

    But when I preview the report, nothing is displayed:

    http://i45.tinypic.com/2mqlwdc.jpg

    The stored proc is:

    CREATE PROCEDURE [dbo].[UspRepCpu]

    @ServerName VARCHAR(100),

    @StartDateCHAR(10),

    @EndDateCHAR(10)

    AS

    DECLARE

    @Query VARCHAR(4000)

    Set @ServerName = @ServerName + 'PerfMonData'

    SET @Query =

    '

    WITH CpuUse ([Server], [Counter], [Date], [Time], [Val]) AS

    (

    SELECT

    Servers.ServerName AS [Server] ,

    Counters.CounterName AS [Counter] ,

    convert (varchar(16),DATEADD ( hh , DATEDIFF ( hh , 0 , ' + @ServerName +'.CounterDateTime ) , 0 ),103 ) AS StartDate ,

    right(Ltrim(rtrim(convert(nvarchar(16), DATEADD ( hh , DATEDIFF ( hh , 0 , ' + @ServerName +'.CounterDateTime ) , 0 ), 121))),5) AS StartTime,

    ROUND ( AVG ( ' + @ServerName +'.CounterValue ) , 0 )AS CounterValue

    FROM

    Counters

    INNER JOIN ' + @ServerName +'

    ON Counters.Id = ' + @ServerName +'.CounterId

    INNER JOIN Servers

    ON ' + @ServerName +'.ServerId = Servers.Id

    AND

    (Counters.CounterName LIKE ''%Processor%'')

    GROUP BY

    Servers.ServerName ,

    Counters.CounterName ,

    DATEADD ( hh , DATEDIFF ( hh , 0 , ' + @ServerName +'.CounterDateTime ) , 0 )

    UNION ALL

    SELECT

    Servers.ServerName AS [Server] ,

    Counters.CounterName AS [Counter] ,

    convert (varchar(16),DATEADD ( hh , DATEDIFF ( hh , 0 , PerfMonData.CounterDateTime ) , 0 ),103 ) AS StartDate ,

    right(Ltrim(rtrim(convert(nvarchar(16), DATEADD ( hh , DATEDIFF ( hh , 0 , PerfMonData.CounterDateTime ) , 0 ), 121))),5) AS StartTime,

    ROUND ( AVG ( PerfMonData.CounterValue ) , 0 )AS CounterValue

    FROM

    Counters

    INNER JOIN PerfMonData

    ON Counters.Id = PerfMonData.CounterId

    INNER JOIN Servers

    ON PerfMonData.ServerId = Servers.Id

    WHERE Servers.ServerName = REPLACE(''' + @ServerName +''',''PerfMonData'','''')

    AND

    (Counters.CounterName LIKE ''%Processor%'')

    GROUP BY

    Servers.ServerName ,

    Counters.CounterName ,

    DATEADD ( hh , DATEDIFF ( hh , 0 , PerfMonData.CounterDateTime ) , 0 )

    )

    SELECT * FROM CpuUse

    WHERE [Date] Between ''' + @StartDate + ''' AND ''' + @EndDate + '''

    ORDER BY [Date] DESC, [Time] DESC, [Counter] ASC

    '

    exec (@Query)

  • Hi,

    I m not sure ,i noticed u r using startdate and enddate in sp as 'Char' but in report ur using 'Date' datatype chage u r Datatype in Sp as date time hopefully it will work....

    Thanks

    Veeren

    Thanks & Regards,
    Veeren.
    Ignore this if you feel i am Wrong. 😉

  • Yeah you were right. Not long after I made the post I changed the stored procedure to:

    ALTER PROCEDURE [dbo].[UspRepCpu]

    @ServerName VARCHAR(100),

    @StartDateDATETIME,

    @EndDateDATETIME

    AS

    DECLARE

    @Query VARCHAR(4000)

    Set @ServerName = @ServerName + 'PerfMonData'

    SET @Query =

    '

    SELECT

    Servers.ServerName AS [Server] ,

    Counters.CounterName AS [Counter] ,

    convert (char(10),DATEADD ( hh , DATEDIFF ( hh , 0 , ' + @ServerName +'.CounterDateTime ) , 0 ),103 ) + '' '' + convert(CHAR(8), DATEADD ( hh , DATEDIFF ( hh , 0 , ' + @ServerName +'.CounterDateTime ) , 0 ), 114) AS StartDate ,

    ROUND ( AVG ( ' + @ServerName +'.CounterValue ) , 0 )AS CounterValue

    FROM

    Counters

    INNER JOIN ' + @ServerName +'

    ON Counters.Id = ' + @ServerName +'.CounterId

    INNER JOIN Servers

    ON ' + @ServerName +'.ServerId = Servers.Id

    AND

    (Counters.CounterName LIKE ''%Processor%'')

    GROUP BY

    Servers.ServerName ,

    Counters.CounterName ,

    DATEADD ( hh , DATEDIFF ( hh , 0 , ' + @ServerName +'.CounterDateTime ) , 0 )

    UNION ALL

    SELECT

    Servers.ServerName AS [Server] ,

    Counters.CounterName AS [Counter] ,

    convert (char(10),DATEADD ( hh , DATEDIFF ( hh , 0 , PerfMonData.CounterDateTime ) , 0 ),103 ) + '' '' + convert(CHAR(8), DATEADD ( hh , DATEDIFF ( hh , 0 , PerfMonData.CounterDateTime ) , 0 ), 114) AS StartDate,

    ROUND ( AVG ( PerfMonData.CounterValue ) , 0 )AS CounterValue

    FROM

    Counters

    INNER JOIN PerfMonData

    ON Counters.Id = PerfMonData.CounterId

    INNER JOIN Servers

    ON PerfMonData.ServerId = Servers.Id

    WHERE Servers.ServerName = REPLACE(''' + @ServerName +''',''PerfMonData'','''')

    AND

    (Counters.CounterName LIKE ''%Processor%'')

    GROUP BY

    Servers.ServerName ,

    Counters.CounterName ,

    DATEADD ( hh , DATEDIFF ( hh , 0 , PerfMonData.CounterDateTime ) , 0 )

    '

    EXEC (@Query)

  • Hi ,

    Great please let us know for u r questions keep on post ur querys in SSC.

    Thanks

    Veeren:-)

    Thanks & Regards,
    Veeren.
    Ignore this if you feel i am Wrong. 😉

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply