Difference between revisions of "Reporting"

From High5Wiki
Jump to navigation Jump to search
m (Dead link for report builder PDF. Replaced w/ new URL.)
 
Line 280: Line 280:
 
See ReportBuilder for information on the Report Builder tool.
 
See ReportBuilder for information on the Report Builder tool.
 
You can also download a pdf called "Learn Report Builder" from here:
 
You can also download a pdf called "Learn Report Builder" from here:
<br>http://www.digital-metaphors.com/pdf/LearnReportBuilder.pdf
+
<br>http://www.digital-metaphors.com/download/pdf/RBuilder.pdf
  
 
== Report Builder Tutorial ==
 
== Report Builder Tutorial ==
Line 288: Line 288:
 
<br>The tutorial, available from the following link will familiarize you with the Custom Report UI, form controls, data links, etc. The version implemented into SME does not include the entire range of functionality that is found in the tutorial. It will introduce you to the reporting controls, design user interface, and basic report modification functionality. Creating, duplicating, and/or modifying SQL queries to pull data for reports in SME is not covered in this tutorial.
 
<br>The tutorial, available from the following link will familiarize you with the Custom Report UI, form controls, data links, etc. The version implemented into SME does not include the entire range of functionality that is found in the tutorial. It will introduce you to the reporting controls, design user interface, and basic report modification functionality. Creating, duplicating, and/or modifying SQL queries to pull data for reports in SME is not covered in this tutorial.
  
<br><span>&nbsp;</span>"http://www.digital-metaphors.com/download/learning_reportbuilder.html"<span><span>
+
<br><span>&nbsp;</span>"http://www.digital-metaphors.com/download/pdf/RBuilder.pdf"<span><span>
  
 
== Customizing Reports with Report Builder ==
 
== Customizing Reports with Report Builder ==

Latest revision as of 22:29, 23 May 2019

Report Builder is the reporting component used in SME for creating and customizing reports. To access Report Builder:


Report Builder Module

To access Report Builder in SME go to the Reports module> Customize Reports> Open Report Designer.

If the report you are editing is a Standard Report you will be asked to create a custom report. Refer to Duplicating a Report for Custom Use.

To become familiar with the report builder module and user interface refer to the Report Builder Tutorial.

Some SME reports use SQL queries to extract data. If you are editing a report with the report file name starting with "qry" contact High5 Support for assistance modifying the report.


How to Duplicate Standard Reports for Custom Use

  • Go to the ‘Report Center> Customize Reports’
  • 1 Image(1).png
  • Retrieve the report you want to duplicate from ‘Search Standard’.
  • 2 Image(1).png
  • After the report loads click on the ‘Duplicate Report’ button.
  • 3 Image(1).png
  • SME will auto fill the fields with the word ‘Custom’ and ‘Copy of’. At this time it is recommended that you rename the report to a name unique for your company or use.
  • 4 Image(1).png
  • Similar to this:
  • 5 Image(1).png
  • Save the report.
  • Click on the ‘Open Report Designer’ button.
  • 6 Image(1).png
  • When the report designer opens click on ‘File> Save As’ and rename the report.
  • Note: It is recommended that you add a prefix or suffix to the current report name as this will enable High5 support to readily assist with troubleshooting should an issue arise.
  • 88 Image(1).png
  • Click ‘Save’ and close the report designer.
  • Click on the browse ellipsis to the right of the ‘Filename’ field and select the file you saved in the step above.:
  • 7 Image(3).png
  • Click on Open:
  • 8 Image(2).png
  • Click on Save and the custom report will now be unique and ready for further editing.
  • 9 Image(1).png


Reports Center

Purpose
The purpose of this document is to further explain the different options under the Customize Reports section of SME. Most reports can be run from their respective modules. While other details reports, like the Aging and Tax Reports are actually ran from the Reports Module.


Document Center Overview Main Screen
The main function of the document center is that of a central repository which will allow you to keep track of external documents you deem important, as well as any reports templates you want to create for SME.


Option Found on the Main Screen (Details and Settings)


Short Name

Short Name is internal name of the document for SME.

Title

In some reports it is possible to generate a title on the fly. Which will be explained in detail later in this document.

File Name

This is the physical name of the file.

Description

General description of what the file is used for.

Is Restricted

Checking this makes it so only an administrator can view this report.

File Name

This is the physical name of the file.

Is External

Used to determine if this is an SME report builder report, or an external report or document.

Duplicate Report

This creates a new entry in the Docs table, with all same information as above. (Used for making a custom aging report).

Report Designer

Used to Launch Report Designer.


Availability
The Availability tab allows you to allow you to choose where a report will be located when you want to print the report, as well as its location under the Reports Module.

Reports Specifiers
The Report Specifiers tab function is your main options in setting up Reports for SME to use. This includes filters, Main table, as well other options as explained below.


Master Table This is the main table from which data will be pulled from. Also note that all filters
applied are against the master table.

Another thing to note about master table, is that certain tables, such as Service Orders,
Install Orders, Invoices, and Proposals will bring in all the other related tables.

Date Specifier This option is to be used when you want to apply filter on reports by a date range. Now
this can be either a single date range, or Beginning and Ending Date range. Date field
will be determined by what you pick as your master table. Note: Running the report with
filters is done though the Reports Module.
Status Specifier Just like the Date Specifier the status specifier acts as a filter true or false
fields. Example of this would be the Active vs Inactive Customer.
Alternative Column Format Currently not in use.
Email Option Available This will give you the option to email report both in the Reports Module as well as
report specific module when active.
Show on Module Print Menu When enabled it will, and you have the security clearance you will see the report under
the Print button found in any module. Note to determine which module a report will be
printed in is set by the Availability tab.
This is a Service and Install
Ticket
Currently not used
Allow Queries for this Report This allows you to setup your own query. This is always against the Master Table. This
also needs to be run in the Reports Module.
Allow Indexes Change In report where you are using grouping. You will be able to change what is the original
index to something else. Visible in Reports Module.
Allow Save Query This will allow you to save a query after its run. Note that you can run the query once,
then uncheck it so that the query always exist.Done in Reports Module.
Allow on Print Package This will enable the report to show on Print Package for the module it is in.
Index Name The default index used when Printing a report that is using groups.



Details Specifiers
The purpose of the detail specifier tab is to setup the pipes to other tables. This is done by connecting the Primary Key of the Master table, with the Foreign Key of Detail Table. As well as connect Details Detail table to the Detail table. Note: In report designer each table you choose will show up under the Data Pipes.


Details -> Master


Details Table This is where you set the table you will be adding to addition to the master table.
Details Index This is the Foreign Key inside the Details table that relates back to the master table.
Master Table Linking Field This is the Index from the Master table you are using.


Details Detail-> Details Link


Details Table This is the table that will be linked to the Details Table
Details Index This Foreign Key found within the Details Detail table
Detail Linking Field This is the field in the Details table that will be link from the Details Index.


Specified Links
Once accept your link the new link will show up in the Specified links in the following format. Details Table, Details Detail Table, Details Index, Master Index, Details Detail Index, Detail Linking Field.

Hints to Linking Reports
One thing to know about linking reports is that you can bring in as many reports as possible. You are not limited one Details and Details Detail. Each entry will show up in the Specified Links.

Try to use the highest level table as the master table in relation to what you are looking for in your data. If your report is based off Service Orders, the master table should then be ServiceOrders. Also to note any filters applied to a report before processing is based off the Master table.

All tables have a UID attribute. This is the primary key for that table. Many tables have one than more foreign key. All Foreign key will have the word UID in there name. Example of this is that CustUID can be found in the ServiceOrders, thus you can connect Customers with ServiceOrders. When linking tables you will always want do this by UIDs.

The Master Table by default is the whole of the Report Document. Any reports with multiple entries in details table will need placed in a sub report. Good example of this with ServiceOrder and ServiceOrderMaterials. To see all your materials you will need to place your materials in a sub report.

Few tables can be linked in the details detail table. If you are unable to access any drop down, it is because this table is not a secondary table.


Titles
To set up a report where you can change the title all you will need to do is make sure that label used in SME is pulling from the SystemsDefaults> ReportTitle. SME will always use the Default time unless it is changed in the Reports Module.


See ReportBuilder for information on the Report Builder tool. You can also download a pdf called "Learn Report Builder" from here:
http://www.digital-metaphors.com/download/pdf/RBuilder.pdf

Report Builder Tutorial

SME uses a tool called Report Builder to customize the report/document forms.


The tutorial, available from the following link will familiarize you with the Custom Report UI, form controls, data links, etc. The version implemented into SME does not include the entire range of functionality that is found in the tutorial. It will introduce you to the reporting controls, design user interface, and basic report modification functionality. Creating, duplicating, and/or modifying SQL queries to pull data for reports in SME is not covered in this tutorial.


 "http://www.digital-metaphors.com/download/pdf/RBuilder.pdf"

Customizing Reports with Report Builder

This tutorial assumes that you are modifying an existing report, where the settings under Customize Report are correctly set. If you wish to modify those details, read Customizing Reports.


To access the Report Builder, go to Customize Reports under the Reports tab in SME and click the Open Report Designer button.


Adding New Text Fields

In this tutorial, the Standard Purchase Order report will have phone, fax, and due date fields added to it.

Step 1: making room for the new fields. Phone and fax information are only listed once per page, so they belong in the Header or Group Header. Drag the Group Header down to free up space. To move the headings for the details down, drag a box around them and drag them down to be right above the Group Header ribbon.


Step 2: adding new non-repeating fields. Phone and fax information can be found under the Customer table, in the Data Tree on the right side of the screen. Select the Customer table, then the Phone and Fax fields, and drag them onto an empty spot on the report.


Step 3: adding repeating fields. Because Due Dates show up on every item, that field belongs in the Detail tab. In this report, the details are in a SubReport. Click on the tabs on the bottom of the screen to move between the main report and subreports. The DueDate field is in the PurchaseOrders table, so select it then drag DueDate to where you want it.

Once you're finished arranging the text fields, save the report and make sure the Filename is specified as the new report file in the Details and Settings section of Customize Report.


Formatting: Page Breaks

This tutorial is useful for if you only want to print one item or one customer per page, and the report is not set to page break on a change in that field. Placing a field in either the header or group header space will result in only one value being displayed. Placing the field in the details space will display all values in the table.

To print one main field (customer, item, location, etc) per page:

  1. Move the field you want to page break on to the Group Header. Right-click the Group Header ribbon and select Groups, or go to Report tab and click Groups.
  2. Change Break On to the data field you want to page break on.
  3. Check “Start new page” under On Group Change.
  4. Click Ok.



Company Reports

There are over 144 standard reports in SME.

To Access the entire list go to Reports> Print Reports.

Reports are listed by module.


All standard reports in SME, listed by type

Computer

None

Customer


Standard Accrued Balance Report/Billing
Standard Aging Report by Date Range
Standard Batch Invoice
Standard Billable Customer Report
Standard Credit Report
Standard Customer Contact List
Standard Customer Equipment List
Standard Customer Install History
Standard Customer Label 5160-Billing
Standard Customer Label 5160-Site
Standard Customer Maintenance Expired
Standard Customer Message Center
Standard Customer Notes Report
Standard Customer Open Deposits
Standard Customer Report
Standard Customer Service History
Standard Customer SnapShot Report
Standard Customer Statement
Standard Customer Statement/Due Date
Standard Customer SubContractor Report
Standard Customer System Report Detailed
Standard Customer System Report Summary
Standard Customer Trunk Report
Standard Customers By Name
Standard Customers By Sales Person
Standard Directions Report
Standard Envelope Window Location
Standard Labor Report, By Order
Standard Maintenance Contracts Report
Standard Open Invoice Reports
Standard Order Material List
Standard Past Due Notice
Standard Payment Receipt
Standard PM Planning List
Standard Prepaid Hours Balance Report
Standard Prepaid Hours Balance Summary
Standard Prepaid Service Balance Report
Standard Undistributed Payments
Standard Warranty Expired

Direct Sales

None

Equipment


Standard Equipment List by Site
Standard Equipment List Excel
Standard Equipment Spares by Site

Fixed Assets

Standard Fixed Assets

Install Order


Standard Closed Order List
Standard Closed Order Report
Standard Install - Dot Matrix
Standard Install Ticket
Standard Report, by Order
Standard Material Request, Install Order
Standard Open Order List
Standard Open Order Report
Standard Open Order Report, No Due Date
Standard Order Material List
Standard Order Profit/Loss Report
Standard Order Ticket
Standard Order Ticket - Dot Matrix
Standard Order Ticket - Order Item
Standard Order Ticket - Summary
Standard Order Ticket 1
Standard Order Ticket Detailed
Standard Order with Mat/Service
Standard Packing List
Standard Packing List Order Items
Standard Sub Ticket - Order Item
Standard Tech Order Report

Inventory


Standard Inventory by Location Serial #
Standard Horizontal Barcode
Standard Inventory by Location QTF
Standard Inventory Count Sheet
Standard Inventory Items
Standard Inventory Report
Standard Inventory Summary Report
Standard Inventory with Location Details
Standard Vertical Barcode

Inventory Services

None

Inventory Transfer

None

Invoice


Standard Accrued Balance Report/Billing
Standard Accrued Balance Report/Site
Standard Accrued Sales Tax Report
Standard Aging Report by Date Range
Standard All Cash Sales Tax Report
Standard All Invoiced Orders-Compressed
Standard All Invoiced Orders=Suppressed
Standard All Invoiced Orders Detailed
Standard All Invoiced Orders Summary
Standard Batch Invoice
Standard Bill History By Sales Person
Standard Bill History By Service Code
Standard Billing History Column
Standard Cash Excise Tax Report
Standard Cash Sales Tax By Codes
Standard Cash Sales Tax Report
Standard Credit Report
Standard Customer Maintenance Expired
Standard Customer Statement
Standard Customer Statement/Due Date
Standard Incurred Charges Report
Standard Invoice - Order Items
Standard Invoice Profit/Loss Report
Standard Invoice Registry
Standard Invoices Breakdown
Standard Itemized Invoice
Standard Job Cost Report
Standard Labor Report, by Order
Standard Open Invoice Report
Standard Open Invoices Summary
Standard Open Order List
Standard Open Order Report
Standard Open Order Report, No Due Date
Standard Order Materials List
Standard Order Profit/Loss Report
Standard Order Ticket
Standard Order Ticket - Dot Matrix
Standard Order Ticket - Summary
Standard Past Due Invoice Date Cutoff
Standard Past Due Notice
Standard Past Due Summary
Standard Payment Receipt
Standard Prepaid Services Applied, Inv
Standard Sales History Report
Standard Sales Tax Liability-Accrued
Standard Sales Tax Liability-Cash
Standard Services Sold

Issues


Standard Issues Report Summary

Maintenance Contracts


Standard Maintenance Contracts Report

Package


Standard Package List

Parts


Standard Parts Report

Payment


Standard Credit Report
Standard Payment by PayType
Standard Payment Receipt
Standard Payments
Standard Payments by Sales Person
Standard Prepaid Services Applied, Inv
Standard Undistributed Payments

Projects

None

Proposal


Standard Bid and Quotes
Standard Open Proposal Activity by Cust
Standard Open Proposal Activity by Date
Standard Order Item Proposal
Standard Proposal Profit/Loss Report

Purchase Orders


Standard Order JIT Report
Standard Order JIT Report by Order
Standard Purchase Order

Recurring Invoices


Standard Recurring Billing Report
Standard Recurring Invoice Profit/Loss

Recurring Orders

None

Returns


Standard Return Ticket by Vendor
Standard Return Ticket by Ship From

Rolodex

Standard Contact List

Sales


Standard Payments by Sales Person
Standard Sales Leads

Sales Help Desk
Standard Sales Knowledge Base


Scheduled Services


None


Service Order


Standard Closed Order List
Standard Closed Order Report
Standard Customer Maintenance Expired
Standard Job Cost Report
Standard Labor Report, by Order
Standard Material Request, Service Order
Standard Open Order List
Standard Open Order Report
Standard Open Order Report, No Due Date
Standard Order calls - Summary Format
Standard Order Material List
Standard Order Profit/Loss Report
Standard Order Ticket
Standard Order Ticket - Dot Matrix
Standard Order Ticket - Order Item
Standard Order Ticket - Summary
Standard Order Ticket 1
Standard Order Ticket Detailed
Standard Order with Mat/Service
Standard Packing List
Standard Prepaid Service, Service
Standard Service - Dot Matrix
Standard Service Ticket
Standard Sub Ticket - Order Item
Standard Tech Order Report


Tech Help Desk


Standard Tech Knowledge Base


Technician


Standard Closed Order Report
Standard Labor Report, by Tech
Standard Labor Utilization - Actual
Standard Labor Utilization - Estimated
Standard Open Order Report
Standard Open Order Report, No Due Date
Standard Tech Order Report


User


Standard Activities Report
Standard SQLink Name Length Checker


Vehicle


Standard Vehicle Fuel Receipts
Standard Vehicle List
Standard Vehicle Maintenance History
Standard Vehicle Maintenance Scheduled
Standard Vehicle Snapshot
Standard Vehicles
Standard Vehicles Tags Due


Vendor


Standard Vendor Report