Part Total For Each Job

Hi,

Is there any way I can produce a report exportable for an excel spreadsheet that contains a field that allows me to show the total part costs for each individual job based on showing approximately 590 workorders.

I also need to be able to show the sites postcode and workorder item type.

Many thanks - Emma

p.s. I am a novice when it comes to using report templates so simple language would be appreciated. :smiley:

I forgot the following field required

Emma (7/2/2008)Hi,

Is there any way I can produce a report exportable for an excel spreadsheet that contains a field that allows me to show the total part costs for each individual job based on showing approximately 590 workorders.

I also need to be able to show the sites postcode and workorder item type & category.

Many thanks - Emma

p.s. I am a novice when it comes to using report templates so simple language would be appreciated. :smiley:

Hi Emma

Yes, you can create a report with these specific fields of data from a workorder from the Service Workorder grid.

If you would like me to create this report for you, as outlined on the forum topic Custom-report-templates we can do so, but would need the following from you to provide you a quote on the cost to create this for you:

  • A mockup of the report you would like (in Excel) identifying what chacters in the mock up is data and what are labels etc

  • Any additional information you believe may be relevant that will help me understand exactly how you want the report to come out

  • Attach the above to an email directly to support@ayanova.com along with referencing this topic, and I will get back to you ASAP with a quote to create this for you.

  • Joyce

Hi Joyce,

Thanks for your response, however I would like it if I could understand how to make the report work so I could re-use the function in other reports I create.

I can get the other fields I require to display fine, it’s just the logistics or working out the total part charge on each work order that I am struggling with. So far my attempts have either produced a duplicate of the first part item on the first item on every record of the report or it has produced a grand total for everything.

Emma

Hi again Emma

If you export your report template to a file, zip it using WinZip, attach it to this forum topic reply identifying by its name(s) exactly what field you have a question about, exactly what you want to do etc than I can see it and point you in the right direction if it is possible

  • Joyce

Hi Joyce,

I have attached zip file containing current exported information. I have also added comments to the colums reference what I am trying to do. If this is still not clear and I am not explaining myself very well, please contact me.

You can always e-mail me direct on sales@bar-tech.co.uk.

Many thanks for your understanding & most of all your patience!

Emma :smiley:

Hi again Emma
Thank you for attaching the Excel mock up so that I understood what you were looking for.

Unfortunately no - I spoke too soon. In your mockup, you want only the final complete total for all parts for each workroder to show - correct? For a total to be arrived at with a detailed report template from the Service Workorders grid, at minimum each part record’s net total in the each workorder item of the workorder must actually display on the report so that a running total of all of them can be obtained.

For example:

Open up the Sample Detailed Service Workorder with Grand Total report template.
Do a Print Preview in the designer so that you can see the totals at the bottom of reports for different workorders.
Note how if there are parts in this workorder, that the report shows details about each part including its Net Total etc
Note how in Print Preview under the black line at the bottom of a report there is the Net Parts, Grand Total etc and there is a $ amounts under these?
Now go back to the designer
Click on the DetailReportItemPart - WorkorderItemWorkorderItemPart band to select it, and than select the Properties tab
For the Visible property, set this to False (what we are doing here is mimicing what would show in the Net Parts total at the bottom of the report if you did not show the individual parts in the workorder itself
Now select Print Preview again
Note how the workorder report where previously it showed a Net Parts total if there was parts in this workorder, now shows $0?
This is because in a detailed report template, the final totals are determined from a running total of what shows in the Parts band (the one you set Visible to False above)
If you select the field xrNetPart (at the bottom of the report template designer) and expanded its Scripts property, you will see that it has a OnBeforePrint script which tells it to gets its total from a running total setup from the Part Net Total field from the details band of DetailReportItemPart - WorkorderItemWorkorderItemPart
(you may want to cancel out of this report template designer so that your changes are not saved)
Because of this that a running total of all the parts net totals must be shown in the report itself before you can get a actual total for the workorder, you can not have your report show as per your Excel spreadsheet.

Instead, what you could do is create a “summary” report from the Parts grid in the Service navigation pane. You will not get a full invoice net total of parts for each workorder, but you can get it to show you the Net Total for each record of each part. You also do not have access to show the client’s postal code.

I have attached a example report template (example1.zip) made from the Parts grid simply using the Wizard where I select each of the fields (Client, Category, Workorder Summary, Net Total), and than set the currancy format on the Net Total datafield.

I have also attached a second example (example2.zip), where again using the Wizard I did the same as above, but grouped by client so that could than do a Sum value of all the part Net’s for that client (and than a little cosmetic clean up of labels and sizes at the end). See again, for the report to show a total gathered from the records, those amounts themselves have to show - so this would not be useful for exporting to Excel - I would suggest use of the first example that you can further customize as needed.

  • Joyce

Also check out the new example at topic Example-report-showing-grand-total-on-single-line-useful-for-exporting-too