SAP Crystal Reports

8 Posts authored by: Radhika Rao

     In a scenario where there is a grouping within the report and the group data goes beyond one page, and the user wants to display message at the bottom of the page

“Data continued on next page” can be achieved by creating following formula within the report:

1) Create Formula#1 (Name it as T1) and place it on the group header and suppress the formula T1 placed on the group header:

WhilePrintingRecords;
BooleanVar var_continue:= True;

2) Create Formula#2 (Name it as T2) and place it on the group footer and suppress the formula T2 placed on the group footer:

WhilePrintingRecords;
BooleanVar var_continue:= False;

3) Create Formula#3 (Name it as T3) and place it on the page footer and do not suppress the formula T3 placed on the page footer as it contains the continuation message:

WhilePrintingRecords;
BooleanVar var_continue;
if var_continue
then "Data continued on next page"
else ""
 

 Hence the continuation message will be returned by the formula#3 (T3) if the group data exceeds more than one page.

    In some scenario we might need to calculate number of Mondays/Tuesdays etc. in a given date range.

   Let us take an example where we calculate number of Wednesdays. The below example demonstrates how to calculate number of Wednesdays in a given date range. The input dates can be database fields, formula fields or parameter fields.

The DateDiff built-in function can be used to achieve the above mentioned calculation.

The formula can be changed to count another day of the week by changing the number 4 to any number from 1 to 7, where:

Sunday ->1

Monday->2

Tuesday ->3

Wednesday->4

Thursday ->5

Friday->6

Saturday->7

The standard function is in following format:

DateDiff (intervalType, startDateTime, endDateTime, firstDayOfWeek)

Formula to be used:

DateDiff ('ww', currentdate, (currentdate+12), 4)

Output: 2.00

Where currentdate is today’s date i.e. 26-Dec-2011, and in the date range between today’s date and today’s date plus 12 days, there are total 2 Wednesdays.

The parameter 'ww' is used to calculate the number of firstDayOfWeek's occurring between two dates. In above example firstDayOfWeek=4 (Wednesday), hence 'ww' will count total number of Wednesday's between 2 dates.

As the output is with two decimal places, below mentioned formula can be used to replace “.00” with blank.

Replace (totext (DateDiff (‘ww’, currentdate - 1, (currentdate+22), 4)),'.00','')

Output: 2

Note - You should subtract one day from the start date if you want date ranges that start on Friday to include that first Friday in your count.  In other words if an 8 day range from Friday to Friday should count 2 Fridays.

      There can be a scenario that the string values in the database field are not available in proper case and user wants to change the case in the report output. Hence to present the string values in the correct / expected format in the report output we have 3 built in functions within Crystal Reports which take input values as only “string”->

1)      Propercase

2)      LCase/LowerCase

3)      UCase/UpperCase

Now let us take an example and see how to use it:

For example there is a database field called Name and the values under names are as follows:

Name:

BOB GraysON

RAnDY ThoMAS

RoberT ALAMAR’s

1) ProperCase Function Implementation: This function takes input value as string and capitalizes the first letter of each word in the string and converts all other letters to lower case & if any letter that follows a non-alpha character it capitalizes that letter as well. For example if we pass Name value of the database field we get following result:

ProperCase ("BOB GraysON") -> It will return “Bob Grayson”

ProperCase ("RAnDY ThoMAS") -> It will return “Randy Thomas”

ProperCase ("RoberT ALAMAR’s") -> It will return “Robert Alamar’S”

2) LCase/LowerCase Function Implementation: This function takes input value as string and converts the input string to all lower case. Note LCase and LowerCase are equivalent functions.

For example if we pass Name value of the database field we get following result:

LCase ("BOB GraysON") -> It will return “bob grayson”

LCase ("RAnDY ThoMAS") -> It will return “randy thomas”

LCase ("RoberT ALAMAR’s") -> It will return “robert alamar’s”

3) UCase/UpperCase Function Implementation: This function takes input value as string and converts the input string to all upper case. Note UCase and UpperCase are equivalent functions.

For example if we pass Name value of the database field we get following result:

UCase ("BOB GraysON") -> It will return “BOB GRAYSON”

UCase ("RAnDY ThoMAS") -> It will return “RANDY THOMAS”

UCase ("RoberT ALAMAR’s") -> It will return “ROBERT ALAMAR’S”

  • Note: Numbers that are part of the input string are not affected by  any of the above mentioned function.

            The dependency checker feature present under “Report” menu (Check Dependencies) allows the user to see if there is anything that would prevent the report from  running. This feature is available from Crystal Report 2008 onwards.

The Dependency Checker registers several types of errors, for example:

  • Table is deleted and the fields pertaining to that table is used in the report.
  • Any formula in the report uses custom functions and those function dlls are no longer available/ Formula compilation errors.

The error/warning/success sign displayed under Dependency Checker are as follows:

Signs for DC

Below screen shot covers different scenarios, which covers success, error & warning message with the use of Dependency Checker:

1) Success: The below screen shot shows the success message.

Success

2) Warning: The below Screen shot displays the warning message stating the excel sheet which is being used as the data source has been changed. Displays the message to verify the database once.

Warning

3) Error: The below Screen shot displays the error message that the excel sheet which is being used as the data source has been deleted.

 Error

To further fix the problem double-click on the error to open the target report, and navigate to the report object to fix the same.

Workday functionality in Crystal Reports:

   The Workday function within EXCEL returns the Serial number of the date before or after a specified number of workdays. This Blog also covers the similar functionality, which can be implemented in Crystal Reports.

Note: The above functionality will take into consideration of weekends & listed holidays.

                The above functionality can be achieved by writing a formula in crystal or through a custom function. The below example shows the implementation of WORKDAY function through a formula.

Example of WORKDAY function in EXCEL: (where 12-Dec-2011 & 23-Dec-2011 are set as holidays)

Excel Workday Screenshot

 

Steps to achieve WORKDAY functionality in Crystal Reports:

1)      Enter the list of holiday dates in a formula; I have created a formula naming “Holidays”, where I have set 12-Dec-2011 & 23-Dec-2011 as holidays. Following is the formula content (Keep the “Holidays” formula on the report header and suppress the section):

BeforeReadingRecords;

DateVar Array date_holidays := [Date (2011,12,23), Date (2011,12,12)];

0;

 2)      Create another formula to add or subtract a specified number of business days from a specified start date. As compared to excel we need to input following information into the formula to get the desired output

a)    Start Date b)    Number of days c)    Holiday Dates

 I have created “Workdays” formula within crystal reports and following is its content:

WhileReadingRecords;

DateVar Array date_holidays;

DateVar output_date:= date({Sheet1_.Start Date});  // output_date is the startdate value

NumberVar days:= {Sheet1_.Days}; // days=  Is the number of days to add

NumberVar daysadded := 0; // This will get incremented if the holidays fall on the business day

WHILE daysadded<days

Do (output_date := output_date +1;

    if dayofweek (output_date) in 2 to 6 and not (output_date in date_holidays)

        then daysadded:=daysadded+1

        else daysadded:=daysadded);

WHILE daysadded > days

Do (output_date := output_date -1;

    if dayofweek (output_date) in 2 to 6 and not (output_date in date_holidays)

        then daysadded:=daysadded-1

        else daysadded:=daysadded);

);

output_date;

 The screen shot below displays the start date, days and workday column, where start date and days are input values to the formula and workday column is the return value of the formula for each database value.

Crystal Reports Workday Output Screenshot

Crystal Reports 2008 -> Performance Improvement Techniques

Following are the steps to be taken care of while designing the crystal report to improve the performance of the designed crystal report:

1) Using a command Object/Stored Procedure as the data source will be faster than using crystal report to link tables or views.

2) Remove unused tables, unused formulas and unused running totals from the report.

3) Whenever possible, limit records through selection, not suppression, meaning use conditional formulas to return a desired field result, instead of using suppression to eliminate unwanted records.

4) If the database is of large size try to use a selection formula within the report to only return those records that you need for the report, rather than having crystal read all the records.

5) If a selection formula is used in the report, make sure that no reference to any crystal functions (i.e. totext, cDate etc) or any other formula is there. If it is referencing crystal functions or report formulas, the database will return all records because it does not understand the crystal functions on the database side, hence filter at database level if possible or use SQL Expression to achieve it, as SQL Expressions are always handled on the database whereas formulas are handled on the report level. Use the Crystal functions in specific formulas rather than the selection formula.

6) Using Indexes for faster data retrieval also improves the performance of the reports. This can be achieved by using the option under menu File|Report Options, “Use Indexes or server for speed”.

7) The fewer sub reports in the report, the faster the report will run. Each sub report will hit the database again to gather data. Preferably use the “On Demand Sub Reports” instead of normal sub reports. Since using the normal sub reports will be a cost factor to retrieve the data from the data source. Also, Linked sub reports will always perform faster than unlinked sub reports, because they are being run on a subset of data being passed from the main report, rather than returning all records.

8) While using text objects, avoid inserting database or formula fields inside them.

9) If some sections are not required to be displayed on the report, try and suppress sections (no drill-down) if you don't need to see drill-down information. This will speed up report performance, as hiding a section which will calculate and save drill-down information takes longer than a section that is suppressed (no drill-down).

10) The more OLE objects you have inserted, the slower the report will run. The bigger or more complex the OLE object is, the slower the report will run.

Crystal Reports 2008 -> Runtime settings for custom date type:

   For DateTime type Database or Formula field, we have different formatting options available in Crystal report.

Benefit:

Formula can be written to set the Date format dynamically (Run Time) based on requirement.  It can be based on parameter value or any database field. For example if Currency is a database field in the report, then based on its value if Currency= ‘USD’ then display date as 03/01/1999 and if Currency= ‘AUD’ then display date as 1999/01/03 .

Hence above criteria can be met using below mentioned information.

How To Use:

To set Custom format for Date field follow below mentioned steps:

1)    Place the Date field on any of the report section (Report Header, Page Header etc.) as per requirement.

2)    Right click on the Field, go to Format field and click on “Date and Time” Tab.

3)    Select Custom Style from the options provided under Style.

4)    Then click on Date Tab to do Date settings.

Note: 01 March 1999 is the example date value taken to display below options.

Following are the options available to set custom date values:

I)Date Type: This list lets you set following options:

a)    Choose one of the two default styles:

1) Windows default Long (Date will be displayed as Monday, March 01, 1999 [Note the other settings might affect its value.])

2) Windows default Short (Date will be displayed as 3/1/199 [Note the other settings might affect its value.])

b)    Choose Custom to create your own style in the Format Editor.

II)Calendar Type: If you are using Windows NT/95/98 your operating system may support more than one calendar type. For instance, your operating system may support a native calendar (Japanese), a Gregorian calendar (in the local language) and a Gregorian English calendar. If your operating system supports multiple calendar types you may use the Calendar Type list to select a calendar type for the date field.

III)Format: Month, Day, Year and Era can be set through this option.

a) Month: If you want to create a custom format for a date field object, use the below list to specify the way you want the report to print/display the month part of the date. The below options can be used in formula editor at runtime: (For e.g. If March is the Month then following Values will be returned)

      crNumericMonth (e.g. 3)  , crShortMonth (e.g. Mar) , crLongMonth (e.g. March), crLeadingZeroMonth (e.g. 03), crNoMonth (Nothing will be displayed)

It can be used in following manner:

 If Currency= USD then crNumericMonth

Else If Currency= AUD then crShortMonth etc…..

c) Day: If you want to create a custom format for a date field object, use this list to specify the way you want the program to print/display the day part of the date.

Following are the options available: crNumericDay (e.g. 1), crLeadingZeroDay (e.g. 01), and crNoDay (Nothing will be displayed)

c) Year: This conditional formatting formula must return one of the following Year Constants:

  crShortYear (99), crLongYear (1999),  crNoYear (Nothing will be displayed)

d) Era/Period type: Use the Era/Period Type list to select either a short or long era/period of time.

crShortEra,  crLongEra,  crNoEra

IV) Order: The order of display of DATE/MONTH/YEAR can be set through this option. If you want to create a custom format for a date field object, use these options to select the order in which you want the elements of the date to appear. Following are the options available:

crYearMonthDay (Prints the date in the order Year, Month, Day)

 crMonthDayYear (Prints the date in the order Month, Day, Year)

crDayMonthYear (Prints the date in the order Day, Month, Year)

V)Day of Week: When you are setting up a custom format for a date field object, if you want the name of the day (or an abbreviation of that name) to appear before the rest of the date, use this option to set the format for that name. Following options are available:    crShortDayOfWeek (e.g Mon), crLongDayOfWeek (e.g. Monday) and crNoDayOfWeek (Nothing will be displayed).

For Short Day and Long Day few more options can be set:

Sep: When you are setting up a custom format for a date field object, if you set the Leading Day Type property to short or long (so a day name or abbreviated day name appears before the date), you need to specify the character(s) you want to use to separate the name from the date itself. You do that in this box. Within practical limits, you can use as many characters as you need. For example,

To separate a date name from a date, you may use Comma  & Space (, ) as your separators. Type in the characters you want to use. The program modifies the sample date at the bottom of the dialog box as you do.

Encl: Use the Encl list to select parentheses or square brackets as an enclosure for date/time fields on your report.

 crDayOfWeekNotEnclosed,  crDayOfWeekInParentheses,  crDayOfWeekInFWParentheses,  crDayOfWeekInSquareBrackets,  crDayOfWeekInFWSquareBrackets

Position: Use the Position list to select either a Leading or a Trailing position.

 crLeadingDayOfWeek,  crTrailingDayOfWeek

VI)Separators: Following options are available:

a)Prefix: Use the Prefix box to create a prefix for dates in your date field, for example, DATE. The date will be displayed as DATE 01/03/1999.

b)First & Second: When you are setting up a custom format for a date field object, the program enables you to specify the separators, if any that you want to use to separate the date elements. For example,

If you are using a short format, all numbers, you may want to specify a hyphen (-) as the first separator and as the second separator as well (like 01-03-1999).

c)Suffix: Use the Suffix box to create a suffix that follows the dates in your date field, for example, DATE. The date will be displayed as 01/03/1999 DATE.

VII)Sample: This displays the sample date at the bottom to demonstrate the style chosen.

Note: The values can be chosen as STATIC from then dropdown or can be made DYNAMIC, by writing conditions in formula editor, available next to each option. 

1)Crystal Report -> Setting Horizontal Alignment of fields dynamically:

Benefit:

Horizontal Alignment formatting property can be used for the dynamic nature reports, where field values are shown dynamically based on required condition, which in turn will reduce the overload of creating many formulae to achieve the alignment.

How to use:

To change the Horizontal Alignment of the database field/formula field follow the below mentioned steps:

1) Right click on the field.
2) Click on Format Field Option.
3) Select the Common Tab.
4) Click the formula editor in front of Horizontal Alignment and add the setting required.
5) Following are the Alignment constants available:

Constant                              Description

crDefaultHorAligned           Set the default alignment to horizontal.
crLeftAligned                     Align to the left.
crRightAligned                   Align to the right.
crCenteredHorizontally       Center horizontally.
crJustified                        Set alignment to be justified

For Example:   If a specific formula in the report returns distinct values at run time, may be string, date (converted to string) or number  (converted to string) and based on the values returned; alignment needs to be set then following condition can be used:

if currentfieldvalue= "field.salary" then crRightAligned
elseif currentfieldvalue="field.name" then crLeftAligned
elseif currentfieldvalue="field.date" then crCenteredHorizontally

 Hence using above mentioned Horizontal Alignment property, additional formula creation can be avoided.

Note: The above property is available only for Database Field & Formula field, not for Static text box field.

2)Crystal Report -> Use of ToText Function:

Benefit:

When Sorting or Grouping is required based on return type of any Parameter value, ToText function can be used to convert different type of database field (i.e. number, date etc) to same type i.e. String.

How to use:

For Example: The parameter name used in the report for dynamic sorting is “SORTFIELD”, then following formula can be created to achieve dynamic sorting:

If SORTFIELD= “Name” then field.name
Elseif SORTFIELD=”Amount” then ToText(field.Amount,”############.##”)
Elseif SORTFIELD=”Date” then ToText(field.Date,”yyyyMMdd”)

Where Database field type is:
field.Name is string
field.Amount is Number (Above # is replaced by blank space)
field.Date is Date (Above yyyy is for year (e.g. 2011) , MM is for month (09) and dd for Date (23).

  Hence the return type of the formula will be same as STRING, even though different type of database field is being used.

Actions

Filter Blog

By author:
By date:
By tag: