Crystal Reports And Formula

Quick Start Crystal Reports And Formula

There are several different kinds of formulas in Crystal Reports: report, conditional formatting, selection, search formulas, and running total condition. The majority of formulas in a report are report formulas and conditional formatting formulas.

To Start Up With Basics Of  Formulas In Crystal Report

1)If You Are Very Unknown To Crystal Report Or Not Understanding What I am Talking About You Can View My Previous Blog Which Will Help You To Quick Start With Quick Start With Crystal Report

And If You Understand Keep Continue With This......

1)First Of All,Open Your Crystal Report File In Visual Studio,Now You Have A Field Explorer Like Below

2)Now Right click On Formula field ,And Click New,You Will get A Window Like Below One.

3)Now Type The Name You Want To Give Your Formula,(Which You'll Further Just Drag On Your report And It Will Show The Value Coming From The Output Of The Formula Execution).

Below Is The Screen You'll Get After Clicking Use Editor.Now Understand The Below Image And Apply Your Self Defined Formulas In Report

Below Are Sample Which You Can Use To Understand Formula Rules For Crystal Report.
Some Thing You Note Written Under {} are field of particular table or view.

Formula Field Is Easy To Use

1)

"FY " +  Totext(ToNumber({wsp_DSSRptPerfCrit;1.startyear})+ 1)

will give an date(eg.) FY 2,009.00

2)

"FY " +  Totext(ToNumber({wsp_DSSRptPerfCrit;1.startyear})+ 1,0)

 will give u the same date(abv eg.) FY 2,009

These above called basic syntax

But this one called crystal syntax

Sum Up A Column Could Be Done As

local numberVar a;

a:=a+ ToNumber({vw_PackingInvoicePart2Merge.totalpcs});

ToText(a);

To 'SUM' up you can also right click on report and go for this
insert>summary
choose your column name from summary window...and select calculate summary as SUM it will sum up that particular coloumn

Discount Could Be Done As
local numberVar a;


 local numberVar b;

a:=sum({VIEWINVOICEFRESH.multy});
b:=ToNumber({VIEWINVOICEFRESH.Discount})*a/100;

ToText(b);

Number To Word Could Be Done As

local numberVar a;

 local numberVar b;
 local numberVar c;
 local numberVar d;
 local numberVar e;
a:=sum({VIEWINVOICEFRESH.multy});
b:=ToNumber({VIEWINVOICEFRESH.Discount})*a/100;
c:=ToNumber({VIEWINVOICEFRESH.Fright});
d:=ToNumber({VIEWINVOICEFRESH.ET});
e:=a+c+d-b;
ToText(e);

ucase(ToWords(e,0)+" and "+{VIEWINVOICEFRESH.Small_Unit}  +" " + towords((Round(e,2)-int(e))*100,0)+" Only");

And What if there is a condition that write 25.00 as twenty five but 23.89 as twenty three Cents eighty nine only(Dynamically in Crystal report)??

In Such Case Use This:

if Round({@Total Amount},2) - Int({@Total Amount}) = 0 then
"Ringgit Malaysia: " + ToWords ({@Total Amount},0) + " only"
else
"Ringgit Malaysia: " + {@NumToWords1}  + " and " + "Cents " + ToWords ((Round({@Total Amount},2) - Int({@Total Amount})) * 100, 0) + " only"



Now The Things You Must Ask Me Are....
what is?
ucase()-To Write Words In UPPER CASE

towords()-use to understand an integer as word

ToText(e)-Convert Any Thing as String

ToText(e,0)-Remove decimal from end like 25.00 to 25

sum()-Sum Up Selected Column

(Round(e,2) -Use To Round Off Figure of Integer.

To Fit Size Of Detail Section 

print at bootom of page for group footer

fit section for detail section