In celebration of OTRS 9th birthday *, the community is pleased to publish the following two News/Stories.

Today's News

User Story VIII - 20k savings!

by Shawn Beasley

LH-Cargo Call Center Network needed an application to make things happen. Wait, things were happening: It needed an application to monitor and track these happenings and to communicate with the internal help desk (Paragrine Service Center *SHIVER*) of the LH-Systems. We chose OTRS.

During the evaluation phase, it was a no-brainer. Other solutions did not even come close, and started out at 20K Euros or more. However, the learning curve (at the version 2.0) was still very high) - thank god for the community.

The never tiring community of users helped me through an 18 Month excursion of discovery, planing, installation, configuration, implantation, training, and the cultural experience when introducing such a tool into an environment ruled by MS Outlook and paper trails.

Speaking of this experience reminds me of the largest problem with the cultural change process. This was an agent who was supposed to work with OTRS (as was everyone) explicitly. The only emails that were to be written in and responded to via the email client were emails that were forwarded to him out of OTRS. These could be responded to, because the answer landed in OTRS. The agent would then write emails in his client to communicate with the customer, and paste the communications into a note, complete circumventing all that make OTRS holy and good!

Do not be afraid of the culture shock caused by a centrally managed ticket system, OTRS will win on acceptance if you just keep at it! Thanks OTRS for the 9 Years, and have fun at your party!

Shawn Beasley (shawn.beasley@t-online.de)

Extension VIII - Aloha Editor Integration

by Martin Edenhofer

You want to get the new HTML5 spirit and the hype of Aloha Editor in OTRS?

Here you get a package of the Aloha Editor integration.

Take care, it's still beta. Any feedback is welcome, just leave a note.

The Aloha Editor, is the new HTML5 WYSIWYG with a completely new approach. With this new way you can edit web content much more efficiency. For more see http://www.aloha-editor.org/

Visit Aloha Editor also at jQuery Conf 2011 in SFO and TYPO3 Conf 2011 in SFO!

Prerequisite
OTRS 3.0

Installation
Just install the package via the package manager.

Available via OPAR

  1. http://opar.perl-services.de/bin/index.cgi/package/M/MA/MARTINEDENHOFER/AlohaEditor

Martin Edenhofer (m@edenhofer.de)

Know How II - OTRS Reporting with Pentaho

by Johannes Nickel

Based on the Open Source reporting suite "Pentaho".

Pentaho is Java based and could run on a bunch of Operating System's.

In this Howto I'll show you how to genrate an OTRS report using the OTRS MySQL Database & Pentaho - Report Designer.

There are a lot of features inside the Pentaho suite.

The Report Designer is only a small piece of this suite.

NOTICE: Dont do this on your live System. Some of the Queries may lock or slow down your Database. Make a copy and youse this instead.

Our example report should give us an overview about the total count of created and closed tickets in a period of time per queue.

Here is a preview how the report will look like.

Obviously you could change the layout to fit your needs / CI.

Lets start.

First:

Download Pentaho Reporting Community Edition.

The latest stable build is 3.70, this version is used for this HowTo.

Unpack to a directory of your choice and lets start.

Please run set-pentaho-env.bat(Windows) or set-pentaho-env.sh (Linux/Mac) first! After this you could start pentaho report-designer.bat (Windows) report-designer.sh (Linux/Mac)

Please click "New Report"

So...lets define a data Source.

This is our OTRS databse. In my case "localhost" with MySQL.

In the menu select Data->Add Datasource->JDBC

Select the MySQL Connector and enter you credentials.

Hint: if the connector is not in your list go to: http://www.mysql.de/products/connector/ and download the JDBC Connector from MySQL.

Now lets add the first query to the datasource.

The list of queues in our database.

Push the green "+" enter a name: "Queue" for example

    SELECT q.id, q.name from queue q ORDER BY q.name asc

Hit the "+" again and enter as name: "Closed Tickets"

    SELECT DISTINCT
         count(*) AS anz
         , q.name
         , q.id
    FROM
         `queue` sq INNER JOIN `ticket` st ON sq.`id` = st.`queue_id`
         INNER JOIN `ticket_history` th ON st.`id` = th.`ticket_id`
         INNER JOIN queue q on q.id = st.queue_id
    WHERE
        st.queue_id IN (${queue})
        AND th.history_type_id IN (1,27)
        AND th.state_id IN (2,3)
        AND th.create_time BETWEEN ${startdate} and ${enddate}
    GROUP by q.name
    LIMIT 100000000

And another time ..."+" for our new tickets:

    SELECT
        count(*) AS anz
        , q.name
        , q.id
    FROM ticket st
        INNER JOIN queue q ON q.id = st.queue_id
    WHERE
        st.queue_id = (${id}) 
        and st.create_time between ${startdate} and ${enddate}
    GROUP BY q.name

Lets take a look at this query in the line wich start with st.queue_id IN you see a parameter (${queue}). This Parameter set`s the queue id`s for the user selected queues.

Now take a look at the line starting with "AND st.create_time BETWEEN".

Here you find 2 parameters the user has to set in the interface.

We need a min date (for example 2011-01-01) and an max date (2011-01-31).

Leave this screen an go back to main view.

Right click on "Parameters" and select Add Parameter.

First we need our Queue ID Parameter.

The parameter values are filled by the "Queue" query, the result is a set of id's(queue id's=value id) and the name of the corresponding queue is the display name. We get this from our query Queue (q.name)

We need two more Parameters startdate & enddate. Fill out the form like I did in the screenshot and save it.

Your Sidebar should now look like mine on the screenshot.

Lets design the report itself:

In the page heade we put our logo and the column headers, wich should appear on every page.

Simply drag and drop the elements you want from the toolbar on the left side.

In the "No Data" Area put some default values which will appear when the query fails / or 0 results are returned.

Drag a text field into the detail area and double click on it.

A dropdown appears

Select the Field "name". This Field represents the queue name.

Drag another text field in the detail area and double click again to select the field "anz". which will be our total count of closed tickets.

Now we need a sub report. This subreport will display the "new" tickets.

Drag it from the siedebar to the deatilview an double click on it.

A blank report shows up.

Now we have to link the parameters from the "main" report to the "sub" report. Right click on parameters and select "Edit Sub-report Parameters". Now we link every "internal" parameter to our "external" parameters.

Now drag a textfield to the detail area an select "anz" from the dropdown.

Your report should now look like mine

You should save and close the subreport now.

So now we are ready to fire up :)

Click on the blue eye to preview the report and test everything.

You should now see a list of your queues select one or more an andjust the start and end time.

If you want you could put an image on top of your header, adjust the font of yout text fields to fit your needs. And add a line on top to draw the period you had selected on top of the report.

Drag a message field on your report an adjust the text to:

Statistik im Zeitraum $(startdate, date, dd.MM.yyyy) bis $(enddate, date, dd.MM.yyyy).

I attached the prpt File to this article. You could add this report to a pentaho server so your users could generate reports for theirself.

Remeber to add/change your database to the data sets.

This values are not saved in the prpt file.

If you have any questions feel free to contact me:

Johannes Nickel (johannes.nickel@googlemail.com)

* This is the Birthday Party of the OTRS Software (March 11th 2002, was the first official release of OTRS). Later, the Company OTRS AG was founded in 2003. The Open Source Project was founded in 2001 by Martin Edenhofer.
Copyright © 2011 OtterHub / OTRS Community