Basics of Crystal Report for .NET Programmers
www.codeproject.com
Introduction
After I wrote my article about "How to use Crystal Report in .NET", I received lots of E-mails
about it. Some of you wanted me to make a complete example and explain it more. Now I create
an example for who wants to know more about using Crystal Report in C#.NET or VB.NET. My
example is designed at Visual Studio.NET 2003 and I'll make another one at VS.NET 2005 as
soon as possible.
Now if you are ready, just follow me and send me your idea at the end!
Part 1- "Designing SQL database"
1) Imagine you want to make a little program for your father's shop, for saving his
employees salary. (It's not a real example. It's used for learning.)
2) Open your Enterprise manager and create a new database, named "Shop".
3) Create a new Table, named "Employee".
4) Create another one, named "Salary".
5) Make a SQL diagram as like as Fig. 1-1
Fig. 1-1
6) Insert some information in the tables. For example:
Employee Table
Fig 1-2
Basics of Crystal Report for .NET Programmers Shahab Fatemi
www.codeproject.com Page 2 Of 23
Salary Table
Fig 1-3
7) My father wants to have a report for his employee’s salary. I’m ready to make a report for
him. This report must be used for all search conditions we need. For this purpose, create a
store procedure, named "SP_Search". (We make just the search part of program.)
CREATE procedure SP_Search
@e_Name as nvarchar(20),
@e_Family as nvarchar(30),
@From_Date as nvarchar(10),
@To_Date as nvarchar(10),
@SortOrder as int
As
Declare @strQuery as nvarchar(1024)
SET @strQuery = 'SELECT Employee.e_ID, Employee.e_Family, Employee.e_Name,
Salary.s_Date, Salary.s_Salary
FROM Employee INNER JOIN Salary ON Employee.e_ID = Salary.s_eID Where (1=1) '
IF @e_Name <> ''
SET @strQuery = @strQuery + ' AND (e_Name LIKE ''%'+@e_Name+'%'') '
IF @e_Family <> ''
SET @strQuery = @strQuery + ' AND (e_Family LIKE ''%'+@e_Family+'%'') '
IF @From_Date <> ''
SET @strQuery = @strQuery + ' AND (s_Date >= '''+@From_Date+''') '
IF @To_Date <> ''
SET @strQuery = @strQuery + ' AND (s_Date <= '''+@To_Date+''') '
IF @SortOrder = 0
SET @strQuery = @strQuery + ' ORDER BY e_Family , s_Date '
IF @SortOrder = 1
SET @strQuery = @strQuery + ' ORDER BY s_Date , e_Family '
IF @SortOrder = 2
SET @strQuery = @strQuery + ' ORDER BY e_Family , s_Price '
Exec (@strQuery)
/***** END *****/
Basics of Crystal Report for .NET Programmers Shahab Fatemi
www.codeproject.com Page 3 Of 23
Part 2-"Designing your application Form"
1) Create a new project in VS.NET 2003 with C#.NET Windows application or VB.NET
Win application, named "ShopReport"
2) Make it as like as this.
F If you have a good icon or image album, your design will be better.
Fig 2-1
3) Write your code for searching data in database.(Search Button OnClick Event)
private void btn_Search_Click(object sender, System.EventArgs e)
{
try
{
//Clear dataset.
ds_Search.Salary.Clear();
//Set Sql parameters.
da_Search.SelectCommand.Parameters["@e_Name"] .Value = txt_Name.Text.Trim();
da_Search.SelectCommand.Parameters["@e_Family"] .Value = txt_Family.Text.Trim();
da_Search.SelectCommand.Parameters["@From_Date"] .Value = txt_From.Text.Trim();
da_Search.SelectCommand.Parameters["@To_Date"] .Value = txt_To.Text.Trim();
da_Search.SelectCommand.Parameters["@SortOrder"] .Value = cb_Sort.SelectedIndex;
//Fill data in dataset.
da_Search.Fill(ds_Search.Salary);
}
catch(SqlException ex)
{
MessageBox.Show(ex.Message , "Error:"+ex.Number.ToString());
}
finally
{//Do Nothing.}
}
Basics of Crystal Report for .NET Programmers Shahab Fatemi
www.codeproject.com Page 4 Of 23
4) Add a new Crystal file into your project.
- Right click on your project name in the solution bar.
- Choose "Add New Item".
Fig. 2-2
- Select "Crystal Report" Option from Template box, named "Report1.rpt"
- When "Crystal Report Gallery" Opened, Click on OK button.
Fig. 2-3
- Another form for your report wizard will be appearing. Close It.
Basics of Crystal Report for .NET Programmers Shahab Fatemi
www.codeproject.com Page 5 Of 23
Part 3-"Report made by Crystal"
1) A new Report file is created. Welcome to your Report design.
2) A new report has 5 sections.
· Report Header.
When you want to show something at the top of your first page, put it in
report header. For example, you write a subject. This subject will be
shown at the top of your first page, even you have more than one pages.
· Page Header.
When you want to show something at the top of all pages, put it in page
header.
· Detail.
It will be repeated for each row. It depends on the number of records you
pass to your report (By your Dataset or …)
· Report Footer.
It's as like as Report Header, but shown at the bottom of your page.
· Page Footer.
It's as like as Page Header, but shown at the bottom of your page.
3) Field explorer is the most important dialog bar, placed at the left side of your VS.NET, at
the bottom of your Toolbar. When you open a report file, it will be appeared and when
you close it, it will be hidden.
Fig 3.1
Basics of Crystal Report for .NET Programmers Shahab Fatemi
www.codeproject.com Page 6 Of 23
Field Explorer has 7 parts.
· Database fields.
It's a place you must add your database file to your report.
It's necessary for your report, because if you don't attach your report, it
won't work.
· Formula fields.
It's a place you make formula for your reports, if you need.
· Parameter fields.
Sometimes you need pass parameters from your program to your report. In
this situation you create a Parameter Field.
· Group name fields.
Don't worry. I'll tell you what it is.
· Running total fields.
· Special fields.
· Unbound fields.
4) Crystal report is very powerful for making any kind of reports you want.
I know you are new in it, and then let me design a simple example.
DON’T WORRY. I WILL EXPLAIN ALL OF THEM BY SAMPLES.
Part 4- "Design a report!"
In This part, I decide to make some examples and solve them.
My father wants me to make a new report, show his search result in an A4 paper.
Conditions:
A) Our report must have a report header. In the report header, we show our logo, Title of our
report, Date and time.
B) My father wanted me to make a report with these columns:
FAMILY NAME | DATE | PRICE
Problem 1:
Basics of Crystal Report for .NET Programmers Shahab Fatemi
www.codeproject.com Page 7 Of 23
Part A)
-First we design our report header. Put our logo at the top.
For this purpose, go to report header section free space and right click on it and select the Insert
menu and choose Picture option. Then open your logo picture.
Fig. 4-1
-Next select the "Text Object" from Insert menu. Type your factory or shop name and change its
font and size. You can change the format of your text by right click on the text object and select
the FORMAT option.
- Then go to "Field Explorer" - placed at the left side of your VS.NET, at the bottom of your
Toolbar – and open "Special field" part. Drag Print Date object and drop in at the right side of
your header report.
Fig. 4-2
Do it again for Print Time.
Maybe it's better to put Print Date and Print Time at the right side of Page Footer section!
It depends on your idea.
- Finally right click in your report header section part again, go to Insert menu and select LINE
option. Draw a line at the bottom of your report header.
Basics of Crystal Report for .NET Programmers Shahab Fatemi
www.codeproject.com Page 8 Of 23
Part B)
Now we want to show our data.
- First, you must add your data source to your report. Ok. Are you ready?
Open the "Field Explorer Bar" again. Right click on "Database fields" and choose the
"Add/Remove Database" option. The database expert sheet will be appeared.
Open the "Project Data" folder. Then Open the "ADO.NET datasets" and Look.
It's your project dataset. (ShopReport.Ds_Report)
Open your project dataset. Look at the list of tables added in it. Select the "Salary" table and add
it to the right side and click OK.
Fig 4-3
- Then go to the "Field Explorer Bar" again and open the "Database fields".
You can see all of your fields nested after the table name!
Select each of fields and drop it the "Detail Section". The header of your field will be shown at
the "Page header section" automatically.
When you want to show your data in your report, you must set them in the “Detail Section”.
Crystal Report will be automatically shown your data one by one and row by row in your report.
Note that you must fill your data in your dataset. Until you don’t do it, report cannot show
anything.
- Finally Select each other and change their font, size, Format, etc, if you want.
Our report has finished! How was that? Is that easy?
Basics of Crystal Report for .NET Programmers Shahab Fatemi
www.codeproject.com Page 9 Of 23
Fig 4-4
How I can add my report to my .NET program?
- That’s easy.
Choose the "Report Document" component from your Toolbar\components.
Select the "Un typed Report Document" option in the Report document blew.
Fig 4-5
Set the name of Report Document object to "rpt_Report1".
Open your code source and find the place that .NET define "rpt_Report1" in your codes
automatically and change the name of its class to the name of your report class.
Problem 2:
Basics of Crystal Report for .NET Programmers Shahab Fatemi
www.codeproject.com Page 10 Of 2 3
Fig 4-6
Change the high lighted part shown in Fig 4-6 to:
Private ShopReport.Report1 rpt_Report1;
F When you create a new report, studio.NET creates a new class for it. The name of its class
is the same with your report file name.
Each report has a class in .NET that you can work with it.
In the other place, you must make a change. In line number of your code that make a new object
for your report object. For finding this, compile your program. Compiler will find one error in
your program. It's the place that you must change.
You will see this line:
this.rpt_Report1=new CrystalDecisions.CrystalReports.Engine.ReportDocument();
Change it to this:
this.rpt_Report1 = new ShopReport.Report1();
Now, build your project again. (0 Error, 0 Warning)
For Click event of your print button in your project form, type this code:
private void btn_Print_Click(object sender, System.EventArgs e)
{
////******* EXAMPLE 2 *********/
/*Line 1*/ rpt_Report1.SetDataSource(ds_Search.Salary);
/*Line 2*/ rpt_Report1.PrintToPrinter(1 , true , 0 , 65500);
}
Basics of Crystal Report for .NET Programmers Shahab Fatemi
www.codeproject.com Page 11 Of 2 3
Line 1) first of all you must send your data source to your report. Its in your dataset.
"SetDataSource" function get the source of your data must be shown in the report.
Line 2) "PrintToPrinter" send your report to the printer. It's not a good idea because
Sometimes your user want to see a preview of the report and if it is acceptable,
Send it to the printer. VS.NET has another component for this purpose. It's "Crystal
Report Viewer"
Now add a new form to your project and named "ReportViewer.cs". We want to use this form for
showing a preview of our report in another form. You can use "Crystal Report Viewer"
component in your main form. It depends on your idea!
After you add "ReportViewer.cs" to your project (It’s a form), set its property to these:
Fig 4-7
Then put "Crystal Report Viewer" in it -It placed on your Toolbar\Windows Forms- and named
it "crv_Viewer" as public modifiers and set its "Dock" property to "Fill".
Fig 4-8
Basics of Crystal Report for .NET Programmers Shahab Fatemi
www.codeproject.com Page 12 Of 2 3
Now make a new object of this form in the "Form1".
private ReportViewer frm_Viewer;
Change Print Button Click codes to these:
private void btn_Print_Click(object sender, System.EventArgs e)
{
frm_Viewer = new ReportViewer();
rpt_Report1.SetDataSource(ds_Search.DailySelling);
frm_Viewer.crv_Viewer.ReportSource = rpt_Report1;
frm_Viewer.ShowDialog();
}
It's better than before.
Now build your solution and Enjoy!
After I printed the first report of my program, my father said: "Oh! Separate salary of each
employee’s by their Last Name."
He said that he want to have an Employee name at the top and a list of salary at the bottom of
each one.
For example:
1) Watson Jane
2006-04-20 50$
2006-04-21 60$
2006-04-22 48$
2) J. Herbert Oliver
2006-04-20 60$
2006-04-21 40$
2006-04-22 55$
What should I do?
Crystal has a facility can make this report.
I knew that my father wants more reports, and then I made a Folder in my project and add other
reports to that. You must right click on your solution and choose Add \ NEW FOLDER.
F NOTE: Don’t move your Report1.rpt to the folder. It makes some problems to your project.
Problem 3:
Basics of Crystal Report for .NET Programmers Shahab Fatemi
www.codeproject.com Page 13 Of 2 3
I want to make a group of data. First add a new report to your project (Add it to your Reports
folder) and design it as you like.
F NOTE: A report is an output of your program. Design it more beautiful because it can shows
your program ability. It’s very important. Believe me!
Add a new report, named “GroupReport.rpt”.
I want to make a group of data. When you make a group, your group header will be shown at the
top and its contents will be at the bottom of it.
Each group has Header and Footer for itself. When you put something at group header, it will be
shown at the top of each group and when you put something at group footer; it will be shown at
the bottom of it.
For solving problem 3 we must insert a group to our report. Because we want to show each
employee at the top and then show its salary, we must insert a group by employee family or
employee first name. I think it’s better to group it by employee ID.
1) Go to field explorer bar, right click on the “Group Name Fields” and choose “Insert
Group”.
2) In the Insert Group page, select e_ID as shown in Fig 4-9 and click OK.
Fig 4-9
3) Group header and footer will be appeared. Delete “Group #1 Name” object in your Group
Header.
Basics of Crystal Report for .NET Programmers Shahab Fatemi
www.codeproject.com Page 14 Of 2 3
4) Drag “e_Family” and “e_Name” fields from “Database Fields” and drop them into your
group header.
You can add a Text Object to Group Header and save.
Fig 4-10
5) Add a “Report Document” object to your project (rpt_GroupReport) and set it as I told
you before.
6) Change Print Button code to this:
frm_Viewer = new ReportViewer();
rpt_GroupReport.SetDataSource(ds_Search.Salary);
frm_Viewer.crv_Viewer.ReportSource = rpt_GroupReport;
frm_Viewer.ShowDialog();
7) Build you project and see the result.
Basics of Crystal Report for .NET Programmers Shahab Fatemi
www.codeproject.com Page 15 Of 2 3
New Order! Please show sum of salary for each employee and show sum of all at the
bottom of page.
This is a new thing that my father wants.
Oh! He is so generous!
We have 2 problems.
First, How we can show sum of all salaries and Second, How we can show sum of salaries for
each employee?
Part A) Show sum of all salaries.
1) Go to “Field Explorer” bar and right click on “Formula Fields” and select “New”.
2) Choose “Sum_All” for your formula name and click OK.
3) A New page will be shown. This is formula editor. If I want to talk about it, I think I must
write more than 100 pages just about this part.
4) Type “sum( )” in the editor. Then move your arrow key between parentheses. Drag
“Salary.s_Salary” from the top left side of the editor and drag in between parantheses.
You will have this formula:
SUM ( Salary . s_Salary )
It means that we want to get sum of “s_Salary “field.
Depends on your need, you can use AVGERAGE, MAXIMUM, VARIANCE, etc.
5) Click on the “Error Check” button at the top of formula editor. It checks your errors.
Fig 4-11
Problem 4:
Basics of Crystal Report for .NET Programmers Shahab Fatemi
www.codeproject.com Page 16 Of 2 3
Fig 4-12
6) If you have 0 Error, Save and Close the formula editor.
7) You can see “Sum_All” in your Formula part. Drag it and drop into your report footer
section. (Fig 4-13)
Fig 4-13
Now Build your project again and see its result.
Part B) Show sum of all salaries for each employee.
Formula part is useful for global formulas but when we want to show the sum of each
employee we can’t use formula section.
In this case we use “Running Total Fields”, placed at the “Field Explorer”.
1) Choose “New” by right click on “Running Total Fields”, placed at the “Field Explorer”.
2) Type “Sum_Group” for its name.
3) As you see, the “Running Total Fields” has 3 sections. (Summery, Evaluate and Reset)
Select s_Salary field from the left side and add it to the SUMMERY section.
Choose “SUM” for Type Of Summery.
4) Set RESET section to the “On Change Of Group”. It means that we want to get the
summery of “s_Salary” field but the summery must be set to zero for each group.
(Fig 4-14)
5) Add “Sum_Group” into the Group Footer section of your report.
Basics of Crystal Report for .NET Programmers Shahab Fatemi
www.codeproject.com Page 17 Of 2 3
Fig 4-14
Fig 4-15
Run you program and see its result.
Basics of Crystal Report for .NET Programmers Shahab Fatemi
www.codeproject.com Page 18 Of 2 3
Part 5- "Intermediate Design"
You are familiar with the basics of designing a report. Now it’s the time to jump up!
After 1 month, I saw that I can make other useful reports for my father.
I want to design a report that shows the summery of salary for each employee
without its details.
In this case we can use Group as like as Problem 3 & 4 and just shown the group header and
group footer, without it’s detail. But we have a better choice. CROSS TAB.
Cross Tab is a Tabular object that we can set each parameter for it.
1) Add a new report (ChartReport.rpt) to your project and set its design as you like.
2) Go to Report Footer section and right click in a free space of this section. Choose Insert \
Cross Tab from menu as shown in Fig 5-1.
Fig 5-1
3) In the “Format Cross Tab” form, we set the cross tab rows and columns.
A Cross Tab is a table that we must set its columns and rows and set relax and cross tab
shows all things we want in our report.
(It’s as easy as eating a peach!)
Ex 1
Basics of Crystal Report for .NET Programmers Shahab Fatemi
www.codeproject.com Page 19 Of 2 3
4) Add “e_Family” and “e_Name” Fields to the row section and choose “s_Salary”, then
Click on the “Add Summarize Field” button. (Fig 5-2)
Fig 5-2
5) Go to “Style” sheet in Cross Tab format page and choose a format of your table you like.
6) Click on OK and save your report.
7) Add a “Report Document” object to your program for your new report and change Print
Button function to this:
#region Ex1
frm_Viewer = new ReportViewer();
rpt_ChartReport.SetDataSource(ds_Search.Salary);
frm_Viewer.crv_Viewer.ReportSource = rpt_ChartReport;
frm_Viewer.ShowDialog();
#endregion
Basics of Crystal Report for .NET Programmers Shahab Fatemi
www.codeproject.com Page 20 Of 2 3
At the end of my report, I want to show a chart of sum of salary for each
employee.
1) Choose “Insert Chart”. It’s near the place that you choused Cross Tab.
2) At the “Chart Expert” sheet, choose “PIE” type. (Fig 5-3)
Fig 5-3
3) At the “Data” sheet, We want to show our report in the Footer section. Then choose
“Footer” in “Placement” section.
4) We want to show the changes of summery of salary for each employee. Then choose
“Salary . s_Family” and add to “On Change Of” part.
Fig 5-4
Ex 2
Basics of Crystal Report for .NET Programmers Shahab Fatemi
www.codeproject.com Page 21 Of 2 3
5) The value of chart that must be shown is “SUM OF s_Salary”.
Choose “s_Salary” and add to “Show Value”.
6) In the “Text” sheet, you can set the size of your chart font.
7) Click OK and see your chart in your program.
8) At the first time, the chart will be appear at the top of Cross Tab. Right Click on the
“Report Footer” border and choose “Move Section”. You can change the format of each
section.
Fig 5-5
9) In the “Section Format” dialog, change the position of “Report Footer A” with “Report
Footer B”
Fig 5-6
Basics of Crystal Report for .NET Programmers Shahab Fatemi
www.codeproject.com Page 22 Of 2 3
10) The “Section Expert” dialog is the other important place in a report that I don’t have
enough time in this article to explain it.
11) Click Ok and build your program again.
This is a part of report result.
Fig 5-7
Basics of Crystal Report for .NET Programmers Shahab Fatemi
www.codeproject.com Page 23 Of 2 3
How we can send a parameter from our program to our report?
(Sometimes we want to pass a value from our program to our report.)
1) In the “Filed Explorer” bar, right click on the “Parameter Fields” and select a new one.
2) Set its name to “StringParameter” and click OK.
3) Drag it from “Parameter Fields” and drop into “Page Header” section.
4) That’s it. Change your Print Button function to this:
#region Ex1
frm_Viewer = new ReportViewer();
rpt_ChartReport.SetDataSource(ds_Search.Salary);
rpt_ChartReport.SetParameterValue("StringParameter" , "This is a test!");
frm_Viewer.crv_Viewer.ReportSource = rpt_ChartReport;
frm_Viewer.ShowDialog();
#endregion
Now, rebuild your solution and run it again.
Ok. That’s good. Enjoy!
I think if I want explain all things I know about crystal report, it needs more
than 4 months and I must write a book about it.
If you read this article (Big Article) and find it useful and have some questions,
don’t hesitate to contact me. My E-Mail address is shahabfatemi@hotmail.com
If you can help me to improve or complete it, or you are eager to help me to
write a book, please contact me.
I will be happy if I see an E-mail from you!
With the special thanks for www.codeproject.com
Sincerely
Shahab Fatemi.
2006-10-07 I.R.IRAN – Esfahan.
õõõ
Ex 3
No comments:
Post a Comment