How To Create Crystal Report?

Posted by Prakashmondal5-21103 under Windows Forms on 8/16/2017 | Points: 10 | Views : 505 | Status : [Member] | Replies : 2
I want to create crystal report the following format..
I have three main tables.



The Crystal report format bellow attached.

Please help me.




Responses

Posted by: Ajay2707 on: 8/17/2017 [Member] Starter | Points: 25

Up
0
Down
Hi Prakash,

First of all I suggest something based on your database you attached. In your master table like employee and loan, you add another column as a primary-key and give refence to loan detail table. This I suggest based on Database standard . Here I give you table structure

Database Structure

declare @Emp table(emp_id int identity(1,1), Emp_no varchar(50) , Emp_name varchar(50) , Department varchar(50) , Designation varchar(50) , Grade_Pay int )

declare @Loan table(Loan_Id int identity(1,1), Loan_no varchar(50) , Loan_name varchar(50) , Loan_amount int )

declare @LoanDetail table (emp_id int , Slno int , Org_name varchar(50) , Loan_Id varchar(50) , Pay_amount int , Pay_date datetime)


insert into @Emp values ('EMP2002' , 'Suresh' , 'Math' ,'Professor', 5400) , ('EMP2005' , 'Amir' , 'Phys' ,'Asst Professor', 5400)

insert into @Loan values ('L/2015/1001' , 'GPF Loan' , 10000) , ('L/2015/1002' , 'Home Loan' , 50000), ('L/2015/1003' , 'Car Loan' , 30000)

insert into @LoanDetail values
(1 , 1, 'TCS' , 1 , 20000, '2017-07-17') ,
(1 , 2, 'TCS' , 3 , 30000, '2017-07-20') ,
(1 , 3, 'TCS' , 1 , 10000, '2017-07-25') ,
(1 , 4, 'TCS' , 2 , 50000, '2017-08-02')

Declare @empid int = null--comment this line and uncomment above and below line to create procedure
Select
Emp_no, Slno, Org_name, Loan_no, Pay_amount, convert(varchar(10), Pay_date , 105) Pay_date
From
@LoanDetail ld
Join @Emp e on ld.emp_id = e.emp_id
Join @Loan l on ld.Loan_Id = l.Loan_Id
Where
(e.emp_id = @empid or @empid is null)-- this condition will get result based on search employee or if not pass empid, then get all records

/*****Create new procedure for this which get loan detail , read instruction to create procedure****/
/*This below procedure will fetch the data based on employee search */
--Create Procedure GetLoanDetail
--( @empid int = null)
--As
--Begin
-- Select
-- Emp_no, Slno, Org_name, Loan_no, Pay_amount, convert(varchar(10), Pay_date , 105) Pay_date
-- From
-- @LoanDetail ld
-- Join @Emp e on ld.emp_id = e.emp_id
-- Join @Loan l on ld.Loan_Id = l.Loan_Id
-- Where
-- e.emp_id = @empid -- this condition will get result based on search employee
--End




Now I step by step guide , how to create your crystal report. Before this check the link that how to start crystal report.

Step by Step learn Crystal Report links - https://www.codeproject.com/Articles/142064/Step-by-Step-Creation-of-Crystal-Report-using-its , http://www.c-sharpcorner.com/UploadFile/b926a6/create-crystal-report-step-by-step-using-sql-database-view/


1. First create and procedure of your 3 table (In above database structure, I commented procedure, please uncomment and run in sqlserver.
2. No open CR (Crystal report), get procedure reference in it.
3. Now based on layout you want , place database field in different different section.
a. your loan payment statement heading will come in report header include employee and his /her organization detail .
b. your loan detail portion which is repeated put fileds in detail section.
c. for Total paid amount, use running total field of CR based on paid amount.
d. that's done, just preview it and you will get the result in CR.

4. Now if you want bind this report with .net application then again ping me.



Prakashmondal5-21103, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Ajay2707 on: 8/21/2017 [Member] Starter | Points: 25

Up
0
Down
Hi Prakash, Is the above post is helpfull to you?

Prakashmondal5-21103, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response