Create SQL Statements

In order to create SQL statements you must have a working knowledge of the SQL language used by Microsoft SQL Server.  There are many resources available to you that can help you learn SQL, including books, Microsoft's Web site, and Microsoft SQL Server Help files.

You can utilize SQL Query Analyzer, SQL Enterprise Manager (recommended), or the available TMS Report Utility database for developing SQL queries used in reporting. If you use the Report Utility database, all double-quotes must be removed prior to adding to C1ReportDesigner.

SQL Enterprise Manager is the preferred method for building SQL queries because it builds the joins based upon your input.  Most joins between multiple tables are LEFT OUTER joins. There are, however, exceptions that you will encounter.

Parameters in SQL Statements

Including parameters in a designer report gives you some flexibility as to what data is displayed by the report.

1. Build your query including example data where you want to insert your parameters.

   The first line of the data source must be the PARAMETERS statement.

2. Do not use only field names in parameter names (WONnumber, AssetNumber)

   The format for parameters is as follows:

   • Declaration - Enter the word Parameters.

   • Parameter Name - The parameter name is contained in brackets [Enter Start Date] and can be descriptive to inform the user of what to enter when prompted.

   • Dates

     Enter Start Date

     Enter End Date

   • Building Enter Bldg Code

   • Site

     Enter Site Code

   • Material Warehouse

     Enter Whse Code

   • Master Codes

     Enter Asset Number

     Enter Resource Number

     Enter WO Number

     Enter Item Number

     Enter PO Number

     Enter PM Number

     Enter PE Number

     Enter Procedure Number

     Enter Risk Number

   • Average Hours Enter Average Hours

   • Codes

     Enter WOActionCode

     Enter WOProblemCode

     Enter WOItemCode

     Enter WOCauseCode

     Enter (WorkOrder, Asset, Resource) Type Code (Enter WorkOrder Type Code)

     Enter True or False

   The next item is the data type. Use only SQL standard data types.

   • Date

   • Text (or string)

   • Number

   • Boolean

3. You can enter a default value for the parameter, ensuring accurate and current data is entered each time the report is executed.  

   Parameters are separated by commas (, ).

   The parameters declaration line must end with a semi-colon ( ;).

Here is an example of a SQL statement using parameters:

Parameter [Enter Start Date] Date;

Select wonumber, datecreated

From vworkorders

Where datecreated >= [Enter Start Date] AND datecreated < dateadd(day, 
 1, [Enter End Date])

When using date range parameters, the [Enter End Date] parameter must always be used as written in the example above.