JDONE Need help creating a Workorder Labor Summary Management Report

Background Information: We need to produce a monthly Management summary report for a customer that shows all of the

service we have performed in the previous month. This customer has multiple branches.

We need to print this report and list each workorder, the workorder category, the workorder summary, the date of service, and the workorder total labor hours, sorted by branch office, and workorder number.

The report needs to look like this:

Workorder Labor and Labor Hours Summary

Net
Workorder Workorder Category Summary Service Date Hours


Client1Branch1

7580 Service James is unable to print 11/14/2006 .50
7586 Service QuickBooks’ crashed 11/17/2006 .75
7595 Administration Create AD user account for Victor 11/21/2006 .25

Client1Branch2

7423 Scheduled MaintenanceExchange Server Maintenance 11/03/2006 1.00
7441 Service Add E-mail account for Stacey 11/09/2006 .25
7486 Installation Setup Savin copier for network printing 11/16/2006 1.50

C

Hi Bob

There is not an existingsample report template made that matches what you have listed.

Do attach a copy of the report template you have created if you would like me to take a look at it.

  • Joyce

Joyce,

I would appreciateyoutaking a lookatit.

Here it is.

Thanks,

Bob

Hi Bob

Thank you for attaching the report template. I will take a look at it as soon as I can and post back.

  • Joyce

Hi Bob

Sorry but there were a number of things funky with that report template you provided - data fields were not pointing towards the WorkorderHeader dataset and subsets, more than one workorder item per workorder was not displaying, etc

I’ve instead recreated the basic layout of the report template and it is available via http://forum.ayanova.com/Topic2401-103-1.aspx

This report template is accessible via the Labor grid in the Service navigation pane - as from the sample you provided what you want to provide is only labor hours.

  • Joyce

Hi again Bob

Also posted another Labor type report template made via a detailed available for the Service Workorders grid (also at http://forum.ayanova.com/Topic2401-103-1.aspx

  • Joyce

Hi Joyce,

Thank you for the templates!

The first template is good but itprovides more detail than the customer wants to see on a report -everyworkorder item.

Thesecond template http://forum.ayanova.com/Topic2401-103-1.aspx is veryclose to what they desire.

After viewing how you summedeach workorder’s labor hours (that is only what we need to print out on the report) I changed my thinking tomakethe workorder items (details) invisible anduse the WO Scheduled date for the Service Date. Then all that needs to be figured out is how to show only the Net Hours per workorder on the report.

I know one thingthat would really make this simple. If the Report Writerfolks could provide a NET ‘labor’ field choice similar to the present NET’ dollar amounts’ then all we would have to do is drag and drop.

Thank you again for your help.

Bob

Hi Joyce,

I havecreateda summary report in the format I need usingthe sample templates you provided “Thank You”… and through lotsof persistence. :smiley:

When I achieved the result I was looking for I couldn’t leave well enough alone and decided I also wanted the total number of all labor hours per customer to be displayed on the report. The only way I saw to accomplish this was through the use of scripts. The scripts you created in the “Scheduleable User Billing & Hours Report” looked perfect for the task, however,I am unable to get an accurate total numbers of hours per customer.

Would you please take a look at my template and tell me where I am going wrong?

Thanks,

Bob

Hi Bob

I downloaded your report template and imported it, andopened it in the report designer

Your report templateis completely designed differently than Sample Schedulable User Billing Hours with Grand Total - you have no detailBand, your scriptsare all in the GroupHeader instead of referring to the actual datafields and databand, completely different setup then the sample report template provided- resulting in there not being a way with your report template to do what you want.

If you had instead followed the format of the Sample Schedulable User Billing Hours with Grand Total sample report template, than you would have been able to utilize similar scripts provided in that sample.

As it is, your report template can not use the scripts from Sample Schedulable User Billing Hours with Grand Total to get what you want. I would not know how to script towork with the way you have designed this report template as it is not following the standards laid out in the sample report templates that I have previouslyprovided.

I would recommend the following:

That you refer back to the Sample Schedulable User Billing Hours with Grand Total - make a copy of it (named Client Billing Hours with Grand Total for example), and customize it with the fields you want to see.

For example, instead of groupHeaderBand1 displaying by Schedulable User - change it so it is by Client. Change the datafield xrLabel2, and in property of groupHeaderBand1 itself, change the GroupFields Collection to sort by Client, not by User.

In detailBand1, remove the data fields you do not want (Service Rate, Rate Unit, etc), and add in the ones you do (Workorder Category, Summary etc) leaving the NoChargeQuantity and Service Rate Quantity as these already are referred to in the script in the detailBand1 script property OnBeforePrint

In GroupFooter1, remove the table cells you do not want to show (Total Net For This User, etc), perhaps also changing the text of the label fields for No Charge Total and Billable Total for that client - as well as perhaps move the fields to the right so they line up under the No Charge Hours and Billable Hours from the detailBand1 band.

In ReportFooter, remove the fields for xrGrandTotal and Grand NetTotal for All as this is no longer being processed.

And then you would have your report template that shows no charge hours and billable hours per client, plus a total of those two per client, and then a grand total of all billable hours and no charge hours at the very end of the report template.

  • Joyce

Hi Joyce,

The template was not based off Schedulable User Billing Hours. I only borrowed the scripts from the Schedulable User Billing Hours because they produced a single summary of total hours. I was trying to get the total hours to display once per client group, and to appear to the right of the total billable and no charge hour’s columns.

I tried movingthe scripts to the different bands, and I even added the band name to the scripts but the debugger kept popping up with and error that the context was invalid - I am sure thiswas due to my lack of C++ scriptinglanguage knowledge.

Scripting aside, the reason the total billable and no charge hours’ data fields are in the group header is this was the only way I could get them toappear once in summary fashion per client group. When I moved them into the details or footer bands I would end up with repeating rows of total hours’ summaries per client group.

I will recreate as you recommend.

I appreciate you taking a look at this and thank you for your time,

Bob

Hi again Bob - good to hear that you will customize the existing Sample Schedulable User Billing Hours with Grand Total report template - it already has the scripts in place which would be a lot easier for you to customize to look design-wise the way you want it to be as the actual sums and scripts are already there.

Perhaps you might upload the finished report template as an attachment for other AyaNova users when the suggestions above are completed?

  • Joyce

Hi Joyce,

I do want to share it but as you can see in it’s present state :w00t:it’s nowhere near ready for prime time.

Even though it is in lessthan a pristine state, our service supervisor is now runningitdaily to quickly spot if techs are getting in over their heads.

I appreciateall of your help, guidance… and patience.

Bob

Hi Bob

You are welcome.

I’ve alsouploaded a new sample report template for the Labor grid based on theSample Schedulable User Billing Hours with Grand Total report templatein topic http://forum.ayanova.com/Topic2598-103-1.aspx

This report template is similar to that you were making other than that it doesn’t show the workorder category, or Summary field - plus it still shows the actual billing amount in dollars still. This report template can of course be customized further to add those fields or remove the fields not needed as well as changepositioning or lengthof fields, but as the Sample Schedulable User Billing Hours with Grand Total report template already had the scripts, it was very straight forward to edit.

  • Joyce

Hi Joyce,

I have completed my Masterpiece… :blink:

I did as you suggested, used the Sample Schedulable User Billing Hours with Grand Total report, and started over. This gave all of the information required.

The only problem I encountered , no matter what I tried, Workorders that had more than a single Workorder Item would repeat for every extisting Workorder Status item.

Using your template as a guideline I was able to have the total number of hours appear at the bottom of each Customer Group. When I printed the report I could see that having a Grand total next to each work order was now unnecessary. I then eliminated the details and all scripts except for the Date field. I left that script in order to remove hours from the date.

Although the template may look a bit unorthodox, it now prints exactly as I want; in Summary fashion, displaying each Workorder once per customer group, and next to each Workorder is displayedthe Contact, WO Date (scheduled date), WO Category, WO Summary, total Billable Hours and No Charge Hours per work order. The total number of Billable and No Charge hours aredisplayed at the bottom of each customer group, and a Grand Total of all these hours appears at the end of the report.

I have verified all pertinent data within the Workorder is extracted properly by the Report Writer and the hours displayed are correct.

I needed to display a Service date range at the top of the report. I was able to do this by creating a text label box that can be easily edited on-the-fly to manually enter a date range; open the template, enter the dates, save and print.

There is one minor problem when printing the report. Although the template layout is set to LetterRotated (Landscape) mode, the template does not send the Landscape codes to the printer, so this needs to be done manually at print time.

I have attached the template and hope others may benefit from using it. Run the report from the Service Workorders>Labor grid.

If anyone would like to spruce it up please feel free to do so.

Thanks,
Bob

Hi Bob

Great to see AyaNova users posting their own custom reports!

To set a report template to use Landscape, set the Landscape property for the xtraReport1 to True. This will automatically set the page width and length too.

The PaperKind property is for the “kind of paper” used - such as “Letter” for normal North American printers, “A4” for European, etc. This may be why the printer does not print landscape mode.

  • Joyce