Creating an rdlc report with multiple tables (one-to-many relationship)

Question

So, I'm new to rdlc (and reporting in general actually). I have a table that has a one-to-many relationship with another table, and I'm trying to represent them in an rdlc report as multiple tables for each item.

Note: The tables are originally created using Entity Framework code-first.

Here are the two tables (and the parent one):

Now, normally if I only have the [Quotation] and some [QuotationItem]s, I'd just add the info from the [Quotation] on the top of the report, and the info from each [QuotationItem] would be represented in a row inside a table (Tablix).

The problem is: Each [QuotationItem] also has many of [QuotationItemQuantity] (currently three), and they need to be represented too.

So, my report should look something like this:

But I'm stuck on how to display multiple tables (or a list and tables) for each item (QuotationItem). I tried nested tables and tables inside a list, but this doesn't seem to be allowed (I get a "Detail members can only contain static inner members" error).

I read about sub-reports and I think this might be the way to go, but I'm not sure how to use sub-reports in this case, or if this is actually the right approach.

Note: As mentioned above, each QuotationItem currently has 3 quantities, but that might be changed in the future, so would be great if the columns can be dynamic, however, this isn't a requirement at this point.

Any suggestions?

Answer 1

Well, I was hoping (and still do) to get a more elegant solution instead of this ugly one (which I had to use for now) but it does what it's supposed to anyway.

I'm posting it as it might help someone with a similar problem. Here's what I did:

Preparing the data:

• Created a dummy DataSet using the VS designer, and added one DataTable to it.
• Added all the required columns from both tables into this DataTable.
• Added the DataSet as the data source for the report.

• In order to pass the actual data, I would fill a DataTable with the same structure as the designed one and then pass it to the report using something like the following:

  private void Form_Load(object sender, EventArgs e)
  {
      rptViewerMain.LocalReport.ReportEmbeddedResource = "MyProjectName.QuotationReport.rdlc";
      rptViewerMain.LocalReport.EnableExternalImages = true;
  
      if (QuotationInfo !=null && QuotationItems != null)
      {
          SetupReport();
      }
  
      this.rptViewerMain.RefreshReport();
  }
  
  private void SetupReport()
  {
      var param1 = new ReportParameter("MyFirstParameter", SomeValue);
      var param2 = new ReportParameter("MySecondParameter", SomeOtherValue);
      // etc
  
      // Pass Parameters
      rptViewerMain.LocalReport.SetParameters(new[] { param1, param2, "etc" });
  
      // Prepare the DataTable and add the values to it
      DataTable dt = new MyDummyDataset.MyDesignedDataTable().Clone();
  
      foreach (var qItem in QuotationItems)
      {
          dt.Rows.Add(qItem.ItemNumber, qItem.ItemDescription, "and",
                      "so", "many", "more", "values");
      }
  
      // Pass the DataTable to the report as the data source replacing the dummy DataSet
      rptViewerMain.LocalReport.DataSources.Add(new ReportDataSource("MyDummyDataset", dt));
  }
  

Designing the report:

• Created only one table (Tablix) in the RDLC report and used the cell borders to make it look like 3 tables by removing the borders of the unwanted cells between the "tables":
• Added the fields from the dummy DataSet to the corresponding cells in the first table (the quantities info) and above the table (the quotation item info).
• For the second and third "tables", simply used expressions to do the calculations based on the values from the first table.

Note: Some fields/variables names were changed in both the question and the answer, but the idea stays the same.