7 Replies - 5464 Views - Last Post: 02 October 2012 - 06:09 PM Rate Topic: -----

#1 christian Mukeba  Icon User is offline

  • D.I.C Head

Reputation: -2
  • View blog
  • Posts: 96
  • Joined: 06-September 12

Display data in a report from 2 different Tables

Posted 30 September 2012 - 10:43 PM

Hi every1


i am new in crystal report and i am working on a project where i have to create a crystal report for the Tender per sales person but i am facing a problem where i can't display data from the second table in the report, i can only display data from one tables.

here is the code i am using:
Dim con As New SqlClient.SqlConnection(My.Settings.NewEvapcoDBConnectionString.ToString


   




Dim cmd As New SqlClient.SqlCommand("Select Tender.ETNumber, Tender.EnquiryDate, Tender.ProjectName, Tender.UnitType, Tender.ReqDate, Tender.NumberUnits, Sales.SalesPerson From Likelihood INNER JOIN Tender ON Likelihood.LikelihoodID= Tender.LikelihoodID INNER JOIN Sales ON Tender.SalesID= Sales.SalesID WHERE Likelihood.LikelihoodLevel LIKE '%A'")
 

 

Dim da As New SqlClient.SqlDataAdapter(cmd)
 
      


Dim ds As New TenderDataSet
 



Dim strReportName As String
 



Dim rptdoc As New CrystalDecisions.CrystalReports.Engine.ReportDocument
 



Dim strReportpath As String = ("D:\New EvapcoDB\New EvapcoDB\TenderReport.rpt")




Try
 
cmd.Connection = con
 
con.Open()
 
ds.Clear()

 da.Fill(ds, 


"TenderReport")
 
strReportName = 


"TenderReport"
 



If Not IO.File.Exists(strReportpath) Then
 
MsgBox(


"An Error occured, impossible to find the report", vbCritical, "Error")
 
con.Close()
 



Exit Sub
 



End If
 
rptdoc.Load(strReportpath)
 
rptdoc.SetDataSource(ds.Tables(1))
 
rptdoc.Refresh()
 
TenderCrystalReportViewer.ReportSource = rptdoc
 
TenderCrystalReportViewer.Refresh()
 



Catch ex As Exception
 
System.Windows.Forms.


MessageBox.Show(ex.Message)



Is This A Good Question/Topic? 0
  • +

Replies To: Display data in a report from 2 different Tables

#2 christian Mukeba  Icon User is offline

  • D.I.C Head

Reputation: -2
  • View blog
  • Posts: 96
  • Joined: 06-September 12

Re: Display data in a report from 2 different Tables

Posted 30 September 2012 - 10:50 PM

PS: i am able to display data only from one table but the data from the other tables are not showing
Was This Post Helpful? 0
  • +
  • -

#3 artemix22  Icon User is offline

  • D.I.C Head

Reputation: 13
  • View blog
  • Posts: 142
  • Joined: 21-January 12

Re: Display data in a report from 2 different Tables

Posted 01 October 2012 - 01:44 AM

maybe you need pass those 2 tables first to DGV or Listview, and then put another DGV or Listview for final table (choose data from DGV1/Listview1 and data from DGV2/Listview2 to this final DGV) and then final table become your report table.

just idea :rolleyes:

This post has been edited by artemix22: 01 October 2012 - 01:44 AM

Was This Post Helpful? 0
  • +
  • -

#4 November-06  Icon User is offline

  • D.I.C Regular

Reputation: 46
  • View blog
  • Posts: 395
  • Joined: 04-January 11

Re: Display data in a report from 2 different Tables

Posted 01 October 2012 - 02:30 AM

In Crystal Report, you can create a DataSet with multiple DataTables. You can populate these tables dynamically in the code using the following codes:

rptdoc.Load(strReportpath)
rptdoc.Database.Tables("TableName1").SetDataSource(ds.Tables(1))
rptdoc.Database.Tables("TableName2").SetDataSource(ds.Tables(2))
rptdoc.Database.Tables("TableName3").SetDataSource(ds.Tables(3))


Was This Post Helpful? 0
  • +
  • -

#5 christian Mukeba  Icon User is offline

  • D.I.C Head

Reputation: -2
  • View blog
  • Posts: 96
  • Joined: 06-September 12

Re: Display data in a report from 2 different Tables

Posted 01 October 2012 - 03:00 AM

Thanks alot November you your solution worked for me but i had to put
rptdoc.Load(strReportpath)  

rptdoc.Database.Tables("TableName1").SetDataSource(ds.Tables(1))  

rptdoc.Database.Tables("TableName2").SetDataSource(ds.Tables(1))  

rptdoc.Database.Tables("TableName3").SetDataSource(ds.Tables(1)) 


instead of
rptdoc.Load(strReportpath)  

rptdoc.Database.Tables("TableName1").SetDataSource(ds.Tables(1))  

rptdoc.Database.Tables("TableName2").SetDataSource(ds.Tables(2))  

rptdoc.Database.Tables("TableName3").SetDataSource(ds.Tables(3)) 



thanks again and i have another problem maybe you can help me again
Problem: the name of my tables are Tender, Sales and Likelihood
the tender is kinda the main table and i have a GUI for the tender where i capture all my infos and save it into my DB then the user can create a report from there now i want 2 kind of report
1) i want the report base on the sales person (attribute from the sales table) and the likelihood level ( attribute from the likelihood table)which means before the user create the report he must be ask to select the likelihood level and the sales person then after that the report can be generated for that specific sales person and the likelihood level the user has specify
2)the report must only be base on the sales person only which means the user do not have to specify the likelihood level

anyone can help me with that!!!!
Was This Post Helpful? 0
  • +
  • -

#6 November-06  Icon User is offline

  • D.I.C Regular

Reputation: 46
  • View blog
  • Posts: 395
  • Joined: 04-January 11

Re: Display data in a report from 2 different Tables

Posted 01 October 2012 - 06:26 PM

What have you started so far?

Did you place two buttons for the different reports? Did you place a single button but with radio buttons as selections for the type of report to you want to print?

Now, depending on what you did, in the button click, validate the report the user wants to print, depending on the result, you may show a popup asking him of requirements (the sales or the likelihood or both). Pass these as parameters to your SQL query (it is best if you are using a stored procedure) then the result of the query may be printed in your crystal report.
Was This Post Helpful? 0
  • +
  • -

#7 christian Mukeba  Icon User is offline

  • D.I.C Head

Reputation: -2
  • View blog
  • Posts: 96
  • Joined: 06-September 12

Re: Display data in a report from 2 different Tables

Posted 01 October 2012 - 10:58 PM

Tx for your reply November!!!!!

so far the only code i have are the one in my first post plus the ones you helped me with.
and my scenario is as follow, on my Tender GUI (main form which capture infos and save it inot the database ) i place a button create report then when clicked another form with 2 buttons (print report base on likelihood and sales person and print report base on sales person only) depending on what the user click another form with 2 combobox(likelihood combobox and sales person combobox these combobox are fill in with data coming from the database) after this second form popup the user have to select now the sales person and the likelihood if he had previously choose to print a report with the likelihood and sales person and on the other hand if he had previously choose to print base on the sales person only, then the user can only choose the sales person and print the report.
so rem i said i am new with CR so pls can you show me how i can write a perfect SQL query to accomplish this
pls i hope u understand my problem now.
Was This Post Helpful? 0
  • +
  • -

#8 November-06  Icon User is offline

  • D.I.C Regular

Reputation: 46
  • View blog
  • Posts: 395
  • Joined: 04-January 11

Re: Display data in a report from 2 different Tables

Posted 02 October 2012 - 06:09 PM

By Perfect SQL, I am not sure what you mean. Using SQL with Crystal report and without it do not really make a lot of difference with retrieving data from database. The only difference is what you do after you retrieve the tables (eg. Do you display it in a gridview or do you fill a crystal report with the following data?)

You said you are a beginner in Crystal report but you didn't say you are a beginner in SQL. So I assume you at least know the basics of querying and retrieving data from database. So how did you fill your dataset with tables from your SQL?

Before you do the codes you did in your first post, there should be tables inside your ds. So, can you post the codes you did to insert tables in your ds?
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1