Crystal Reports In Winforms Windows Forms With Parameters

In this example i am explaining how to create Crystal Reports In Winforms Or Windows Forms Application With Parameters from user to filter report using C#.NET and VB.NET

Crystal Reports In Winforms Or Windows Forms Application With Parameters
For this i have created two tables in database named Employees and Projects and fetching data from both tables

I've grouped results by Department name using group expert in crystal reports and put a dropdown on the form to select project name to display related report.

Employee table schema

ID    int  
FirstName    varchar(50)
LastName    varchar(50)   
Department    varchar(50)   
ProjectID    numeric(18, 0)  
Expenses    money   


Projects table schema 

ProjectID    numeric(18, 0)   
ProjectName    varchar(50)  









Create a new project in VS and go to solution explorer and add new item > crystal report.
Select Blank report option from the wizard window
 
Now click on CrystalReports menu and select DataBase Expert 
Now in next window expand Create new connection section and OLEDB(ADO) and in next window select SQL Native Client

Enter you SQL Server name , username and password , select database name from the dropdown and click on ok
In next window expand to find your tables and add them in right pane
Click OK to finish

Now Right Click on Group Name Fields in Field Explorer and Select Group Expert.
In group expert box select the field on which you want data to be grouped.
 
  
Design your report by dragging the fields in section3 (Details) 
my design look like this  
In the form add a combobox and drag and drop CrystalReport Viewer from toobox. click on smart tag and choose the report we created earlier (CrystalReport1.rpt) 
Form look like this 
When we build and rum this report , it asks for Database login username and password , we need to provide database username and password in code behind.
 we need to write code in code behind to filter report based on user selected value or value provided by user 
C# code behind
//Code to populate dropdown
//Fill dropdown in form_Load event by calling 
//function written below
private void FillDropDown()
{
 SqlConnection con = new SqlConnection
       (ConfigurationManager.AppSettings["myConnection"]);
 SqlCommand cmd = new SqlCommand
("Select distinct ProjectID,ProjectName from Projects", con);
 con.Open();
 DataSet objDs = new DataSet();
 SqlDataAdapter dAdapter = new SqlDataAdapter();
 dAdapter.SelectCommand = cmd;
 dAdapter.Fill(objDs);
 cmbMonth.DataSource = objDs.Tables[0];
 cmbMonth.DisplayMember = "ProjectName";
 cmbMonth.ValueMember = "ProjectID";
 cmbMonth.SelectedIndex = 0;
}
private void cmbMonth_SelectedIndexChanged
              (object sender, EventArgs e)
{
      //Create object of report 
CrystalReport1 objReport = new CrystalReport1();

    //set database login information
objReport.SetDatabaseLogon
    ("amit", "password", @"AVDHESH\SQLEXPRESS", "TestDB");

//write formula to pass parameters to report 
crystalReportViewer1.SelectionFormula 
    ="{Projects.ProjectID} =" +cmbMonth.SelectedIndex;
crystalReportViewer1.ReportSource = objReport;
}
      

VB.NET code behind
Private Sub FillDropDown()
    Dim con As New SqlConnection
   (ConfigurationManager.AppSettings("myConnection"))

    Dim cmd As New SqlCommand
("Select distinct ProjectID,ProjectName from Projects", con)
    con.Open()
    Dim objDs As New DataSet()
    Dim dAdapter As New SqlDataAdapter()
    dAdapter.SelectCommand = cmd
    dAdapter.Fill(objDs)
    cmbMonth.DataSource = objDs.Tables(0)
    cmbMonth.DisplayMember = "ProjectName"
    cmbMonth.ValueMember = "ProjectID"
    cmbMonth.SelectedIndex = 0
End Sub

Private Sub cmbMonth_SelectedIndexChanged
(ByVal sender As Object, ByVal e As EventArgs)
    
    'Create object of report 
    Dim objReport As New CrystalReport1()
    
    'set database login information
    objReport.SetDatabaseLogon
("amit", "password", "AVDHESH\SQLEXPRESS", "TestDB")
    
    'write formula to pass parameters to report 
    crystalReportViewer1.SelectionFormula 
= "{Projects.ProjectID} =" & cmbMonth.SelectedIndex

    crystalReportViewer1.ReportSource = objReport
End Sub

Hope this helps

Download sample code



other articles on Crystal reports and winforms
Creating Crystal reports in ASP.NET C# VB.NET

Creating winforms AutoComplete TextBox using C# in Windows application

OpenFileDialog in winforms windows forms C# .NET VB.NET windows application

SubReports in Crystal Reports in ASP.NET
If you like this post than join us or share

21 comments:

Anonymous said...

Hi, I am currently doing a report and I use windows application (c#).

I want to display data in crystal report based on the user's input in my GUI. just like in my windows app, i have a textfield and when the user entered a name in the textfield like "Purple", all data of "Purple" will be displayed in my crystal report and when i exit my crystal report, the user can input again another name in the textfield in which this time is "Orange", then i want my crystal report to display only data of "Orange"...

What I want is, whatever name the user input in the textfield, the crystal report will only display data from my database for that name only..


Please help me how to do it.. Thank you. Your help will be reallly appreciated.


Anonymous said...

Please, if it is possible to get back again to me quickly, it will be much much appreciated.. Thank you.. I really need this one to be answered.


amiT jaiN said...

@Anonymous

Download the code you requested from link below

http://rapidshare.com/files/256720994/CrystalReportsWinFormsColor.zip.html

You need to create a table in database connect to database and table as i mentioned in article and change login info in code behind

Table columns can be whatever you want but one column should be named color


Anonymous said...

Thank you... really a big help!!!!.. :)


By the way, i have a question again.

This time, I have 2 tables in my database, namely, PersonalInfo(column names are:name, age,address,empID) and EmployeeData(colums are: empID,salary,empStatus). I want to use crystal report wherein it joins this 2 tables. Like when I entered the empID of a person in the textfield of my GUI(windows application), the data only of the specified empID like its name,address and salary will be the one displayed in my crystal report. And when I exit my crystal report, I could enter again another empID in which the crystal report only display the data of the specified empID in the textfield.


Hoping for your prompt reply again.. And again, thank you... You're really a big big help.. Thank you . :)


Anonymous said...

hi same as the question with the first comment.. i also have the same problem.. but my question is what if i need to show in report another field from another table.. its like using inner join.. how it will be made in crystal report.. thanks in advance


amiT jaiN said...

@Anonymous:

If you read this article carefully, i've done exactly what u r talking about , i'm fetching data from two tables 1. Project, 2. Employee
And im also using generating crystal report based on parameter or input(Project Name in the example) provided by the user


Anonymous said...

how we can print with crystal reports the records
shown in the gridview with checboxs but only the ones wich have chekboxs cheked
please help me i need it urgently.


ashish said...

Is it Necessary to prepare a separate .rpt for each report?
can we use single rpt file to shoe varius reports?
If yes how?
if no why?


Niranjan said...

i want crystal report programs using C# .net


Anonymous said...

In my windows application I need to use untyped datasets for report making.In my case report type won't be an issue.I need to display data in tabular form.So, I think ReportViewer will be better option than Crystal Report.

So,how to generate report using untyped dataset?


venkat said...

venkat said

Thanks for you r code , but i want to print no.of prints at a time , means when ever press print button i want 100 print at a time , please send me that type of code

Thanks& Regards,
Venkat Goud,


Sumedh Borkar said...

Nice Article for displaying crystal report...
But sir i have another problem which i mention below, please go through it.. give appropriate solution on it

I want to display record in Horizontal format.

Like below...

Id 1 2 3
Name Amar Suhas Nayan
Address Nagpur Pune Nashik
DepartmentName Science Art Commerce

Instead Of

Id Name Address DepartmentName
1 Amar Nagpur Science
2 Suhas Pune Art
3 Nayan Nashik Commerce

Please help me....

Best Regards
Sumedh Borkar
(sumedh.borkar18@gmail.com)


Amin said...

AmiT:

I get the error message "The file could not be found. Please check the download link." when I tried to go to the link you provided in a response to "Anonymous" regarding Crystal Reports.

http://rapidshare.com/files/256720994/CrystalReportsWinFormsColor.zip.html


Anonymous said...

hello , i am trying to select records from acess data base on a date field.
crystalReportViewer1.SelectionFormula = "{chqs.doe}= " + DateTime.Parse(comboBox1.Text) ;
i get the message " a number ,currency,amount,boolean,date,time,date-time or string is expected here ". how to solve this one ?


Anonymous said...

i am developing a project in c#, i have to generate auto numbers in every id field of each form but i have no idea how to do it. can you help me plz?


Anonymous said...

Hi
I am using Visual C# 2008 to programm a school project but I don't know how to generate report using C#. so I wonder if you can help me by showing or sending me a sample project mainly focused on generating report using C#.
thank you


amiT jaiN said...

@Above: why don't u download the sample attached from download link in this post


syed imtiaz said...

hi..
i am doing a project in windows application that is .net where i want to move up a image vertically inside picture box.. can u please help me out from this. i am not able to find this code
can u please help me finding code...


Anonymous said...

mjd



its not working in windows form that too
SQL connections.....


Engineer Khurram Ashfaq Qazi said...

I want to create a dynamic crystal report that gets some input from user and diplays report in C# uing windows application.Kindly send me some code example for guidance


Anonymous said...

HI,

i have a form with a 3 checkbox and each chckbox have a combobox, and 1 date(from and to)
I want to display the Crystal Report based on the selection of ComboBox, Checkbox and date field. Please help me.


Find More Articles