CODEWITHSUNDEEP
reporting-services
user2233506
user2233506

Reputation: 81

SSRS adding Date Parameters causes report to spin and spin

This seems so simple I can't understand where the problem is. I have done this before but not any time recently so I hope I am missing something simple.

I have a stored procedure on my 2005 SQL box that is a simple select statement with a few table joins to get the data I need.

I want to set the report to pull invoice dates using variables @StartDate and @EndDate.

When I am creating my report in SSRS 2005, I know I have to Declare and set static @StartDate and @EndDate values in the stored procedure for the report wizard to allow its creation.

Declare @StartDate datetime
Declare @EndDate datetime

Set @StartDate = '2014-08-01'
Set @EndDate = '2014-08-31'

This is all fine so far, my report gets created and I can run the report in about 3 seconds and it returns the expected 3000 rows of data for the previous month.

The problem happens after I switch the stored procedure type from command type: Text (the default type it creates the report as) to Stored Procedure and add my variables back in. (If I leave it as Text, it yells at me that it is expecting parameter @StartDate so I know I have to change it to Stored Procedure).

Then I go into the stored procedure and comment out the above 4 lines and add in (before AS BEGIN): ( @StartDate datetime, @EndDate datetime )

I then go into my Report menu and create the StartDate and EndDate parameters as DateTime.

Next, I go into my dataset properties and in the Parameters tab I add @StartDate selecting the report variable StartDate and @EndDate pointing to the EndDate variables I just created in the Report menu.

Now, the problem occurs when I view the report, I select the same dates I previously used above and run the report, but the report spins and spins and never finishes.

This is driving me crazy because I know I have done this before.

Upvotes: 0

Views: 1063

Answers (2)

user2233506
user2233506

Reputation: 81

It started working and I did not make any changes. This is how I have everything configured:

Stored Procedure Declare Variables:

Stored Procedure Declare Variables

Stored Procedure Where condition:

Stored Procedure Where condition

SSRS Report Parameters:

SSRS Report Parameters

SSRS Dataset Parameters:

SSRS Dataset Parameters

Upvotes: 0

Tak
Tak

Reputation: 1562

It sounds like you are not passing the Start date and End Date to the Stored Procedure and I suspect it is retrieving all the records without condition.

In your stored procedure you need to declare @StartDate and @EndDate as parameters

CREATE PROC usp_someSP @StartDate date, @EndDate date
AS
BEGIN
    -- some code here .. select .....
END

In your report you pass the start date and end date parameters.

Upvotes: 1

Related Questions