Grouping different datasets

lambdab

<p>Hello everyone,</p><p> </p><p>First of all i am new to birt, so sorry if my question is noobie.</p><p>I have two different data sets "Requested_Products" and "Used_Materials":</p><p> </p><p>

---

</p><p>Requested_Products</p><p>

---

</p><p>OrderId | ProductId | Product_Description | Quantity</p><p> </p><p> </p><p>

---

</p><p>Used_Materials</p><p>

---

</p><p>OrderId | MaterialId | Material_Description | Quantity</p><p> </p><p> </p><p> </p><p>As you can see, datasets are related by column "OrderId". So what i am trying to do is to group those datasets by OrderId and display Requested_Products and Used_Materials for that OrderId. Lets say my data sets are:</p><p>

---

</p><p>Requested_Products</p><p>

---

</p><p>order1 | product1 | description1 | 1</p><p>order2 | product2 | description2 | 2</p><p>order2 | product3 | description3 | 5</p><p> </p><p>and</p><p> </p><p>

---

</p><p>Used_Materials</p><p>

---

</p><p>order1 | material1 | description4 | 3</p><p>order1 | material2 | description5 | 6</p><p>order2 | material3 | description6 | 8</p><p>order2 | material4 | description7 | 4</p><p>order2 | material5 | description8 | 2</p><p> </p><p>Wanted Result:</p><p>

---

</p><p>order1</p><p>

---

</p><p>Requested products:                                              Used materials:</p><p>Product Id | Product description | Quantity              Material Id | Material description | Quantity</p><p>

---

             

---

</p><p>product1   | description1            | 5                          material1  | description4             | 3</p><p>                                                                                material2  | description5             | 6</p><p>

---

</p><p>order2</p><p>

---

</p><p>Requested products:                                              Used materials:</p><p>Product Id | Product description | Quantity              Material Id | Material description | Quantity</p><p>

---

             

---

</p><p>product2   | description2            | 2                          material3  | description6             | 8</p><p>product3   | description3            | 5                          material4  | description7             | 4</p><p>                                                                              material5  | description8             | 2</p><p> </p><p> </p><p>I tried to use joint data set, but the problem is that rows count for Requested_Products and Used_Materials are different, so after joining data sets rows count multiplies and report displays incorrectly. What should i do to achieve desired result?</p><p> </p><p>Thank you for the help</p><p> </p>

BRM

<p>You might try something like this...</p><p>1. make a dataset that has all the unique order numbers</p><p>2. put a three column table on the report</p><p>3. put order number in the first column</p><p>4. in the second column put the requested products table filtered by order number</p><p>5. in the third column put the used materials table filtered by order number</p><p> </p><p>Nesting tables can be a handy way to solve stuff like this.</p>