Implementation Guide › Implementation › Create Deliverables (Contract Manager) › How to Create Reports › Free-form Reports

Free-form Reports

Free-form reports enable users to generate reports based on SQL queries of the CA Business Service Insight database or from any other external data source which can be accessed via a connection from the CA Business Service Insight server. This also includes any other types of data source which can be accessed via ODBC, such as Excel, Access, Lotus Notes, Text files etc. Free-form reports are commonly used to configure statistical reports based on data created by the Business Logic commands Tools.SaveRecord and Tools.SaveFields.

Free-form reports connect via a connection string to a selected database and execute an SQL query on the database using a query string. Parameters can be added to both strings in order to create dynamic reports that enable the user to enter or select specific values to include in the query, such as the user name and password for connecting to the database.

Free-form reports are displayed in Chart, Data and Filter tabs similar to reports generated using the Report Wizard.

Note: Free-form reports can include charts only if all columns, apart from the first column, are numeric. The data in the first column is used for the X-axis titles. Column names are used for other titles.

Due to the fact that free-form reports use direct access to a database and an open SQL query, maintenance is problematic. Great care should be taken not to affect the underlying data which serves as a source for the free-form reports. When reports are generated from an external data source, it is recommended that a notification process be set up to ensure that those data sources are not changed without first consulting the Contract Manager responsible for the free-form data reports.

General Information to keep in Mind when Creating Free-form Reports.

• Due to international date formatting problems, it is often useful to specify the exact format for the date by creating a custom formula in the Business Logic. This converts the date to a string with the same format every time and avoid issues of US vs EU date formatting. It should be used for dates being written out to the table T_SLALOM_OUTPUTS via the Tools.SaveFields or Tools.SaveRecord commands. The sample formula is provided below:

Function FormatDate(DateField)

    If DateField = "" Then
      FormatDate = ""
    Else
      Dim PeriodYear, PeriodMonth, PeriodDay, PeriodHour,
  PeriodMinute, Periodsecond
      PeriodYear    = DatePart("yyyy",DateField)
      PeriodMonth   = DatePart("m",DateField)
      PeriodDay     = DatePart("d",DateField)
      PeriodHour    = DatePart("h",DateField)
      PeriodMinute  = DatePart("n",DateField)
      Periodsecond  = DatePart("s",DateField)
      FormatDate  = PeriodDay&"/"&PeriodMonth&"/"&PeriodYear& _
 " "&PeriodHour&":"&PeriodMinute&":"&Periodsecond
    End If
End Function

• When using the 'time' parameter from the Business Logic event handlers, or any timestamps from the eventDetails object, always be aware of the impact of the timezone shift. Be aware that the dates when written to the table may be in UTC time rather than local time. You may need to use the Tools.GetLocaleTime() function to correct this.
• You can still use the Report Designer utility (when a free-form report produces a graph) to customize the appearance.
• The PDF exporting options for the free-form report are customizable using the Report Parameters section of the free-form setup screen. Things such as page layout (landscape, portrait)
• HTML can be embedded in the reports to perform different functionalities, such as adding hyperlinks or changing column and row colors, fonts or display settings.
• Database Views (Oracle functionality) can be created on the T_SLALOM_OUTPUTS table in order to simplify the SQL required for the reports.
• When specifying parameters for the reports, it is possible for them to be set in a number of ways including: Free Text, Numeric (validate), Password (characters hidden - useful for passwords to DB connections), Date (using the provided date picker utility) and Lists (created by specifying an SQL statement to populate the list)
• Parameter values specified in the SQL section of the free-form report need to be substituted with the parameter name, preceded by the '@' symbol. i.e. @PARAM1. The use of quotes around this parameter value may be required if the values are strings.