Tag Archives: Reports
071111_2046_ReportFeatu1.png

Report Features – 2 of 3

July 11, 2011 by Arlene 1 Comment

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

Feature Spotlights
, ,
062811_1626_ReportFeatu4.png

Report Features – 1 of 3

June 28, 2011 by Arlene 0 Comments

Have you ever wondered how to remove extra field prompts from the Report Criteria Dialog so that only the prompts that will be used in the report are displayed? Would you like to learn a bit more about creating custom expressions to perform calculations in report columns? Might you want to investigate sending reports out on a scheduled basis, ensuring they are only sent if certain criteria are met? These and other questions will be explored in this three part series on Report Features and Tips.

Report Criteria Configuration

The Report Criteria Dialog that displays each time you run a report displays a broad spectrum of logical field choices on which to filter your report data. The fields that are displayed can be “configured”, removing prompts that will not be used or adding prompts that are desired, but not displayed. For example, an open work order list for a particular shop might be configured to display simple prompts, allowing the user running the report to specify a date range and work order types:

Since modifying Report Criteria is an advanced feature, it is designed for System Administrators and is somewhat “hidden” in the interface. Accessing the feature also requires that the user be a member of an access group with permission to “Access Report Criteria Edit” (new permission in Version 5.0):

Let’s modify the Report Criteria on a sample Closed Work Order Report, removing some fields and adding a new field prompt for the “completed” date:

  • Open the Reporter, select the Work Order (Closed) Reports Folder and select the Closed Work Order List.
  • Select the Copy Option to create a test report that can be modified.
  • Enter a new “name” for your report so you easily find it after copying (e.g. Blog Test Report)
  • Click Apply, then find your new report on the Report List and Run it.
  • With the Report Criteria Dialog displayed, double click on the label at the top that says “Report Criteria“:

  • The “Manage Report Criteria Fields” Dialog will display.
  • Since we plan to add the “Complete” date, let’s first remove the Target Date prompt. Click on the WO.TargetDate field prompt in the Display Fields List in the middle of the dialog, and then click the Remove Arrow just to the left of the field:

  • Repeat this step to remove any additional field prompts you do not wish to display.
  • To add the “Complete” date field prompt, scroll to the WO.Complete field on the list in the left column and then click the right arrow to move it to the Display Fields List. The field will be placed at the bottom of the list, so you will not see it initially.

  • Scroll down to the end of the Display Fields List and select the WO.Complete field. Click the Page Up Arrow to the right of the Display Fields List several times, until the field is showing at the top of the list.

  • Click Apply to save your changes. Your modifications will not show on the Report Criteria Dialog until you exit the dialog and allow it to refresh.
  • Click Cancel to exit the dialog, and then select the report and Run it again. This time, your new field should show at the top of the list and any removed fields should no longer display.
  • As one last step, since we added a date field, we must “set” the default dropdown:
    • Change the dropdown to “is”
    • Change it back to “is within” and select a value from the date options on the right (such as Current Month).
    • Click the Save Criteria option and then click Apply to save your settings.

For more information on using this feature, refer to the “Custom Runtime Criteria” Tutorial in the CSP (Customer Service Portal).

Feature Spotlights
, ,

Version 5.0 Release – Purchase Order Module Enhancements

February 3, 2011 by Arlene 1 Comment

Version 5.0 includes several enhancements to the Purchase Order Module, including a new approval process and additional configuration options for the Purchase Order Report.

Purchase Order Approvals

A new approval process is available that allows you to define required approval levels based on the total amount of the purchase order. You can define up to five different approval levels for each Repair Center. As an example, you could specify that all purchase orders above $100 require Level 1 Approval, all purchase orders over $500 require Level 2 Approval, and all purchase orders over $1000 require Level 3 Approval.

 

With these settings, a purchase order for $1500 would need three levels of approval.  To provide maximum flexibility, approvals can be made in any order. That is, if desired, a Level 2 Approver can process an approval before a Level 1 Approval has been completed. A new “Approval Status” section provides a clear indication of which levels have been approved, and which still need approval.

 

The previous approval process, which bases approval requirements on the “permissions” of the individual creating the purchase order, rather than the amount of the purchase order, continues to be available.

Purchase Order Report Configuration

Extensive new preferences are now available to configure the Purchase Order Report to better reflect the needs of your organization. Some preferences allow you to include additional fields for display, while others allow you to hide data that was previously shown by default.

 For example, new line item columns including “Account,” Sub-Account,” and “Stockroom/Bin” may be displayed, while the existing “Discount” column may now be hidden.  

Next Week….

Stay tuned next week for a preview of additional features included in Version 5.0, such as, the ability to process manually generated PMs and Projections in the background, and a new password protection feature to avoid accidental record deletions!

Release Notes
, , ,

Version 5.0 Release – Reporter Enhancements

January 8, 2011 by Arlene 0 Comments

In this second blog in the series summarizing new features in Version 5.0, we will review several valuable enhancements to the MC Reporter, including the ability to export reports to a PDF, new multi-series charting capabilities and a new “save all” feature for editable smart reports.

Export to PDF

Reports displayed in the Report Preview Window can now be exported to a PDF.

 

The exported PDF is automatically opened in a browser, allowing you to “Save” or “Print”.

 

Multi-Series Charting

New options are also available to create multi-series charts:

 

In the following example, a multi-series bar chart was created displaying work order count, target hours and actual hours for different work order types:

 

In this example, a stacked bar chart was created summarizing labor and material costs for different work order types:

 

Save As Function in Smart Reports

A “Save All” Button has been added to “Editable” Smart Reports to allow changes made to multiple rows to be saved in a single step. After saving, the page refreshes showing the records that updated correctly in “green” and highlighting rows with errors in “red”:

 

Next Week….

In next week’s blog previewing Version 5.0 features, we will preview enhancements to the Work Order Module.

Release Notes
, ,

Magic SQL Reports

April 20, 2010 by Arlene 0 Comments

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

Feature Spotlights
, , ,
Newer Entries
Older Entries

Follow

Get every new post delivered to your Inbox.