Managing SQL reports

Trimble Web Products 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:

  • Order Start = within a defined date/time range

  • Order Status = AVL or STD

  • Order Origin State = OH

  • Order Destination State = CA

image1

Predefining restriction criteria using User Profile properties

In addition to predefining restrictions in your SQL queries, you can customize reports for specific users. You can base report restrictions on information in a user’s Trimble Web Products profiles.

For example, you can create a report that restricts the data received by the user according to their company ID in Trimble Web Products. A TMW Web Products user with a company ID of ABC in their user profile sees different data than a user with a company ID of XYZ.

These examples show how the same report, executed by different users, restricted the data according to an ID in the user’s profile.

  • User profile ID #1

    This information on the user’s My Profile page shows that this user has an ID of GRECAR.

    image3

    The resulting report, titled Order Report - User Profile, shows a set of order numbers only for the ID GRECAR.

    image4

  • User profile ID #2

    This information on the user’s My Profile page shows that this user has an ID of MOVAL.

    image5

    The resulting report, titled Order Report - User Profile, shows a set of order numbers only for the ID MOVAL.

    image6

Allowing users to set their own restriction criteria

You can allow users to enter restriction criteria before running a report. These restriction fields are available to the user as drop-down lists, list boxes, date fields, and text boxes.

Drop-down list

This example shows how a user can select an order status from a drop-down list.

Restriction criteria:

image7

Resulting report:

image8

List box

This example shows how a user can select one or more order statuses from a list box.

Selection Criteria page:

image9

Resulting report:

image10

Masked date/time

This example shows that while the SQL query defined a default date range for the report, a user can enter different start and end dates.

Selection Criteria page:

image11

Resulting report:

image12

Free-form text box

This example shows how a user can enter all or part of an order number in a free-form text box. In this case, order numbers that contain the string 45 are included in the report.

Selection Criteria page:

image13

Resulting report:

image14

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 > Reporting > SQL Reports > 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 Trimble Web Products 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 Trimble Web Products 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.

  9. If you entered lookups for variables, proceed to Defining SQL report variables.

Control types

For each type, replace name with your desired value.

@dd_name

Description

Use a drop-down list to allow users to select one variable from the restriction set. name represents the label you want to use for the drop-down box.

Report query

Include a where clause. Use appropriate syntax for the values listed in the drop-down box.

  • Text fields should be enclosed in single quotation marks.

  • Numeric fields do not require single quotation marks.

Example

where ord_originstate = '@dd_Origin'

image16

@lb_name

Description

Use the list box to allow users to select one or more variables from the restriction set. If you choose to allow multiple selections, a comma-separated list of values will replace that variable in the SQL query.

Report query

Include a where clause. To allow the selection of only one variable:

  • Text fields should be enclosed in single quotation marks.

  • Numeric fields do not require single quotation marks.

To allow the selection of multiple variables:

  • Single quotation marks will be supplied as needed by the reporting engine.

  • Numeric fields do not require single quotation marks.

Example

Single variable:
where ord_status in ('@lb_status')

Multiple variables:
where ord_status in (@lb_status)

image17

@tb_name

Description

Use the free-form text box to allow users to restrict the data returned by entering characters they want to match. You can allow the use of a wildcard (%) or identify the variable in the SQL as starts with, ends with, or contains.

Report query

Include a where clause.

To include the use of a wildcard in the SQL, enclose the variable reference with percent signs.

To allow the user to supply the wildcard in the text box, do not enclose the variable reference with percent signs.

Example

Wildcard included by you:
where ord_number like '%@tb_Order%'

Wildcard added by the user:
where ord_number like '@tb_Order'

image13

@dt_name

Description

Use the masked date/time variable to allow users to restrict the data returned by indicating a date/time.

Report query

Include a where clause.

Example

where ord_bookdate between '@dt_Start_Date' and '@dt_End_Date'

@prop_name

Description

Use the user profile variable to have the system restrict the data retrieved based on a property in their Trimble Web Products User Profile.

Report query

Include a where clause.

Example

where ord_carrier = '@prop_carrierid'

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.

      image19

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

      image21

  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