Tag Archives: report setup

Report Features – 3 of 3


In this last blog in the series on Report Features and Tips, we will review the options available to automate the distribution of scheduled reports. Report scheduling is used for many purposes including weekly status updates for shop supervisors, expiration reminders (e.g., for licenses or certifications), and executive/management updates. Reports can be scheduled on a wide variety of intervals:

Note:  Only customers that have the MC Agent Service can use this scheduling feature. Also, if the Schedule Tab is not displayed, you may be a member of an access group that does not have access to this feature.

To schedule a report for automated distribution:

  • Access Report Setup for the report on which automation will be scheduled.
  • Select the Schedule Tab. The Email Sub-Tab will be selected by default, allowing you to specify email instructions.
  • To define automated generation and distribution, change the Email Schedule Dropdown Control to “On“. The remaining controls on the page will display.


  • Select the desired frequency from the Frequency dropdown controls. For example, to schedule a management report for distribution the first of each quarter you could select: “Every 3 Month(s)“.
  • In the Begin On dropdown controls, specify the first date you would like this report to be sent. In our example, you could select “10/1/2011“, as this is the next date that corresponds to the first of a calendar quarter.
  • Specify the Attachment Type. For most reports, the HTML option is preferable. The XML and CSV options are most often used when reports are to be opened in Excel or another software system.


  • The “Do not send if report returns no records” option is especially valuable for conditional notifications. For example, you could send a license expiration report only if there are individuals with licenses expiring or a list of work orders pending approval only if there are work orders in this state.
  • In the Email Recipients area, you can directly enter the email addresses of the individuals who should receive this report, separated by commas. Alternatively, if email addresses are already defined for these individuals in their Labor or Requester Records, it is best to add the addresses using the Add Button.
    • Click Add and the Select Recipients Dialog will display.
    • Change the dropdown control at the top of the page to locate the desired individual(s), typically “Labor“.
    • All individuals of the chosen type will be displayed. Those with valid email addresses specified will be selectable.
    • Select the individuals that you wish to receive this report.
    • Click Apply when finished.


       

  • In the area on the right of this page, specify the Email Address and Name that should appear on the email that distributes the report. It should be noted that a default value for “new reports” can be specified in System Preferences.


Click Apply when finished and the MC Agent will prepare and distribute the report to specified recipients on the next scheduled date.

Report Features – 2 of 3


In this second blog in the series on Report Features and Tips, we will explore creating custom expressions to modify the display of a field or perform calculations in report columns. Two common examples of modifying the display of a field include changing the “case” of a text field (displaying in all upper case) or altering the format of a date display (removing the time stamp).

A common mathematical calculation includes determining elapsed time, such as days to complete work order or hours to respond. You can perform mathematical calculations on individual fields, or perform calculations between fields (e.g., sum costs).

Create Custom Expressions

As a simple example, let’s calculate the number of days a work order has been open:

  • Access Report Setup for the report on which the calculated field is to be displayed. To create a calculated field, you must specify a “placeholder” field on which the calculation will be specified.
    • Select a field from the Available Fields List to use as a placeholder (field cannot already be shown in the Display Fields List).
    • For our example, select  “[WO]UDF Field 10″ and move it to the Display Fields List.
  • Select UDFField 10 in the Display Fields List and click Edit, just below the field list. The “Report Field Options” dialog will display for this field:


  • Enter the desired “label” for the report column in the “Field Label (Custom)” prompt. In our example, you could enter “Days Open“.
  • Select the appropriate field type for your calculated result from the Field Type dropdown. In our example, you should select “Numeric“, as we will be calculating the “number of days” into this field.
  • Click the “Yes” control in the “Custom Expression?” field prompt toward the bottom of the dialog, letting the system know that a custom calculation or expression will be used.


    • The expression for the calculation must be placed in the “Custom Expression Box“. In our example, we will create a custom expression that calculates the number of days between “today” and the date the work order was “requested.” A function called “DATEDIFF” is used to calculate elapsed time between dates. The format of this function is as follows:
      • DATEDIFF(increment to display – such as ‘DD’ for days, field/value to be subtracted, field/value to subtract from)
      • To tell the system to calculate from “today”, we must use the expression “GETDATE()” to signify the current date. The correct expression would be:

      DATEDIFF(DD,WO.REQUESTED,GETDATE())

  • In our example, the custom expression box might appear as follows:


  • Click Apply to return to the Report Setup Window.

Sample Custom Expressions

Magic SQL Reports


Did you know that users who are proficient in SQL have access to an Advanced Tab in the Report Setup window that lets them build reports directly from SQL Statements? This feature lets users prepare SQL to define the report content and then have the results magically display in our Report Preview Window. This provides a great alternative to individuals who are comfortable with SQL, but do not wish to learn a new reporting tool or be confined by the constraints of a defined reporter.

To use this feature, it is recommended that you test your SQL Statement in the Query Analyzer tool until the desired records and fields are returned.  Once the desired records are obtained, the statement can be copied onto your clipboard for later retrieval in the Reporter.

Once you have your desired statement, go to the Reporter and locate the report on which the SQL is to be executed. It should be noted that it is advisable to copy an existing report for this purpose as the report you modify will lose its existing field settings. Modify the “Setup” of the report as follows:

  • Select the Advanced Tab

  • Select the SQL Structure Sub-Tab

  • In the From (SQL) Text Box, enter your entire SQL Statement (paste from your clipboard if available). Even though there are text boxes for “Join, Where and Having” clauses, the entire statement should be entered into the From (SQL) Box. As a simple example, the following statement would return a series of asset fields to the Report Preview Window for all assets of the “HVAC” Classification:

     

     

  • When you are finished placing the content into the “From (SQL)” Box, click the Apply Button.

 

The Report Preview Window will display showing the fields designated in the SQL Select Statement.

 

Once this feature has been used to specify report content, all further edits must be made directly to the SQL Query from the Advanced Tab. You will no longer be able to use standard features such as the “Available Fields List” or “Smart Reports” to define the report, as the report presentation will bypass most report setup features. You can, however, use the setup tabs to alter some formatting (report heading, style, font) and to schedule the report. In addition, Report Criteria can be passed from the Report Criteria Window to the SELECT statement in the query using a special code, “[WHERE]”. For more information on using the special WHERE clause or if you would like any additional information on using the Magic SQL Reports feature, please contact Customer Support.

Want to try it out? Copy/paste the below SQL statement into the From SQL in the Advanced Tab of any report, and see what you get:

SELECT WOID, Reason, TargetDate, Status FROM WO WHERE IsOpen=1

Spotlight on Reports: Week 2 of 3


Last week in our spotlight on reports, we described how you can quickly add and remove columns as well as adjust the column sorting. This week we will be showing how to quickly sort your report information as well as adding column and group totals.

Las Vegas Training Event
More information has been released on the next user training seminar. Please click on the image below to download a PDF with additional information.

clip_image001

Report Sorting, Grouping, and Totaling
When viewing any report, one of the main tools used to analyze the data is sorting. Using the SETUP button in the toolbar at the top of a report, you can access the Report Setup screen. In the lower half of the form, you will see a tab group that defaults to the sort / group tab.

image

By using the 5 drop down options, you can determine the sort order of the reports. In the example above, the data on the report will first be ordered by the Shop Name and then by the farthest Target Date in the future. Using the Group and Total check boxes will add a Group Band to the report as well as Totaling the group prior to the start of the next group.

image

Tip: When Grouping, it is best to have your groups at the top of the sort list in the order that you want the grouping to occur. For example, you would want to Group By Repair Center, Shop, and then sort by Target date. In the screenshot below it shows Grouped By Shop Name, Sorted By Target Date (Desc), and a total was added to the group by clicking the total check box.

image

Finally, in addition to the totaling option on the group, you can specify which numeric fields you want to total or hide the total. To do this, in the report setup, click on the field you wish to edit and then simply set the Total if Selected option to Yes or No.

image

Spotlight on Reports: Week 1 of 3


Many Maintenance Connection customers have been able to leverage the Reporter to extract and analyze data as this is one of the more powerful aspects of the Maintenance Connection system. For the next couple weeks, we will highlight a few key features of the Reporter application.

Next User Training Event
Before digging into this week’s topic on field editing, make a note on your calendar about our next training seminar. The next user event will take place in Las Vegas this October. More details to follow shortly.

Editing Report Fields
One of the simple features of the Reporter is the ability to customize which fields display as columns in a report. Using the SETUP button in the toolbar at the top of a report, you can access the Report Setup screen. You will notice a column on the right with fields currently on the report, and available fields to the left to add.

By selecting a field and using theand buttons you can easily add/remove move columns from a report. Using the and buttons to the right of the Display box, you can move the columns up/down which corresponds to their position when the report generates.

Tip: Most users will take an existing System Report, copy it, then make the changes. This allows you to preserve the canned report formats. You can find the Copy Report button on the main Reporter screen.

One final tip on editing the fields on reports: once you’ve run the report, you can also reorder the columns by simply dragging and dropping a the column header to the desired location on the report. Saving is automatic so the change will remain intact next time you run the report.