SSRS. How to pass value in report for parameter from stored procedure?

Question

I have multiple reports with different versions (each version have different users) where I need to create drop down filters with ability to choose users whose are assigned in choosen version. I'm using following stored procedure to receive users list depending on version.

CREATE PROCEDURE sp_ContList
@version INT
AS
BEGIN
    SELECT [User] 
    FROM FormInstances 
    WHERE FormVersionId = @version
    GROUP BY [User]
END

Now I need to create drop down filter in report (Microsoft Visual Studio 2013) with user for example where @version = 1. How to pass parameter's value in SSRS correctly?

Answer 1

Create a parameter and select available values from created parameter. Then assign created dataset with procedure to Dataset dropdown. Set value field and label field from that dataset.

Answer 2

Create dataset that uses the stored proc.In parameter section set up the @version parameter to point to your @version parameter in the report, add parameter that will be the dropdown filter and in parameter properties you can select available values from query/dataset. You can then select the User as the display text and also as the value. You can then set the version number with default value and then this should pass through to the stored proc and return in the dropdown the correct users.

Also microsoft recommend that you avoid using the sp_ prefix for naming stored procedures as this is a reserved prefix for system stored procedures.