I am creating a report in SSRS, using Stored Procedure as 'Command Type' with 5 parameters including StartDate, EndDate. The other 3 parameters are just filters used in my where clause, but are not there in the select statement. If i preciew my SSRS report, i see my other 3 parameters as blanks. How can i get to see the values in there ? Craete Proc as Test @StartDate DATETIME, @EndDate DATETIME, @Area VARCHAR(50), @Mid BIGINT, @AgID VARCHAR(5) AS BEGIN SET NOCOUNT ON; -- To avoid parameter sniffing DECLARE @SD DATETIME DECLARE @ED DATETIME DECLARE @A VARCHAR(50) DECLARE @M BIGINT DECLARE @I VARCHAR(5) SET @SD = @StartDate SET @ED = @EndDate SET @A = @Area SET @M = @Mid SET @I = @AgID Select d.Value, h.Number, f.Id FROM eg1 d with (nolock) JOIN eg2 h with (nolock) ON h.id = d.id and h.date = d.date and h.no = d.no JOIN eg3 f with(nolock) on h.seq = f.seq JOIN eg4 m with(nolock) on h.tid = m.tid JOIN eg5 a with(nolock) on a.areaid = m.areaid JOIN eg6 r with(nolock) on r.rid = a.rid WHERE h.tdate >= @SDate AND h.tdate <= @EDate AND r.Name = @A AND h.BNumber = @M AND d.AID = @I GROUP BY h.date END So, r.Name, h.BNumber and d.AID are not there in the "SELECT" statement, but while the user accesses the report, I want him to select the values from a list. I am not getting any values in the list of these three parameters (@A, @M, @I)

Any issue with Parameter Sniffing?

How can we create a parameterized report based on this stored procedure?

Hi There By default the parameters values will not get filled automatically. if you would like your parameter to get the values from database then You have to create a separate dataset for these parameters and assign the dataset to available values as well as default values, however if you would like to give your parameter a constant values you can then specify these values. I hope this will help I am putting screenshot for your help Many Thanks Syed Qazafi Anjum Please click "Mark as Answer" if this resolves your problem or "Vote as Helpful" if you find it helpful. for more info http://sql-bi-dev.blogspot.co.nz/2010/07/report-parameters-in-ssrs-2008.html http://munishbansal.wordpress.com/2008/12/29/passing-multi-value-parameter-in-stored-procedure-ssrs-report/

So, do you recommend creating 3 new datasets (for the 3 parameters that I have), and in their dataset query, I will have something like this: For 1st parameter - Select distinct r.name from eg6 as r For 2nd parameter - Select distinct h.BNumber from eg2 as h For 3rd parameter - Select distinct d.Aid from eg1 as d After doing this, i should add new parameters by right clicking on "Parameters" ! But, then what will happen to the existing stored procedure parameters (Area, Mid, AgId) ? Also, in that article, where can i use that JOIN function to be able to select multiple values from the list ??? Is that done in the 1st dataset? I will be really glad if you can give me a screenshot of that !

Hi There Thanks for your posting. No you do not need to create new parameters to assign the dataset values. Just right click on your existing parameters and go to properties and go to available values and assign the dataset values as I have mention above, you can assign the same dataset to your default values as well. If you are using multivalued parameter in SSRS 2005, Yes the join will be used in your main dataset where you calling your stored procedure but If you are using SSRS 2008 you might not need to use this Join function for stored procedure, However you need to create User defined function for splitting your multivalued sting. Here is good lick for creating that function http://sql-bi-dev.blogspot.co.nz/2010/07/function-to-split-multi-valued.html http://blog.logiclabz.com/sql-server/split-function-in-sql-server-to-break-comma-separated-strings-into-table.aspx This is very good link for using multivalued parameter used in stored procedure http://blog.summitcloud.com/2010/01/multivalue-parameters-with-stored-procedures-in-ssrs-sql/ If you have any questions please let me know Many Thanks Syed Qazafi Anjum Please click "Mark as Answer" if this resolves your problem or "Vote as Helpful" if you find it helpful.

Thanks for the reply. Also, is it possible that my CASE statements in the stored procedure sql give a different result and when i use that same stored procedure in an SSRS report, results look different to me. What's the reason for that?

Hi There Can you please run the report in your query designer inside SSRS whether you get same results? That would be the good starting point for you Many Thanks Syed Qazafi Anjum Please click "Mark as Answer" if this resolves your problem or "Vote as Helpful" if you find it helpful.

The results in Query Designer are different than those in the Stored Procedure, and same as compared to SSRS preview.

Hi There Are you passing the exact same parameter values both in SP as well as in SSRS? Can you please share your query for case statement then I might be able to help you Many thanks Syed

Can i send an e-mail to you, if you don't mind?

Hi There Thanks for your posting. Yes off course you can send an email to me at sqazafi@hotmail.com Many thanks Syed Qazafi Anjum

Will do that. Thanks Syed.