December 9, 2015 at 6:42 am
I have a shared dataset built around a stored procedure that accepts two date parameters. I also have a report which references that dataset and has two date parameters added to it. When I run the report I am presented with two date selection boxes, but the dates selected don't seem to be affecting the data being returned. The parameters have the same name in both the report (start_date, end_date) and the dataset (@start_date, @end_date). Is there another way I have to associate them? The query requires those parameters when run in SSMS, and it runs correctly when they're supplied. What am I doing wrong?
December 9, 2015 at 1:44 pm
Sorry, gotta read more slowly.
What does the WHERE clause of your stored procedure look like? Do you have defaults set for the two parameters?
December 10, 2015 at 5:02 am
The WHERE clause in the sp is pretty standard:
WHERE p.utc_date BETWEEN @start_date AND @end_date
The report parameters have default values specified (expressions calculating the first and last days of the month). However, even with these default values set, the initial report returned doesn't adhere to them. It's like there is a disconnect between the report parameters and the dataset/query parameters.
December 10, 2015 at 10:43 am
Any caching set up on the dataset?
Are you running the report in report manager or BIDS/VS?
Is the dataset set to Query Type Text or Stored Procedure?
If text, is the text "procname @start_date @end_date" or just "procname"
If Stored Proc, are the parameters looking correct on the parameters tab? (names match etc)
December 11, 2015 at 7:27 am
Caching is not set up for this dataset.
I've run the report both in BIDS and in the report manager.
The dataset is using a stored procedure, and the parameters look correct.
Gazareth (12/10/2015)
Any caching set up on the dataset?Are you running the report in report manager or BIDS/VS?
Is the dataset set to Query Type Text or Stored Procedure?
If text, is the text "procname @start_date @end_date" or just "procname"
If Stored Proc, are the parameters looking correct on the parameters tab? (names match etc)
December 11, 2015 at 8:36 am
Cheers, can you capture the proc running in profiler? Should be able to confirm the parameter values that the proc's executing with.
December 11, 2015 at 9:18 am
Mark Harley (12/11/2015)
Caching is not set up for this dataset.I've run the report both in BIDS and in the report manager.
The dataset is using a stored procedure, and the parameters look correct.
are the parameter names using the same case?
does the stored procedure have default values for the parameters?
Don Simpson
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply