none
How do I access the set of rows in a SSRS DataSet from embedded code in Report Designer? RRS feed

  • Question



  • I have a SQL Server Report with 2 DataSets. The second DataSet returns 0 or more rows of data using the same parameter as the first DataSet when calling the underlying stored procedure in the DataSource. I want to be able to construct a string containing carriage return/line feeds from the second DataSet and set that string into a TextBox on my report.

    In this screenshot, there could be multiple "adjustment" lines in the DataSet similar to the "Fuel Surcharge". I want to be able to access all of these descriptions/values and output them to the TextBox. If there are no values returned by the second DataSet, I want to output "Adjustments: $0.00".

    Can I access the row collection of the second DataSet from within embedded code? Will the TextBox grow vertically to accommodate the returned string?

    For example, the data returned from the second DataSet may contain:

    Description Amount
    Fuel Surcharge 34.56
    Processing Fee 22.98

    I want to output these data items in a single TextBox, so my embedded code would create and return a string that contains the description and amount for each record in the DataSet separated by a CR/LF.

    enter image description here


    • Edited by JimNickFL Wednesday, October 9, 2019 12:39 PM remove bad data
    Wednesday, October 9, 2019 12:38 PM

Answers

  • The dataset was stored in the .rdl file as SQL query, we could not access detail data or rows from only the .rdl file.

    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.

    • Marked as answer by JimNickFL Wednesday, October 16, 2019 8:33 AM
    Monday, October 14, 2019 6:41 AM

All replies

  • Hi, 

    How dataset1 is related to dataset2? Could share excel or ssms query example to show us the data structure?

    How would you like to use the data in dataset2 ? If you just want to get the sum, you could in the table just directly use sum function and specify the scope as "dataset2".

    ----In this screenshot, there could be multiple "adjustment" lines in the DataSet similar to the "Fuel Surcharge". I want to be able to access all of these descriptions/values and output them to the TextBox. 

    It will be easy for us to understand if you could make some excel or text example to show the data structure. Just wordy description is hard to get exact what you want and what the situation should be .

    Regards,

    Lukas


    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.

    Thursday, October 10, 2019 6:27 AM
  • In essence, it is immaterial what the data structures look like or even how the data from the 2 DataSets relate. Assume I have no way to see the underlying data structure but I know that there are two fields in each row, Description and Amount. What IS important is to determine if there is any way to access the collection of rows in a dataset from code embedded in a report. You can imagine the data structure any way you like.

    I solved the problem one way but now I would like to see if there are any better solutions. Unfortunately, I use words to describe things, not pictures. I read novels, not comic books. 

    Thursday, October 10, 2019 10:31 AM
  • The dataset was stored in the .rdl file as SQL query, we could not access detail data or rows from only the .rdl file.

    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.

    • Marked as answer by JimNickFL Wednesday, October 16, 2019 8:33 AM
    Monday, October 14, 2019 6:41 AM