SSRS Design to collapse sales lines into 1 line

• kwoznica

  SSCertifiable

  Points: 5275

  More actions

  September 16, 2015 at 3:19 pm

  #304737

  In our Sales order entry line details, Dynamics AX, we may create multiple lines on a sales order for the same item. If a sales order has lines in this manner what would be the best way to have the Sales order confirmation report print in such a way that it collapses those lines into 1 line on the report?

  Also the grouping condition could be the Customer Line or the Item. Notice the Customer Line:1A

  For example I have a Sales Order id of SO-123456 and on that sales order my lines appear as such.

  Line 1 - Customer Line:1A -Item: Gold Watch - Unit Price: 5000 - Quantity: 1

  Line 2 - Customer Line:1A -Item: Gold Watch - Unit Price: 5000 - Quantity: 1

  Line 3 - Customer Line:1A -Item: Gold Watch - Unit Price: 5000 - Quantity: 1

  Line 4 - Customer Line:2A -Item: Silver Watch- Unit Price: 2000 - Quantity:1

  When the sales order confirmation prints the requirement would be to show 1 line which will be sent to the customer.

  Such as below:

  1A Gold Watch Unit Price 5000 Quantity 3

  2A Silver Watch Unit Price 2000 Quantity 1

  What would be the best way to design the details portion of the report?

  I appreciate your response.

• pietlinden

  SSC Guru

  Points: 63428

  More actions

  September 16, 2015 at 5:12 pm

  #1827260

  One option is to use a tablix, and add the Item to the Row Groups section, and then put the quantity INSIDE that group.

  Then you end up with

  Gold Watch 3

  Silver Watch 1

  The price would go inside as well...