Print Page | Close Window

Display data in a report from 2 different Tables

Printed From: Crystal Reports Book
Category: Crystal Reports for Visual Studio 2005 and Newer
Forum Name: Writing Code
Forum Discription: .NET programming API, report integration
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=17651
Printed Date: 19 May 2024 at 10:58pm


Topic: Display data in a report from 2 different Tables
Posted By: ChristianMuk
Subject: Display data in a report from 2 different Tables
Date Posted: 27 Sep 2012 at 8:43pm
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)

End Try

i am able to display all the items in my command but not the sales person which comes from the sales teble can any1 help me please???


Replies:
Posted By: ChristianMuk
Date Posted: 27 Sep 2012 at 9:05pm
another problem i would like to solve is:
i want my report to be display per sales person meaning i want first to select the sales person then create a report regarding the sales person that i have selected is that possible in CR, if it is please help me how to do that?

Posted By: kevlray
Date Posted: 28 Sep 2012 at 6:08am
I am not real good with the code stuff, but I have created some CR's for one of our programmers.  Your first post confuses me a bit, but you can have SQL code or distinct tables in the CR template.  You also can define prompts in CR in which you pass the prompt value to the CR.  Or you can define a select statement (filter) to pass the the CR.

I hope this helps.

Posted By: ChristianMuk
Date Posted: 30 Sep 2012 at 7:40pm
Tx Kevlray for your reply but as you can see I am very new with CR so by any chance is ther anyone whose more familiar with CR and the coding that can help me solve this issue because its really killing me now
 
Please i need help!!!

Posted By: hilfy
Date Posted: 01 Oct 2012 at 6:15am

Have you checked the SQL in a database tool outside of Crystal?  Based on your code, I assume you're using MS SQL Server, so you should try to run it in SQL Server Management Studio to make sure that the salesperson name is showing up in your SQL.  If it's in the data, it should be available in the report.  The rest of your code looks ok.

To filter on a single sales person adding a filter to the Where clause in your SQL before you load the data for the report is the most efficient way to handle it - that way the database does the filtering.  Adding a parameter to the report and setting the parameter value in your code is another option, but it's not as efficient because Crystal will have to process through all of the records in memory to filter the data, which is slower than having the database do the work.

-Dell


-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics

Posted By: ChristianMuk
Date Posted: 01 Oct 2012 at 8:15pm
i resolved this issue here's my solution
 
rptdoc.databse.tables("first DB table name").setdatasource(ds.tables(1))
rptdoc.database.tables("secdond DB table name").setdatasource(ds.tables(1))
and so on
but i have another question how can i create a report base on the items i select from a combobox let's say i select the sales person to be Chris then i want to create a report only about chris how can i achieve that?

Posted By: hilfy
Date Posted: 03 Oct 2012 at 3:33am
To filter on a selected person - add a parameter to the report that will identify the sales person.  Then use that parameter in the Select Expert to filter your data.
 
-Dell


-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics


Print Page | Close Window