C# .NET - Is it possible to fill data for more than one table in a report viewer

Asked By Daniel on 28-Sep-11 06:57 AM

Hi !I have a problem with my crystal report viewer when I am using one table into a load page of crystal report viewer

its display the data of that table but when I am loading the crystal report viewer with more than one table its display nothing here is my code that I am using in a load page of crystal report viewer:

if (cSqlServerData.LoadNumtel(ref dtblMylogin, ref dtblTransactionB, ref dtblTransactionM, ref sNumtelSearch) || sNumtelSearch != "")

{

ReportDocument oRpt = new ReportDocument();

//load report

oRpt.Load(Server.MapPath("Reports/ViewTransactionReportClient1.rpt"));

//oRpt.Load("D:/Asp.Net/DanielProject/Website/Website/ClientTransactReport.rpt");

//Main Report fill data

oRpt.SetDataSource(dtblMylogin);

oRpt.SetDataSource(dtblTransactionB);

oRpt.SetDataSource(dtblTransactionM);

....

I just want to know if it is possible to use more than one table.If yes am I loading in a correct way?thanks!

Anoop S replied to Daniel on 28-Sep-11 07:02 AM

With SQL Server; I simply just call my stored procedures in Crystal Reports. With the stored procedures you can write queries that grab data from multiple tables.
for eg:

CREATE PROCEDURE My_Stored_Procedure AS

Select a.UserID, a.UserName, b.User_FontColour
From [Users] a
Inner Join User_preference b on a.UserID = b.UserID

GO

That is a simple stored procedure that will select the userID and UserName from the Users table and will select the User_FontColour from the User_preference table

You would do this in your SQL Server Enterprise manager. Open your database, right click on the stored procedures folder click "Create new stored procedure" and write your query.

When you are in crystal reports; you can then make reference to that stored procedure and it will automatically add the fields in your select statement as fields you can place on the report.

Rohan Dave replied to Daniel on 28-Sep-11 07:05 AM

Yes definitely you can display data from more than one table in your crystal report..

What you need to do is you need to use Joins in your SQL Select query to pull data from different table instead of single separate select query to pull the data..

so try below in your query..

Select tblEmp.EmpID, tblED.Address1, tblED.Address2, tblED.City, tblED.State, tblED.Country, tblED.PostCode
From Employee tblEmp
INNER JOIN EmployeeDetail tblED On tblEmp.EmpID = tblED.EmpID

something like that..

Web Star replied to Daniel on 28-Sep-11 07:05 AM

Better is you can use stored procedure to get result set from multiple table and that sp use to get data from database in report viewer

Suchit shah replied to Daniel on 28-Sep-11 07:07 AM

You can add more than one datasets to your reports. After u have done that in the report parameters you can specify for each parameter to take the value for a different dataset.. for eg..

Suppose you create dataset A,B,C and your report accepts X,Y,Z parameters.

Then in the report parameters you when u select Parameter X you have an option to select which data set you u want to get that value from..

Daniel replied to Anoop S on 28-Sep-11 07:18 AM

but I hav create a stored procedure but When trying to select parameters to crystal report It refuzing

please see the following stored procedure that I have created and tell me how can I call to crystal report:

ALTER PROCEDURE [dbo].[ViewClientTransactions]

(

@TranstionType VARCHAR(50),

@numtel bigint

)

BEGIN

IF @TranstionType='TRANSFER ECASH'

BEGIN

SELECT dbo.MyLogin.numtel, dbo.MyLogin.nom, dbo.MyLogin.prenom, dbo.TransactionM.debitmonta, dbo.TransactionM.charge, dbo.TransactionM.DateTrans,

dbo.ozekimessageout.receiver

FROM dbo.MyLogin INNER JOIN

dbo.ozekimessageout ON dbo.MyLogin.id = dbo.ozekimessageout.id INNER JOIN

dbo.TransactionM ON dbo.MyLogin.numtel = dbo.TransactionM.numtel

WHERE MyLogin.numtel=@numtel

END

ELSE IF @TranstionType='RECHARGE ECASH'

BEGIN

SELECT dbo.MyLogin.numtel,dbo.MyLogin.nom, dbo.MyLogin.prenom, dbo.TransactionB.numcompte, dbo.TransactionB.debitmont, dbo.TransactionB.creditmont,

dbo.TransactionB.Datetranb, dbo.TransactionM.debitmonta, dbo.TransactionM.creditmonta, dbo.TransactionM.DateTrans, dbo.TransactionM.charge

FROM dbo.Client INNER JOIN

dbo.CompteBanque ON dbo.Client.idcarte = dbo.CompteBanque.idcarte INNER JOIN

dbo.McellAcc ON dbo.Client.idcarte = dbo.McellAcc.idcarte INNER JOIN

dbo.MyLogin ON dbo.McellAcc.numtel = dbo.MyLogin.numtel INNER JOIN

dbo.TransactionB ON dbo.CompteBanque.numcompte = dbo.TransactionB.numcompte INNER JOIN

dbo.TransactionM ON dbo.McellAcc.numtel = dbo.TransactionM.numtel

WHERE dbo.MyLogin.numtel=(SELECT numtel FROM dbo.MyLogin WHERE numtel=@numtel)

END

Anoop S replied to Daniel on 28-Sep-11 07:33 AM

Choose File -> Options menu. In the Options dialog box
Click the Database tab and ensure that Stored Procedures is
selected. Selecting "Stored Procedures" automatically
displays any available stored procedures when you log on to an SQL database.
Click OK to exit the Options dialog box.
On the Start Page, click Blank Report.
Locate and select the SQL Server data source that contains the stored procedure you want to use.
Click Next to go to the Connection Information dialog box.
Enter the required information to log in.
Click Finish.
Highlight an SQL stored procedure in the Stored Procedures
folder, and click the > arrow to add it to the Selected
Tables list.
The Enter Parameter Values dialog box appears.
Highlight a parameter in the Parameter Fields list.
Assign a value by typing into the Discrete Value box and then click OK.
You are returned to the Database Expert.
Click OK and create your report using the fields in the stored procedure.

Daniel replied to Anoop S on 28-Sep-11 12:26 PM

Hey Anoop!I do not understand where to begin,you said choose file ->options menu...it is a file for what?what program to use becoz I am not seeing that in vs 2008.