In unserem vorherigen Blogbeitrag haben wir das Grundprinzip für dynamische Berichte mit Cubeformeln vorgestellt.
Wenn man nun z.B. einen Bericht mit Teilergebnissummen erstellen möchte, besteht die zusätzliche Herausforderung darin, dass sich die Zwischensummen abhängig von der jeweiligen Filtersetzung in unterschiedlichen Zeilen befinden:
Bei Filterwechsel auf das Jahr 2006 zeigt sich ein erheblich reduziertes Sortiment und das erste Teilergebnis ist in Zelle C7 statt C5:
Tipps und Tricks
Identifikation von Teilergebniszeilen
Während sich Teilergebniszeilen recht einfach an Leerzellen in den weiteren Detailspalten identifizieren lassen (WENN (D4=““; Teilergebnis ; Detailzeile)), kann man die Werte leider nicht so einfach ansprechen: In der Pivottabelle werden die Zeilen mit der Erweiterung “Ergebnis” angezeigt, in der Cubelementformel darf das allerdings nicht mit verwendet werden. Mit WECHSELN(…; “ Ergebnis“ ; ““) wird man das los. Für die farbliche Darstellung verwendet man dann bedingte Formatierung.
Datumsspalten formatieren
Datumsfelder, die auch eine Uhrzeitangabe tragen, müssen sehr speziell formatiert werden, um von der Cubelementformel richtig erkannt zu werden. Selbst wenn man das Datum mithilfe eines benutzerdefinierten Formats in diese Form bringt: 2015-12-31T00:00:00 scheint die Cubeformel das aber trotzdem nicht zu akzeptieren. Ich behelfe mir dann mit folgendem Konstrukt:
TEXT(ZelleInPivot;“JJJJ-MM-TT“)&“T“&TEXT(ZelleInPivot;“hh:mm:ss“)
Performance und Übersichtlichkeit
Wenn man eine Pivottabelle mit mehreren Feldern in den Zeilen oder Spalten zu Formeln umwandelt, werden in den Cubelementformeln viele Redundanzen erzeugt. Jedes hinzugefügte Feld „erbt“ die Cubelementspezifikation seiner (sämtlichen) Vorgänger – dadurch wird die Formel unnötig lang, denn man benötigt nur den letzten Definitionsteil, um die notwendige Eindeutigkeit zu erhalten. Der rot gefärbte Bereich kann komplett entfernt werden:
=CUBEELEMENT(„ThisWorkbookDataModel“;
{„[Production_ProductSubcategory].[Name].&[Locks]“.
„[Production_Product].[Name].&[Cable Lock]“.
„[Production_Product].[SellStartDate].&[2006-07-01T00:00:00]“.
„[Production_Product].[SellEndDate].&[2007-06-30T00:00:00]“})
Der Grund liegt evtl. darin, dass die Cubewertformel standardmäßig immer stur auf das letzte Zeilenfeld zugreift. Wenn das nun wie in unserem Fall hier, nicht eindeutig ist (30.06.2007), würde dann kein korrekter Wert zurückgeben werden.
Daher kann man auf der einen Seite zwar die Cubeelementformeln auf das letzte Attribut verringern, muss dann aber den Bezug in der Cubewertformel auf das eindeutige Element umstellen:
Datenschnitte nachträglich hinzufügen:
Dafür geht man einfach in die alte Pivottabelle, fügt den Datenschnitt ein, schneidet ihn aus und platziert ihn im Berichtsblatt. Nun kann man ihn ganz einfach als zusätzliches Filterelement in den Cubewertformeln mit ansprechen.
Die restliche „Intelligenz“ besteht dann nur noch aus Wenn-Dann-Fällen. Alle Formeln werden in die WENNFEHLER-Klammer gesetzt, um bei variabler Berichtslänge die NVs zu unterdrücken.
Tags: #Cubeformel #Excel #PowerPivot #Pivot #dynamisch #interaktiv #Bericht #Teilergebnis #Zwischensummen
Weitere Tutorials?
Natürlich haben wir noch mehr wertvolle Tipps rund um Microsoft BI für Sie!