| Author |
Message |
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
   Posted: 09 Nov 2011 at 4:18am |
also for your grouping formuals you might want to use a final else to cature any items you missed. Helps with finding errors in your process or handles future additions to the database options in a standard way. If you leace a space in front of the verbage it make sure it is always first or last in the sorting process (asc/desc)
if {table.logreason} in ['MAC','Service','Training'] then 'Billable' else if {table.logreason} in ['Warranty','Lunch'] then 'Installation time' else if {table.logreason} in ['Travel'] then 'Travel Time'
else ' Category Not Identified'
|
 IP Logged |
|
chadbrewyet
Newbie
Joined: 26 Oct 2010
Online Status: Offline
Posts: 22
|
Posted: 09 Nov 2011 at 5:40am |
I can see why you are a senior member.  Everything works just like I want it.
If I may bother you just one more time for a little more customization.
When we go to bill, any logged time under an hour is billed for a full hour and any time over an hour is rounded to the next half hour (i.e.- 45 min = 1 hour, 1hr 15min = 1hr 30min, 1hr 50min = 2hr) Is there an easy way to handle the rounding using this process? My managers would like to see this if it is possible. This would only apply to the Billable category but they would like to see both the amount of time that was actually logged that was Billable and the Rouded Billable time as well.
Also, for the Cross-Tab, is there a way to move the Total to the bottom? And is there a way to include categories that have a value of 0? So if there was no time logged for Travel, it would show 0:00.
Thanks again. You have been a huge help!
Edited by chadbrewyet - 09 Nov 2011 at 5:44am
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 09 Nov 2011 at 5:51am |
for rounding the bill when does it occur?.. at the data row?at the day? at the technician level?
The crosstab total issue:
right click on the word total
select 'Row grand totals'
select 'totals on top' (should currently have a check mark next to it, when you select it it will uncheck it and move it to the bottom)
Using a Crosstab there is no (easy) way to add rows with zero if that category does not exist at in the group.
If this is critical there are other ways to do it but they tend to be less efficent and more labor intensive and make the report require potentially more long term maintenance.
If you still need it to do this I can show you how using Running Totals (RTs) instead of a CT.
|
|
IP Logged |
|
chadbrewyet
Newbie
Joined: 26 Oct 2010
Online Status: Offline
Posts: 22
|
Posted: 09 Nov 2011 at 5:58am |
The 0 total is not a big deal. If is is going to be too much work, I am all for keeping it easy.
For the rounding, it would be done at the TimeLog level. So for each 'Billable' TimeLog, they would like to see the actual time logged as well as the rounded time. All other categories would not be rouded.
|
|
IP Logged |
|
chadbrewyet
Newbie
Joined: 26 Oct 2010
Online Status: Offline
Posts: 22
|
Posted: 09 Nov 2011 at 6:01am |
|
Oh, and in the Crosstab, if a value converts with minutes under 10, it is only showing a single digit. (6 hrs 8min - 6:8. Needs to show 6:08)
Edited by chadbrewyet - 09 Nov 2011 at 6:22am
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 09 Nov 2011 at 6:50am |
for the "00" change the display formula to
totext(floor(currentfieldvalue/60),0,'00') + ':' & totext(remainder(currentfieldvalue,60),'00')
billing rounding....maybe this?...
if {table.logreason} in ['billable logreason1','billable logreason2','billable logreason3'] then
({table.loghours}*60)+(60*(ceiling(({table.logminutes}/60),(.5))))
Edited by DBlank - 09 Nov 2011 at 6:52am
|
|
IP Logged |
|
chadbrewyet
Newbie
Joined: 26 Oct 2010
Online Status: Offline
Posts: 22
|
Posted: 09 Nov 2011 at 7:46am |
That rounding formula didn't do exactly what I needed but I think I was able to get it through a series of If/Then statements. Also, I just told my boss it will just be easiest to only see the rounded billable and she was ok with that.
You have been a tremendous help.
|
|
IP Logged |
|
chadbrewyet
Newbie
Joined: 26 Oct 2010
Online Status: Offline
Posts: 22
|
Posted: 08 Mar 2013 at 6:03am |
|
I know this is an old topic but I have another request if anyone would oblige.
Everything done previously has been working flawlessly since it was implemented. Now, our managers (grrrr) have decided to credit travel time as billable time. I know it would be easy just to move that Log Reason to the "Billable" category but there's a catch (there always is).
The managers only want to credit half of the travel time as billable but still want to credit the full amount of travel time to the "Travel" category. So, if a travel log took an hour, 30 min would be credited to Billable and the full 60 would go to Travel as well.
I would imagine I am setting myself up for quite a bit of work.
Thanks.
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 08 Mar 2013 at 6:45am |
i have not reread the enirte posting but it appears that you are using one final field to do all of your calculations. It will be difficult in not impossible to include the one row (travel) into two buckets.
If youc an alter your source via a stored proc to duplicate those rows and apply the half time rule there. The recategorize the halfed to a new name type (travel bill?). Now you could pull two rows of data into the report and not have to change much at all.
anothr possiblility would be to just pull the travel time into a command, recategorize then name in the type, half teh time and then uinion that command into the rest of the data
|
|
IP Logged |
|
chadbrewyet
Newbie
Joined: 26 Oct 2010
Online Status: Offline
Posts: 22
|
Posted: 08 Mar 2013 at 9:44am |
|
I don't think the stored procedure will work well for us because the database is pretty locked down by the developer. I am interested in your other 2 options, though.
By "pulling the travel time into a command", do you mean by using a separate formula just for travel?
|
|
IP Logged |
|
|
|
