Dynamische Charts in Excel – erweiterte Filter und Crossfiltering
In meinem letzten Beitrag habe ich ein einfaches Konzept vorgestellt, wie man interaktive Charts erstellen kann, bei denen der Nutzer selbst auswählen kann, welche Werte dargestellt werden sollen.
Jetzt sollen weitere Filtermöglichkeiten hinzugefügt werden: Nachdem man sich also z.B. für eine Darstellung nach Regionen entschieden hat, kann man die darzustellenden Werte noch weiter eingrenzen: Entweder einzelne Regionen auswählen oder Filter aus ganz anderen Kategorien setzen. Alles was im Datenmodell vorhanden und mit den Werten entsprechend verbunden ist, kann als zusätzliches Filterfeld mit aufgenommen werden. In unserem Beispiel werden durch die Beschränkung auf Category „Components“ einige Regionen rausgeworfen:
Dabei startet man genauso wie in unserem vorherigen Beispiel, jedoch fügt man der Pivottabelle bereits die weiteren Datenschnitte hinzu, bevor man sie in Formeln konvertiert.
Die Spaltenüberschriften müssen nun nicht nur auf den ersten Auswahlfilter (Legendenfilter) reagieren, sondern zusätzlich noch auf alle (!) anderen gesetzten Filter. So wird z.B. die Menge der anzuzeigenden Regionen durch einen Filter im Datenschnitt Categories eingeschränkt (Crossfiltering).
Da wäre es ja sehr schön, wenn man so wie im vorherigen Beispiel vorgehen könnte: Cuberangeelement auf die Cubemenge, diese nur um die zusätzlichen Datenschnitte gefiltert. Leider habe ich bislang noch keinen Weg gefunden, wie man den Datenschnitt als Filter in die Cubemengenfunktion dynamisch mit einbauen kann (Ideen dazu werden dankend entgegen genommen!).
Als Alternative nehmen wir dann wieder den Dummy-Trick für Cubefunktionen: Man erzeugt im Hintergrund Pivottabellen, die mit den Datenschnitten des Charts verbunden sind und die dann die benötigten Werte an die MDX-Ausdrücke übergeben:
Ja, pro Legendeneintrag braucht man tatsächlich ein separates Pivot sein, denn nur so erhalten wir die benötigten Textausdrücke.
Da unsere Cubelementformeln ja bereits mit allen Datenschnitten verbunden sind, müssten sie uns ja eigentlich bereits die richtigen Ergebnisse liefern. Tun sie aber leider nicht und das liegt daran, dass sie keine redundanten Filter vertragen: Der Datenschnitt Region ist explizit in der Formel enthalten, zusätzlich wird er jedoch auch indirekt über die Spaltenselektion mit übergeben. Da streikt die Formel dann. Hier bleibt uns leider nichts anderes übrig, als die Fälle entsprechend abzufangen und pro Legendenselektion den doppelten Datenschnitt zu entfernen:
Nicht vergessen, die Werteselektion zu dynamisieren (=CUBEELEMENT(_DataModel;“[Measures].[„&SlicerValue&“]“)) – und dann ist man auch schon fertig.
Download der Datei: [wpfilebase tag=file id=18 tpl=filebrowser /]
Variante:
So viele Filter haben allerdings nicht nur Vorteile, denn man kann hier auch mal schnell den Überblick verlieren oder Selektionen übersehen. In PowerView bleiben da ja die Ursprungswerte im Hintergrund erkennbar, was manchmal bei der Orientierung hilft, finde ich. Dies lässt sich auch in Standard-Excel ganz einfach realisieren:
Zunächst fügt man als erste Spalte des Wertebereichs die [All]-Auswahl mit ein:
Danach entfernt man die Datenschnittfilter aus der Cubewerteformel und zieht davon die Summe der ausgewählten Felder ab:
Fertig ist die Summe der nicht gefilterten Bereiche (in hellgrau dargestellt):
Schon lassen sie die Filterdimensionen viel besser einordnen.
Download der Datei: [wpfilebase tag=file id=17 tpl=filebrowser /]
Jetzt müsste Microsoft nur noch eine Standardmöglichkeit einführen, mit dem man den auszuwählenden Datenbereich für ein Chart dauerhaft dynamisch gestalten kann… (oder gibt es den schon und ich habe den einfach nur übersehen?)
So muss man den Bereich immer gemäß dem längsten Datenschnitt auswählen und mit den leeren Strichen in der Legende leben – oder die Legende ganz entfernen und die Namen stattdessen direkt an der Datenreihe anzeigen lassen.
Edit 03.03.15 15:13:00:
Mithilfe eines Makros kann man die leeren Legendeneinträge natürlich verschwinden lassen:
Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
Dim cell As Range
Application.ScreenUpdating = False
For Each cell In Worksheets("HideLogic").Range("D13:O13")
cell.EntireColumn.Hidden = cell.Value = "hide"
Next cell
Application.ScreenUpdating = True
End Sub
Download der Datei: [wpfilebase tag=file id=20 tpl=filebrowser /]
Weitere Tutorials?
Natürlich haben wir noch mehr wertvolle Tipps rund um Microsoft BI für Sie!
