locked
"Define query parameters" popup in Dataset properties -> Refersh field, not displayed for Sps in SSDT 2010 (Works fine in BIDS) RRS feed

  • Question

  • When we upgraded our SSRs projects to 2012, we are facing an issue in Data tools environment.

    When we click REFRESH FIELDS button in Dataset properties, With query type as SP (For all our sps), the "Define query parameters"  popup is not displayed. 

    When we checked the SQL Profiler for queries executed at back end , NULL values are used for parameters.

    But when we Used the QUERY DESIGNER button, and clicked execute for sp, the popup comes.

    For User defined Functions, the Popup comes correctly.

    In BIDS, still we can open the RDLs and do the operations correctly

    Please advice



     

    • Moved by Kevin Cunnane [MSFT] Wednesday, November 12, 2014 10:23 PM Moving to the SSRS forum as it's related to that topic.
    Tuesday, November 11, 2014 8:37 PM

Answers

  • Hi Anoop A M,

    By design, Reporting Services provides graphical and text-based query designers to help you build queries for each data source type in your report. A data processing extension and its associated query designer can differ in support for data sources in the following ways:

    • By query designer type.
    • By query language variation
    • By support for the schema part of a database object name.
    • By support for query parameters.
    • By ability to import queries.

    We can use Refresh Fields to update the list of fields in the report to match the current list of fields from the shared dataset query. Refreshing the field list does not affect the calculated fields that you define in your report.

    I am afraid there is no other approach to work around the issue. If you have any concerns about this feature, you can submit a new feedback to Microsoft Connect at this link https://connect.microsoft.com/SQLServer/Feedback. Your feedback is valuable for us to improve our products and increase the level of service provided. Thanks for your understanding.

    If you have any more questions, please feel free to ask.

    Best Regards,
    Wendy Fu

    • Marked as answer by Wendy Fu Sunday, December 7, 2014 6:14 AM
    Wednesday, November 19, 2014 3:05 AM

All replies

  • Hi Anoop A M,

    According to your description, when you execute the stored procedure in Query Designer, “Define query parameters" will popup. But the data fields cannot be retrieved when click the Refresh Fields button.

    Currently, we can use "SET FMTONLY" statement which returns only metadata to the client as a workaround, please refer to the following steps:

    1. Change the query type to "Text".
    2. Execute the script:
    SET FMTONLY ON;
     EXEC <Stored Procedure>
     SET FMTONLY OFF;

    Since the query will only return the data fields names, after executed it, please change the query back to the original procedure. For more information about FMTONLY, you can refer to:
    http://msdn.microsoft.com/en-us/library/ms173839(v=sql.110).aspx

    If you have any more questions, please feel free to ask.

    Best Regards,
    Wendy Fu

    Friday, November 14, 2014 10:43 AM
  • Dear Wendy Fu

    My Scenario is different.

    In Query Designer, Every thing is fine (Parameter Popup is coming, and fields are populated under data set)

    But what we need to do is

    1, Select Query Type As SP

    2, Select the required SP

    3, Click Refresh Button

    4, A "Define query parameters" window popsup

    5, Provide values and click Ok

    6, Fields will be populated under the dataset

    The issue we face is that, we are not getting the  Define query parameters popup,

    Hence NULL values are passed to execute the sp, to retrieve the fields set.

    The Work around we are using now is editing the sp, and use

    set @Parameter = Value

    for each parameters in sp, and then we get the fields populated under dataset, when we click refresh.

    The issue exists in both  MSDT for Visual studio 2010 &  MSDT for  Visual studio 2012.

    But every thing is fine in BIDS with VS 2008 Shell for the same RDL

    Tuesday, November 18, 2014 9:04 PM
  • Hi Anoop A M,

    By design, Reporting Services provides graphical and text-based query designers to help you build queries for each data source type in your report. A data processing extension and its associated query designer can differ in support for data sources in the following ways:

    • By query designer type.
    • By query language variation
    • By support for the schema part of a database object name.
    • By support for query parameters.
    • By ability to import queries.

    We can use Refresh Fields to update the list of fields in the report to match the current list of fields from the shared dataset query. Refreshing the field list does not affect the calculated fields that you define in your report.

    I am afraid there is no other approach to work around the issue. If you have any concerns about this feature, you can submit a new feedback to Microsoft Connect at this link https://connect.microsoft.com/SQLServer/Feedback. Your feedback is valuable for us to improve our products and increase the level of service provided. Thanks for your understanding.

    If you have any more questions, please feel free to ask.

    Best Regards,
    Wendy Fu

    • Marked as answer by Wendy Fu Sunday, December 7, 2014 6:14 AM
    Wednesday, November 19, 2014 3:05 AM