Search code examples
sqlreporting-services

SSRS Lookupset showing every item associated with the account in one cell

I am currently working on making an itemized list with the associated contract prices but because of how the tables are setup I cannot have it all in the same dataset, so I've been using the lookupset expression, but I can't find a way to separate the items in the column into individual rows. All of my prices are correct, but every item associated with that contract account shows in each price cell.

This is the lookupset that I've been using and I've tried multiple different joins for it but it all leads to the same issue

Item Price
Item1 Item2 price
Item3 Item4
Item5 Item6
Item1 Item2 price
Item3 Item4
Item5 Item6

I just need a solution to break up the items into their own cells with the price lining up and if I need to add code to it then that's possible but I can't find the way to go.

Solution

  • Unfortunately, you will not be able to break up JOINed data into separate rows.

    I think your easiest solution is a subreport.

    Make a new report with the other dataset. Create a table with the Items (and price?). You could copy your current report and delete the items you don't need.

    Then add the subreport to the table in your original report with the CustomerCode as the Parameter.

    enter image description here

    Easier to make and runs faster than the LookUpset.