System Report Rules

  1. Any text used inside a formula must be wrapped in the translate() function. This function can be found in the Crystal Repository.
  2. Any Date or DateTime field must be set to use the System Default Short Format.
  3. When the $ is used for showing currency the System Default Currency Format must be used. The Crystal function ccur() can be used to convert a value to currency.
  4. When using the System Header and Footer from the Crystal Repository, they must be re-imported after previewing a report in crystal. Otherwise, the header will no show properly in Control.
  5. When using a SQL command in crystal, all tables must use the with(nolock) option. Example. Select * from Account with(nolock) where Account.ID 1000
  6. Formulas should not be placed inside of Text Boxes. Control cannot translate those.

Functions used for Report Coloring

Formula NameUsed WhenFormula
DetailRowColorShowing Detail RowIf (RecordNumber mod 2 1) then Color(201,209,237) else crWhite
Group1HeaderColorShow Group Header with DetailsColor(96,120,202)
Group1FooterColorShow Group Footer with DetailscrWhite
GroupDetailRowColorShow Group Header or Footer as a Detail Row with no DetailsIf (GroupNumber mod 2 1) then Color(201,209,237) else crWhite
Group2HeaderColorShowing 2nd Group HeaderColor(128,147,213)
Group2FooterColorShowing 2nd Group FootercrWhite
Group3HeaderColorShowing 3rd Group HeaderColor(167,181,226)
Group3FooterColorShowing 3rd Group FootercrWhite

Control ID Parameters
Control understand the following type of ID parameters and can create appropriate user interface sections for the reports dialog.

  • STOREID
  • ACCOUNTTYPE
  • EMPLOYEEID
  • SALESPERSONID
  • INSTRUCTORID
  • BANKACCOUNTID
  • PAYMENTMETHODID
  • ORDERID
  • ESTIMATEID
  • LINEITEMID
  • SERVICETICKETID
  • SERVICETICKETITEMID
  • PRODUCTID
  • CHECKID
  • CONTACTID
  • PRODUCTCATEGORYID
  • CUSTOMERID
  • PARTID
  • LOCATIONID
  • CONTACTACTIVITYID
  • STATIONID
  • JOURNALID
  • DIVISIONID
  • PURCHASEORDERID
  • PURCHASEORDERITEMID
  • BILLID
  • RECEIVINGDOCID


Using multiple parameters in the Record Selection Formula:
Example – A report with Three Parameters:
Support that you want to create a report that allows for 3 parameter inputs:

  • Customer_CustomerID (a -1 value indicates to show all)
  • Order_OrderID (a -1 value indicates t show all)
  • Date (date range)

In order for the Selection Formula to filter using all these parameters correctly, use the following code in the Crystal Reports filter:

 \\
( if ({?Customer_CustomerID}  -1 OR {//TableName//.AccountID}  {?Customer_CustomerID} ) \\
AND ( {?Order_OrderID}  -1 OR {//TableName//.TransHeaderID}  {?Order_OrderID} ) \\
AND ({//TableName//.OrderDate}  {?Date}) \\
then TRUE \\
else FALSE \\
<code> \\
Range Parameters  \\
Crystal Reports provides mechanisms to compare values to ranges in most places within the program. The primary exception to this rule is within Command SQLs. It is not possible to test if a value is within a range represented by a parameter using Crystal Reports XI within a SQL command. \\
Adding the prefix Fx_RangeStart_ or Fx_RangeEnd_ to a parameter name (e.g. Fx_StartRange_GLDate, Fx_EndRange_GLDate) gives it special meaning and is used to split the ranges in discrete values \\
Generally speaking, as Fx_RangeStart_ and Fx_RangeEnd_ should always go together. If it is not already there, Control creates a parameter without the suffix. It assumes the value will cover a range (usually a date range), and then assigns the starting and ending values to the Fx_RangeStart_ and Fx_RangeEnd_ parameter. \\
It may be necessary to use ranges in Crystal Commands to process SQL queries Server Side. \\
|| **Parameter Name** || **Type** || **Prompt Text** || **Mult** || Rng ||
|| Fx_RangeStart_ || Depends on Matching Parameter ||   ||   ||   ||
|| Fx_RangeEnd_ || Depends on Matching Parameter ||   ||   ||   ||
|| Fx_AsList_ || String ||   ||   ||   ||
||   ||   ||   ||   ||   ||
Example of Fx_Range parameters:
In a SQL command, you might use the query to select all of the records within a certain range. The following code
[[code_formatsql|code format"SQL"]]

Select * From AnyTable

Where AnyTable.Date between Fx_RangeStart_TheDate and Fx_RangeEnd_TheDate

<code>
List Parameters
When designing a Crystal Report, if you are using report parameters in the SQL Command, make sure the default value of the parameter results in a valid SQL syntax.
Adding the prefix Fx_RangeStart_ or Fx_RangeEnd_ to a parameter name (e.g. Fx_StartRange_GLDate, Fx_EndRange_GLDate) gives it special meaning and is used to split the ranges in discrete values
Example of Fx_Range parameters:
In a SQL command, you might use the query to select all of the orders selected by the user (from Order Explorer, for instance):

Select * from TransHeader

Where ID in ({$FX_AsList_Order_OrderID})

Important Note: Since this is not a valid SQL statement, it causes problems when Control creates its initial connections to the data, which is before the parameter values are set to the passed values. Instead of leaving the parameter default set to empty string, set it to 0 (or any other number) and that will get around the issue. System Parameters The following pre-defined system parameters are also available within Crystal:

  1. Store_propname would return the published property propname for the current store object.
  2. Employee_propname would return the published property propname for the logged in employee..
  3. Session_propname would return the published property propname for the session object.
  4. Application_propname would return the published property propname for the application object.

Predefined Store Parameters

  • AutoDecLunch: Boolean
  • BankAccountNumber: string
  • BankCode: string
  • BankName: string
  • BillingOptionOverridden: Boolean
  • ChamberCommerceNumber: string
  • CompanyName: string
  • DefaultAreaCode: string
  • DefaultCountryCode: string
  • DefaultDueTime: TTime
  • DepositMinimum: Extended
  • EmailAddress: string
  • EquipmentCostMultiplier: string
  • FaxFormattedNumber: string
  • FinanceChargeAccountName: string
  • FreightCostMultiplier: string
  • HoursBeforeLunch: Integer
  • InvoiceAsOrderNumber: Boolean
  • InvoicePrefix: string
  • InvoiceTemplateName: string
  • LaborCostMultiplier: string
  • LogoID: TIDRecord
  • LunchMinutes: Integer
  • MaterialCostMultiplier: string
  • MaxClockOutTime: TTime
  • MinClockInTime: TTime
  • MktgListCode1Name: string
  • MktgListCode2Name: string
  • OtherCostMultiplier: string
  • OutsourceCostMultiplier: string
  • PhoneFormattedNumber: string
  • PrimaryFormattedNumber: string
  • PrimaryNumberTypeText: string
  • RoundingDigits: Integer
  • RoundingFactor: Double
  • RoundingOption: TRoundingOption
  • RoyaltyFee1: Double
  • RoyaltyFee1Name: string
  • RoyaltyFee2: Double
  • RoyaltyFee2Name: string
  • ScreenTemplateName: string
  • SecondaryFormattedNumber: string
  • SecondaryNumberTypeText: string
  • StarShipProductName: string
  • StoreLogoPath: string
  • StoreNumber: string
  • TagLine: string
  • TaxIdentifier: string
  • TipsPayOffAccountName: string
  • TipsRevenueAccountName: string
  • TrackBillingTime: Boolean
  • UseTaxClassLookup: Boolean
  • WebAddress: string
  • WorkOrderTemplateName: string
  • WriteOffAccountName: string

Predefined (Logged In) Employee Parameters

  • Birthdate: TDate
  • City: string
  • Country: string
  • County: string
  • Department: string
  • DiscountLevel: Double
  • EmployeeStatus: TEmployeeStatus
  • GroupName: string
  • HireDate: TDate
  • IsClockedIn: Boolean
  • IsInstructor: Boolean
  • IsLoggedIn: Boolean
  • IsSalesperson: Boolean
  • IsWorkerResource: Boolean
  • MailCity: string
  • MailCountry: string
  • MailCounty: string
  • MailPostalCode: string
  • MailState: string
  • MailStreetAddress1: string
  • MailStreetAddress2: string
  • PostalCode: string
  • PricingLevel: Double
  • PricingLevelName: string
  • ReportMenuTemplateName: string
  • ReportsToName: string
  • ShowOnActivityManager: Boolean
  • ShowOnLineItems: Boolean
  • ShowOnTimeClock: Boolean
  • State: string
  • StreetAddress1: string
  • StreetAddress2: string
  • TaxNumber: string
  • TerminationDate: TDate
  • TimeClockStatus: string

Predefined Application Parameters

  • AppType: TCyrAppType
  • BaseLanguage: string
  • DatabaseCount: Integer
  • ExpirationDate: TDate
  • HelpFileName: string
  • IsHidden: Boolean
  • IsServerApp: Boolean
  • LongVersion: string
  • ReleaseNumber : Word
  • ServerName: string
  • ServerVersion: string
  • TranslationLanguage: string
  • TrayApp: Boolean
  • Version: string
  • VersionNo : Extended
  • VersionPostfix: string
  • WebEnabled: Boolean

Predefined Session Parameters

  • DatabaseName: string
  • DatabaseUNCPath: string
  • DisplayName: string
  • IsLocked: Boolean
  • LoggedIn: Boolean
  • LoginFullName: string
  • LoginName: string

Predefined Option Parameters

You can use predefined Option_xxxx parameters to pull information from the user or store options. This is helpful for configuring general purpose reports to reflect differences in user settings, such as suppressing the Inventory from a report is a user does not have inventory enabled. Control will automatically fill in an parameter that starts with Option_ with the option value, if defined.

Any parameter starting with “Optionckgedit> will be hidden from the user. Some of the predefined options parameters are (Option_ will need to be added to the front of the parameter name)…

  • UseDivisions
  • UseVariations
  • SalespersonCount
  • CompanySingular
  • CompanyPlural
  • ContactSingular
  • ContactPlural
  • SalespersonSingular
  • SalespersonPlural
  • CourseSingular
  • CoursePlural
  • OrderSingular
  • OrderPlural
  • EstimateSingular
  • EstimatePlural
  • WorkAssignmentSingular
  • WorkAssignmentPlural
  • IsClientSingular
  • IsClientPlural
  • ContractPeriodSingular
  • ContractPeriodPlural
  • CourseEventSingular
  • CourseEventPlural
  • CourseSectionSingular
  • CourseSectionPlural
  • CompanyOrigin
  • ProofDateName
  • ContactPositionSingular

When Control comes across a parameter name that starts with “Optionckgedit>, it will strip off the “Option_” to get the option name. Then it will look for an option with that name in the user options. If it doesn't find one, it will check the stores. If it still doesn't find one it will check the application options. If it is still not found it returns empty string.

Module Authorized Parameters

You can add a Boolean Parameter to see if a certain Module is Authorized in the Key to hide or show certain areas. The parameter needs to start with ModuleAuthorized_. Any parameter starting with this will be hidden from the user.

The current modules names are:

  • Estimating
  • Work Order
  • Invoicing
  • Payment Tracking
  • Sales Management
  • Marketing
  • Parts
  • Macros
  • Customizable
  • Inventory
  • Credit Card Processing
  • Shipping
  • Services Tickets
  • Bar Coding
  • Courses
  • AP and GL
  • Online Tax Lookup
  • Production Terminal


You could leave a comment if you were logged in.