Multiple DataSet error SSSRS

Raj 21 Reputation points
2021-02-08T13:46:20.527+00:00

Hi,

Hope everyone is doing good.

I am working on SSRS ( Report Builder), and I am trying to make a report. I am trying to pass property name via parameter, from different dataset.

System.Web.Services.Protocols.SoapException: The Value expression for the text box 'Textbox5' refers directly to the field 'Property_Name' without specifying a dataset aggregate. When the report contains multiple datasets, field references outside of a data region must be contained within aggregate functions which specify a dataset scope.
at Microsoft.ReportingServices.Library.ReportingService2010Impl.CreateReport(String Report, String Parent, Boolean Overwrite, Byte[] Definition, Property[] Properties, ItemType ItemType, CatalogItem& ItemInfo, Warning[]& Warnings)
at Microsoft.ReportingServices.Library.ReportingService2010Impl.CreateCatalogItem(String ItemType, String Name, String Parent, Boolean Overwrite, Byte[] Definition, Property[] Properties, CatalogItem& ItemInfo, Warning[]& Warnings)
at Microsoft.ReportingServices.WebServer.ReportingService2010.CreateCatalogItem(String ItemType, String Name, String Parent, Boolean Overwrite, Byte[] Definition, Property[] Properties, CatalogItem& ItemInfo, Warning[]& Warnings)

Please check the attached image.

Thanks and regards.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,860 questions
SQL Server Reporting Services
SQL Server Reporting Services
A SQL Server technology that supports the creation, management, and delivery of both traditional, paper-oriented reports and interactive, web-based reports.
2,818 questions
0 comments No comments
{count} votes

2 answers

Sort by: Most helpful
  1. Joyzhao-MSFT 15,566 Reputation points
    2021-02-09T08:41:02.517+00:00

    Hi @Raj

    System.Web.Services.Protocols.SoapException: The Value expression for the text box 'Textbox5' refers directly to the field 'Property_Name' without specifying a dataset aggregate. When the report contains multiple datasets, field references outside of a data region must be contained within aggregate functions which specify a dataset scope.
    at Microsoft.ReportingServices.Library.ReportingService2010Impl.CreateReport(String Report, String Parent, Boolean Overwrite, Byte[] Definition, Property[] Properties, ItemType ItemType, CatalogItem& ItemInfo, Warning[]& Warnings)
    at Microsoft.ReportingServices.Library.ReportingService2010Impl.CreateCatalogItem(String ItemType, String Name, String Parent, Boolean Overwrite, Byte[] Definition, Property[] Properties, CatalogItem& ItemInfo, Warning[]& Warnings)
    at Microsoft.ReportingServices.WebServer.ReportingService2010.CreateCatalogItem(String ItemType, String Name, String Parent, Boolean Overwrite, Byte[] Definition, Property[] Properties, CatalogItem& ItemInfo, Warning[]& Warnings)

    Before that, let’s take a look at the following example:

    1. Add a textbox to the report as the title, then drag the field of Title to the textbox, the expression in the textbox is =First(Fields!Title.Value) then it will display the same error as you gave.
    2. Then I add a scope for this expression to this =First(Fields!Title.Value, "DataSetName"), then everything is fine.

    So I would recommend you have a check to see whether you have some other textboxes whose expression lacks of scope name. If all the expression is correct, you could delete this textbox to make sure whether the root cause comes from it. If it does not work, please give a feedback and more detailed information, which is a big help for us to supply a further assistance.
    Regards,
    Joy


    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    1 person found this answer helpful.
    0 comments No comments

  2. Michael Taylor 49,076 Reputation points
    2021-02-08T14:46:51.91+00:00

    You are attempting to pass the field of a dataset to something else. However you are doing so outside the container that uses the dataset. Since that field changes for each row in your dataset it cannot be used outside the container that is enumerating the results. However if you use an aggregate function like Max or Sum then that aggregates across the entire dataset to produce a single value. In this case that aggregated value can be used outside the container because it isn't specific to a single row.

    To fix your issue you need to ensure that you're either passing the property name while inside the container that is enumerating the dataset results OR you need to use an aggregate function to get the min/max/count/sum/whatever of the entire dataset and pass that as the value instead.

    I don't see any image attached but a description of what property you're passing and whether it is inside the container that is enumerating the dataset would be useful.

    0 comments No comments