Managing SQL reports

TMT Fleet Maintenance provides a web-based reporting interface that uses SQL statements to develop reports. You can create ad hoc or regularly-generated reports based on information in databases to which you have a connection.

When you set up the report, you assign it to user roles. Only users with those roles can access it. Reports are accessed through Report Viewer, as an individual menu option, or from a hyperlink to the report’s URL.

Defining restriction criteria

Using restrictions, you limit the report to records that meet specific requirements. When you create a report, you can handle restriction criteria in several ways.

Predefining restriction criteria in your SQL query

When designing your report, you can identify restriction criteria when you write the SQL query. Predefining restriction criteria is useful when the report is to show the same data to anyone permitted to view it.

This example shows predefined restriction criteria for:

  • Domicile = 1

  • Cost Center Code = Delivery or Retail

  • Sorted by description

image2

Creating and updating a SQL report

To create a new SQL web-based report or update an existing report, follow these steps.

  1. Go to Menu > Reports > Reports - SQL > Manage Reports.
    The Manage Reports page opens.

    If no other reports exist, enter a unique name for the report in the Report Name field.

    If other reports exist, and you want to create a new report:

    1. Click Add New Report.
      A blank report form appears.

    2. In the Report Name field, enter a unique name for the report.

    If you want to update an existing report, select the report you want to change in the Report Name field. The Manage Reports page displays the current settings and SQL query for the selected report.

    image15

  2. In the Report Description field, enter the text you want to appear at the top of the report, below the report title.

  3. If you want to add the report to a menu, select Add to Page Definition.

    A report can be viewed using Report Viewer or added as a menu option.

    • If you want to be able to add this report as a menu option, select this checkbox.

    • If you want the report to be viewed only by using Report Viewer, clear the checkbox.

  4. If you want to limit access to this report to specific roles, use the Role Assignment field to make a role selection.

  5. From the Database Connection list, select the location of the database to be used as the source for the report.

    Note: The list of available database locations is determined by the settings in the connections section of the web.config files for TMT Fleet Maintenance and Web Services. All connections preceded with "Report_" are shown in the Database Connection drop-down list. For more information on these settings, refer to the TMT Fleet Maintenance Installation Guide.

  6. Define the Report Query in SQL.
    You can include any of the following types of controls to define a variable.

    Notes:

    1. Variables must start with one of the strings listed below and may need to be enclosed in single quotation marks.

    2. Variables are not case-sensitive.

    3. If you want the label (name) for the input field to contain blank spaces, use underscores in the name. For example, @dt_Date_From produces the label Date From for the input field.

  7. If you want, change the report colors and fonts.

  8. Save the report.

    • New report
      Click Create.
      A message appears confirming the report was successfully created.

      The new report is added to the list of available reports.

      Note: The successful creation of a report does not guarantee the report will generate without error.

    • Existing report
      Click Update.
      A message appears confirming the report was successfully updated.

      Note: The successful update of a report does not guarantee the report will generate without error.

Changing SQL report colors and fonts

You can customize the appearance of a report by changing background colors, fonts, and font properties.

To customize a SQL report, follow these steps.

  1. Go to Menu > Reporting > SQL Reports > Manage Reports.
    The Manage Reports page opens.

  2. From the Report Name field, select the report you want to change.
    The Manage Reports page opens with the current settings and SQL query for the report you selected.

    image15

  3. Click Show Report Properties.
    The page expands to display the report properties.

    image18

  4. Determine the component of the report you want to change.

    • Header
      This example shows the changes.

      image20

    • Detail
      This example shows the background color of the odd rows within the body of the report changed and the font changed to Calibri.

      image22

  5. To change the color of a report component, do one of the following:

    • Enter the HTML color code.

    • Click image23 Pick Color and select a color from the palette.

      Note: You may need to drag the arrow on the color bar up to a lighter color range before selecting from the palette.

      image24

  6. Change the text font.

    • In the field of the item to change, enter the name of the font.

      Notes:

      1. You should select a standard font for web browsers, such as:

        • Arial

        • Helvetica

        • Courier New

        • Lucida Console

        • Lucida Sans Unicode

        • Tahoma

        • Times New Roman

        • Trebuchet MS

      2. If the person running the report does not have the chosen font installed, the browser will display the next closest font.

    • Select or clear the Bold checkbox, if available.

  7. Click Hide Report Properties to collapse the Properties portion of the window.

  8. Click Update.
    A message confirms the report was successfully updated.

Defining SQL report variables

Any variables included in the design of a SQL report must be configured.

To define variables for a SQL report, follow these steps.

This procedure does not apply to '@prop_' (User Profile).

  1. Go to Menu > Reporting > SQL Reports > Manage Report Variables.
    The Manage Report Variables page opens.

  2. From the Reports field, select the report containing the variable you want to configure.
    A blank form for the selected report appears.

  3. From the Variable Name field, select the variable to configure and its sequence number.

    Note: A sequence number is automatically added to each variable in the report to indicate the order of variables on the form. You can change the order in which the variables will be listed by selecting a different sequence number from the Seq field.

    image25

  4. Specify whether the variable is required or optional.

    When the checkbox is:

    • Cleared
      The variable field is required. The field name shows in red. It is preceded by an asterisk.

      Note: Since any variable in the SQL script requires a value to process correctly, all variable fields are required by default.

    • Selected
      The variable field is optional.

      Note: To make a variable optional, your report SQL query must handle the possibility that the user does not select a value.

    In this example, the Start Date and End Date fields are required. Users must select the variables before the report can be generated.

    image26

  5. Enter the Lookup SQL for the variable.

  6. In the Variable Help field, add text to assist the report user.

    You can include a brief description of or additional details about the variable to help the user run the report.

    image34

    This example shows how your help text will appear in Report Viewer. A question mark is added to the field name. When the user moves the pointer over the mark, the help text appears.

    image35

  7. Click Save.

  8. Repeat Steps 3 through 7 for each variable in the report.

  9. To configure variables for a different report, click Select New Report, select another report from the Reports list, and repeat this procedure.

Lookup SQL

@dd - drop down

Report query

If you included @dd_, as in this example:

where ord_originstate = '@dd_Origin'

Lookup SQL

Write a statement such as the following:

select
distinct ord_originstate as name,
ord_originstate as value
from orderheader
order by ord_originstate

Preview

image27

Additional settings

Default values:

  • To identify default values for the variable, in the Preview area, select the default value or values, and click Save as Default Value(s).

  • Once saved, you can clear the default values by clicking Remove Default.

image28

@lb - list box

Report query

If you included @lb_, as in this example:

Where ord_originstate = '@lb_Origin'

Lookup SQL

Write a statement such as the following:

select 'Available' as name, 'AVL' as value
union
select 'Dispatched', 'DSP'
union
select 'Completed', 'CMP'
union
select 'Planned', 'PLN'
union
select 'Started', 'STD'
union
select 'Tendered', 'TND'
union
select 'Request for Quote', 'RFQ'
order by name

Preview

image29

Additional settings

Allow Multi-Selection field:

  • To allow the user to select more than one variable from the restriction set, select the checkbox.

  • To allow the user to select only one variable from the restriction set, clear the checkbox.

Default values:

  • To identify default values for the variable, in the Preview area, select the default value or values, and click Save as Default Value(s).

  • Once saved, you can clear the default values by clicking Remove Default.

image28

@tb - freeform textbox

Report query

If you included '@tb_, as in this example:

where ord_number like '%@tb_Order%'

Lookup SQL

No further SQL is necessary. Freeform text box variables cannot be customized.

Preview

image30

Additional settings

Default values:

  • To identify default values for the variable, in the Preview area, select the default value or values, and click Save as Default Value(s).

  • Once saved, you can clear the default values by clicking Remove Default.

image31

@dt - masked date/time

For the dates @dt_ variable, you can:

  • Change the mask used for them

  • Choose the offset from the current date.

Report query

If you included @dt_, as in this example:

where ord_bookdate between '@dt_Order_Date_Start' and '@dt_Order_Date_End'

Lookup SQL

Edit the way you want the date to display, such as:

  • MM/dd/yyyy HH:mm

  • MM/dd/yyyy

Preview

image32

Date Options

Use Date Mask as selected. Edit the mask in the Lookup SQL field.

Date Offset from Today

Enter the offset number for the default date.

  • Negative numbers display the number of days before today’s date.

  • Positive numbers display the number of days after today’s date.

  • Zero displays today’s date.

Minimum Date and Maximum Date

These dates prevent a user from running a report with a date range that would return a massive amount of data.

These dates also allow you to control how far back in history you want data retrieved. For example, data that is more than one year old is not relevant for this report.

Additional settings

Default values:

  • To identify default values for the variable, in the Preview area, enter the default date, and click Save as Default Value(s).

  • Once saved, you can clear the default date by clicking Remove Default.

image33