January 25, 2012 at 3:24 pm
I have a filter page set up in .Net that passes parameters to my SQL Server Report. The report uses a Stored Procedure as its dataset. One particular parameter can have multiple values (posNumShift). When I run the Stored Procedure from SQL Server as such:
EXEC rptSP_GetDiscipline @startDate = '1/1/2009', @endDate = '1/1/2011', @filterShifts='A,B,C,D'
it works fine. The Stored Procdure splits the multi-valued parameter and puts the values into a temp table which is then used in an IN clause. Code below (I removed all of the other parameters for easier reading):
SELECT [Proposed Action Date], PosNumShift
FROM v_Reprimand
WHERE @startDate<=[Proposed Action Date] AND [Proposed Action Date]<=@endDate AND EIN=COALESCE(@filterEIN, EIN) AND WorkGroupAbbrv=COALESCE(@filterStation, WorkGroupAbbrv)
AND EEO=COALESCE(@filterEEO, EEO)
AND PosNumShift IN (SELECT Val FROM fn_String_To_Table(@filterShifts,',',1))
In my report I have the parameter @filterShifts set up as text with a default value of A,B,C,D (no quotes). Since the parameter is being passed to the report as 'A' or 'A,B', etc..., I figured I don't need to set it up as allowing multiple values (although I tried setting that property to no avail). I've also read about using the JOIN property to set the default value, but since its being passed in already delimited, I didn't think this was relavent. Any ideas?
January 25, 2012 at 4:54 pm
I assume the report actually renders fine, but doesn't return the expected results.
Have you ran profiler? 😛
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgJanuary 26, 2012 at 7:45 am
Since you are using a text parameter, how are you guaranteeing that the values entered in the test parameter are comma-delimited?
As Jason mentioned, I'd set up a profiler trace to see what is really being passed to the SQL Server.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
January 26, 2012 at 9:07 am
Found the answer. I had to pass each value as if a different parameter from my .Net page. If a user selected A and B, my string had to have:
...&filterShifts=A&filterShifts=B
@filterShifts was set as multi-valued in my report. Then I had to go to my datasets properties to set the query parameter value to:
=CStr(Join(Parameters!filterShifts.Value,",")) to join the values together.
The CStr was necessary, otherwise I got an error.
I ran the Profiler, but it didn't help much. What was a bigger help was looking at the table, ExecutionLogStorage in my ReportServer database. The parameter column showed what was being passed.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply