none
Having difficulty using two dataset for a single report (where one dataset is just total/count of records in a table? RRS feed

  • Question

  • Hello i have two dataset.

    Dataset 1. Bunch of records of rows that have Name, Groups and other details. 

    Dataset 2. Just a total number of data from above regardless of Name, Groups, etc. 

    After both DataSet to my SSRS solution and when i try to drag-and-drop DataSet2.Total to a Textbox in my report, it gives me the following error. 

    "Severity Code Description Project File Line Column
    Error [rsFieldReferenceAmbiguous] The Value expression for the text box ‘textbox1’ refers directly to the field ‘Expr1’ 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." 

    Why is this happening? It's separate SQL used to DataSet. Is there any way to make it "independent" and insert it into my report? 

    Thursday, July 9, 2020 5:31 PM

Answers

  • Hi SpicySoftTofuSoup,

    You can add the total column as a calculated field to dataset1.

    You can right-click DataSet1, and select add calculated field.

    Please refer to add a calculated field which might help.

    Or you can add name column and comments column to DataSet2, then use lookup function or lookupSet function in expression.

    Please refer to this similar thread which might help.

    Best Regards,

    Amelia


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, July 10, 2020 2:03 AM

All replies

  • You are getting error as Two Datasets are not linked together on any parameters. So when you using the DataSet2, it is failing to link with  Dataset1 and you are displaying the data from Both the dataset.

    You can embed the DateSet2 value on Text field on Top layer of report before you start using Dataset1 for Detail section.

    Hope this helps.

    Thursday, July 9, 2020 9:41 PM
  • Hi SpicySoftTofuSoup,

    You can add the total column as a calculated field to dataset1.

    You can right-click DataSet1, and select add calculated field.

    Please refer to add a calculated field which might help.

    Or you can add name column and comments column to DataSet2, then use lookup function or lookupSet function in expression.

    Please refer to this similar thread which might help.

    Best Regards,

    Amelia


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, July 10, 2020 2:03 AM
  • Hi SpicySoftTofuSoup,

    Did the answers above help you?

    Please feel free to let us know if you have any other question.

    If you find any post in the thread is helpful, you could kindly mark it as answer. This would benefit the community, and encourage the community member to keep working on your issues.

    Best Regards,

    Amelia


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, July 13, 2020 1:05 AM