report of time per agent // outside reporting
Ok, So if I go into the SQL box and run a query, I can pull up how many "work units" each agent accumulated in the current month: SELECT t.create_by, u.first_name, u.last_name, Sum(t.time_unit) FROM time_accounting AS t Left Join system_user AS u ON t.create_by = u.id WHERE month(t.create_time) = '08' AND year(t.create_time) = '2008' GROUP BY t.create_by However, for the life of me, I don't see how to create a report showing the same. So I'm looking for any report gurus out there or some good documentation/howtos on the subject. Also, has anyone gone into using an outside program to pull reports from OTRS (like Crystal Reports or DataVision)? -John -- John Hogenmiller - ytjohn@gmail.com Used for mailing lists - sporadic response
Have you tried otrs-manager ? version 0.5.1 generates a agent/ticket
table stat in word, there should be easy applying a vbmacro to create
a grafical stat one, this could just an option with it.
the link to that tool: otrs-manager.sourceforge.net/index.php?page=homepage_en
On Thu, Aug 14, 2008 at 9:08 AM, John (yt) Hogenmiller
Ok,
So if I go into the SQL box and run a query, I can pull up how many "work units" each agent accumulated in the current month:
SELECT t.create_by, u.first_name, u.last_name, Sum(t.time_unit) FROM time_accounting AS t Left Join system_user AS u ON t.create_by = u.id WHERE month(t.create_time) = '08' AND year(t.create_time) = '2008' GROUP BY t.create_by
However, for the life of me, I don't see how to create a report showing the same.
So I'm looking for any report gurus out there or some good documentation/howtos on the subject.
Also, has anyone gone into using an outside program to pull reports from OTRS (like Crystal Reports or DataVision)?
-John
-- John Hogenmiller - ytjohn@gmail.com Used for mailing lists - sporadic response _______________________________________________ OTRS mailing list: otrs - Webpage: http://otrs.org/ Archive: http://lists.otrs.org/pipermail/otrs To unsubscribe: http://lists.otrs.org/cgi-bin/listinfo/otrs Support or consulting for your OTRS system? => http://www.otrs.com/
-- Marco Vannini home: +39 02 900091454 mobile: +39 335 5340819 msn: marco69v@hotmail.com talk: marco.vannini@gmail.com icq: 58389316 skype: marco69v
Creation of specific reports is relatively easy if you decide to use OTRS framework for report generation. For each specific report you need a module placed in $OTRS_HOME/Kernel/System/Stats/Static The module should have the following two methods: Param method should get the time frame for the report and to return it in an array. It should look like: sub Param { my $Self = shift; my ($Select, %Agents, @row); # get current time my ($s, $m, $h, $D, $M, $Y); my ($Ymin, $Ymax, $Select, %Agents, @row); ($s, $m, $h, $D, $M, $Y) = $Self->{TimeObject}->SystemTime2Date( SystemTime => $Self->{TimeObject}->SystemTime(), ); # get one month before if ($M == 1) { $M = 12; $Y = $Y - 1; } else { $M = $M -1; } # create possible time selections $Select = qq(SELECT EXTRACT(YEAR FROM (MIN(change_time))), EXTRACT(YEAR FROM (MAX(change_time))) FROM otrs.time_accounting ); $Self->{DBObject}->Prepare( SQL => $Select); ($Ymin, $Ymax) = $Self->{DBObject}->FetchrowArray(); my %Year = (); foreach ($Ymin..$Ymax) { $Year{$_} = $_; } my %Month = (); foreach (1..12) { $Month{$_} = sprintf("%02d", $_); } push (@$Params, { Frontend => 'Year', Name => 'Year', Multiple => 0, Size => 0, SelectedID => $Y, Data => { %Year, }, }, ); push (@$Params, { Frontend => 'Month', Name => 'Month', Multiple => 0, Size => 0, SelectedID => $M, Data => { %Month, }, }, ); return @Params; } Run method does the actual computation. It has to return "return ([$Title],[@HeadData], @Data)" $Title holds the report title, @HeadData holds column headers and @Data is an array of @Row, where each @Row is an array of row values. You can get data from user input via %Param e.g. sub Run { my ( $Self, %Param ) = @_; my @HeadData = ('Created', 'First name', 'Last name', 'Time units'); #get the time period my $Year = $Param{Year}; my $Month = $Param{Month}; my ($DBObject, $Select ); my (@Data, $Title); $DBObject = $Self->{DBObject}; $Select = qq(SELECT t.create_by, u.first_name, u.last_name, Sum(t.time_unit) FROM time_accounting AS t Left Join system_user AS u ON t.create_by = u.id WHERE month(t.create_time) = $Year AND year(t.create_time) = $Month GROUP BY t.create_by ); $DBObject->Prepare( SQL => $Select); #Read SQL result and put it in @Data return ([$Title],[@HeadData], @Data); } These are just code fragments, not a real module, so you may need to fix some little things. I didn't sent the modules we use because our OTRS installation is integrated with custom CRM and the tables we have are different. After module creation you have to go Stats->New, to choose Static-File and to select the module from select box. I hope it will help. Best, Atanas Karashenski BlueBoard LLC
Thanks Atanas! This was a very useful post!!!! Cheers, d. -----Messaggio originale----- Da: otrs-bounces@otrs.org [mailto:otrs-bounces@otrs.org] Per conto di Atanas Karashenski Inviato: giovedì 14 agosto 2008 11.39 A: otrs@otrs.org Oggetto: Re: [otrs] report of time per agent // outside reporting Creation of specific reports is relatively easy if you decide to use OTRS framework for report generation. [bla bla bla]
participants (4)
-
Atanas Karashenski -
CARNINO Daniele (FIAT SERVICES) -
John (yt) Hogenmiller -
Marco Vannini