Transcription
Crystal Reports
Table Of ContentsReports . 1Reports Overview . 1Entering the Crystal License Code into ServiceCEO. 1Report Categories . 2Report Types. 2Customer Reports . 2Scheduling Reports . 3Employee Reports. 5Financial Reports . 7Quotes/Estimates Reports . 10Products/Services Reports . 11Printing/Previewing Reports . 11Standard Report Preview page . 15The Crystal Reports Report Preview page . 15Exporting Report Data . 16Exporting Data from a Standard Report . 17Exporting Data from a Crystal Reports Report . 17Editing Crystal Reports . 18Creating a New Crystal Report . 19Crystal Reports Fields . 20Crystal Reports Fields . 20Adding Fields to a Crystal Report . 21Formatting Crystal Reports Fields . 22Formatting Numbers in Crystal Reports . 24Grouping Fields . 26Advanced Crystal Reports Editing . 26Formulas Overview . 26Editing Crystal Reports Formulas . 27Report Syntax . 28Creating Crystal Reports Formulas . 28Casting Numbers as Currency . 29iii
Crystal ReportsRunning Totals Overview . 30Creating a Running Total . 30Data Sources Overview . 32Adding Tables. 32Stored Procedures . 33Subreports . 33Parameters . 34Saving Crystal Reports in ServiceCEO . 41The Report List. 43Managing the Report List . 43Adding New Reports to ServiceCEO . 44Adding Reports to Jobs, Estimates and Projects . 45Editing Report Description and/or Section . 46The RPT File Location . 46Removing Crystal Reports from ServiceCEO . 48Additional Information . 48Index . 51iv
ReportsReports OverviewServiceCEO contains a large number of reports. These reports are designed to help yougenerate useful data to help you make better business decisions.All of the ServiceCEO reports can be accessed by clicking Reports on the Navigator Bar.For more, see the Printing/Previewing Reports section.Tip:To navigate through the reporting topics in order, please click on thebuttons at the top of the page.andEntering the Crystal License Code into ServiceCEOTo create or customize Crystal Reports for use within ServiceCEO, you need either:A full version of Crystal Reports 9.0. At the moment, only Crystal Reportsversion 9.0 works with ServiceCEO.An Integrated Crystal Reports Designer License. For licensing and pricingdetails, point your browser to ing/crystalreports/. You can also purchase one from Online Store in theMember Center. After purchasing your license, you will receive a license codefrom Insight Direct. The license can be purchased directly from thehttp://www.insightdirect.com/login.htmlNote:The report designer within ServiceCEO provides a 14-day trial (from the date offirst use), but after this time you will need to purchase a license to use thedesigner.Tip:If you are not comfortable with editing your own reports, we can always createyour report for you! To learn more, please reporting/Custom report quotations can be purchased directly from the Online Store in theMember Center.If you have the full version of Crystal Reports, you can create or customized reports rightaway. See the Editing Crystal Reports and Adding New Reports sections.If you have purchased the Integrated Crystal Reports Designer License code, follow thesesteps to enter it into ServiceCEO:1. Login to ServiceCEO as an Administrator.1
Crystal Reports2. Select Help Enter Crystal Reports License. The Confirmation dialog boxappears. The License Code dialog box appears.3. Select Yes when prompted to continue.4. Type the Integrated Crystal Reports Designer License code into the field. Notethat any code you were provided with the full version of Crystal Reports will notwork here.5. Click OK.6. Close ServiceCEO.7. Login to ServiceCEO as an Administrator. The report designer is now enabled.Note that only the Administrator can edit Crystal Reports through ServiceCEO.Tip:If you have a full version of Crystal Reports version 9, you can create or edit areport in that program and then import your changes into ServiceCEO. See theManaging the Report List section for details.Report CategoriesReport TypesThere are two types of reports in ServiceCEO:Active Reports. Indicated by , these reports are default ServiceCEO systemreports. These reports cannot be customized.Crystal Reports. Indicated by , these reports are built using Crystal Reports .If you have purchased an Integrated Crystal Reports Designer License, you canedit the structure and format of these reports.Customer ReportsServiceCEO's Customer reports display information about your customers. These reportsinclude:NameDescriptionReportTypeAlert ActivityDisplays all alerts for a given date range bycustomer and alert type.CrystalReportBirthday andAnniversaryGenerates a list of all customers with a birthdayor service anniversary (based on the EnteredDate) within your defined date range.CrystalReport2
ReportsCredit CardExpirationGenerates a listing of all customers whose creditcard expires within your defined date range.CrystalReportCustomer ListingGenerates a list of all customers and theiressential contact information (name,organization and phone numbers). You cangenerate a listing of all corporate customersand/or individual customers.ActiveReportLocation AccessDetailsGenerates a listing of customers with theiressential data and the values in their AlarmCode and Access fields as defined in theAddress Information dialog box.ActiveReportMarketingModuleGenerates printed mailing labels, conducts mailmerges, and exports contacts to Microsoft Excel . See the Demographic Filters section foran explanation of the filters in this report.ActiveReportNotesGenerates a list of all customer notes in yourdate range. You can also include system notes notes generated by pirationGenerates a list of all equipment warrantiesexpiring within your defined date range.CrystalReportService ContractExpirationGenerates a list of all service contracts expiringwithin your defined date range.CrystalReportTo learn how to print and/or preview these reports, see the Printing/Previewing Reportssection.Scheduling ReportsThe Scheduling Reports display information about your schedules and jobs. These reportsinclude:NameActive RecurringSchedulesDescriptionGenerates a listing of all schedules with astatus of “Active Recurring” within yourdefined date range. You can sort theseschedules by customer name or by the dateReportTypeActiveReport3
Crystal Reportsthey are scheduled to occur.Crystal RouteSheetGenerates a route sheet for every team workingon jobs for every day in your defined daterange.CrystalReportE-mailable WorkOrderGenerates a basic work order that is ready to beemailed to your customers.CrystalReportJob CancelationsGenerates a listing of all job and/or schedulecancellations within your defined date range.CrystalReportMy TasksGenerates a listing of tasks assigned to thecurrent user within the defined date range.CrystalReportNew SchedulesGenerates a listing of all new jobs andschedules created within your defined daterange. You can display these schedules bydate, customer, frequency, or campaign.ActiveReportProjectManagementGenerates a detailed summary of all projectsand project visits within your defined daterange.ActiveReportRoute SheetGenerates a listing of all jobs for each day inthe defined date range. The Classic Style isgrouped by date while the CLIP style isgrouped by team.ActiveReportRoute Sheetincluding TasksIdentical to the standard route sheet reportexcept that it includes tasks.CrystalReportScheduleCancelationsDisplays all schedules that have been cancelledin the defined date range.ActiveReportCrystalReportScheduleFrequency ReportWeekly ScheduleGenerates a detailed summary of the schedulefor the week. You can organize this report forall teams by date or by team for each week.CrystalReportWork OrderGenerates all the information an individual orteam of employees needs to know for a singlejob, including any customer or job notes. Thework orders are generated based on theselected date range with a single work orderCrystalReport4
Reportsgenerated for every scheduled job within yourselected date range. The work order report canbe run for just a particular client or a particularteam of employees.To learn how to print and/or preview these reports, see the Printing/Previewing Reportssection.Employee ReportsPrevious Topic: Scheduling ReportsServiceCEO Employee Reports display information about your employees. These reportsinclude:NameDescriptionReportTypeBirthday andAnniversaryGenerates a list of all customers with abirthday or service anniversary (based on theHire Date field) within your defined daterange.CrystalReportEmployee LossGenerates a list of all schedules that have beencancelled or rescheduled within your selecteddate range. Also includes the employee whoserviced the customer before the schedule wascancelled or rescheduled so that you canidentify problematic schedules along with theemployees who are losing customers.CrystalReportEmployee PayrollSummaryDisplays all pay for all employees within thedate range (based on the date you select whencreating a paycheck). If a payroll item has notyet been included on a paycheck, it will notappear in this report. Other information thatappears on this report includes:CrystalReportEmployee Name. All employees withat least one paycheck in the date rangewill appear.Pay Types. The amount of payment foreach payment type will appear in itsown column. Payment types are salary,hourly pay, commissionable pay, flatfees, tips, and miscellaneous pay.Total Pay. The total payment for each5
Crystal Reportsemployee in the date range.Grand Totals. The totals for everypayment type, and total pay for allemployees in the date range, appears atthe bottom of the report.Tip: Once run, you can take the informationfrom this report and input it into youraccounting package, where you can deducttaxes, etc. from it and generate the actualpaycheck. You can do this in two ways:1. Export this gross pay to another formatand import it into your accountingpackage. For details, read the ExportingReport Data section.2. Manually enter the gross pay for eachemployee into your ys all jobs, tasks, and adjustments in theselected date range. This report was known asthe Payroll report in versions of ServiceCEObefore 6.0.CrystalReportListingGenerates a listing of all current employees,including the employee‟s name, SSN, title,home phone, work phone and e-mail address.The Reservice Rate is the amount of times anemployee has worked on a job that hasgenerated a reservice divided by the number ofjobs the employee has worked on. The threenumbers in the report column show thepercentage for three time periods:ActiveReportThe last 90 daysThe last yearAll jobs for the employeeNote: A reservice is created by clicking the Redo button on the customer's Jobs tab.The Rank column's value is determined byadding up all the note ranks on the employee'sPersonnel File tab. Each note has a rankassociated with it when it is created, that is6
Reportsstored on the employee's personnel file. Thethree numbers in the report column show theemployee‟s rank for three time periods:The last 90 daysThe last yearAll jobs for the employeeMailing LabelPrints mailing labels for your employees. Ifyou do not filter the report, ServiceCEO willprint labels for all employees in the list.ActiveReportNotesBased on the contents of an employee‟sPersonnel File tab, the Notes report can be runfor all employees, a select employee and/or fora specific note type.CrystalReportPaycheckSummarizes the employee's paycheck(s) in thedefined date range, details the paid jobs andtasks, and its corresponding monetaryinformation. Designed so you can easily exportthis information into a third party accountingapplication if necessary.CrystalReportTo learn how to print and/or preview these reports, see the Printing/Previewing Reportssection.Financial ReportsServiceCEO's Financial Reports display information about your company‟s payment,deposit, and revenue details.Tip:The data that appears on ServiceCEO's financial reports is controlled by thePosted value. Please see the Posting Invoices section for details.These reports include:NameAccountsReceivableDescriptionGenerates a list of each customer‟s outstandingpayments broken down to increments ofcurrent, 30 days, 60 days and 90 days. Invoicesappear on the report based on the date of theinvoice, even if the invoice's job(s) are notReportTypeCrystalReport7
Crystal Reportscompleted.By default, the Detailed A/R report onlydisplays customers with a non-zero (positive ornegative) balance.Business AnalysisReportSummarizes your company‟s financial factors.Note that projects are not included in thisreport.ActiveReportCampaign ROIDisplays the return on your investment fromyour campaigns.CrystalReportEstimated JobCost DetailDisplays a forecast of job costing andprofitability of scheduled jobs in a defined daterange.CrystalReportEstimated JobCost SummaryDisplays a forecast of job costing andprofitability of scheduled jobs in a defined daterange.CrystalReportCredit CardTransactionIf Credit Card Processing is enabled, displaysall processed credit card transactions in thedate range you define.CrystalReportInvoiceGenerates an invoice for every completed jobwithin your defined date range.CrystalReportJob CostingJob Costing at the summary level.CrystalReportJob Costing - LineItem Level DetailsA detailed Job Costing report.Caution: Due to the amount of data containedin this report, it may take some time to open.Please be patient.CrystalReportJob Costing Caution:Travel time will only appear in the Job Costing report if:The employee is paid an hourly rate.The Cost travel time to: check box is selected in the Payroll Optionsdialog box. See the Defining Payroll Options section for details.In addition, if you have more than one team assigned to different visits of aproject, the project will show as unassigned on the Job Costing report. The JobCosting - Line Item Level Detail report will show you the specific employeecosting information for the project.8
ReportsJob PODisplays every inventory product assigned to ajob during the report's date range, along withthe job number(s) on which it is being used andthe Purchase Order (PO) line item(s) oradjustment from which the stock originated.CrystalReportPast DepositsDisplays details associated with payments thatwere deposited to an accounting packagewithin the defined date range. If desired, thesedetails can include any notes that may havebeen added from the Deposit Wizard. Inaddition, the Past Deposits report can includeall deposits in the selected date range or belimited to specific deposit IDs.ActiveReportPending DepositDisplays all completed payments that have notyet been deposited—i.e., those displayed in theDeposit tab of the Office Duties section.ActiveReportPurchase OrderDisplays all Purchase Orders in the selecteddate range. Only used if you are using theInventory module.CrystalReportQuickBooksExportsTransactionA detailed transaction report displaying all ofyour exports to QuickBooks for a defined daterange.CrystalReportRevenueDisplays projected or actual revenue for yourdefined date range. The report can becustomized to include only revenue based on aparticular customer or campaign. This reportwill use a job's date of service and not the job'sinvoice date.ActiveReportService ContractGenerates a listing of all service contracts inthe system for your defined date range.CrystalReportService ContractCostingGenerates a listing of all costs associated withservice contracts in your defined date range.CrystalReportStatementsGenerates a summary of invoices createdduring your defined date range. These reportscan be used for general information purposesor as actual bills to be sent to the customer forremittance. Will only include customers withoutstanding balances.CrystalReport9
Crystal ReportsTransaction - ByJob DateWill display all job-related transactions(charges, payments, and adjustments) forcompleted jobs. Projects will be displayed inthe following manner:CrystalReportFixed project charges will appear onthe project's first visit date, if at leastone visit is completed.Variable project charges will appear onthe visit to which they are assigned, ifthe visit is completed.Transaction - ByInvoice DateWill display all transactions present oninvoices on the invoice date. Project chargeswill appear when the invoice is posted (bydefault, when the final visit of the project iscompleted).CrystalReportTo learn how to print and/or preview these reports, see the Printing/Previewing Reportssection.Quotes/Estimates ReportsServiceCEO's Quotes/Estimates Reports display specific information about the quotesand estimates in the system. These reports include:NameDescriptionReportTypeEstimateGenerates a list of all estimates during yourdefined date range.CrystalReportEstimateTrackingDisplays a listing of all jobs that originated asan estimate in your defined date range.ActiveReportQuoting WizardCanceled QuotesDisplays all quotes that were canceled in yourdefined date range and the notes that wereentered when you canceled them.ActiveReportQuoting WizardQuotes by UserDisplays all quotes organized by user duringyour defined date range.ActiveReportQuoting WizardQuotes withSchedulesDisplays all quotes that involve schedules foryour defined date range.ActiveReportQuoting WizardDisplays all quotes that do not involveActive10
ReportsQuotes withoutSchedulesschedules for your defined date range.ReportTo learn how to print and/or preview these reports, see the Printing/Previewing Reportssection.Products/Services ReportsServiceCEO's Products/Services Reports display information about your company‟sproducts and services. These reports include:NameDescriptionReportTypeInventory Levels– By StockLocationDisplays the current inventory levels for everystock location. Also displays the total value ofinventory per stock location as well as for theentire company.CrystalReportInventoryTransactionDisplays the flow of inventoried parts throughServiceCEO, from the time they are received tothe time they are s the current stock level thresholdquantities and comments for every activeinventory item. Note that this report willinclude all active products, not just thoseactively used in inventory tracking.CrystalReportMaster Productsand ServicesDisplays a list of either all products, services,or all products and services in ServiceCEO.CrystalReportTo learn how to print and/or preview these reports, see the Printing/Previewing Reportssection.Printing/Previewing ReportsTo print and/or preview a ServiceCEO report:1. Click Reports from ServiceCEO's Navigator bar. The Available Reports dialogbox appears.11
Crystal Reports2. Select the appropriate report category. There are six report categories:1. Customer Reports2. Scheduling Reports3. Employee Reports4. Financial Reports5. Quotes/Estimates Reports6. Products/Services Reports3. Highlight the report you want to run.4. Click Select. The Print Options dialog box appears.12
ReportsThe Print Options dialog box enables you to define the criteria for the report. This criterianormally includes:Print Style. Some reports let you choose between different versions of the report(e.g., Summary and Detailed versions).Group By. Some reports let you define the grouping order of the report results byselecting it from the Group By drop-down list. (Note that this option is notavailable in the above screenshot.)Print Range. Many reports require that you select the range of dates for whichyou want to display information.Check Boxes. Some reports let you include or exclude information depending onwhat check boxes are selected. In the screenshot above, you have the option toinclude your company information on the report header and to display any add-onlines that may be present.Copies. If printing the report, select the number of reports you want printed fromthe Number of Copies spin box.Additional report criteria may be available on the Contents tab. Examples of possibleadditional criteria includes:Customers. You can include all customers in the report by keeping the AllCustomers check box selected.To run the report for a specific customer, deselect the All Customers check boxand type search criteria for the customer you want to appear in the field beneaththe All Customers check box. You can search by customer name or by customerID. A list of customers corresponding to your search criteria appears in the listbox. Select the customer you want to display from this list.Options. You can filter the report by the values you select from the drop-downlists in the Options section. Only values that match the criteria you define herewould appear in the final report.13
Crystal ReportsCompany Structure. If you are running ServiceCEO Enterprise, you can filterthe report by company structure. Depending on your security rights and loginlevel, you may not see this tab.Some reports, such as the Revenue and Marketing Module report, have more extensivereport criteria options. For a report of this nature, you would define your report criteria byselecting a tab or tabs in the first section and defining the appropriate criteria. Next, youwould select the remaining criteria—the sections numbered two and three—beforepreviewing or printing your report.For all reports, you can either print your report directly or preview it before you decide toprint the report. To print the report without seeing it first, click Print. The Report willautomatically print on your default system printer.To preview the report, click Preview. The Report Preview page appears. For more onStandard reports, see the Standard Report Preview page section. For more on CrystalReports reports, see the Crystal Reports Report Preview page section.14
ReportsStandard Report Preview pageThe Report Preview page enables you to view your report before you print it out. Tonavigate between the report records, use these buttons:Previous recordNext recordThe Crystal Reports Report Preview pageTo preview a Crystal Reports report, click Preview from the Print Options page. TheReport Preview page appears.15
Crystal ReportsThis page will be different if previewing a Standard report. See Standard Report Previewpage for more.The Report Preview page enables you to view your report before you print it out. Tonavigate between the report records, use these buttons:First recordPrevious recordTo print the report, clickNext recordLast record.Exporting Report DataWhile ServiceCEO‟s reports are extremely useful, there may be time where you want tomanipulate the data contained within them. To do this, you can export report data todifferent formats.16
ReportsExporting Data from a Standard ReportYou can export data from one of ServiceCEO's Standard reports into the followingformats:Microsoft Excel . Click Excel Export. Saves the report in a Microsoft ExcelformatPDF. Click PDF Export. Saves the report in .pdf format, so it can be opened withAdobe Reader.RTF. Click RTF Export. Saves the report in Rich Text Format.HTML. Click HTML Export. Saves the report in a .html format so it can beopened with a browser like Microsoft Internet Explorer .Clicking one of these buttons displays a Save As dialog box, where you can navigate toand select the location on your computer where you want to save the exportedinformation. Once saved, you can open the file with the appropriate program.Exporting Data from a Crystal Reports ReportTo export data from a Crystal Reports report:1. Click. The Export Report dialog box appears.2. Select the format to which you want to export the report from the Format dropdown list. Your options are:oAcrobat Format (PDF). Will save the report as a .pdf file.oHTML 3.2. Will save the report in a .html file, compliant with HTML 3.2.oHTML 4.0. Will save the report in a .html file, compliant with HTML 4.0.oMS Excel 97-2000. Saves the report in a Microsoft Excel format, withformatting.oMS Excel 97-2000 (Data only). Saves the report in a Microsoft Excelformat, with no formatting.oMS Word. Saves the report in Microsoft Word format (.doc).oRich Text Format. Saves the report in a .rtf format.oTab-separated text. Saves the report in a .ttx format.oText. Saves the report in a .txt format.17
Crystal Reports3. Click OK. A Save As dialog box, where you can navigate to and select thelocation on your computer where you want to save the exported information.Once saved, you can open the file with the appropriate program.Editing Crystal ReportsTo customize a Crystal Report within ServiceCEO, you must first preview it. For detailson previewing a report, see the Running/Previewing Reports section.If you have a full version of Crystal Reports, you can use it to edit any Crystal Reportoutside of ServiceCEO.Tip:For a template you can use to create a brand new crystal report, download thefollowing report file: rptNew.rpt. Add this report to ServiceCEO, preview it, thencustomize it based on the steps below.From the Report Preview page, click Customize to edit the report. The Design Viewpage appears:18
ReportsNote:If the Customize button is not active, you do not have a Crystal Reports license.To edit Crystal Reports reports, users must either purchase a full copy of CrystalReports version 9.0 or obtain an Integrated Crystal Reports Designer Licensethrough Insight Direct. Go to ing/crystalreports/ to learn more about obtaining an Integrated CrystalReports Designer License.Tip:You must be logged into S
Crystal Reports 2 2. Select Help Enter Crystal Reports License. The Confirmation dialog box appears. The License Code dialog box appears. 3. Select Yes when prompted to continue. 4. Type the Integrated Crystal Reports Designer License code into the field. Note that any code you were provided with the full version of Crystal Reports will not .
