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

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

01//Code to populate dropdown

02//Fill dropdown in form_Load event by calling

03//function written below

04private void FillDropDown()

05{

06 SqlConnection con = new SqlConnection

07       (ConfigurationManager.AppSettings["myConnection"]);

08 SqlCommand cmd = new SqlCommand

09("Select distinct ProjectID,ProjectName from Projects", con);

10 con.Open();

11 DataSet objDs = new DataSet();

12 SqlDataAdapter dAdapter = new SqlDataAdapter();

13 dAdapter.SelectCommand = cmd;

14 dAdapter.Fill(objDs);

15 cmbMonth.DataSource = objDs.Tables[0];

16 cmbMonth.DisplayMember = "ProjectName";

17 cmbMonth.ValueMember = "ProjectID";

18 cmbMonth.SelectedIndex = 0;

19}

20private void cmbMonth_SelectedIndexChanged

21              (object sender, EventArgs e)

22{

23      //Create object of report

24CrystalReport1 objReport = new CrystalReport1();

25 

26    //set database login information

27objReport.SetDatabaseLogon

28    ("amit", "password", @"AVDHESH\SQLEXPRESS", "TestDB");

29 

30//write formula to pass parameters to report

31crystalReportViewer1.SelectionFormula

32    ="{Projects.ProjectID} =" +cmbMonth.SelectedIndex;

33crystalReportViewer1.ReportSource = objReport;

34}

35 

VB.NET code behind

01Private Sub FillDropDown()

02    Dim con As New SqlConnection

03   (ConfigurationManager.AppSettings("myConnection"))

04 

05    Dim cmd As New SqlCommand

06("Select distinct ProjectID,ProjectName from Projects", con)

07    con.Open()

08    Dim objDs As New DataSet()

09    Dim dAdapter As New SqlDataAdapter()

10    dAdapter.SelectCommand = cmd

11    dAdapter.Fill(objDs)

12    cmbMonth.DataSource = objDs.Tables(0)

13    cmbMonth.DisplayMember = "ProjectName"

14    cmbMonth.ValueMember = "ProjectID"

15    cmbMonth.SelectedIndex = 0

16End Sub

17 

18Private Sub cmbMonth_SelectedIndexChanged

19(ByVal sender As Object, ByVal e As EventArgs)

20 

21    'Create object of report

22    Dim objReport As New CrystalReport1()

23 

24    'set database login information

25    objReport.SetDatabaseLogon

26("amit", "password", "AVDHESH\SQLEXPRESS", "TestDB")

27 

28    'write formula to pass parameters to report

29    crystalReportViewer1.SelectionFormula

30= "{Projects.ProjectID} =" & cmbMonth.SelectedIndex

31 

32    crystalReportViewer1.ReportSource = objReport

33End Sub

Hope this helps