In the article “JTL-Wawi – Solving Direct Sales with Sales Employees“, I explained the requirements for the sales evaluation for sales employees and already outlined a solution approach. Subsequently, in the article “LibreOffice Base + JTL-Wawi – connection of JTL-Wawi database” shows how to connect to the database using the ODBC interface and in the article “JTL-Wawi – Secure access to the SQL database” is used to show how the connection is secured so that only read rights are available for the evaluation. Now I’m going to start the evaluation with LibreOffice Calc. But before I read the data into Calc and evaluate it with a pivot table, I need to include an intermediate step.

JTL-Wawi uses the Microsoft SQL server. This is a relational database system. For me, this means that the information is distributed over several tables in the database and connected to each other via so-called relations. These relations are not necessarily taken over by LibreOffice Base. So I need to create a query over the appropriate database tables so that I can then evaluate the data from the query in LibreOffice Calc. To do this, I open my eazyBusiness.odb again, click on Queries under Database on the left, and then click on “Create query in design view…”

An empty query design window and the “Add table or query” window will open. From the “Add Table or Query” window, I now select the tables I need for my evaluation and with a click on add, the tables are added to the query design. I selected the following tables for my evaluation:

• tOrderCornerData
• torder
• trechnung
• tkunde

In the upper half of the query design window, I now have all the tables I need for my query. Here I have arranged the table in the window so that in the next step I can set the relations between the corresponding table fields with the mouse.

A relation is the relationship between two fields. If we look at the table tOrderCornerData and tOrder, we see that the field kBorder exists in both tables. In the tables, these fields are easy to recognize because they usually have a key symbol in front of them that stands for primary key (without me going into more detail now). The kBestellung field in both tables is used to link the records so that they can be displayed later in the forms and reports. To create the relation in LibreOffice Base, I left-click on the kBestellung field in the tBestellungEckDaten table, hold down the mouse button and then drag the mouse to the kBestellung field in the tbestellung table and release the left mouse button. The process again I pick up for the fields:

• Table tcustomer: kcustomer – tcustomer_kcustomer
• Table calculation: tOrder_kOrder – kBOrder

After that, my layout window looks like the screenshot below.

Okay now I can create my query in the bottom half of the query designer window. Column 1 of the query should contain the year of the invoice. I can find the creation date of the invoice in the table trechnung. Unfortunately, however, only as a date in the form day month year. That’s why I need to extract the year from the date. I do this with the SQL function YEAR(date). So I enter in the line Field the function

YEAR(trechnung.dErstellt)

and in the line Alias I enter the value Year. This gives me the year. I do the same in the second column, only that I use the function MONTH(Date) and store it as alias month.

I have cleared the first hurdle with it. Now I can conveniently filter the data by year and month later in the pivot table. Then I just drag and drop the cInvoiceNo field from the trechnung table and the cOrigin and cCompany fields from the tCustomer table into my query design. I connect the fields cFirstName and cName from the table tCustomer, in which I simply enter in the next column

tKunde.cVorname + „ „ + tKunde.cName

and enter the value Name as alias. Now two columns are missing for my evaluation. Once the value of the order minus credits and vouchers and the commission calculated from this. So in the next column I enter the following formula in the Field row:

tBestellungEckDaten.fWert – tBestellungEckDaten.fGutschrift – tBestellungEckdaten.fGutschein

in the line Alias I enter value. In the same way, I calculate the commission for the sales representative. Here I just need to take the formula from the Value column and put it in parentheses and multiply by 0.03 to get a 3 percent sales commission. The next column thus contains the following formula in the Field row:

(tBestellungEckDaten.fWert – tBestellungEckDaten.fGutschrift – tBestellungEckdaten.fGutschein) * 0.03

Please note that instead of the comma a point is written in the formula! In the Alias line I enter the value Provision.

The whole thing then looks like this:

If you want to make it easy for yourself, you can switch to the SQL view under View – Switch Design View On/Off and copy the following code into the text window:

SELECT YEAR( trechnung . dErstellt ) AS Jahr , MONTH( trechnung . dErstellt ) AS Monat , trechnung . cRechnungsNr AS ReNr , tkunde . cHerkunft AS Herkunft , tkunde . cFirma AS Firma , cVorname + + cName AS Name , tBestellungEckDaten . fwert - tBestellungEckdaten . fGutschrift - tBestellungEckdaten . fGutschein AS Wert , ( tBestellungEckDaten . fwert - tBestellungEckdaten . fGutschrift - tBestellungEckdaten . fGutschein ) * 0.003 AS Provision FROM eazybusiness . dbo . tbestellung AS tbestellung , eazybusiness . dbo . trechnung AS trechnung , eazybusiness . dbo . tkunde AS tkunde , eazybusiness . dbo . tBestellungEckDaten AS tBestellungEckDaten WHERE tbestellung . kBestellung = trechnung . tBestellung_kBestellung AND tbestellung . tKunde_kKunde = tkunde . kKunde AND tbestellung . kBestellung = tBestellungEckDaten . kBestellung

Then switch on the design view again and save the query as qryUmsaetze. Now the database can be closed again.

The last step now consists of creating a pivot table for the evaluation in an empty LibreOffice Calc workbook. I open Calc and choose Data → Pivot Table → Create… from the menu.

In the Select Source window, I check “Data source logged into LibreOffice” and click OK.
I select eazyBusiness as my database, then I restrict the type to query and select my data source qryUmsaetze.
Now I can drag the fields from Available Fields to the correct areas of my pivot table. Year I drag by page fields, Origin by column fields. I drag month, company and name by row fields. Value and commission go into the data fields and I leave the Sum setting in front of them. I remove data by clicking on the entry and then pressing the Delete key on the keyboard. Now I can already click OK to create my pivot table.

That was it. I can now save this table. I don’t have to worry about the data anymore. A simple refresh under Data → Pivot Table → Refresh is sufficient to bring the data of the table up to date. For this purpose, it is not even necessary to enter the password.