Crystal Reports Notes
Crystal Reports, what a love-hate kind of thing.
A few notes - watch for syntax that has been eaten by this UI:
-
Comment syntax is //, just like Java/PHP/C++ etc. Block comments with /* */ are OK too.
-
? is parameter
-
@ is calculated
-
Forums: http://www.tek-tips.com/ - http://forumtopics.com
-
Other forums on the Crystal Decisions website. You have to hit "support" then "participate in user forums". It's not the best interface... nor is it easy to find, so here's the URL: http://support.businessobjects.com/forums/?ref=default.asp_selectlist
-
When connecting to a Microsoft Access database, make sure you use UNC paths, e.g. \\server\shared\file.mdb. NOT a mapped drive. It can cause grief...
-
Report Design Export - You can export a text file that includes the definitions for everything. Nice if you're trying to track down variables used in a complex report: File > Print > Export > Select "Report Definition" from the format drop down list.
Found on this page: http://www.tek-tips.com/viewthread.cfm?SQID=790518&SPID=149&page=1
-
Limit number of pages in report by adding a formula to the suppress field of the sections: e.g. 'PageNumber > 2'
http://www.tek-tips.com/viewthread.cfm?SQID=104842&SPID=149&newpid=149&page=1
-
These folks sell add-ons (free demos, and a few utilities too) to Crystal Enterprise to make managment a LOT easier:
http://www.apos.on.ca/sections/07-SoftSol-Downloads.shtml
-
Schedule of a Crystal Enterprise report can be changed from the "Schedule Manager" -- I think it's new in version 9.
-
Running Total workaround - (or do something much more complex than running totals):
-
http://www.crystalkeen.com/articles/crystalreports/runningtotalvar.htm
-
A formula like this in a header row to reset the count: WhilePrintingRecords; NumberVar Amount :=0;
-
A formula like this to do the running total: WhilePrintingRecords; NumberVar Amount := Amount + ;
-
Lastly, to get the value without incrementing again: WhilePrintingRecords; NumberVar Amount;
-
There is also an example in the CE9 User Guide. Search for WhilePrintingRecords. This is commonly referred to as a three part or three function trick.
-
Formula for setting a Formula Field to a date relative to the runtime - "CurrentDate - 1"
-
BeforeReadingRecords; WhileReadingRecords; WhilePrintingRecords; EvaluateAfter(); - tell CR in which pass to do things. Term is "Evaluation Times and the Multi-Pass Reporting Model"
-
Crystal Syntax Formula w/variable (very poorly written)
-
Local StringVar str:= Trim();
-
// str := str & "TEST"; -- worked
-
If <> "" Then
-
( str := str & ", " & ;)
-
Else
-
-
If <> "" THEN
-
( str := str & ", " & ;)
-
Else
-
-
// return combined string
-
str;
-
Sub Reports - good list of when to use them - http://www.dotnetglobe.com/2008/02/to-subreport-or-not-to-subreport.html
-
-
Date Syntax - if { DocDate } = Date ( 2011,10,01 ) THEN 'Text' else { TaxIdNum }
-
-
To make part of the SELECT CRITERIA optional, use hasvalue() - CR 2008 or newer I think - e.g.
(not(hasvalue( { ? Group } ) ) or { Groups.Code } = { ? Group } )
-
-
-
-
Trick to have DISTINCT COUNT skip counting some rows - @null has nothing in it - // Do DISTINCT COUNT to count # of stores.
-
-
IF ( = "A" AND = "N") THEN
-
-
ELSE
-
// dummy variable to keep distinct count from counting the row ("" would get counted).
-
-
-
Trick to have DISTINCT COUNT skip counting some rows - @null has nothing in it - // Do DISTINCT COUNT to count # of stores.
-
-
IF ( = "A" AND = "N") THEN
-
-
ELSE
-
// dummy variable to keep distinct count from counting the row ("" would get counted).
-
-
Tags: ce, cr, crystal, reports,