{"id":55000848,"date":"2012-08-01T00:00:00","date_gmt":"2020-05-22T21:47:52","guid":{"rendered":"http:\/\/access-im-unternehmen.aix-dev.de\/aiu\/?p=848"},"modified":"-0001-11-30T00:00:00","modified_gmt":"-0001-11-30T00:00:00","slug":"Kreuztabellen_in_Berichten_anzeigen","status":"publish","type":"post","link":"https:\/\/access-im-unternehmen.de\/Kreuztabellen_in_Berichten_anzeigen\/","title":{"rendered":"Kreuztabellen in Berichten anzeigen"},"content":{"rendered":"

\"\"<\/p>\n

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.<\/b><\/p>\n

Das erste Beispiel soll die Umsätze der Mitarbeiter über die einzelnen Kategorien auflisten. Das gewünschte Ergebnis finden Sie in Bild 2.<\/p>\n

\"pic001.png\"<\/p>\n

Bild 1: Darstellung einer Kreuztabelle in einem Access-Bericht<\/span><\/b><\/p>\n

\"pic002.png\"<\/p>\n

Bild 2: Diese Kreuztabelle dient als Datenherkunft für den Bericht<\/span><\/b><\/p>\n

Aufbau des Berichts<\/b><\/p>\n

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.<\/p>\n

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. <\/p>\n

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.<\/p>\n

Bedeutet das nun Mehraufwand Nein! Sie brauchen der Eigenschaft Datenherkunft <\/b>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. Bild 3). Fertig – der erste Bericht auf Basis einer Kreuztabellenabfrage steht!<\/p>\n

\"pic003.png\"<\/p>\n

Bild 3: Entwurfsansicht des Kreuztabellenberichts<\/span><\/b><\/p>\n

Spaltenüberschriften einschränken<\/b><\/p>\n

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.<\/p>\n

Zunächst einmal muss dazu die zugrunde liegende Abfrage angepasst werden. Dies erreichen Sie, indem Sie ein Kriterium für das Feld Kategoriename<\/b> festlegen (s. Bild 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")<\/b> eintragen.<\/p>\n

\"pic004.png\"<\/p>\n

Bild 5: Kreuztabellenabfrage mit eingeschränkter Spaltenzahl<\/span><\/b><\/p>\n

Das Kriterium wird aber nach dem Speichern und erneuten Öffnen der Abfrage in ein eigenes Feld mit der Funktion Bedingung <\/b>ausgegliedert.<\/p>\n

Das Ergebnis der Abfrage in der Datenblattansicht liefert schon einmal die gewünschten Werte (s. Bild 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.<\/p>\n

\"pic005.png\"<\/p>\n

Bild 6: Entwurf des Berichts mit nur noch zwei Kategorien der Kreuztabellenabfrage<\/span><\/b><\/p>\n

Kein Wunder: Der Bericht enthält immer noch sechs Textfelder, deren Eigenschaft Steuerelementinhalt <\/b>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 <\/b>und Gewürze<\/b>.<\/p>\n

Der Entwurf sieht nun wie in Bild 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.<\/p>\n

\"pic006.png\"<\/p>\n

Bild 4: Einschränkung der Spaltenüberschriften einer Kreuztabellenabfrage<\/span><\/b><\/p>\n

Dynamische Berichtserstellung<\/b><\/p>\n

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<\/a><\/span>.<\/p>\n

Listing 1: Erstellen eines Kreuztabellenberichts per VBA<\/p>\n

Public Sub Kreuztabellenbericht_Erstellen(strDatenherkunft As String, intAnzahlZeilenueberschriften As Integer)\r\n    Dim db As DAO.Database\r\n    Dim rst As DAO.Recordset\r\n    Dim rpt As Report\r\n    Dim ctl As Control\r\n    Dim i As Integer\r\n    Dim strBericht As String, strBerichtTemp As String\r\n    Dim sngSpaltenbreite As Single\r\n    strBericht = Replace(strDatenherkunft, "qry", "rst")\r\n    Set db = CurrentDb\r\n    Set rst = db.OpenRecordset("SELECT * FROM " & strDatenherkunft & " WHERE 1=2", dbOpenDynaset)\r\n    Set rpt = Application.CreateReport\r\n    On Error Resume Next\r\n    DoCmd.Close acReport, strBericht\r\n    DoCmd.DeleteObject acReport, strBericht\r\n    On Error GoTo 0\r\n    strBerichtTemp = rpt.Name\r\n    rpt.RecordSource = strDatenherkunft\r\n    rpt.Section(acPageHeader).Height = 400\r\n    rpt.Section(acDetail).Height = 330\r\n    sngSpaltenbreite = 1400\r\n    For i = 0 To rst.Fields.Count - 1\r\n         Set ctl = Application.CreateReportControl(strBerichtTemp, acLabel, acPageHeader, , , _\r\n            i * sngSpaltenbreite, 200, sngSpaltenbreite, 300)\r\n        ctl.FontSize = 9\r\n        ctl.TextAlign = 2\r\n        ctl.ForeColor = &H0\r\n        ctl.FontBold = True\r\n        ctl.Caption = rst.Fields(i).Name\r\n        ctl.Name = "lbl" & rst.Fields(i).Name\r\n        ctl.BorderStyle = 0\r\n        If i < intAnzahlZeilenueberschriften Then\r\n            ctl.TextAlign = 1\r\n        End If\r\n        Set ctl = Application.CreateReportControl(strBerichtTemp, acTextBox, acDetail, , , _\r\n            i * sngSpaltenbreite, 0, sngSpaltenbreite, 300)\r\n        ctl.FontSize = 9\r\n        ctl.TextAlign = 2\r\n        ctl.ForeColor = &H0\r\n        ctl.ControlSource = rst.Fields(i).Name\r\n        ctl.Name = "txt" & rst.Fields(i).Name\r\n        ctl.BorderStyle = 0\r\n        If i < intAnzahlZeilenueberschriften Then\r\n            ctl.FontBold = True\r\n            ctl.TextAlign = 1\r\n        Else\r\n            ctl.TextAlign = 3\r\n        End If\r\n    Next i\r\n    rpt.Printer.Orientation = acPRORLandscape\r\n    DoCmd.Save acReport, strBerichtTemp\r\n    DoCmd.Close acReport, strBerichtTemp\r\n    DoCmd.Rename strBericht, acReport, strBerichtTemp\r\n    DoCmd.OpenReport strBericht, acDesign\r\nEnd Sub<\/pre>\n
<\/p>\n
Die Prozedur erwartet als Parameter den Namen der Kreuztabellenabfrage, deren Daten der Bericht anzeigen soll, sowie die Anzahl der enthaltenen Zeilenüberschriften. Ein Beispielaufruf könnte etwa so aussehen:<\/p>\n
Kreuztabellenbericht_Erstellen "qryUmsatzNachKategorieUndMitarbeiter_Kreuz", 1<\/pre>\n
Die Prozedur legt zunächst den Namen des zu erstellenden Berichts fest. Dazu verwendet sie schlicht und einfach den Namen der Abfrage und ersetzt qry <\/b>durch rpt<\/b> – so wird aus qryUmsatzNachKategorieUndMitarbeiter_Kreuz <\/b>beispielsweise rptUmsatzNachKategorieUndMitarbeiter_Kreuz<\/b>. Dann öffnet sie eine Datensatzgruppe auf Basis der per Parameter übergebenen Nachfrage, die allerdings keine Datensätze enthalten soll (Kriterium 1=2<\/b>). Danach erstellt die Prozedur mit der CreateReport<\/b>-Methode einen neuen Bericht und weist diesen der Objektvariablen rpt <\/b>zu. Sollte der zu erstellende Bericht bereits vorhanden sein, wird dieser geschlossen und gelöscht. Der Name des neu erstellten Berichts (üblicherweise Bericht1 <\/b>oder ähnlich) wird in der Variablen strBerichtTemp <\/b>gespeichert.<\/p>\n
Nun beginnen die Arbeiten am neu erstellten Bericht. Dieser erhält den Namen der übergebenen Abfrage als Datenherkunft, außerdem wird die Höhe des Seitenkopfbereichs auf 400 <\/b>Pixel eingestellt. Für die Breite der einzelnen Spalten nimmt die Prozedur den Wert 1400<\/b> an. Diesen können Sie gegebenenfalls anpassen, als Parameter übergeben oder auch dynamisch anhand der enthaltenen Daten ermitteln. Die Höhe des Detailbereiches erhält den Wert 330<\/b>.<\/p>\n
Nun folgt die Erstellung der Bezeichnungsfelder im Seitenkopf und der Textfelder im Detailbereich. Beides kann in einer einzigen Schleife über alle Felder der Datenherkunft geschehen. Die Schleife startet mit dem Erstellen eines neuen Bezeichnungsfeldes über die Methode CreateReportControl<\/b>. Diese erwartet den Namen des Zielberichtes, den Steuerelementtyp, den Zielbereich sowie einige optionale Parameter. Dazu gehören die Position und die Größe des Steuerelements. Die Breite liefert die Variable sngSpaltenbreite<\/b>, den Abstand vom linken Rand eine Formel, welche die Gesamtbreite der bereits angelegten Steuerelemente ermittelt. Der Abstand zum oberen Rand sowie die Höhe werden mit festen Zahlenwerten angegeben. Der Verweis auf das frisch erstellte Bezeichnungsfeld wird in der Variablen ctl <\/b>gespeichert. Auf diese Weise kann die Prozedur noch weitere Eigenschaften einstellen, zum Beispiel Schriftart<\/b>, Textausrichtung<\/b>, Beschriftung<\/b>, Name<\/b> et cetera. Sollte es sich bei dem Bezeichnungsfeld um die Zeilenüberschrift handeln, wird die Eigenschaft Textausrichtung <\/b>auf den Wert Linksbündig <\/b>eingestellt.<\/p>\n
Im gleichen Schleifendurchlauf erstellt die Prozedur auch noch das entsprechende Textfeld im Detailbereich des Berichts. Dies geschieht auf die gleiche Weise wie beim Bezeichnungsfeld. Es gibt nur einen Unterschied: Die Eigenschaft Steuerelementinhalt <\/b>(ControlSource<\/b>) muss zusätzlich festgelegt werden. Das ist aber kein Problem, da diese dem Feldnamen des soeben durchlaufenen Feldes der Datensatzgruppe entspricht. Auch hier werden einige spezielle Formatierungen für die Zeilenüberschriften durchgeführt.<\/p>\n
Schließlich stellt die Prozedur noch die Ausrichtung des Berichts auf den Wert acPRORLandscape <\/b>ein, was schlicht Querformat bedeutet und für dieses Beispiel sinnvoll ist. Der noch unter dem temporären Namen (etwa Bericht1<\/b>) bekannte Bericht wird gespeichert und geschlossen, entsprechend dem weiter oben zusammengesetzten Berichtsnamen umbenannt und zur Kontrolle in der Entwurfsansicht geöffnet.<\/p>\n
Auf diese Weise erstellen Sie sehr schnell einen Bericht, der sogar eine Einschränkung der in der Kreuztabellenabfrage enthaltenen Spaltenüberschriften berücksichtigt.<\/p>\n
Dynamische Berichtsanpassung<\/b><\/p>\n
Dem gegenüber steht die dynamische Anpassung eines bestehenden Berichts an die flexible Auswahl der Spaltenköpfe. Grundlage für dieses Beispiel ist eine Kreuztabellenabfrage, die alle Kategorien anzeigt und die erst zur Laufzeit mit einem entsprechenden Kriterium ausgestattet wird.<\/p>\n
Außerdem benötigen Sie einen Bericht, dessen Bezeichnungsfelder und Textfelder noch nicht beschriftet beziehungsweise mit einer Datenherkunft versehen sind. Dieser sieht wie in Bild 7 aus und wird durch die Prozedur Kreuztabellenbericht_Dynamisch <\/b>des Moduls mdlBerichtErstellen <\/b>angelegt. Diese Prozedur arbeitet genau so wie die zuvor beschriebene. Es gibt nur wenige Unterschiede: Die Bezeichnungsfelder heißen lbl1<\/b>, lbl2 <\/b>und so weiter, die Textfelder entsprechend txt1<\/b>, txt2 <\/b>… Außerdem enthalten die Textfelder noch keinen Wert für die Eigenschaft Steuerelementinhalt<\/b> und der Bericht enthält noch keine Datenherkunft.<\/p>\n
\"pic007.png\"<\/p>\n
Bild 7: Berichtsentwurf für eine variable Anzahl Kategorien<\/span><\/b><\/p>\n
Diese soll erst beim Öffnen des Berichts zusammengestellt werden, und zwar auf Basis der vom Benutzer gewählten Kategorien. Diese wählt er mit dem Formular frmBerichtMitVariablenKategorien <\/b>aus Bild 8 aus. Das Listenfeld verwendet die folgende Abfrage als Datenherkunft:<\/p>\n
\"pic008.png\"<\/p>\n
Bild 8: Formular zur Auswahl der Kategorien für den Kreuztabellenbericht<\/span><\/b><\/p>\n
SELECT tblKategorien.Kategoriename\r\nFROM tblKategorien;<\/pre>\n
Außerdem stellen Sie die Eigenschaft Mehrfachauswahl <\/b>des Listenfeldes auf Einzeln <\/b>ein (oder Erweitert<\/b>, je nach Wunsch). Ein Klick auf die Schaltfläche cmdBerichtOeffnen<\/b> soll einen Ausdruck für die In<\/b>-Klausel der Bedingung für die Datenherkunft des Berichts zusammenstellen und diese beim Öffnen des Berichts per OpenArgs<\/b>-Parameter an diesen übergeben. Dies erledigt die Prozedur aus Listing 2<\/a><\/span>.<\/p>\n
Listing 2: Zusammenstellen eines Kriteriums und Öffnen des Kreuztabellenberichts<\/p>\n
Private Sub cmdBerichtOeffnen_Click()\r\n    Dim i As Integer\r\n    Dim intIndex As Integer\r\n    Dim strKategorie As String\r\n    Dim strKategorien As String\r\n    For i = 0 To Me!lstKategorien.ItemsSelected.Count - 1\r\n        intIndex = Me!lstKategorien.ItemsSelected(i)\r\n        strKategorie = Me!lstKategorien.ItemData(intIndex)\r\n        strKategorien = strKategorien & ";" & Chr(34) & strKategorie & Chr(34)\r\n    Next i\r\n    If Len(strKategorien) > 0 Then\r\n        strKategorien = Mid(strKategorien, 2)\r\n        DoCmd.OpenReport "rstUmsatzNachKategorieUndMitarbeiter_Kreuz_Dynamisch", _\r\n            View:=acViewPreview, OpenArgs:=strKategorien\r\n    End If\r\nEnd Sub<\/pre>\n
Der Bericht muss nun beim Öffnen folgende Aufgaben erledigen:<\/p>\n