Skip to Content
0
Former Member
Aug 09, 2016 at 10:32 AM

Display retrieved data from query in report

46 Views

Hello, I'm new to using Crystal Reports and I have a question.

I'm trying to design a Crystal Report with C#. I got stuck when trying to use the following dataset:

http://i.stack.imgur.com/r2Igl.png

Domain:

- Every bill has 1 customer

- A bill can have many advertisements

- Every advertisement has 1 edition

I want to show every advertisement for each Bill with the same bill Id. I retrieve my data from a SQL server database with entity Framework to an `IEnumerable<Bill>`.

I need to show the Bill, the customer information, and every advertisement with their respective editionDates. How can I display this data in my report?

This static method handles the conversion:

private static BillSet ToBillSet(IList<Bill> bills)

{

BillSet fs = new BillSet();

DataTable customerTab = fs.tblCustomers;

DataTable EditionTab = fs.tblEditions;

DataTable BillTab = fs.tblBills;

DataTable advertTab = fs.tblAdvertisements;

foreach (var Bill in bills)

{

DataRow billRow = BillTab.NewRow();

billRow["BillNumber"] = Bill.BillNumber;

billRow["BillDate"] = Bill.BillDate.ToShortDateString();

billRow["TaxesPerc"] = Bill.Taxes;

billRow["TotalIncTaxes"] = Bill.TotalIncTaxes;

billRow["BillId"] = Bill.BillId;

billRow["CustomerId"] = Bill.CustomerId;

DataRow CustomerRow = customerTab.NewRow();

CustomerRow["Name"] = Bill.Customer.Name;

CustomerRow["Address1"] = Bill.Customer.Address1;

CustomerRow["Address2"] = Bill.Customer.Address2;

CustomerRow["PostalCode"] = Bill.Customer.PostalCode;

CustomerRow["City"] = Bill.Customer.City;

CustomerRow["CustomerId"] = Bill.Customer.CustomerId;

foreach (var advert in Bill.Advertisements)

{

DataRow advertRow = advertTab.NewRow();

advertRow["Description"] = advert.Description;

advertRow["Width"] = advert.Width;

advertRow["Tarief"] = advert.Tariefbedrag;

advertRow["Height"] = advert.Height;

advertRow["Eenheid"] = advert.TariefEenheid.ToString();

advertRow["TaxesPercentage"] = advert.TaxesPercentage;

advertRow["TaxesTotal"] = advert.TaxesTotal;

advertRow["DiscountTotal"] = advert.DiscountTotal;

advertRow["EditionId"] = advert.EditionId;

advertRow["CustomerId"] = advert.CustomerId;

advertRow["BillId"] = advert.BillId;

advertRow["AdvertisementId"] = advert.AdvertisementId;

DataRow editionRow = EditionTab.NewRow();

editionRow["Date"] = Edition.Date;

editionRow["EditionId"] = advert.Edition.EditionId;

}

Thank you very much in advance!

Kind Regards