Differences

This shows you the differences between two versions of the page.

Link to this comparison view

report_rules_fx_functions_and_system_parameters [2019/01/17 09:04]
 report_rules_fx_functions_and_system_parameters [2019/01/17 09:04] (current)
Line 1: Line 1:
 +{$page} 
 +
 +
 +
 +System Report Rules 
 +  - Any text used inside a formula must be wrapped in the translate() function. This function can be found in the Crystal Repository.
 +  - Any Date or DateTime field must be set to use the System Default Short Format.
 +  - 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.
 +  - 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.
 +  - 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
 +  - 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: \\ </code> \\ ( 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):
 +
 +
 +
 +</code>
 +
 +
 +
 +Select * from TransHeader
 +
 +
 +
 +Where ID in ({$FX_AsList_Order_OrderID})
 +
 +
 +
 +</code>
 +
 +
 +
 +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:
 +  - Store_propname would return the published property propname for the current store object.
 +  - Employee_propname would return the published property propname for the logged in employee..
 +  - Session_propname would return the published property propname for the session object.
 +  - 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
 +
 +
 +