August 2, 2011 at 8:22 am
Hi,
I am on SSRS 2008R2 and need to implement a multi value parameter list of cities for a report but I am getting unexpected results. After continued research I found that the issue is because SSRS passes multi value parameters as comma seperated and not the standard T-SQL IN list. My situation is also unique in that some of my parameter values have spaces - e.g. 'san diego'. Below is my @city query:
SELECT DISTINCT city AS city FROM [dbo].[My Table] ORDER BY city ASC
and my query where clause is:
WHERE city IN ( @city )
Thanks for your help.
MCTS: BI 2008, MCITP: BI 2008
Stay Thirsty My Friends
August 2, 2011 at 11:35 am
I got this working by reading a great blog by David Leibowitz [/url]
David leverages a Table Function to parse the comma seperated list that SSRS multi value parameter passes and passes that to a SQL stored procedure that returns the report dataset.
MCTS: BI 2008, MCITP: BI 2008
Stay Thirsty My Friends
August 2, 2011 at 11:43 am
Bingo, you've found one of the hidden "features" of ssrs. Wreaks havoc with dates too (if your ssms settings are different than Bids when you copy queries from profiler too ssms).
1 side note. Assuming that this reports runs on any decent amount of data, I've found that inserting the results of the splitter function into a temp table with PK gives the best results for performance.
The 50 000 foot review is that the function will be evaluated as 1 row returned (expected). This can cause drastic under-estimations of the real amount of data returned by that filter.
Now combine that with 5 joins and other steps in the SP and you report is dead in the water.
I've seen a report go from 50 M reads down to 2M just by switching 2 functions to temp tables. Not to be under-estimated.
To put that into perspective, the DB had ± 20GB of data. 50M reads is 400 GB. 😉
August 2, 2011 at 12:35 pm
Ninja's_RGR'us (8/2/2011)
Bingo, you've found one of the hidden "features" of ssrs. Wreaks havoc with dates too (if your ssms settings are different than Bids when you copy queries from profiler too ssms).1 side note. Assuming that this reports runs on any decent amount of data, I've found that inserting the results of the splitter function into a temp table with PK gives the best results for performance.
The 50 000 foot review is that the function will be evaluated as 1 row returned (expected). This can cause drastic under-estimations of the real amount of data returned by that filter.
Now combine that with 5 joins and other steps in the SP and you report is dead in the water.
I've seen a report go from 50 M reads down to 2M just by switching 2 functions to temp tables. Not to be under-estimated.
To put that into perspective, the DB had ± 20GB of data. 50M reads is 400 GB. 😉
WOW!! Thanks for the heads up. To your point about DateTime params behaving badly I think I am experiencing that now.
I am getting invalid date range on my date params when passed in the stored procedure from SSRS. The SSRS UI has a DateTime format set to 'MM/DD/YYYY' but when I run a SQL Profiler trace the value passed to my stored procedure shows DateTime format as 'YYYY-MM-DD HH:MI:SS' e.g @StartDate = N'2011-06-20 00:00:00:000'
@EndDate = N'2011-07-15 00:00:00:000'
EXEC myStoredProc @City = N'Chicago, Atlanta, San Diego', @StartDate = N'2011-06-20 00:00:00:000', @EndDate = N'2011-07-15 00:00:00:000'
The stored procedure WHERE CLAUSE is:
WHERE [sales date] BETWEEN (@StartDate) AND (@EndDate)
[sales date] is defined as DATETIME datatype.
Thanks
MCTS: BI 2008, MCITP: BI 2008
Stay Thirsty My Friends
August 2, 2011 at 12:40 pm
That shouldn't happen (only ever saw that from profiler to ssms).
Are you sure that the parameters are declared as datetime in the proc, the parameters and the column. The later is less likely but would basically force index scans.
August 2, 2011 at 12:48 pm
Ninja's_RGR'us (8/2/2011)
That shouldn't happen (only ever saw that from profiler to ssms).Are you sure that the parameters are declared as datetime in the proc, the parameters and the column. The later is less likely but would basically force index scans.
Yes the DateTime parameters are DECLARE as DateTime. The DATA TYPE of the DateTime field is DATETIME as well.
@MultiVal NVARCHAR(MAX) ,
@StartDate DATETIME ,
@EndDate DATETIME
MCTS: BI 2008, MCITP: BI 2008
Stay Thirsty My Friends
August 2, 2011 at 12:52 pm
And the SSRS parameter is DATETIME and NOT string?
(just want to be 100% certain).
If it still doesn't work, please post the actual execution plan so that I can take a look.
August 2, 2011 at 1:09 pm
Yes the SSIS parameter is DateTime which results in the calendar control on the UI. The reporting is working just that it is returning less records than expected because it leaving out records on the date range.
MCTS: BI 2008, MCITP: BI 2008
Stay Thirsty My Friends
August 2, 2011 at 1:15 pm
You mean SSRS?
Never seen that.
You could always use
SET DATEFORMAT YMD as the first line of code in SSRS. That might fix it. Don't know what else to try.
August 2, 2011 at 1:30 pm
Ninja's_RGR'us (8/2/2011)
You mean SSRS?Never seen that.
You could always use
SET DATEFORMAT YMD as the first line of code in SSRS. That might fix it. Don't know what else to try.
Where do I added SET DATEFORMAT YMD? In the stored procedure?
MCTS: BI 2008, MCITP: BI 2008
Stay Thirsty My Friends
August 2, 2011 at 1:32 pm
No in the dataset.
August 2, 2011 at 1:37 pm
Are you sure the dataset is set as SP rather than text? Maybe that could be the difference :unsure:
August 10, 2011 at 12:50 pm
Ninja's_RGR'us (8/2/2011)
Are you sure the dataset is set as SP rather than text? Maybe that could be the difference :unsure:
Ninja's_RGR'us thanks for your help and patience but it's working now. My test dataset was off by a couple of hundred rows which lead me to believe that the parameter wasn't pulling all rows in the date range.
Thanks
MCTS: BI 2008, MCITP: BI 2008
Stay Thirsty My Friends
August 10, 2011 at 1:04 pm
Thanks for the feedback.
I was about reading to get my brain checked over this one! 😀
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply