Kreuztabellen in Berichten

Kreuztabellenabfragen erlauben die zweidimensionale Gruppierung und Berechnung von Daten. Leider ist standardmäßig nur die Datenblattansicht vorgesehen. Mit ein paar Tricks zeigen Sie das Ergebnis von Kreuztabellenabfragen jedoch auch in Berichten an. Dieser Beitrag zeigt, wie Sie Berichte für verschiedene Kreuztabellen erstellen, und erläutert, welche besonderen Kniffe für die Darstellung von Daten mit dynamischen Spaltenüberschriften nötig sind.

Das erste Beispiel soll die Umsätze der Mitarbeiter über die einzelnen Kategorien auflisten. Das gewünschte Ergebnis finden Sie in Abb. 1. Beginnen wir beim Aufbau der Kreuztabelle: Diese enthält alle Tabellen, um die Zusammenhänge vom Mitarbeiter über die Bestelldetails bis zur Artikelkategorie zu beschreiben, also tblPersonal, tblBestellungen, tblBestelldetails, tblArtikel und tblPersonal. Die Kreuztabelle bildet allerdings nur drei Felder ab, die jedoch teilweise mehrere Felder zu einem berechneten Feld zusammenfassen. Das erste Feld heißt Personal und fasst die Inhalte der Felder Nachname und Vorname des jeweiligen Mitarbeiters zusammen. Dieses Feld liefert die Daten für die Zeilenüberschriften. Das zweite Feld fügt die Kategorienamen als Spaltenüberschriften zur Kreuztabellenabfrage hinzu. Der aus den Feldern Einzelpreis, Anzahl und Rabatt berechnete Umsatz liefert schließlich die Grundlage für die nach Mitarbeiter und Kategorie gruppierten Daten. Den kompletten Entwurf dieser Kreuztabellenabfrage finden Sie in Abb. 2.

pic001.png

Abb. 1: Darstellung einer Kreuztabelle in einem Access-Bericht

pic002.png

Abb. 2: Diese Kreuztabelle dient als Datenherkunft für den Bericht

Aufbau des Berichts

Wie bekommen wir nun die Daten der Kreuztabellenabfrage in den Bericht Grundsätzlich gelingt dies wie bei einem herkömmlichen Bericht. Dazu fassen wir das Abfrageergebnis unabhängig davon, dass es sich hierbei um eine Kreuztabellenabfrage handelt, als ganz normales Abfrageergebnis mit Feldnamen und Datensätzen auf.

Der erste Unterschied zu einem herkömmlichen Bericht ist, dass die Spaltenüberschriften nicht den Feldnamen der zugrunde liegenden Abfrage entsprechen, sondern den per Kreuztabellenabfrage ermittelten Spaltenüberschriften.

Der zweite Unterschied ist, dass auch die Textfelder zur Anzeige der Daten im Detailbereich nicht an echte Felder einer Tabelle gebunden werden, sondern an Ausdrücke, die auf den Spaltenüberschriften der Kreuztabellenabfrage basieren – in diesem Fall also beispielsweise Getränke, Gewürze et cetera.

Bedeutet das nun Mehraufwand Nein! Sie brauchen der Eigenschaft Datenherkunft des Berichts einfach nur den Namen der Kreuztabellenabfrage zuzuweisen. Danach stehen die generischen Felder in der Feldliste zur Verfügung, sodass Sie diese einfach nur wie gewohnt in den Detailbereich des Berichtsentwurfs ziehen und anschließend anordnen müssen (s. Abb. 3). Fertig – der erste Bericht auf Basis einer Kreuztabellenabfrage steht!

pic003.png

Abb. 3: Entwurfsansicht des Kreuztabellenberichts

Spaltenüberschriften einschränken

Steigern wir den Schwierigkeitsgrad ein wenig. Zum Beispiel, indem wir den Benutzer festlegen lassen, welche Kategorien im Bericht berücksichtigt werden sollen. Wenn der Benutzer nun also entscheidet, dass statt der vorhandenen acht Kategorien nur zwei Kategorien in der Kreuztabelle und somit auch im Bericht abgebildet werden sollen, können wir den soeben erstellten Bericht nicht so einfach weiterverwenden. Immerhin wäre ein Eingriff in den Entwurf des Berichts notwendig, da ja nur noch sechs Felder mit Umsätzen angezeigt werden sollen.

Zunächst einmal muss dazu die zugrunde liegende Abfrage angepasst werden. Dies erreichen Sie, indem Sie ein Kriterium für das Feld Kategoriename festlegen (s. Abb. 4). Dies können Sie direkt unter dem als Spaltenüberschrift festgelegten Element im Entwurf der Kreuztabellenabfrage erledigen, indem Sie dort einen Ausdruck wie IN("Getränke";"Gewürze") eintragen.

pic004.png

Abb. 5: Kreuztabellenabfrage mit eingeschränkter Spaltenzahl

Das Kriterium wird aber nach dem Speichern und erneuten Öffnen der Abfrage in ein eigenes Feld mit der Funktion Bedingung ausgegliedert.

Das Ergebnis der Abfrage in der Datenblattansicht liefert schon einmal die gewünschten Werte (s. Abb. 5). Nun müssen wir dieses noch in den Bericht übertragen. Dass dies in der aktuellen Konfiguration nicht funktioniert, war abzusehen – es erscheint eine Fehlermeldung, dass einer der Kategorienamen nicht als gültiger Feldname erkannt werden kann.

pic005.png

Abb. 6: Entwurf des Berichts mit nur noch zwei Kategorien der Kreuztabellenabfrage

Kein Wunder: Der Bericht enthält immer noch sechs Textfelder, deren Eigenschaft Steuerelementinhalt auf die Felder der per Kriterium ausgeblendeten Spaltenüberschriften der Kreuztabellenabfrage zeigt. Wenn Sie die notwendigen änderungen manuell durchführen wollten, müssten Sie schlicht und einfach die überzähligen Felder entfernen, in diesem Fall also alle Felder außer Getränke und Gewürze.

Der Entwurf sieht nun wie in Abb. 6 aus. Damit wäre das Problem grundsätzlich gelöst, allerdings nur für diese spezielle Konstellation. Für jede andere Zusammenstellung der Kategorien müssen Sie nun erneut in den Entwurf des Berichts eingreifen.

pic006.png

Abb. 4: Einschränkung der Spaltenüberschriften einer Kreuztabellenabfrage

Dynamische Berichtserstellung

Der erste Ansatz, dieses Problem zu lösen, ist das dynamische Erstellen eines Berichts in Abhängigkeit von den gewählten Kategorien. Das heißt, dass der Bericht bei jedem Aufruf gelöscht und neu erstellt wird. Diese Lösung finden Sie in Listing 1.

Listing 1: Erstellen eines Kreuztabellenberichts per VBA

Public Sub Kreuztabellenbericht_Erstellen(strDatenherkunft As String, intAnzahlZeilenueberschriften As Integer)
    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim rpt As Report
    Dim ctl As Control
    Dim i As Integer
    Dim strBericht As String, strBerichtTemp As String
    Dim sngSpaltenbreite As Single
    strBericht = Replace(strDatenherkunft, "qry", "rst")
    Set db = CurrentDb
    Set rst = db.OpenRecordset("SELECT * FROM " & strDatenherkunft & " WHERE 1=2", dbOpenDynaset)
    Set rpt = Application.CreateReport
    On Error Resume Next
    DoCmd.Close acReport, strBericht
    DoCmd.DeleteObject acReport, strBericht
    On Error GoTo 0
    strBerichtTemp = rpt.Name
    rpt.RecordSource = strDatenherkunft
    rpt.Section(acPageHeader).Height = 400
    rpt.Section(acDetail).Height = 330
    sngSpaltenbreite = 1400
    For i = 0 To rst.Fields.Count - 1
         Set ctl = Application.CreateReportControl(strBerichtTemp, acLabel, acPageHeader, , , _
            i * sngSpaltenbreite, 200, sngSpaltenbreite, 300)
        ctl.FontSize = 9
        ctl.TextAlign = 2
        ctl.ForeColor = &H0
        ctl.FontBold = True
        ctl.Caption = rst.Fields(i).Name
        ctl.Name = "lbl" & rst.Fields(i).Name
        ctl.BorderStyle = 0
        If i < intAnzahlZeilenueberschriften Then
            ctl.TextAlign = 1
        End If
        Set ctl = Application.CreateReportControl(strBerichtTemp, acTextBox, acDetail, , , _
            i * sngSpaltenbreite, 0, sngSpaltenbreite, 300)
        ctl.FontSize = 9
        ctl.TextAlign = 2
        ctl.ForeColor = &H0
        ctl.ControlSource = rst.Fields(i).Name
        ctl.Name = "txt" & rst.Fields(i).Name
        ctl.BorderStyle = 0
        If i < intAnzahlZeilenueberschriften Then
            ctl.FontBold = True
            ctl.TextAlign = 1
        Else
            ctl.TextAlign = 3
        End If
    Next i
    rpt.Printer.Orientation = acPRORLandscape
    DoCmd.Save acReport, strBerichtTemp
    DoCmd.Close acReport, strBerichtTemp
    DoCmd.Rename strBericht, acReport, strBerichtTemp
    DoCmd.OpenReport strBericht, acDesign
End Sub

Möchten Sie weiterlesen? Dann lösen Sie Ihr Ticket!
Hier geht es zur Bestellung des Jahresabonnements des Magazins Access im Unternehmen:
Zur Bestellung ...
Danach greifen Sie sofort auf alle rund 1.000 Artikel unseres Angebots zu - auch auf diesen hier!
Oder haben Sie bereits Zugangsdaten? Dann loggen Sie sich gleich hier ein:

Schreibe einen Kommentar

Kreuztabellen in Berichten

Martin Hoffmann, Düsseldorf

Access bietet sowohl dem Einsteiger als auch dem Profi zahlreiche Hilfsmittel und Assistenten, um komplexe Berichte sehr leicht gestalten zu können. Es gibt Assistenten für fast jeden Zweck. Aber in Access gibt es keine Berichte, bei denen die Spaltenanzahl von den Daten abhängt, wie es bei Kreuztabellen vorkommt. Sie können zwar einen Bericht auf Basis einer Kreuztabelle in Access anlegen, wenn aber in der Kreuztabelle neue Spalten hinzukommen, fehlen diese anschließend im Bericht. In diesem Beitrag lernen Sie, wie Sie solche Berichte entwerfen – ohne Datenverlust.

Damit wir uns ganz auf die Programmierung des Berichts konzentrieren können, ist das Datenbankmodell für die Beispieldatenbank sehr einfach gehalten. Das Modell besteht lediglich aus der Tabelle tblUmsätze (s. Abb. 1), die als Basis für die Kreuztabelle dient.

Abb. 1: Aufbau der Tabelle tblUmsätze

In der Tabelle werden die Umsätze mit Eingangsdatum gespeichert. Jeder Umsatz kann dabei einer Kategorie zugeordnet werden. Der Einfachheit halber wurde das Feld Kategorie nicht als Nachschlagefeld mit einer gesonderten Tabelle tblKategorien angelegt, sondern lediglich als Textfeld.

Ebenso einfach wie die Datenbasis ist auch die Eingabe der Umsätze gehalten.

Die Eingabe erfolgt über das Formular frmUmsätzeErfassen. Die Umsätze werden direkt in der Datenblattansicht eingegeben.

Abb. 2: Eingabe der Umsätze

Dabei wird ein Kombinationsfeld verwendet, durch das sich die doppelte Eingabe der Kategorien verhindern lässt.

Das Kombinationsfeld zeigt alle bereits in der Tabelle tblUmsätze vorhandenen Kategorien über die folgende Abfrage an:

Abb. 3: Die Auswahlabfrage für die Kreuztabelle

Abb. 4: Gruppierungsabfrage für die Kreuztabelle

Abb. 5: Die Kreuztabelle in der Entwurfsansicht …

SELECT DISTINCT [tblUmsätze].[Kategorie] FROM tblUmsätze ORDER BY [tblUmsätze].[Kategorie]; 

Damit eine neu eingegebene Kategorie direkt im Kombinationsfeld zur Auswahl steht, wird die Datensatzherkunft des Kombinationsfeldes nach Speicherung des Datensatzes aktualisiert. Dazu wird im Formularereignis Nach Aktualisierung der folgende Programmcode eingetragen:

Private Sub Form_AfterUpdate()
    Me!Kategorie.Requery
End Sub

Abb. 2 zeigt das Formular mit einigen Beispieldatensätzen.

Die Daten für den Bericht werden in einer Kreuztabelle aufbereitet. Dabei sollen die Umsätze pro Kategorie monatsweise und in Summe aufgeführt werden. Gehen Sie dabei in den folgenden Schritten vor:

  • Legen Sie zunächst eine neue Auswahlabfrage auf Basis der Tabelle tblUmsätze an.
  • Definieren Sie die Spalten der Abfrage wie in Abb. 3 zu sehen. Das Feld Umsatz ist zweimal als Ausgabefeld vorhanden, da es in der Kreuztabelle sowohl für die Monats- als auch für die Kategoriesummen benötigt wird. Zur Bestimmung des Monats benutzen Sie den Ausdruck
  • Monat: Format([Eingangsdatum];"jjjj/mm")
  • Sortieren Sie die Abfrage nach den Feldern Kategorie und Monat.
  • Aktivieren Sie über den Menübefehl Ansicht ( Funktionen die Gruppierungsfunktionen.
  • Wählen Sie für die Felder Kategorie und Monat die Funktion Gruppierung und für die Umsatzfelder die Funktion Summe aus (s. Abb. 4). (
  • Die Daten sind nun bereits richtig in der Abfrage zusammengestellt. Jetzt fehlt nur noch die Darstellung der Daten in Form einer Kreuztabelle, bei der die Monate als Spalten- und die Kategorien als Zeilenüberschriften angezeigt werden.

    ändern Sie dazu den Abfragetyp über den Menüpunkt Abfrage ( Kreuztabellenabfrage von Auswahlabfrage in Kreuztabellenabfrage. In der Zeile Kreuztabelle wählen Sie jetzt die Funktion der einzelnen Felder aus.

    Setzen Sie bei den Spalten Kategorie und UmsatzProKategorie den Eintrag Zeilenüberschrift. Für das Feld Monat verwenden Sie die Funktion Spaltenüberschrift und beim Feld SummeVonUmsatz die Funktion Wert (s. Abb. 5).

    Abb. 6 zeigt das Ergebnis der Abfrage in der Datenblattansicht.

    Abb. 6: … und in der Datenblattansicht

    Im Beispiel sind die Monate Januar, Februar und März mit Umsätzen belegt. Hier wird noch einmal deutlich, dass beim Hinzufügen von neuen Datensätzen in der Tabelle tblUmsätze zum Beispiel für die Monate April und Mai im Ergebnis der Kreuztabellenabfrage automatisch neue Spalten hinzukommen.

    Abb. 7: Starrer Auto-Bericht mit fehlenden Daten

    Wenn Sie jetzt einen Bericht auf Basis der vorhandenen Daten erstellen, würde Access die Spalten fest anlegen. Abb. 7 zeigt als Beispiel einen Bericht, der mit dem Datenbestand aus Abb. 2 entworfen wurde.

    Allerdings wurden seitdem neue Umsätze im September in einer neuen Kategorie eingegeben.

    Dabei fällt auf, dass die Kategorie Administration zwar mit der Kategoriesumme aufgeführt ist, aber der Monat mit dem Umsatz fehlt.

    Auch die Summe bei der Kategorie fzg-boerse stimmt nicht, weil die Umsätze aus September zwar zur Gesamtsumme addiert wurden, die Spalte für den Monat September aber auch hier nicht angezeigt wird.

    Vorgehensweise

    Um den Bericht so flexibel zu programmieren, dass immer alle Daten der Tabelle tblUmsätze berücksichtigt werden und der Bericht zur Laufzeit dynamisch angepasst wird, sind ein paar Vorüberlegungen nötig:

  • Bei jedem öffnen des Berichts müssen Sie im Detailbereich so viele Steuerelemente anlegen, wie die Kreuztabellenabfrage Spalten enthält.
  • Gleiches gilt für die Spaltenüberschriften und -summen.
  • Damit Sie nicht bei jedem öffnen des Berichts die dynamisch hinzugefügten Felder wieder löschen müssen, verwenden Sie eine Berichtsvorlage, in der alle fixen Steuerelemente untergebracht sind. Darunter fallen zum Beispiel die Textfelder für die Kategorie und den UmsatzProKategorie. Aber auch die Berichtsüberschrift, Linien, der Seitenfuß mit Seitenanzahl etc. sind darin enthalten. Als Basis für den eigentlichen Bericht verwenden Sie dann eine Kopie dieser Vorlage.
  • Der Bericht kann nicht mehr direkt aus dem Datenbankfenster oder über einen Standardbefehl (zum Beispiel DoCmd.OpenReport) aufgerufen werden, da je nach vorhandenen Daten gegebenenfalls Spalten in der Datenherkunft fehlen, was zu einem Fehler führen würde. Der Bericht muss über eine separate Funktion aufgerufen werden, die den Bericht jedes mal wieder aus der Vorlage passend zum aktuellen Datenbestand erzeugt.
  • ''Alten Bericht löschen
    DoCmd.DeleteObject acReport, "repUmsätze"
    ''Bericht von der Vorlage kopieren
    DoCmd.CopyObject CurrentDb().Name, "repUmsätze", _    acReport, "repUmsätze_Vorlage"
    ''Bericht versteckt in der Entwurfsansicht öffnen
    DoCmd.OpenReport "repUmsätze", acViewDesign

    Möchten Sie weiterlesen? Dann lösen Sie Ihr Ticket!
    Hier geht es zur Bestellung des Jahresabonnements des Magazins Access im Unternehmen:
    Zur Bestellung ...
    Danach greifen Sie sofort auf alle rund 1.000 Artikel unseres Angebots zu - auch auf diesen hier!
    Oder haben Sie bereits Zugangsdaten? Dann loggen Sie sich gleich hier ein:

    Schreibe einen Kommentar