Report Form Designer

Report forms are user-designed report definition screens for users to provide criteria when generating Crystal Reports. See Designing user search forms.

The Report Form Designer is a User Form Designer program that allows you to create or modify report forms. The program is divided into the following components:

Field	Description
Control & Property Definition	Allows you to build forms using a range of control objects and specify properties on these objects.
Editable Objects and SQL Syntax	Allows you to link objects to the database fields used in a report and then place conditions on these fields. Only one object can be edited at a time on the Report Form Editor; however a grid is used to display all the objects, properties and related SQL Server syntax.

Report Form Designer window key fields and buttons
Fields

Field	Description
Seq	A sequence number is used to order and uniquely identify each of the objects that are used on a form. The first object to be added to a blank form is given the sequence number of 1 and then subsequent additions are numbered accordingly.
Source	As per the Report Selector, Report Forms can belong to various resource types. All forms built by non-Synergetic Management Systems staff are denoted by Site or Stand resource type.
	Option	Description
	Site	User-defined objects. All custom reports and alterations of Synergetic Management Systems reports must be saved as this resource type. Indicated in the grid by S.
	CDA	Synergetic Management Systems defined objects. Indicated in the grid by Z.
	Stand	Customised reports that are used in Standalone versions of Synergetic. That is, Assessment/Reporting Crystal Reports. Indicated in the grid by L.
	Std CDA	Synergetic Management Systems reports used in standalone versions of Synergetic. Indicated in the grid by Y.

Buttons

Button	Description
	Allows an object to be inserted into an existing sequence of objects after the selected object. It forces the sequence number of subsequent objects to be updated.
	Inserts an object before the selected object and moves the following lines down.
	Delete the object and all its related properties that are highlighted in the above grid.
	Save the changes you have made to the user form or report selection window.
	As each object is added to the form with its accompanying SQL syntax, the SQL query in the Crystal Report can be modified by the objects added. The Crystal Report is used to return the data in the report. Select this option to view the basic query extracted from the report before the custom selections are applied.
	Calls the report definition screen and allows it to be tested.

Control and Property Definition fields

Field	Description
Type	Used to specify the type of object used on the form. That is, Label, ComboBox, GroupBox, Button and so on.
Parent	Parent field is a numeric field used to group objects together. GroupBoxes are objects that are used to group like objects together. The parent object for each GroupBox is generally the form, which is indicated by the parent number of 1. Objects contained inside this GroupBox have a parent number of 2. Each GroupBox is then given a sequential parent number and so objects included within these boxes are assigned that parent number. For example, Synergetic Management Systems report definition screens generally group objects according to whether selections are required or optional. The first GroupBox, titled Required Selections, has a parent number of 1, and all objects inside have a parent number of 2. The second GroupBox, titled Optional Selections has a parent number of 1 and all objects within this GroupBox have a parent number of 3.
Top Rel & Left Rel Right Rel	Position of an object on a form is determined by its distance relative to the location of the preceding object. The sequence number therefore plays an important role in governing this. The Top Rel field determines the vertical distance that an object is placed on the form below the preceding object in the sequence. The Left Rel Right field determines the horizontal distance to the right that an object is placed on the screen relative to the rightmost edge of the previous object in the sequence. Units of distance are measured in pixels.
Label Caption	Optional setting useful for describing objects on the form. Some object types, such as labels and checkboxes, do not need a label caption here, as their captions are specified in the Additional Properties field.
luSQL or luTable	When a DBLookupComboBox control or similar object is selected from the Type drop-down list, these two fields become available. The luTable option allows the selection of a lookup table from the Synergetic database to be linked to the DBLookupComboBox control. For example, luCampus. The luSQL option allows you to query a table using an SQL statement to populate the DBLookupComboBox. This is useful for lookup tables that have more fields than a code and description, such as luYearLevel. A simple statement, such as the one below, can be used to only show certain fields in the DBLookupComboBox. _{SELECT DISTINCT Code, Description, YearLevelSort} _{FROM luYearLevel} _{ORDER BY YearLevelSort} If you want to use a finance database as the source of your SQL query, you must add "[Finance]" to the start. For example: _{[FINANCE] SELECT Code, Description} _{FROM luPayrollCategory} _{ORDER BY Code}
Additional Properties	Used to specify additional properties of the control specified in the Type field. For example, the height, width and captions used for a GroupBox must be specified here. The default dimensions of most control objects can be altered using this field also.
Field Ref	Makes it possible to reference a database field so that its value can be called in the luSQL field of another object. For example, for an assessment report it is necessary to be able to select values from the SubjectAssessment class code field. As the values of this class code vary for each of the File Years, File Semesters and File Types, these fields can be referenced in the Field Ref: field. The screen capture below demonstrates how the File Type is handled. Once referenced, the values you select in the report definition screen can be passed through as parameters in luSQL field (that is, %FileType%) to populate the DBLookupComboBox control for Subject Class Codes. You need to put field references in single quotes if they represent strings.. For example, '%ref%'. For example, the following will not work: SELECT Surname, Preferred FROM Community WHERE Surname = %test% ORDER BY Surname whereas the following query, where the field reference is enclosed in single quotes, will: SELECT Surname, Preferred FROM Community WHERE Surname = '%test%' ORDER BY Surname Fields that are numeric, such as integers, do not require quotes.

Editable Objects and SQL Syntax fields

This component of the User Form Designer alters the SQL WHERE clause, SQL JOIN clause or add a report parameter depending on the selections made.

Field	Description
Passed to Crystal Reports	Used to specify how the SQL Server statement from the Crystal Report is altered or if a Crystal Report parameter is being added to the form.
As	Selection	Description
	Where	The selection dynamically alters the WHERE clause in the SQL statement of the report being executed.
	From	Alters (and adds to) the FROM clause. That is, the JOIN parameters.
	Rep Para	Is passed to the Crystal report as a parameter to be manipulated used in the report. For example, this might be used to change the sorting in the report. See Designing User Forms.
Field Name	This field contains the name of the database field or parameter that is linked to the control object.
Field Type	Used to specify the data type of the field or parameter used. The possible data type values are Integer, Varchar, NVarChar, Bit and Date. Note: NVarChar allows the field to contain unicode characters.
Default Value	This is an optional field that sets the default value for the field in question when the report definition screen is opened. An example of a commonly used default value is for the leaving date of a student. That is, setting the default value of Student Leaving Date to Getdate sets the default value on the report definition screen to today's date whenever the report is run through a selection screen. You can use a predefined value from the drop-down list, or type in the value manually.
Select Join	This is used to determine the logic that occurs between the selections made in the WHERE clause of the SQL Server statement. The available joins are: AND OR AND ( AND (...) AND ) OR ( OR (...) OR ...) ). By using the View SQL button the original logic of an SQL Server statement can be easily analysed to better understand how to adapt or add to it.
Select Operator	Select operators are available to be used in conjunction with the database field/parameters used in the SQL Server statement. The available select operators are: = > < <> >= <= LIKE IN IDTag.
Sort Order	The order the objects appear in.
Clear All-able	If set, the selected database/parameter field value is cleared by the Clear All button on the selection grid.
Mandatory	If set, the database/parameter field must have a selected value before the report will open.

Tip: When a report definition screen has been developed or modified it is a good idea to test it thoroughly by entering a value in every field. If the report appears on the screen (regardless of whether it returns any data), you can be sure that the report definition screen is working.
Tip: You can also hold the shift key and click to see the SQL statement including any field values chosen on the window by the user.