locked
Is it not possible to edit the Semantic query when generating a report through Report Builder using a Report Model in SSRS RRS feed

  • Question

  • Hi,

    I want to generate a report using a report builder 3.0 . I have created a report model for the same. But when i try to modify the query according to my need it throws an error as shown below. Please explain me how to get the required data from my Report Model.

    Friday, August 30, 2013 2:49 PM

Answers

  • Ajit,

    I think the problem you're having really stems from a misunderstanding of the purpose of a model.

    The model is meant to simplify the reporting process for report authors, so that they can author reports without knowing the T-SQL syntax. It puts a barrier between the author of the report and direct access to the database. The person who created the model has already decided what data the report author can view, and which columns to give the author access to.

    If you want to write a report using SQL, you've not started in the right place. To do this, you need to launch report builder, and create an ODBC Connection to a SQL database (as opposed to a connection to a model). As long as you can establish a connection, you can then write whatever SQL takes your fancy.

    • Marked as answer by Mike Yin Sunday, September 8, 2013 3:00 PM
    Tuesday, September 3, 2013 4:30 PM

All replies

  • Hi Ajit,

    According to the error message, it may be the data source connection issue. In this case, please check the data source and dataset properties in the report builder 3.0. Please confirm the connection information of the data source which is similar to the example below:

    Type: Report Server Model
    Connection string: server=http://yourserver/reportserver;datasource=/Models/YourModel
    Credentials: Use Windows Authentication

    In addition, please check whether there is the sufficient permission to design the query sentences. Please check the error log from the folder “%programfiles%\Microsoft SQL Server\<SQL Server Instance>\Reporting Services\LogFiles”, and post the detailed error message for further analysis.

    Regards,
    Heidi Duan

    Monday, September 2, 2013 12:36 PM
  • Hi Heidi,

    I am trying to create a Ad hoc report using report builder 3.0 and for that I have already created a Report Model (A view has been used for the same).

    When I select all the fields there are no issues but my req is I need to develop a query according to a specified criteria as specified below:

    SELECT * FROM vNavigation_OrderHeader
    WHERE OrderNumber > 0  AND CompletedPhase
     is null  AND (IsCloseoutPending = 0 OR IsCloseoutPending IS NULL )
      ORDER BY ProjectName, ProjectID, PhaseName

    And  not all the fileds I want to be selected  ie. SELECT * FROM vNavigation_OrderHeader 

    But when i try to edit the query in Query designer I get the above error message posted earlier.

    Please let me know if Report Builder 3.0 allows to modify the query according to the requirement .

    Thanks

    Ajit

    Monday, September 2, 2013 2:53 PM
  • Hi Ajit,

    Thanks for your post.

    Report Designer can provide two query designers to help us specify the data from a Report Server Model data source in a report. According to my test result, the text-based query designer of the report model only works with a Semantic Model Definition Language (SMDL) specification in XML format. This is why the error occurs when you use T-SQL statements in the Query Designer, please ignore my initial post.

    From the screenshot, we can see the SMDL specification:

    Regarding the query sentence you provide, we can add filters to achieve the goal. We can select the drop-down list in New Group, and drag the corresponding fields to the filter area.

    The filter logic is the same as the one shown in the screenshot below:

    For the detailed information, please see the link: http://technet.microsoft.com/en-us/library/ms408278(v=sql.105).aspx

    Regards,
    Heidi Duan

     

    Tuesday, September 3, 2013 7:23 AM
  • Hi Heidi,

    Thanks for your post.

    I an amazed at this point of time. What is the use of query designer if we cannot modify things to our requirements. Simple selecting few fields and displaying the report will be of no great help.

    At this point of time just wanted to make sure if there is any other way or T-SQL cant be used as mentioned above.The filter that you are saying to place as per the specifications will be a tuff task from a user's point of view as they will be the one creating the reports from the Models deployed on the report server.

    Thanks

    Ajit 

    Tuesday, September 3, 2013 11:17 AM
  • Hii..

    Ajit 

    is your query running is sql server?

    and also check the error talking about parameter issue too...


    Dilip Patil..

    Tuesday, September 3, 2013 12:23 PM
  • Hi Dilip,

    Yes my query runs alright in SQL server. Its not about the query exactly because if you simply write a select statement also it throws the same error as posted earlier.

    SELECT * FROM vNavigation_OrderHeader  where OrderNumber =@Parameter

    It throws error while in sql server it runs alright.

    Thanks

    Ajit

    Tuesday, September 3, 2013 2:01 PM
  • Ajit,

    I think the problem you're having really stems from a misunderstanding of the purpose of a model.

    The model is meant to simplify the reporting process for report authors, so that they can author reports without knowing the T-SQL syntax. It puts a barrier between the author of the report and direct access to the database. The person who created the model has already decided what data the report author can view, and which columns to give the author access to.

    If you want to write a report using SQL, you've not started in the right place. To do this, you need to launch report builder, and create an ODBC Connection to a SQL database (as opposed to a connection to a model). As long as you can establish a connection, you can then write whatever SQL takes your fancy.

    • Marked as answer by Mike Yin Sunday, September 8, 2013 3:00 PM
    Tuesday, September 3, 2013 4:30 PM