How to load report control ywith 2 or more related tables

[Yacine Khammal]

I created an InvoiceReport by following the sample included in the SyncFusion ReportViewer 2013 folder (ReportViewerScr and CompanySalesDemo).
Created a simple RDLC file (in SQL Data Tools) based on a single Invoice table.  I then added the ReportViewer custom control to a Lightswitch screen as well as the necessary code to load the data. Everything worked fine.

Now I need to add customer data to my InvoiceReport and so I updated the dataset to include the joined Customer table and added the customer company name to the report. However when I run the screen i now get the error "an error occurred while processing the report item 'List1'. The given key was not present in the dictionary".  I suppose this is due to the fact that the reports's SQL query does not exactly match the Lightswitch query.

Report dataset query :

SELECT        Invoices.Code, Invoices.InvDate, Customers.Company

FROM            Invoices INNER JOIN

                         Customers ON Invoices.Invoice_Customer = Customers.Id

Lightswitch screen query :

object items = this.DataWorkspace.ApplicationData.Invoices

                    .Include("Customer")

                    .Where(s => s.Code.Equals(InvoiceCode, StringComparison.InvariantCulture))

                    .Execute().Cast<object>().ToList();

How can we display reports based on more than 1 table ? Is that beyond the control's capabilities ? Or do we need to create a new Lightswitch datasource and directly access the view used by the report (which would be quite a hassle) ?

[Yacine Khammal]

Closely related question, how would you create a report displaying an invoice with its line items (joining Invoice + Customer + InvoiceDetail tables) ?

[Nithya B]

Hi Yacine,

Thanks for using Syncfusion products.

If we use join queries in SQL it will return a single data table with merged columns, but entity relationship model returns hierarchy object relational model. Using hierarchy object relational model may raise “cross thread exception” inside ReportViewer when viewing RDLC report. So we have to manually retrieve the relational datable and convert them into business objects instead of directly using the relation data in ReportViewer.

We have prepared a sample based on the above mentioned scenario and it can be downloaded from the following location.

http://www.syncfusion.com/downloads/support/directtrac/general/SampleAppln-1221872365.zip

In the above sample, we have created two entity “Table1Item”, “Table2Item” with hierarchy level as shown in the below snap.

http://www.syncfusion.com/downloads/support/directtrac/general/Snapshot601802890.zip

We have created a class (Emp_Model) with properties of both entities to manually relate the values, because the entity “Table1Item” contains reference of “Table2Item”.

public class Emp_Model

{

public int Emp_Id { get; set; }

.. . . . .

}

The values of “Table1Item” retrieved in code behind(Table1Items.GetQuery().Execute()) and created new “Emp_Model” instances for the rows then assigned the Entity values to business object as shown in the below code snippet.

List<Emp_Model> _empModels = new List<Emp_Model>();

foreach (var item in items)

{

if (item.Emp_Id.Count() > 0)

{

foreach (var inner in item.Emp_Id)

{

Emp_Model _model = new Emp_Model();

_model.Emp_Name = item.Emp_Name;

………..

_empModels.Add(_model);

}

}

else

{

Emp_Model _model = new Emp_Model();

_model.Emp_Name = item.Emp_Name;

.....

_empModels.Add(_model);

}

}

And we have to assign the business object collection “empModels” to ReportViewer as shown below.

ReportDataSource dataSource = new ReportDataSource("EmpModel", _empModels);

Please let us know if you have any questions.

Regards,

Nithya

[Yacine Khammal]

Hello,

Thanks for your reply, that confirms what I found out on my own yesterday after deeper investigation : the need to create a class in the client with the same data shape as the report's dataset query, i.e. with a flattened object hierarchy .  Also tried using an anonymous type but that caused an error in the report viewer, so the data view class approach is correct.

Before coming up with this approach I had created a Lightswitch datasource connecting to the saved view in the DB. That worked but involved more work at deploy time + an additional unnecessary query to generate the report (the data is often already available in the screen).

Thanks again for your prompt reaction.  The SF report viewer is a great control, solves the reporting/printing problem in LS.

Cheers,

Yacine

[Nithya B]

Hi Yacine,

Thanks for the update and your feedback.

Also tried using an anonymous type but that caused an error in the report viewer

Our ReportViewer doesn’t support the anonymous data type. We have used reflection GetValue method for get the value from dynamic flattened object and exception will be thrown when we get the value of nested object while we enumerate the business objects properties. As of now we doesn’t handle nested object in Flatten collection. So we suggest you to use data view class approach without the nested object hierarchy. However we will consider this as Feature request and we will implement this feature in any of our upcoming releases. We request you to create a Support Incident (Ticket) to get more details about this. Please log on to our DirectTrac Support System using the below link to create support incidents. https://www.syncfusion.com/account/login?ReturnUrl=%2fsupport%2fdirecttrac%2fincidents

Please let us know if you have any questions.

Regards,

Nithya