NOW...Scripts
#
Creating Reports
#
Sub reports
# Cross
Tabs
# Drill
Down Report
# Forms
Letters
#
Filtering
#
Sorting
#
Grouping
#
Summary
#
Formula
#
Running Total
#
Parameters
#
Hyperlink
#
Formulas
# ARRAY
#
Variable
# LOOP
#
Sections
#
Formatting
#
Boxes/Lines
# Maps
#
BarCode
#
QRCode
#
PIC/Images
XXXXXXXXXXXXXXXXXXXXXXXXX SAP
CRYSTAL REPORT - COURES OUTLINE XXXXXXXXXXXXXXXXXX
# Number
to InWords --ok
(see/search this blog)
#
Currency to InWords (Tk) --ok
(see/search this blog)
# Normal
Serial No
--ok (see/search this blog)
# Group
Serial No --ok
(see/search this blog)
#
Advanced Formatting
#
Alternating Row Formatting
#
Formatting Rows by Alternating the Colors
#
Sorting and Grouping Reports
# Order
by or sorting
# Insert
Multiple Grouping
# Use Of
A Parameter To Allow User Choice Of Grouping
# For
Loop, While Loop
# SELECT
FORMULA
#
Conversion Function
# String
Formulas
#
Numeric Formulas
#
Date/Time Formulas
#
Conditional Formulas
# Cross
Tab Layout report
# Linked
Subreports
#
Unlinked Subreports
#
Inserting Subreports
#
Subreport To A New Report
#
Subreport On-Demand
#
Grouped Reports
# List
Reports
# Use
your own SQL
# Using
the If Statement
# Using
the Select Case Statement
#
Summarizing Records within Groups
#
Geographic Mapping
#
Geographic Maps
#
Special Fields
# Number
of days in month in year
# Lines
and Boxes Hide and show
# Add A
Watermark To A Report
# char
("100"+"100") field convert to number to sum
#
Forcing a Page Break (Group dept like)
#
Variable pass in main to sub-report
#
Keeping Sections Together
# Hide
report, column, data,full Box or line
# Hide
duplicate column and add serial no
# Report
only last page - Add Report Header, Report Fooder
#
Suppress And Alert Option
#
Excel/pdf Export from Crystal
#
Changing The Calculation Within The Data Area
#
Generating The Month Name As A Heading
# Totals
On Or Off Top Or Bottom
#
Formatting A Cross Tab And Its Components
# Use Of
Conditional Formatting In A Cross Tab
#
Cross-Tabs With Group Headers Or Footers
#
Placing More Than One Cross Tab On A Report
#
Sorting A Cross Tab By Its Summarized Data
# Cross
Tab Wizard Vs Manually Creation
# A
Quick Chart From A Cross Tab
# Page break after a complete
group
# How to Insert a page Break in Crystal Reports when Page Total equals or greater than certain value
# Insert page breaks between report groups with two-sided printing
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXX STARTING
SCRIPTS XXXXXXXXXXXXXXXXXXXXXXXXXXXXX
# String Manipulation
Formula In Action
uppercase()
lowercase()
propercase()
left(,2)
--- 2 for first letter
right(,2)
--- 2 for last letter
"*************"&
" " & right({fstname},2)
--- creditcard display
mid({fstname},4,5)
--- 4 nunber
theke 5 ta show
len({fstname})
--- length
CStr (CurrentFieldValue,
"#,##,##,##,##,###.00") ---
format Lakhs and not Millions use in Formating Comman-- right of Display X-2
IIF(IsNull({CompanyName}),
"", {CompanyName} & ",")
--- Concatenate fields with null
isnull({Customer.Address2}),
not (isnull({Customer.Address2}))
# Date Convertions
year({DOB})
---
only year show 1997
month({DOB})
--- month show 01
monthname(month({DOB}))
--- month show JAN
day({DOB})
--- day show 23
weekday({DOB})
---
weekday 4
weekdayname(weekday({DOB}))
--- weekdayname Sat
int((currentdate - {DOB})/365.25)
--- age 20 (Calculating Age With Crystal Reports)
cdate({DOB})
# Formula To
Concatenate
fstname
&" "& lstname
fstname
+" "+ lstname
stringVar x;
if not
isnull({Sites.SiteAddress1}) then (
x:= {Sites.SiteAddress1};
if not isnull({Sites.SiteAddress2})
then
x:= x + ', ' +
{Sites.SiteAddress2};
if not
isnull({Sites.SiteAddress3}) then
x:= x + ', ' +
{Sites.SiteAddress3};
) else '';
//
stringvar address := '';
if not
isnull({address.addr_line1}) then address := address & {address.addr_line1};
if not
isnull({address.addr_line2}) thenaddress := address & ', ' &
{address.addr_line2};
if not
isnull({address.addr_line3}) thenaddress := address & ', ' &
{address.addr_line3};
address;
//
Local StringVar strOut;
If Not IsNull({command.title})
then
strOut := {command.title};
If Not IsNull({command.fname})
then
strOut
:= strOut + " " + {command.fname};
if Not IsNull({command.sname})
then
strOut :=
strOut + " " + {command.sname};
If Not IsNull({command.address1})
then
strOut
:= strOut + chr(13) + {command.address1};
If Not IsNull({command.address2})
then
strOut :=
strOut + chr(13) + {command.address2};
If Not IsNull({command.address3})
then
strOut
:= strOut + chr(13) + {command.address3};
If Not IsNull({command.postcode})
then
strOut :=
strOut + chr(13) + {command.postcode};
Trim(strOut)
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
# Break down space
chr(13)
{tbl.first_name}&
&{tbl.last_name} & chr(13)
&
{tbl.surename}
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
# CONDITIONAL
FUNCTIONS
# IF
If {MyDB.ValueId} = 1 then 'write
text one'
else (should
suppress since no text is needed)
If {My.DB.ValueId}
= 2 then 'write text two'
else (should
suppress since no text is needed)
If {My.DB.ValueId}
= 2 then 'write text two'
else (should
suppress since no text is needed)
If
InStr(LowerCase({Employee.Notes}), " he ")>0 Then
"Mr."
Else If InStr(LowerCase({Employee.Notes}), " she ")>0 Then
"Ms."
Else
"";
Local StringVar strFullName;
strFullName := " ";
If{@Title} <> " " Then
strFullName := {@Title} & “ “;
strFullName := strFullName & {Employee.Last Name}
& “, “ & {Employee.First Name};
If {@Suffix} <> " " Then
strFullName := strFullName & “ “ & {@Suffix};
strFullName
If CurrentFieldValue = 0 Then "NONE"
Else ToText(CurrentFieldvalue)
totext(PrintDate) + ' ' +
totext(PrintTime) + ' - ' + PageNofM
If {CUSTOMER.CUSTOMER_NAME} [1 to
2] = "AN" and
ToText({CUSTOMER.CUSTOMER ID})
[2] = "4" then
"TRUE"
Else
"FALSE"
If ({CUSTOMER.CUSTOMER_NAME} [1
to 2] = "AN" and
ToText({CUSTOMER.CUSTOMER ID})
[1] = "4") or
({CUSTOMER.CUSTOMER_NAME} [1 to
2] = "Ja" and
ToText({CUSTOMER.CUSTOMER ID})
[1] = "2") then
"Five star rating
CUSTOMER"
Else
"1 star rating
CUSTOMER"
# CASE
SELECT {field}
Case
<value>:
action
Case
<another value>:
another action
Default:
action;
SELECT {ORDERS.ORDER_AMOUNT}
CASE IS<=500 :
'BRONZE'
CASE IS >100 :
'GOLD'
DEFAULT : 'SILVER'
select {projects.ProjType}
case is like 'S%'
: "'Shopping Center"
case is like 'I%'
: "Industrial"
case is like 'O%'
: "Office Building"
default : "Other"
SELECT {CUSTOMER.FAX} [1 To 3]
CASE
"604","250" : "BC"
CASE
"206","509","360" : "WA"
DEFAULT : "";
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
# Clever Conditional
Formatting Alternating Page Numbers
Bottom Both Side Page number ode
and even
left page number -- formating
--suppress -- remainder(pagenumber,2)<>0
Right page number -- formating
--suppress-- remainder(pagenumber,2)=0
# Clever Conditional
Formatting Banded Report (olter net record color)
Details Section --formating
--Background Color --
if
remainder(recordnumber,2))=0 then
crSilver
else crWhite
# Create Alternate
Page Numbers Through Conditional Formatting
int(pagenumber/2)<>pagenumber/2
---left side
int(pagenumber/2)=pagenumber/2
---right side
# Suppress
# Conditionally
suppress fields in Crystal Reports
1. Right-click the field and
select Format Field
2. Click X+2 next to Suppress
3. Enter the formula criteria to
suppress (No IF-THEN statement is required)
{ADDRESS_FIELD}<>"Chicago"
/
IF (field1 = "A" and
field2 > 10) or IsNull(field3) THEN True ELSE False
# Conditionally
suppress Section in Crystal Reports
if PageNumber*6-recordnumber=1
then false else true
# conditionally
suppress a drawing box
# conditionally
suppress a Line
# conditionally
suppress a text
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
# Using Formula Fields
While Grouping
currentdate - Maximum ({datecolse},{grouptitle})
--- group
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
# Advance
# Parameters
# Parameter Fields Used To Customize
Formatting
--Create Parameter
FontChoose
--Create Font Formaula Editor and
paste
if
{?FontChoose}="Arial" then
"Arial"
else
(if
{?FontChoose}="Verdana" then
"Verdana"
else
(if
{?FontChoose}="Calibri" then
"Calibri"
else
(if
{?FontChoose}="Comic" then
"Comic
Sans Ms"
else
"Helvetica"
)
)
)
# Letting The User
Change The Color Of Banded Reports
--Create Parameter FontColor
--Create Font Formaula Editor and
paste
if
{?FontColor}="Red" then
crRed
else
(if
{?FontColor}="Gray" then
crSilver
else
(if
{?FontColor}="Pink" then
crFuchsia
else
(if
{?FontColor}="Black" then
crBlack
else
(crNoColor
)
)
)
)
--Details Background Color
if int(recordnumber/2)
=(recordnumber/2) then
{@f_FontColor}
else
crnocolor
-- Details Text Select go to Font
Formula Editor
if {@f_FontColor}= crBlack and
int(recordnumber/2)=(recordnumber/2) then
crwhite
else
crBlack
# Parameter Controlled Order By
if {?sortParam}="EmpID"
then
{X_DAILY_ATTENDANCE_RPT.EMP_NO}
else
(if
{?sortParam}="Position" then
{X_DAILY_ATTENDANCE_RPT.POSITION_NAME}
else
{X_DAILY_ATTENDANCE_RPT.STATUS}
# Parameter Masks To Control User
Input
No..
# Alerts
# Adding And Triggering A Report
Alert
{X_DAILY_ATTENDANCE_RPT.STATUS}="PRESENT"
and not isnull({X_DAILY_ATTENDANCE_RPT.IN_TIME}) and not
isnull({X_DAILY_ATTENDANCE_RPT.OUT_TIME})
# Using A Parameter Value With A
Report Alert
--Formula Editor
"Employee
Present Status " & {?status}
--Condition
{X_DAILY_ATTENDANCE_RPT.STATUS}={?Status}
"Status is on" & totext(currentdate)
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
# Charts
--#
left(ToText(year({tbl.date})),1) & mid(ToText(year({tbl.date})),3,3)
--#
monthname(month({tabl.date}))
--- january month
--# left(monthname(month({tabl.date})),3)
--- jan month
--#
count({tbl.emp_id})<>0
--- Report footer x-2 and Text -- No Data found
--# Formula and
Parameter cerate---
if {?crossTabChoice}
="Item" then
{tbl.itemTitle}
else
if {?crossTabChoice}
="Brand" then
{tbl.Brand}
else
if
{?crossTabChoice} ="Month" then
monthname{tbl.Month}
else
if
{?crossTabChoice} ="Country" then
{tbl.country}
else
totext({tbl.itemoid})
# Advance
Programming
--Declaring Variables To Use In
Formulas
currencyVar
totVal:={tbl.Price}*{tbl.qty};
or
currencyVar totVal;
totval:={tbl.currPrice}*{tbl.qty};
--Application Of A Variable In A
Report
currencyVar q1;
if
datepart("q",{tbl.date})=1 then
q1 :=q1+({tbl.price}*{tbl.qty})
else
q1 :=q1
Multi Pass Reporting
Explained
Altering The Multi
Pass In A Formula
Outputting Multiple Variable
Values From One Formula --quater value 1-4 in years
-- formula
resetQueratr
WhilePrintingRecords;
currencyVar q1:=0;
currencyVar q2:=0;
currencyVar q3:=0;
currencyVar q4:=0;
----formula
QuarterVals
WhilePrintingRecords;
CurrencyVar q1;
CurrencyVar q2;
CurrencyVar q3;
CurrencyVar q4;
if
datepart("q",{tbl.date})=1 then
q1 :=q1+({tbl.price}*{tbl.qty})
else
q1:=q1
;
if
datepart("q",{tbl.date})=2 then
q2 :=q1+({tbl.price}*{tbl.qty})
else
q2:=q2
;
if
datepart("q",{tbl.date})=3 then
q3 :=q3+({tbl.price}*{tbl.qty})
else
q3:=q3
;
if
datepart("q",{tbl.date})=4 then
q4 :=q4+({tbl.price}*{tbl.qty})
else
q4:=q4
;
"Q1:" &
totext(q1) & ".Q2:" & totext(q2) & ". Q3:"
& totext(q3) & ". Q4:" & totext(q4);
# Pass Data Between Sub And Main
Report Using Variables
Using Shared Variables To
Exchange Data With A Sub Report
-- Sub report Formula Editor name
--Totalsales
shared currencyVar totalsales :=
Sum({@LineTotals});
-- Main report Formula Editor
name --Totalsales
shared currencyVar totalsales;
-- Main report Formula Editor
name --GrandTotal
currencyvar grandTotal;
shared currencyvar totalSales;
grandTotal := grandTotal
+totalSales;
User Defined Functions
--for date calculation
--Custom Function
Function
(dateTimevar x)
int ((currentdate
-x)/365.25);
--go to
formula
Age({tbl.dateDOB})
--custom function --Weekday
--Name of function
wkday
Function
(datetimevar z)
left(weekdayname(weekday(z)),3)
--go to
formula
wkday({tbl.dateDOB})
# SQL And
Commands
Standard Query
Language (SQL)
Using Your Own SQL As
A Reports Data Source
Adding Parameters To
User Defined SQL Commands
# Summary and Beyond
Investigating The Data
Map Tool
Distribute Crystal
Reports Viewer To Non Developers
# OTHERS
# Suppressing Report
Footer on Drilldown
right click on the field you want
to suppress,
select format field
select common
select tghe suppress formula and
use
drilldowngrouplevel<>0
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX SAP
CRYSTAL REPORT XXXXXXXXXXXXXXXXXXXXXXXXXX
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX SAP
CRYSTAL REPORT XXXXXXXXXXXXXXXXXXXXXXXXXX
Select {@Age}
Case Is
< 19 :
"0-19"
Case Is
> 19, Is < 34 :
"20-34"
Case Is
> 34, Is < 49 :
"35-49"
Case Is
> 50, Is < 64 :
"50-64"
Case Is
> 64:
"65+"
WhileReadingRecords;
WhileReadingRecords;
Select {Customer.Country}
Case "Canada","Mexico","USA":
"North America"
Case "Australia", "New Zealand":
"Oceania"
Default:
"Outside North America and
Oceania";
WhileReadingRecords;
If {Orders.Order Amount} > 3000
Then
{Orders.Order
Amount}
Else
0;
"Employee ID:
" + CSTR({Employee.Employee ID},0)
If {?Language} =
"English" Then
"Country"
Else If {?Language} = "French" Then
"Pays"
Else If {?Language} = "Italian" Then
"Paese";
Local StringVar CountryString :=
"";
Local NumberVar i;
For i := 1 To
Ubound({?Countries}) Do
(
CountryString :=
CountryString + ", " + {?Countries}[i]
);
CountryString
(If {?Countries} = "*"
Then
True
Else
{Customer.Country} =
{?Countries};)
"(" + Left
(CurrentFieldValue,3) +
") " +
Mid(CurrentFieldValue,4,3) +
"-" +
Right(CurrentFieldValue,4)
# Some quick examples
of SQL expressions that can be used in place of Crystal formula syntax:
Crystal Formula Syntax
SQL Expression (SQL Server Syntax)
IF/THEN/ELSE
CASE [Database Field]
Or
WHEN Condition THEN
Value1
SELECT CASE
ELSE Value2 END
Concatenate
CONCAT([Database(x + y) Field1], [Database
Field2])
MONTH(datefield)
MONTH([Database Field])
# As with brackets,
symbols in the formula language (or in the icons) have specific meaning. To
shed some
light on this, check
out the listing of symbols that represent different field types in Crystal
Reports:
@
= Formula
{@Formula} is a formula field
?
= Parameter
{?Param} is a parameter field
#
= Running Total
{#RunTtl} is a running total field
Σ
= Summary
ΣfieldName is a summary field on the report
%
= SQL Expression {%SQL}
is a SQL expression field
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX Web
Site Link XXXXXXXXXXXXXXXXXXXXXXXXXX
"Sale Period from " + ToText ({X_SALESUMMARYMIX_REP.START_DATE}, "dd/MM/yyyy") + " to " + ToText ({X_SALESUMMARYMIX_REP.END_DATE}, "dd/MM/yyyy")
