Report Rules, Fx_ Functions, and System Parameters

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 Name
Used When
Formula
DetailRowColor
Showing Detail Row
If (RecordNumber mod 2 = 1) then Color(201,209,237) else crWhite
Group1HeaderColor
Show Group Header with Details
Color(96,120,202)
Group1FooterColor
Show Group Footer with Details
crWhite
GroupDetailRowColor
Show Group Header or Footer as a Detail Row with no Details
If (GroupNumber mod 2 = 1) then Color(201,209,237) else crWhite
Group2HeaderColor
Showing 2nd Group Header
Color(128,147,213)
Group2FooterColor
Showing 2nd Group Footer
crWhite
Group3HeaderColor
Showing 3rd Group Header
Color(167,181,226)
Group3FooterColor
Showing 3rd Group Footer
crWhite


Control ID Parameters


Control understands 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


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_<<parameter>>
Depends on Matching Parameter



Fx_RangeEnd_<<parameter>>
Depends on Matching Parameter



Fx_AsList_<<parameter>>
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
SELECT * FROM AnyTable
WHERE AnyTable.DATE BETWEEN Fx_RangeStart_TheDate AND Fx_RangeEnd_TheDate


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 "Option_" 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 "Option_", 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

See Also