none
How to pass report parameters to stored procedure in report builder 3.0 RRS feed

  • Question

  • Report DataSet is based on a stored procedure. The Stored Procedure requires a parameter and the report has the parameter declared.

    I tried passing the parameter in various ways from the RB3.0 query designer.

    TEST_StoredProcedure @parameter

    TEST_StoredProcedure (@parameter)

    TEST_StoredProcedure@parameter

    TEST_StoredProcedure "parameter value"

    TEST_StoredProcedure 'parameter Value'

    TEST_StoredProcedure  Parameters!Description.Value

    These all return various errors.

    However calling the stored procedure without a parameter open the parameter window which allows manual input and then returns the data records as expected.

    There must be a standard way to pass a report parameter to a dataset based on a stored procedure parameter with a parameter requirement.

    Monday, June 4, 2012 2:02 PM

Answers

  • Rob,

    Please refer below screen shots from report builder where Store Procedure is Query Type

    Once you create your dataset by calling Store Procedure then it will create Report Parameters along with your dataset parameters you need to map them correctly. Please refer below screen shot.

    That's it.. Let me know if you have more queries.


    Thanks,
    Sandip Shinde(Blog:bi-bigdata.com|Twitter:@CloudBI_Sandip)

    • Marked as answer by RobGMiller Monday, June 4, 2012 5:11 PM
    Monday, June 4, 2012 2:15 PM
  • Hi,

    When you use a stored procedure when you design the dataset in the Dataset Properties dialog window, you do not use the query designer. In the Dataset properties, at the Query tab, select 'Stored Procedure' for 'Query type', that will give you a dropdown list with all available stored procedure , select your stored procedure. After selection in th eParameters tab you should see the parameters used by the stored proc.

    After this has been set, you will have to define Parameters for your report, in the report data pane, those parameters will be shown by the report at run time and are linked to your dataset parameters.


    Jan D'Hondt - Database and .NET development

    • Marked as answer by RobGMiller Monday, June 4, 2012 5:11 PM
    Monday, June 4, 2012 2:22 PM

All replies

  • Rob,

    Please refer below screen shots from report builder where Store Procedure is Query Type

    Once you create your dataset by calling Store Procedure then it will create Report Parameters along with your dataset parameters you need to map them correctly. Please refer below screen shot.

    That's it.. Let me know if you have more queries.


    Thanks,
    Sandip Shinde(Blog:bi-bigdata.com|Twitter:@CloudBI_Sandip)

    • Marked as answer by RobGMiller Monday, June 4, 2012 5:11 PM
    Monday, June 4, 2012 2:15 PM
  • Hi,

    When you use a stored procedure when you design the dataset in the Dataset Properties dialog window, you do not use the query designer. In the Dataset properties, at the Query tab, select 'Stored Procedure' for 'Query type', that will give you a dropdown list with all available stored procedure , select your stored procedure. After selection in th eParameters tab you should see the parameters used by the stored proc.

    After this has been set, you will have to define Parameters for your report, in the report data pane, those parameters will be shown by the report at run time and are linked to your dataset parameters.


    Jan D'Hondt - Database and .NET development

    • Marked as answer by RobGMiller Monday, June 4, 2012 5:11 PM
    Monday, June 4, 2012 2:22 PM
  • Thank you both for your reply.

    Monday, June 4, 2012 5:12 PM
  • Hi Sandip,

    Thank you for this, but if you use select all option and pass these to stored proc, it does not work or return anything. Do you have any idea why?

    ALTER Procedure  [dbo].[sp_GRNI] (@CutoffDate  Date,@Stock as varchar(50),@ProcCat as varchar(max),@Supplier as varchar(max),@MainAccount as varchar(max))

    in this proc First two parameters will always be one value at a time and it works fin. the remaining three parameters user can select multiple values and it is default to All. When I pass these to store proc it does not return any results.

    Regards,

    Thursday, January 31, 2019 2:01 PM