none
Can stored procedures be used as DSV for creating Ad hoc reports using report builder 3.0

    Question

  • Hi

    Can stored procedures be used as DSV  for creating Ad hoc reports using report builder 3.0

    Thanks

    Ajit

    Wednesday, August 21, 2013 6:42 AM

Answers

  • Hi Ajit,

    If I understand correctly, you want to use a stored procedure inside a datasource view of a report model project. However when we create a Datasource View, there is no option for Store Procedures.

    In a DataSource View, only views, tables or named queries can be used. We can use stored procedures in a query. However, no parameterized queries, parameterized stored procedures or parameterized UDFs can be used in a DSV named query. You can try to using below approach to achieve what you are require:
    To build views in our relational source and add the views to the datashource view to proceed with modelling.

    There is an article about Model Constraints, you can refer to it.
    http://blogs.msdn.com/b/ketaanhs/archive/2006/12/19/report-builder-model-constraints.aspx

    Hope this help. Any misunderstanding, please feel free to let me know.

    Regards,
    Alisa Tang

    Thursday, August 22, 2013 2:46 AM
    Moderator

All replies

  • Hello Ajit,

    Of course you can; in the dataset properties just switch from "Text" to "Stored Procedure" and select the SP from the given list.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Wednesday, August 21, 2013 6:49 AM
  • Hi Olaf,

    I am actually developing a Report model usind BIDS and want to know how stored proc can be used as Data Source views. When i try to include objects from my Relational Database to my Data Source view only the tables and views are getting populated in the list. Is there any way i can select the stored procedures.

    Thanks

    Ajit

    Wednesday, August 21, 2013 8:24 AM
  • I already told you, in dataset properties just switch from "Text" to "Stored Procedure":


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Proposed as answer by Harsh Kumar Wednesday, August 21, 2013 9:01 AM
    Wednesday, August 21, 2013 8:43 AM
  • Hello Olaf

    Please have a look at the screen shot attached. There is no option to select the stored procedures(only tables and views populated).

    However i figured out a different approach. I created another data set and used the stored proc there as per my requirement and then mapped it to my previous data set.

    Thanks for your help!!

    Ajit

    Wednesday, August 21, 2013 10:40 AM
  • You didn't mentioned that you use a "Report Model Project"; you always talk about Report Builder.

    No, it seems you can't use stored procedure in a "Report Model Project"


    Olaf Helper

    [ Blog] [ Xing] [ MVP]


    Wednesday, August 21, 2013 11:08 AM
  • Sorry for the confusion Olaf...I should have mentioned the Report Model

    Many Thanks

    Ajit

    Wednesday, August 21, 2013 12:39 PM
  • Hi Ajit,

    If I understand correctly, you want to use a stored procedure inside a datasource view of a report model project. However when we create a Datasource View, there is no option for Store Procedures.

    In a DataSource View, only views, tables or named queries can be used. We can use stored procedures in a query. However, no parameterized queries, parameterized stored procedures or parameterized UDFs can be used in a DSV named query. You can try to using below approach to achieve what you are require:
    To build views in our relational source and add the views to the datashource view to proceed with modelling.

    There is an article about Model Constraints, you can refer to it.
    http://blogs.msdn.com/b/ketaanhs/archive/2006/12/19/report-builder-model-constraints.aspx

    Hope this help. Any misunderstanding, please feel free to let me know.

    Regards,
    Alisa Tang

    Thursday, August 22, 2013 2:46 AM
    Moderator
  • Hi Alisa,

    I used a stored proc in the data source view that accepts a parameter. While designing my report i explicitly specified values to the parameter and then filtered my report based on that parameter.

    Thanks

    Ajit

    Monday, August 26, 2013 9:57 AM