SAP Crystal report List of Important formula that used frequently during Layout and Report development

Er Tasneem Ahmad Karimi Monday, March 23, 2020

  • Formula to set Default Value

 Create parameter as Static and use below logic in selection formula:

 If Hasvalue({?DocNum}) then

 {DocumentNumber} = {?sDocNum}

 Else

 {DocumentNumber} = {DocumentNumber} 
  • Formula to converts Amounts in Words
stringVar x :=  Split (Replace(totext({OCHO.CheckSum}),",",""),".")[1];
stringVar y := Split (Replace(totext({OCHO.CheckSum}),",",""),".")[2];
stringVar str1 :=  Replace(towords(tonumber(x)) ,"and xx / 100","");
stringVar str2 :=  Replace(towords(tonumber(y)) ,"and xx / 100","");

 if(tonumber(Split (Replace(totext({OCHO.CheckSum}),",",""),".")[2]) > 0) 
THEN Propercase(str1) + {OCRN.CurrName} + " and " + Propercase(str2)  + {OCRN.F100Name} + " only" 
else Propercase(str1) + {OCRN.CurrName}+ " only";

  • Filtering data from 6PM day before
{datetime_field} >= cdatetime(currentdate-1,ctime(18,0,0))

  • Print data only if it is within a year of the print date
{Command.udate} in [cdate(year(currentdate)-1, month(currentdate),day(currentdate)) to currentdate]

  • Showing N/A in a cross tab When Null Value

Right-click one of the cells in the Summary fields > Select Format field > Common tab > Click on the formula button beside 'Display String' and use the following code:

if currentfieldvalue = 0
then "N/A"
else totext(currentfieldvalue)


  • Show week range based on date parameter

DATEPART("ww", {?Date_Parameter}, crSunday) &" - "& DATEPART("ww", {?Date_Parameter}, crSunday)+1

  • Currency Amount In Words
numbervar x := {number_field};
numbervar ipart;
numbervar decpart;
ipart := int(x) ;
decpart := x - ipart;
ProperCase(replace(towords(ipart),"and xx / 100","")&"and "&mid(totext(decpart),instr(totext(decpart),".")+1,len(totext(decpart)))&" / 100 Dollars");

  • Subtract 1 hour from time
{?From Time} - 3600

  • Find maximum value in Row of Values
{Local numbervar array arr;
arr := [{0100}, {0300}, {0500}, {0700}];
Maximum(arr);

  • Create a formula based on group summaries to get the average 

In the Formula editor find the created summaries. Use the following formula:

Sum({same_day},{group1})/Disctinct Count({Date},{Group2})

  • Finding the first space in a string star
  • Finding the first space in a string starting from the right of the string

stringvar str;
str := {string_field_from_database};
strreverse(mid(strreverse(str),instr(strreverse(str)," ")+1, len(str)))

  • Calculate the time difference from the Invoice date to the Current date
In the Formula Editor for this formula, change "Exceptions for NULLS" to "Default Values for NULLS". Then use the following formula:

If ({APINVOICE.INVOICE_DTE} in Aged0to30Days  and {MAINVDTL.MA_REC_NBR} = "")  then "1-30 Days Aging" else
.
.
.

  • Replace digits/numbers with astreik or other character 
 For example the string "Hello 1.123.124.3456" should display as "Hello *.***.***.****"
How the formula below works:

The formula will split the string out at each space and assign each component to an array.
  It will then check the second letter in each word to see if it is uppercase.
  If it is not uppercase then it will apply the proper case function and add it to a string running total.
  If the second letter is already upper case then it will add the word as it is to the string running total.
  array y:= split({your database field}, ' ');

stringvar z:='';

  numbervar x:=0;
  for x:= 1 to ubound(y) do
  (
  if not (asc(mid(y[x],2,1)) in 65 to 90) then
  z:= z + ProperCase(y[x]) + ' '
  else z:= z + y[x] + ' '
  );
  trim(z);

  • Remove X number of characters from a text field
(X = 8 in the below example)
left({table.field},len({table.field})-8)

  • Date Selection formula that will only return data where the date field has no data

{date_field_in_database} = cdate(0,0,0) or isnull({date_field_in_database})

  • Shift the time from GMT to MST


ShiftDateTime({Input_DateTime_field},"GMT,0,GMT", "MST,420,MDT")
Tags: