Anleitung für Statistik über Datenpilot/Pivot (SQL 2005)

[rhapsodie24]

Hallo liebe Statistikfreaks,

nachfolgend wird hier ein Statitik Tool aufgebaut, dass sehr flexibel gestaltbar ist und meiner Meinung nach allen Anforderungen gerecht wird. Wer nach Aufbau dieses Grundgerüstes etwas übt, hat recht schnell den Dreh raus.

Wichtig bei dieser Anleitung: Schritt für Schritt ausführen und nichts auslassen.

Es werden in einzelnen Schritten sog. 'Views' bzw. 'Sichten' direkt aus der Datenbank 'eazybusiness' aufgebaut. Diese 'Sichten' werden dann direkt aus Excel mit der Pivot Funktion bzw. aus OpenOffice-Calc mit dem Datenpiloten abgefragt.

Wichtige Info: Ich benutze grundsätzlich 'Sichten', da 'Sichten' die Datenbankinhalte nicht verändern können. 'Sichten' können nur lesen. Hier sei auch angemerkt: Nicht direkt in den Tabellen rumfummeln!!!

Da ich eigentlich ein Anwender/User bin und sich mit z.B. sql-Scripten schon mal gar nicht auskennt, bitte ich evtl. nicht ganz fachgerechte Formulierungen zu entschuldigen. Wichtig ist ja nur, dass hier ein funktionierendes Statistkpaket aufgebaut werden kann. Das sollte im übrigen alle ermutigen, diese Schritte durchzuführen. Bei mir hat es auch geklappt.

Wichtig: Bevor man Sichten anlegt immer eine DB-Sicherung durchführen.

Schritt 1

Voraussetzung ist das Aufsetzen von SQL-Express 2005 (gibt es kostenlos bei Microsoft). Wie das geschieht, erfährt man hier:

http://jtl-software.de/faq.php

oder direkt hier:

http://jtl-software.de/forum/viewtopic.php?t=3399

Wenn das Aufsetzen geklappt hat, werden die sog. 'Sichten' mit dem Programm SQL Server Management Studio Express erstellt...

Schritt 2

SQL Server Management Studio Express öffnen (sa – Passwort -> OK). Dann sollte folgendes zu sehen sein:

Abb1:
http://www.audiobook24.com/Bilder_Afterbuy/Abb1.JPG

Danach den Strukturbaum 'eazybusiness' öffnen – 'Sichten' anklicken -> Rechte Maustaste -> 'Neue Sicht...'

Abb2:
http://www.audiobook24.com/Bilder_Afterbuy/Abb2.JPG

Es öffnet sich eine Dialogmaske mit allen eazybusiness Datenbanktabellen:

Abb3:
http://www.audiobook24.com/Bilder_Afterbuy/Abb3.JPG

Da nun zuerst eine Statistik-Sicht erstellt wird, müssen folgende Tabellen ausgewählt werden. Diese Tabellen werden, nachdem sie übersichlich angeordnet sind (einfach anklicken und verschieben), miteinander verknüpft (bei einigen Tabellen passiert das schon automatisch):

tbestellpos
tartikel
tbestellung
tkategorieartikel
tliefartikel
tkunde
tkategorie
tlieferant
tkundenkategorie

Die Tabellen sollten so angeordnet und verknüpft werden wie in Abb4. Verknüpfungen können einfach durch ein Mausziehen (anklicken und gedrückt halten) von einem Tabellenfeld zu einem passenden Feld einer anderen Tabelle erzeugt werden: Beispiel: Aus Tabelle 'tbestellpos' das Feld 'tArtikel_kArtikel' anklicken und mit gedrückter Maustaste in die Tabelle 'tartikel' auf das Feld 'kArtikel' schieben.

Abb4:
http://www.audiobook24.com/Bilder_Afterbuy/Abb4.JPG

Bitte genau prüfen, ob alle Verknüpfungen so gesetzt sind wie in Abb4. angezeigt.

Nachdem die Verknüpfungen gesetzt sind, bitte in den einzelnen Tabellen die Felder anklicken, die in der Abb4 bzw. Abb5 in der Spalte 'SPALTE' aufgeführt sind. Die Tabellennamen stehen rechts in der Spalte 'TABELLE'.

Das Feld 'dErstellt' sollte 'Absteigend' sortiert werden. Das hat zur Folge, dass das Datum in der späteren Statistik immer sortiert ist.

Die Rot-markierten Felder in der Liste können nicht angeklickt werden, da es sich hier um Rechenoperationen handelt. Diese „neuen“ Rechenfelder bitte einfach so abschreiben, wie es dort steht.

In der Spalte 'ALIAS' kann man freie Bezeichnungen eintragen. Diese erscheinen dann später so in Excel bzw. OpenOffice. Die Überschriften, die ich gewählt habe, haben sich bei mir bewährt. (Die eckigen Klammer werden automatisch gesetzt, wenn in der gewählten Bezeichnung ein Leerzeichen vorkommt)

Abb5:
http://www.audiobook24.com/Bilder_Afterbuy/Abb5.JPG

Zum Verständnis: Die Rechenoperationen 'SUBSTRING....' musste ich für die Selektion 'JAHR' und 'MONAT' nehmen, da das Datum in JTL-WAWI nicht in Datumsformat, sondern in Stringformat programmiert wurde. 'SUBSTRING...;4;2' für den Monat sagt aus, dass aus der Zeichenfolge des Datums ab Zeichen '4' nur zwei Zeichen anzeigt werden sollen.

So, das war's auf der Datenbank Ebene. Jetzt das Ganze speichern (Diskettensymbol) und der 'Sicht' einen Namen geben: z.B. Statistik.

Nun erscheint die neue Sicht 'dbo.Statistik' unter dem Ordner Sichten. Um zu prüfen, ob alles stimmt, kann man mit der Maus auf die Sicht gehen -> rechte Maustaste-> SICHT ÖFFNEN. Ist die Sicht leer, dann ist irgendwo in den Verknüpfungen ein Fehler aufgetaucht. Einfach alles mal überprüfen (musste ich übrigens mindestens tausendmal machen)

Programm schließen und OpenOffice-Calc öffnen...

Schritt 3

In OpenOffice-Calc den Datenpiloten aufrufen:

Abb6:
http://www.audiobook24.com/Bilder_Afterbuy/Abb6.JPG

Folgende Option auswählen und bestätigen

Abb7:
http://www.audiobook24.com/Bilder_Afterbuy/Abb7.JPG

Die Datenbank 'eazy' auswählen und in der Datenquelle die gerade erstellte 'Sicht' 'eazybusiness.dbo.Statistik' anklicken und mit OK bestätigen (Vorher wird nach dem Passwort der Datenbank gefragt. Z.B. sa-> sa04jT14):

Abb8:
http://www.audiobook24.com/Bilder_Afterbuy/Abb8.JPG

Jetzt öffnet sich die Matrix und man erkennt sofort die zuvor erstelle Statistik-Sicht:

Abb9:
http://www.audiobook24.com/Bilder_Afterbuy/Abb9.JPG

Jetzt können die Felder entsprechend in die Matrix gezogen werden (anklicken->Maustaste gedrückt halten->verschieben. Wie in diesem Beispiel:

Abb10:
http://www.audiobook24.com/Bilder_Afterbuy/Abb10.JPG

Mit OK bestätigen und schon hat man ein Ergebnis. Dieses Ansicht ist immer wieder änderbar, indem man auf die Tabelle klickt->rechte Maustaste->AUFRUFEN. Oder man verschiebt in der aktuellen Ansicht die Felder so wie gewünscht. Hier gilt: versuchen-ausprobieren-lernen

Um eine übersichtliche Auswertung zu erhalten, schiebe ich nun das Feld 'DATEN' durch anklicken->Maustaste gedrückt halten->VERSCHIEBEN nach oben rechts in die Zeile (grüner Pfeil -> Roter Pfeil)(Abb11, Abb12, Abb13)

Abb11, Abb12, Abb13:
http://www.audiobook24.com/Bilder_Afterbuy/Abb11.JPG

http://www.audiobook24.com/Bilder_Afterbuy/Abb12.JPG

http://www.audiobook24.com/Bilder_Afterbuy/Abb13.JPG


Die Auswahlfelder oben links können ebenfalls einfach verschoben werden. Oder man selektiert wie in diesem Beispiel:

Abb14:
http://www.audiobook24.com/Bilder_Afterbuy/Abb14.JPG


Leider habe ich MSExcel nicht mehr auf meinem Rechner. Aber mit der Excel-Pivot-Funktion funktioniert das fast noch besser.

So: Die erstellte OpenOffice-Datei speichern (Bitte nicht als XLS sondern als .ods). Beim nächsten Öffnen wird nur noch das Passwort der Datenbank abgefragt. Aktualisiert wird durch Tabelle anklicken->rechte Maustaste->AKTUALISIEREN.

Viel Spaß mit der neuen Statistik. Bitte prüft die Zahlen. Sollten Fehler auftauchen, bitte hier veröffentlichen. Hat jemand neue Ideen oder neue Lösungen, bitte ebenfalls hier veröffentlichen.

Nach Ostern stelle ich noch eine weitere Sicht rein: Offene Posten

Gruß und ein frohes Osterfest

Johann

 

[Thomas Lisson]

Tolle Arbeit Johann!
Danke schön für die ausführliche Anleitung!

 

[hanskalk]

Hallo Leute,

ich habe die Sicht genau nach deiner Anleitung erstellt, aber leider zeigt er mir keine Daten an. Kann es an den Verknüpfungen liegen?
Ich habe hier mal einen Screenshot eingefügt, kannst du dir das mal ansehen?

http://www.a-k-f.de/gfx/screen.jpg

 

[rhapsodie24]

Hallo Hans,

die Verknüpfungen scheinen OK zu sein. Wie sehen Deine Formeln aus? Bitte davon mal ein Screenshot!

Ansonsten lass die Formeln mal weg und schaue Dir die Sicht dann mal an. Dann müsste etwas erscheinen.

Gruß
Hans

 

[tom]

Die Statistiken stimmen so nicht ganz:
- Wenn der Kunde keiner Kategorie zugeordnet wird, fehlen die Bestellpositionen ganz
- Wenn der Artikel mehreren Kategorien zugeordnet ist, tauchen die Postitionen auch doppelt auf
- Alle Gutschriften tauchen ebenfalls in der Liste noch auf
- Wenn mehrere Lieferanten zu einem Artikel in der WaWi gepflegt sind,
tauchen diese doppelt auf.

Zum letzterem Problem:

Mein SQL Statement sieht momentan so aus:

SELECT     TOP 100 PERCENT dbo.tbestellung.cBestellNr AS [Auftrags Nr.], dbo.tkunde.cVorname AS [Knd Vorname], dbo.tkunde.cName AS [Knd Name], 
                      dbo.tkunde.cLand AS [Knd Land], dbo.tartikel.cArtNr AS [Art.Nr.], dbo.tartikel.cName AS [Art. Bezeichnung], dbo.tartikel.nLagerbestand AS Bestand, 
                      dbo.tartikel.fVKBrutto AS [Art. VK Brutto], dbo.tartikel.cHersteller AS [Art. Hersteller], dbo.tbestellpos.fVKPreis AS [VK Preis], 
                      dbo.tbestellpos.fVKNetto AS [VK Netto], dbo.tliefartikel.fEKNetto AS [EK Preis], dbo.tbestellpos.nAnzahl AS Menge, 
                      dbo.tbestellung.dErstellt AS [Erstellt am], dbo.tbestellung.dVersandt AS [Status Versand], dbo.tlieferant.cFirma AS Lieferant, 
                      (dbo.tbestellpos.fVKNetto * dbo.tbestellpos.nAnzahl) * ((100 + dbo.tbestellpos.fMwSt) / 100) AS [Ges.Auftr.Wert brutto], 
                      SUBSTRING(dbo.tbestellung.dErstellt, 4, 2) AS Monat, SUBSTRING(dbo.tbestellung.dErstellt, 7, 4) AS Jahr, 
                      (dbo.tbestellpos.fVKNetto - dbo.tliefartikel.fEKNetto) * dbo.tbestellpos.nAnzahl AS [Stück Gewinn netto]
FROM         dbo.tlieferant INNER JOIN
                      dbo.tartikel INNER JOIN
                      dbo.tbestellpos ON dbo.tartikel.kArtikel = dbo.tbestellpos.tArtikel_kArtikel INNER JOIN
                      dbo.tbestellung ON dbo.tbestellpos.tBestellung_kBestellung = dbo.tbestellung.kBestellung INNER JOIN
                      dbo.tliefartikel ON dbo.tartikel.kArtikel = dbo.tliefartikel.tArtikel_kArtikel INNER JOIN
                      dbo.tkunde ON dbo.tbestellung.tKunde_kKunde = dbo.tkunde.kKunde ON dbo.tlieferant.kLieferant = dbo.tliefartikel.tLieferant_kLieferant
WHERE     (dbo.tbestellung.cType <> 'A')
ORDER BY dbo.tbestellung.dErstellt DESC

Bekomme ich bei bei "dbo.tliefartikel.fEKNetto AS [EK Preis]" noch ein DISTINCT rein? Geht das irgendwie?
Ich würde bei mehreren Lieferanten dann einfach nur den ersten nehmen.

Danke,
Tom

EDIT:
Die Gutschriften kann man mit folgender WHERE-Klausel ausschließen:

WHERE     (NOT EXISTS
                          (SELECT     kGutschrift
                            FROM          dbo.tgutschrift
                            WHERE      (kRechnung = dbo.tbestellung.tRechnung_kRechnung)))

 

[fidius]

erstmal ein großes hallo an alle hier
und ein lob an Johann für die gute Erklärung.

ich habe einfach mal schritt für schritt nachgeeifert, sprich nechgebaut. jedoch habe ich dasselbe problem wie 'hanskalk': ich bekomme keine Daten angezeigt (Verknüpfungen mehrmals überprüft!)

jemand eine idee wodrans liegen könnte? ich steh grad mal auf dem schlauch

 

[cruzito83]

Hallo!

@rhapsodie: Könntest du einfach mal deinen funktionierenden SQL-Code posten von der Abbildung 5. So könnte man leichter nachvollziehen, wo der Fehler liegt.

Gruß Mario

 

[swisspower]

Hey ho

folgendes problem

alles geklappt bis hin zum open office

dort kann ich keine eazy datenbank auswählen

wo liegt denn da der fehler??

 

[Jolo]

evtl hast du bereits die sql drauf? welche version von der wawi hast du denn?

 

[swisspower]

die neue 9960er

 

[Jolo]

..dann haste auch die neue Datenbank drauf.

 

[swisspower]

also umsonst gearbeitet???

das ja doof geht das mit der neuen net?

 

[Jolo]

..ich denk das geht auch, musst halt mit dem servermanager auf die SQL-Express-Datenbank zugreifen und net auf die alte datenbank... nur frag wie die genannt ist kann ich dir imo nicht sagen, bin net im Büro... ich glaub aber das steht irgendwo in der wiki.

 

[swisspower]

jtlwawi heisst die datenbank

aber frag mich wie das geht absoluter noob in der sache

 

[Jolo]

Ich bin später im Büro (bei uns ist Feiertag) da kann ich mal auf meinem Testsystem schauen, da läuft die 9960er. Ausm Kopf kann ich dir das leider auch nicht sagen.

 

[swisspower]

okaqy hab es selber rausgefunden oder war blind beim lesen

manmuss die datenbank eazy anmelden dann gehts auch

trotzdem danke für die hilfe

 

[scoutladen]

hmmm... verstanden hab ichs wohl, aber so recht gelingen will mir die sicht auf die statistik nicht.

hier mein aktuelles sql

SELECT     dbo.tartikel.nLagerbestand AS Bestand, dbo.tartikel.cBarcode AS EAN, dbo.tartikel.cArtNr AS [Art-Nr], dbo.tkategorie.cName AS [Kat-Bezeichnung], 
                      dbo.tbestellpos.fVKNetto AS [VK-Netto], dbo.tbestellpos.nAnzahl AS Menge, dbo.tbestellpos.fVKPreis AS [VK-Preis], dbo.tbestellung.dErstellt AS Datum, 
                      dbo.tbestellung.dVersandt AS [Status Versand], dbo.tbestellung.cBestellNr AS [Auftrags Nr.], dbo.tkunde.cName AS [Knd Name], 
                      dbo.tkunde.cLand AS [Knd Land], dbo.tkunde.cFirma AS [Knd Firma], dbo.tKundenKategorie.cName AS [Knd Gruppe], 
                      dbo.tbestellpos.nAnzahl * dbo.tbestellpos.fVKNetto AS Gesamtumsatz, (dbo.tbestellpos.nAnzahl * dbo.tbestellpos.fVKNetto) 
                      * (100 + dbo.tbestellpos.fMwSt) / 100 AS [Ges.Auftr.Wert], SUBSTRING(dbo.tbestellung.dErstellt, 4, 2) AS Monat, SUBSTRING(dbo.tbestellung.dErstellt, 
                      7, 2) AS Jahr, dbo.tartikel.cName AS [Artikel-Bez.], dbo.tlieferant.cFirma AS Lieferant
FROM         dbo.tlieferant INNER JOIN
                      dbo.tKundenKategorie INNER JOIN
                      dbo.tkunde ON dbo.tKundenKategorie.kKundenKategorie = dbo.tkunde.kKundenKategorie INNER JOIN
                      dbo.tkategorieartikel INNER JOIN
                      dbo.tartikel ON dbo.tkategorieartikel.kArtikel = dbo.tartikel.kArtikel INNER JOIN
                      dbo.tkategorie ON dbo.tkategorieartikel.kKategorie = dbo.tkategorie.kKategorie INNER JOIN
                      dbo.tbestellpos ON dbo.tartikel.kArtikel = dbo.tbestellpos.tArtikel_kArtikel INNER JOIN
                      dbo.tbestellung ON dbo.tbestellpos.tBestellung_kBestellung = dbo.tbestellung.kBestellung INNER JOIN
                      dbo.tliefartikel ON dbo.tartikel.kArtikel = dbo.tliefartikel.tArtikel_kArtikel ON dbo.tkunde.kKunde = dbo.tbestellung.tKunde_kKunde ON 
                      dbo.tlieferant.kLieferant = dbo.tliefartikel.tLieferant_kLieferant

dabei fällt im übrigen auf, dass in abb. 4 und 5 in der liste die artikelbezeichnung und der lieferant fehlen. beides wird allerdings später in open offive verwendet.

wo hab ich nun einen fehler eingebaut?

 

[scoutladen]

uppss...

hab das nun mit weiteren sichten und einfacheren scripten probiert. im mssse zeigt die sicht dann auch was an.

nur in openoffice kann ich die nicht mehr auswählen. lediglich die als erste angelegte dbo.Statistik ist zu sehen.

wo sind die anderen sichten?

 

[Jolo]

Abb4:
http://www.audiobook24.com/Bilder_Afterbuy/Abb4.JPG

Bitte genau prüfen, ob alle Verknüpfungen so gesetzt sind wie in Abb4. angezeigt.

könnte mir jmd die Verknüpfungen auflisten, irgendwie werd ich aus dem Bild nicht wirklich schlau...

 

[Jolo]

*blubb... nochmal anschieb