Multiple Tables in same Report Viewer.

Question

5.00/5 (1 vote)

See more:

VS2012

Hi,
I am creating a billing application. In there i need to print the bill to customers. I created a custom form for that. Then i found this MSDN Walkthrough[^] . And it works. But it only creates details about one table and displays full table details.

I need to customize this. I need to add details from 3 different tables and filter out the details of only the last entered OrderID.

public PrintPreview()
{
    InitializeComponent();
    _reportViewer.Load += ReportViewer_Load;
}

private bool _isReportViewerLoaded;

private void ReportViewer_Load(object sender, EventArgs e)
{
    if (!_isReportViewerLoaded)
    {
        Microsoft.Reporting.WinForms.ReportDataSource reportDataSource1 = new Microsoft.Reporting.WinForms.ReportDataSource();
        BillingDatabaseDataSet1 dataset = new BillingDatabaseDataSet1();

        dataset.BeginInit();

        reportDataSource1.Name = "DataSet1"; //Name of the report dataset in our .RDLC file
        reportDataSource1.Value = dataset.Order_Products;
        this._reportViewer.LocalReport.DataSources.Add(reportDataSource1);
        this._reportViewer.LocalReport.ReportEmbeddedResource = "Billing.Report1.rdlc";

        dataset.EndInit();

        //fill data into Order_ProductsDataSet
        BillingDatabaseDataSet1TableAdapters.Order_ProductsTableAdapter salesOrderDetailTableAdapter = new BillingDatabaseDataSet1TableAdapters.Order_ProductsTableAdapter();
        salesOrderDetailTableAdapter.ClearBeforeFill = true;
        salesOrderDetailTableAdapter.Fill(dataset.Order_Products);

        _reportViewer.RefreshReport();

        _isReportViewerLoaded = true;
    }
}

Posted 5-Nov-13 2:01am

Kamal Mohan Pillai

Updated 5-Nov-13 6:17am

Richard C Bishop

v2

Add a Solution

Comments

keerth516 5-Nov-13 12:03pm

you mean to say,want to bind three separate tables data which has relation between them in one crystal report right ? or else you want to show three different tables in on ereport?

Kamal Mohan Pillai 6-Nov-13 0:00am

Hi, thanks for the reply. I haven't added a relation between the tables yet. If i relate these tables will it be easier? If that is the best case then i would do that.

Kamal Mohan Pillai 6-Nov-13 0:07am

I have three tables. Order_Details, Order_Products, Products_Master.

Order_Details and Order_Products have OrderID in common and Order_Products and Products_Master have ProductCode in common.

I have to get the last entered OrderID and get its details from Order_Details and Order_Products. Also i need to get the ProductName from Products_Master where ProductCode is the code i get from Order_Products.

I assume you got it.

keerth516 6-Nov-13 0:12am

kamlihere,check the below solution.if you have any doubts please ask me

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

keerth516 · Answer 1 · 2013-11-05T18:10:00

Solution 1

Why dont you create a select query statement for binding three different tables and access this query in .rpt file.

Example:select query

SQL

Select
[MobNo]
      ,[Name]     
      ,[WhomToMeet]
      ,[CheckInTime]
      ,[CheckOutTime]      
      ,[CheckInItems]
      ,[CheckOutItems]    
      ,[PendingItems]      
      ,[PurPoseOfVisit]
      ,[ShiftName]
      ,[DeptName]
      ,[CompName] from Visitor
      JOIN EmpMaster on EmpMaster.EmpMNo = Visitor.VisitorMobNo     
       JOIN ShiftMaster on ShiftMaster.ShiftCode=Visitor.VCompCode
       JOIN DeptMaster on DeptMaster.DeptCode=Visitor.VisitorType 
        where IsBlackList=0
       AND EmpCode =1

Now you can write your own select query as per logic.

Posted 5-Nov-13 18:10pm

keerth516

Comments

Kamal Mohan Pillai 6-Nov-13 0:27am

Do i need to create a table for this query? Or how to access the query from the .rdlc file. the .rdlc file is asking for tables to connect. I am using C#, WPF.

keerth516 6-Nov-13 0:49am

I have worked on.rpt not .rdlc,if you are generating report using crystal report i can help you.How ever the above query logic is same.

keerth516 6-Nov-13 0:57am

http://kishor-naik-dotnet.blogspot.in/2011/06/wpf-rdlc-report-in-wpf.html

this link is good example of doing it.you can change your select query

Kamal Mohan Pillai 6-Nov-13 1:11am

Thanks keerth.

Kamal Mohan Pillai 6-Nov-13 4:51am

Hi, i checked the link and rewrote the code. But the problem is in .rdlc design file only one dataset can be used and hence only data from that table is displayed. The sql query is providing the desired value .

keerth516 6-Nov-13 6:19am

use the select query and set sqlDataadapter Fill method to add the dataset as below

internal IEnumerable GetDataFromDataBase(string tblName, string szQuery)
{
try
{
dt = new DataTable(tblName);
using(con=new SqlConnection(connection))
{
con.Open();
using (SqlCommand cmd = new SqlCommand(szQuery, con))
{
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(dt);
}
}

}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}

finally
{
if (con != null)
con.Close();
}
return dt.DefaultView;
}
It will give you all the required data as datatable or you can use Dataset as return type and bind it.

Satyam Soni 6-Feb-14 2:52am

how to set no of item in 1 page