Reporting Services: Business object data source with parent-child-grandchild

Question

How do I create a report with a parent-child-grandchild relationship using POCOs / custom business objects?

public class Invoice
{
  public List<Account> Accounts { get; set; }
}

public class Account
{
  public List<LineItem> LineItems { get; set; }
}

public void GenerateReport()
{
    var localReport = new LocalReport();
    localReport.LoadReportDefinition(GetEmbeddedResource("Invoice.rdlc"));
    localReport.DataSources.Add(new ReportDataSource("InvoiceDataset", new List<Invoice> { invoices }));
}

Preferably using Table and List controls over Subreports. Reporting Services v10 with Local Processing (.rdlc files).

Baskett

Answer 1

Invoice.rdlc

• Add a dataset named InvoiceDataset (from the Report Data tool window)
• Add a List control (since the report must be bound to a list of Invoices even if the list will only ever contain one element)
• Within the List control add Invoice-level fields like Customer Name
• Within the List control add a Subreport control pointing to Account.rdlc with a name of "Account" and a parameter of InvoiceId

Account.rdlc

• Add a dataset named AccountDataset (from the Report Data tool window)
• Add an InvoiceId parameter to match the parameter specified in Invoice.rdlc Subreport control
• Add a List control
• Within the List control add Account-level fields like Account Number
• Within the List control add a subreport pointing to LineItem.rdlc with two parameters: InvoiceId and AccountId

LineItem.rdlc

• Add a dataset named LineItemDataset (from the Report Data tool window)
• Add InvoiceId and AccountId parameters to match the parameters specified in Account.rdlc Subreport control
• Add a List control
• Within the List control add Line Item-level fields like Description, Quantity, Price

To generate this report as a pdf:

public byte[] GenerateInvoicePdf(Invoice invoice)
{
    var localReport = new LocalReport();

    localReport.LoadReportDefinition(GetEmbeddedResource("Invoice.rdlc"));
    localReport.LoadSubreportDefinition("Account", GetEmbeddedResource("Account.rdlc"));
    localReport.LoadSubreportDefinition("LineItem", GetEmbeddedResource("LineItem.rdlc"));
    var datasource = new List<Invoice> {invoice};
    localReport.DataSources.Add(new ReportDataSource("InvoiceDataset", datasource));
    localReport.SubreportProcessing +=
        (o, args) =>
            {
                if (args.ReportPath == "Account")
                {
                    var invoiceId = long.Parse(args.Parameters["InvoiceId"].Values[0]);
                    var invoice = datasource.First(x => x.InvoiceId == invoiceId);
                    args.DataSources.Add(new ReportDataSource("AccountDataset", invoice.Accounts));
                }
                else if (args.ReportPath == "LineItem")
                {
                    var invoiceId = long.Parse(args.Parameters["InvoiceId"].Values[0]);
                    var accountId = long.Parse(args.Parameters["AccountId"].Values[0]);
                    var invoice = datasource.First(x => x.InvoiceId == invoiceId);
                    var account = invoice.Accounts.First(x => x.AccountId == accountId);
                    args.DataSources.Add(new ReportDataSource("LineItemDataset", account.LineItems));
                }
            };
    return localReport.Render("pdf");
}

Baskett