Introduction
The dynamic report parameter window is used in custom reports and some built-in reports to provide a customizable filter for report data. It is not available in versions of ServiceLedger prior to 5.5.0.54, and may not contain all the functionality mentioned here in versions prior to ServiceLedger 2007.
Parameter Definitions
Parameter definitions are stored in the Global Preferences window, using option names that start with 'report.definition.' followed by the name of the parameter window. Some built-in parameter windows exist (such as 'quote_list'), and the application will also check for custom definitions 'report_1' through 'report_9' when choosing custom reports from the Report menu. To add a custom definition if one isn't present, you'll need to add a global preference with a name such as 'report.definition.report_1' and populate it with the actual definition syntax as explained below.
Definition Syntax
Parameter windows are defined using a combination of fragmentary ServiceLedger XML, SQL, and Crystal Syntax. While the majority of a given definition is XML, the values of certain attributes or the text between certain tags will be in one of the above two other languages. Where any XML reserved characters (<, >, ") are used in these areas, they must be encoded (<, >, &qout;) to make sure the overall definition is valid XML.
An example of a typical definition follows:
<reportfile>account_agreement_list.rpt</reportfile>
<reportname>Agreement List</reportname>
<header>Agreement List</header>
<daterange label="Create Date" default="last month"
filter="agreement.create_date"/>
<combo label="Agreement Status" values="select status_number as name,
status_id as value from agreement_status"
filter="agreement.status_id"/>
<text>Select the desired options, then click OK.</text>
A report definition will consist entirely of a series of XML elements that provide information about, control, or specify parameters for the report. These elements will be either control, information, or filter elements. The purpose of each of these kinds of element, along with a list of the actual elements for each type, is included below.
The following control elements can be included anywhere in a report definition, are usually at the top, and are not visible to the user:
- reportname - passed to Crystal for the ReportName variable
- reportfile - the actual report file in ...\ServiceLedger\Report\ to be used
- filter - a direct filter that will always be applied, regardless of other choices Contents should be in Crystal Syntax
- ?{int|date|string}n - passed directly to Crystal Reports. Filters the nth crystal reports parameter in the report; please make sure to use the correct type! Contents should be in Crystal Syntax
The following information elements are visible to the user in the report parameters window, but have no effect on the report or report filter:
- header - header
- text - one line of text
- br - blank line of space
Finally, these filter elements are shown as user-input controls, and can be used to actively affect the report or filter
- date - single date for before, on, or after
- daterange - range of two dates for between
- combo - dropdown list to filter to a single value
- check - checkbox for filters or parameters
- string - text field to search for "includes" matches
- number - number field to choose exact matches
All filter elements have both a label attribute and a filter attribute. The label is displayed in the parameter window to the left of the control. The filter determines what part of the report the control will affect. Filter can have one of the following values:
- field name - control will filter the field given by augmenting the report's selection formula
Example: ticket.ticket_id, which will add and {ticket.ticket_id} = n to the report selection formula, where n is the chosen work order in a combo control if one is chosen.
- parameter name - control will pass its value to the Crystal Parameter specified, which must be specified by type and number. Syntax is ?{int|date|string}n, where n is the nth crystal report parameter in the report, starting at 0.
Example: in a report with 3 parameters, if the second parameter is a DateTime parameter, you can fill its value by setting the filter attribute to ?{date1}.
- element name - affects the control element specified. For instance, a check element could be used to select one report or another by having its values set to the two different possible reports and its filter attribute set to reportfile.
- direct - The control's value is applied directly to the selection formula. Contents should be in Crystal Syntax
When the above filter attribute is applied, it will depend on the value of the control. For date and daterange controls, the control's value is the date or dates shown. For the combo control, the value is determined by its values attribute, and for check controls, it is determined by the checked (or value) and unchecked attributes.
In addition to the above two attributes (label and filter), each of the filter elements requires its own special attributes in order to function. Below is an in-depth description of each filter element and how what attributes are used to control its behavior.
The date control:
Required attributes:
- label - caption to be displayed by the control
- type - one of {before|after|to|from|on}
- filter - field, parameter, or element to be filtered
The default attribute can be specified for date controls, but the behavior of this attribute is subject to change in the near future. At present, only literal dates can be specified.
Example:
<date label="Created Before" type="before" filter="client.create_date"/>
Filters accounts to those created before (not on) a specified date. For inclusive filters, use from and to instead of before and after.
The daterange control:
Required Attributes:
- label - caption to be displayed by the control
- filter - field, parameter, or element to be filtered
Optional Attributes:
- default - {this|next|last} {day|week|bizweek|month|year} [rel]
- checked - causes the daterange to be checked by default
- required - causes the daterange to be always active (no checkbox)
- expand - applies the date filter as a half-open range, incrementing the right date by one day. *
Example:
<daterange label="Expiration" default="next month rel" filter="agreement.end_date_time"/>
Filters down to agreements expiring in a certain range, defaulting to the next month relative to today.
* Include the expand attribute and set it to true if the report is designed to expecte a half-open range.
For example, if the expand attribute is set and you choose a date range of 1/1/2015 to 1/7/2015, the date filter passed to the report will be: 1/1/2015 >= record.date_field < 1/8/2015. This is useful if the filter field can include times of day and you want to include records throughout the entire day of 1/7/2015 rather than only those on exactly 1/7/2015 at 12:00:00am.
The combo control:
Required attributes:
- label - caption to be displayed by the control
- values - SQL query including which values should be allowed for filtering
- filter - field, parameter, or element to be filtered
The values attribute usually contains a simple sql query from an existing table, such as "select model_number as name, model_id as value from equipment_model", but can also contain a manually defined query:
"select 'All Accounts' as name, 'all' as value
union select 'Active Accounts', 'active'
union select 'Prospects', 'prospect'"
Optional Attributes:
- nonull - ordinarily, an extra option is presented with the text '<All>' that prevents the filter from being applied. If a value must be chosen, specify nonull="true"
- nulltext - overrides the default '<All>' text with the value you provide.
- default - default value or name to be chosen. Some known bugs exist with specifying defaults by name in versions prior to 5.5.0.76
Example:
<combo label="Account Type" values="select type_number as name, type_id as value from client_type" nonull="true" default="prospect" filter="client.type_id"/>
Introduces a required account type filter, with Prospect chosen as the default type. The value (type_id) of whichever type is chosen (type_number) is passed to the report filter.
The check control:
Required attributes:
- label - caption to be displayed by the control
- filter - field, parameter, or element to be filtered
Optional attributes:
- checked - value when checked (defaults to "1")
- unchecked - value when unchecked (defaults to "0")
The default attribute can be specified for check controls, but the behavior of this attribute is subject to change. At present, it can be used to have the box be automatically checked by setting it to be equal to the checked attribute, but this usage has been deprecated.
Example:
<check label="Show percent change for each month" checked="1" unchecked="0" default="1" filter="?int0"/>
Passes either 1 or 0 a Crystal Reports number parameter in an imaginary custom report.
The string control:
Required Attributes:
- label - caption to be displayed by the control
- filter - field, parameter, or element to be filtered
If the filter attribute is a field, the parameter will be set up to filter the report based on that field matching the entered text. The text is matched with Crystal Reports' LIKE operator, which means that you can use the asterisk character (*) to indicate a wildcard match, if desired. An example using wildcards to search for a record would be entering "*John*" in a string control filtering on a name field to filter the records down to just those in which the first or last name included "John", such as "John Smith" or "Henry Johnson".
Example:
<string label="City" filter="ticket.site_city"/>
Allows you to print only jobs with a matching site city in an imaginary custom report.
The number control:
Required Attributes:
- label - caption to be displayed by the control
- filter - field, parameter, or element to be filtered
If the filter attribute is a field, the parameter will be set up to filter the report based on that field matching the entered number exactly. The number must be an exact match and may not include commas or currency symbols. For example, when filtering on a currency field, enter '1200.00' or just '1200' to match records that would display '$1,200.00'.
At this time, filtering number ranges are not supported.
Example:
<number label="Total" filter="invoice.total"/>
Allows you to print only invoices with a matching total in an imaginary custom report.
More Information
Basic knowledge of Crystal Reports and the ServiceLedger database schema can be very helpful in crafting effective report parameters. Information on these topics is available at http://support.serviceledger.com/support/schema/
An understanding of the XML standard is a prerequisite to most automation and customization in ServiceLedger. Numerous quick reference guides to using XML are available on the internet. One decent guide is at http://www.informit.com/guides/content.asp?g=xml&seqNum=3 | 
