There’s probably a really simple way to do this but I can’t find it. What I want to do is add travel and expense data to the “Sample Scheduleable User Billing Hours Report With Grand Total”. If I’m understanding the new report engine (Xtra Reports) the data is arranged in a hierarchy and each band in the report can only include data from its level or lower and there is no way to query the database manually. Looking at the dataset for this report, I can’t figure out a way t do this.
You are correct - if reporting from one of the "sub"grids such as Schedulable Users grid or Travel grid, the data avaialble is for the specific record and those columns that are showing in that grid.
I.E. In Schedulable Users grid, you can report off of who is scheduled for the workorder item, times scheduled etc and its workorder, but the columns showing do not show Travel data.
Whereas if you want Schedulable User info, plus Expenses, plus Travel - you either can report using a Detailed report template from the Service Workorders grid or from Items grid.
- Joyce
OK. Change of strategy. The “Sample Total Billable Grouped By Project Includes No Charge” would work perfectly if I could group it by User. In order to do that I need some kind of user id in the WorkorderHeader or WorkorderHeaderWorkorderItem. I tried grouping by WorkorderHeader >> WorkorderHeaderWorkorderItem >> WorkorderItemWorkorderItemSchedledUser >> User WorkorderItemSchedledUser.UserID but that didn’t work since it’s lower in the hierarchy than the subsequent bands in the report. I also tried grouping by WorkorderItemSchedledUser >> User WorkorderItemSchedledUser.UserID but that didn’t work since there is no labour, travel, or expense data under it. Maybe this is turning into a feature request to include a scheduled user id in the WorkorderHeader because I can’t see another way to do this.
Hi again
The report template “Sample Total Billable Grouped By Project Includes No Charge” is grouped by Project - because there can only be one Project per workorder ever.
It is not possible to display the scheduled users with the workorder record in the Service Workorders grid. The Service Workorders grid displays one record for the workorder itself. As a workorder could have many workorder items, and each workorder item could have many scheduled users, many labor records, many expenses, many travel records, etc - it could not possibly display every possible scheduled user, or labor data etc as there is only one possible record for the workorder.
You could group by Client, you could group by Workorder Status, you could group by Workorder Category, ou could group by Project from the Service Workorder grid etc as these are from the Workorder header itself, but it is not possible to group by schedulable user which is as you say not a groupable datafield because it is not the “top” most record. Or print out reports from each of the other grids specifically for that data that grid provides
- Joyce
So there is no way to generate a report that returns hours, travel, and expenses grouped by user. I can get the info I want in 3 separate reports (labour by user, travel by user, and expenses by user) but there is no way to do it in one report, like this:
User A
Date Hours Rate Travel Rate Expenses
5/27/08 5 25.00 30 0.45 19.99
etc
etc
Take a look at the design of this attached detailed service workorder report template - I have attached an example zipped using WinZip,download and import. It is called Example Charges for Workorders report. Note this is a very rough example only, it would not be expected to be used live as is.
Note how you have a top band for the workorder header fields, than a band under that for the workorder items, and than under the workorder item band, band for each Labor, Misc Expense, Travel. Because this is a “detailed” report template, there are layers - bands - and each band is for data from a specific area of a workorder.
You can include fields from the band “above” into a lower band - i.e. fields from the workorder header into the Labor band such as in my example I have included the Workorder # and the Service Date; but you can not include fields from the same level bands together - i.e. you can not include datafields from Misc Expense into a Labor band area, as the report designer won’t have it tied to the correct band for that workorder.
The report designer won’t know from what dataset you want to get the data from if you did put all datafields into one band. For example, if you did putdatafields from Misc Expense and Travel into the Labor band, if there was more than one Misc Expense record in that workorder item, it won’t know to grab the data from other than one Misc Expense record so you would miss that information.
Do note that each is grouped by the workorder (shown by the workorder number when you preview) which is the “root” of the data for this report.
The sample report templates from the post http://forum.ayanova.com/Topic2424-103-1.aspxdon’t neccessarily have the datafields showing the user for the specific record, but you can easily add those user fields into the design of the report template - just be sure that you are adding the user datafield for the Labor dataset into the Labor band, the user datafield from the Misc Exp into the Misc Exp band etc. If you want a report specificallygrouped by the user for that record, you would print out a report from each of the specificgrids itself - from the Labor grid, from the Travel grid, from the Misc Expense grid. Otherwise, a report from the Service Workorders grid can only be grouped by one of “its” datafields - client, Workorder Category, Project, etc.
- Joyce
--------------- Quote ------------------
If you want a report specifically grouped by the user for that record, you would print out a report from each of the grids itself - from the Labor grid, from the Travel grid, from the Misc Expense grid. Otherwise, a report from the Service Workorders grid can only be grouped by one of “its” datafields - client, Workorder Category, Project, etc.
------------- End Quote -----------------
So there is no way to to do it in one reports, like I could the old version (1.9.3). I now need to run 3 reports. I just wanted to make sure.