Currently Being Moderated

Formulas, formulas, formulas

Purpose

 

In this document, I list some of the more simple to do Crystal Reports formulas that have been asked for in the SAP Crystal Reports SCN Space.

 

 

Overview

 

This document does not, will not list complicated formulas that may need lines and lines of discussion. Rather, the document lists simple and straight forward formulas.

 

 

Introduction

 

Even though the formulas may be simple, they are asked for often enough in the SAP Crystal Reports SCN Space. The plan at this time is that I will be updating the document as I find formulas that are simple yet asked for regularly.

 

 

Format Description

 

The format is as follows:

 

Description of formula linking to originating Discussion

 

Formula

 

Please Note: If you have a question about any of the formulas in this document, do not post a Comment. Rather, either create a new Discussion in the SAP Crystal Reports SCN Space, or add your query to the originating Discussion.

 

It is always advisable to consult the Crystal Reports Help Files (more documentation is on the SAP Help Portal), or searching SCN (search box in top right corner) before posing a question to any SCN Space. For search tips, see the Blogs My tips on searching knowledge content the SCN and How to use SCN search.

 

 

The Formulas

 

Filtering data from 6pm day before

 

 

{datetime_field} >= cdatetime(currentdate-1,ctime(18,0,0))

 

Print the 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 maximim 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 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 asterisk 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")

 

 

Related Content

 

Related Documents

 

SAP Crystal Reports 2011 PAM (supported platforms)

SAP Crystal Reports 2011 Trial Download

SAP Crystal Solutions 2011 - Frequently Asked Questions

From Crystal Reports 2008 to SAP Crystal Reports 2011 or SAP Crystal Reports for Enterprise

Official Product Tutorials – SAP BI Suite

Comments

Delete Document

Are you sure you want to delete this document?

Actions