SQL Server Reporting Services not passing the correct parameter from SQL Stored Procedure

0
votes

I have a SQL stored procedure that calls the most recent date from a certain cycle. This date includes a bunch of data points that are populated into the report. When I execute the SELECT statement alone, it pulls the correct date I want. In SSRS, it pulls the second to last date... I have the TOP 1 in there to select the most recent along with the DESC. I was wondering if I have an issue with my parameters within SSRS. The report is set up as 2 sub-reports. The first sub-report is just an overview page and the second is the actual report that contains all the data with the Start and End date. @Phase1Start and @Phase1End are my parameters being pass to the sub-report that contains all the data points. Also, the reason I do not have single quotes around the 1 is because CycleComplete is a bit. Any solutions?

I have already tried:

-Changing the date types from datetime's to text's -Including single quotes around the 1

Here is my Stored Procedure:

DECLARE @Phase1Start as datetime
DECLARE @Phase1End as datetime

  SELECT
        @Phase1Start = (SELECT TOP 1 [CycleStartedOn] FROM [dbo].[IPEC_P_CIP_TKB_PREFLT] WHERE [CycleComplete] = 1 ORDER BY [CycleStartedOn] DESC) 
        ,@Phase1End = (SELECT TOP 1 [CycleEndedOn] FROM [dbo].[IPEC_P_CIP_TKB_PREFLT] WHERE [CycleComplete] = 1 ORDER BY [CycleStartedOn] DESC) 

  DECLARE @tblReports TABLE
(    
    Phase1Start datetime
    ,Phase1End datetime
    )

    INSERT INTO @tblReports
(    
    Phase1Start 
    ,Phase1End 
    )

Values
(   
    @Phase1Start
    ,@Phase1End
    )

SELECT * FROM @tblReports

END

The stored procedure compiles correctly and gives me the start and end times I want. The issue is within SSRS not pulling the correct date from the stored procedure even though I have @Phase1Start and Phase1End as my parameters being passed to the sub-report that contains all the data.

2
mysqlsqlsql-serverstored-proceduresreporting-services
Are you running in development locally? – Ross Bush
Thanks for the quick reply. SSRS and the SQL database both reside on the same server I am working on yes. And essentially no, I am working on a virtual machine. – drewdaff
Can you hit the refresh arrow on the report toolbar in VS or refresh the dataset to make sure you are not hitting a cache issue. While in dev mode, the data is cached in the *.data files. – Ross Bush
That did not seem to change anything. Thanks for the response though. – drewdaff
your parameter can be set using a query.. create a dataset that is SELECT Top(1) Phase1Start = [CycleStartedOn], Phase1End = [CycleEndedOn] FROM [dbo].[IPEC_P_CIP_TKB_PREFLT] WHERE [CycleComplete] = 1 ORDER BY [CycleStartedOn] DESC and under Default Values for the parameter, pick get values from a query, select the new dataset and pick the correct column for each param – JamieD77

2 Answers

0
votes

Is it possible that our first sub query and the second sub query return different records? Perhaps this might help:

SELECT Top(1) @Phase1Start = [CycleStartedOn], @Phase1End = [CycleEndedOn] FROM [dbo].[IPEC_P_CIP_TKB_PREFLT] WHERE [CycleComplete] = 1 ORDER BY [CycleStartedOn] DESC
0
votes

You could use the group by 

SELECT  @Phase1Start = MAX([CycleStartedOn] ) ,
        @Phase1End = MAX([CycleEndedOn])
FROM        [dbo].[IPEC_P_CIP_TKB_PREFLT] 
WHERE    [CycleComplete] = 1