Heute zeige ich Ihnen, wie man dynamische Charts in Excel erstellen kann, bei denen der Benutzer die anzuzeigenden Werte selbst aussuchen kann. In einem Datenschnitt kann er wählen, welche Werte angezeigt werden sollen (z.B. Umsatz oder Deckungsbeitrag), in dem anderen Datenschnitt kann er die anzuzeigende Dimension wählen (z.B. nach Produktkategorien, Regionen, Verkaufscodes).
Mal so:
Mal anders:
Hier kommen wieder Cubeformeln zum Einsatz, deren prinzipielle Wirkungsweise ich hier erläutert habe.
Die Datenschnitte „Values“ und „Legend“ sind unabhängige Datenschnitte, d.h. Tabellen, die nicht mit den restlichen Tabellen des Datenmodells verbunden sind. Dazu erstellt man einfach in Excel entsprechende Tabellen mit den gewünschten Auswahlmöglichkeiten. Diese ergänzen wir gleich um eine weitere Spalte, die uns später weiterhelfen wird: Die MDX-Pfadangabe des zugehörigen Attributes im Datenmodell (zu finden z.B. im Cubelementausdruck/Spaltenüberschriften der umgewandelten Tabelle). Diese Tabellen werden dann in DiscSlicerLegend und DiscSlicerValue umbenannt und dem Datenmodell hinzugefügt.
Dann erstellt man wieder eine Pivottabelle, die der der Datenquelle für die zukünftige Grafik möglichst nahe kommt, incl. der Datenschnitte aus den gerade hinzugefügten Auswahltabellen. Diese kopiert man dann und wandelt sie in Cubeformeln um.
Wie bringt man denn jetzt den Spaltenüberschriften bei, sich entsprechend der Auswahl des Datenschnittes anzupassen? Dazu müssen wir zunächst die Auswahl im Datenschnitt zu fassen kriegen:
=CUBERANGELEMENT(_DataModel;Datenschnitt_Legend;1)
gibt das erste Element der Cubemenge unseres Legendendatenschnitts zurück. Man sollte also immer nur einen Wert auswählen. Wir nennen dieses Feld „SlicerLegend“. Die gleiche Technik könnten wir ja nun auch für die Spaltenüberschriften verwenden, nur dass der Zähler mit jedem Schritt nach rechts um 1 erhöht wird:
CUBERANGEELMENT(..;SlicerSet; 1..n).
SlicerSet soll die Cubemenge sein, die durch unseren Datenschnitt ausgewählt wurde. Den Ausdruck erzeugen wir mit einem SVERWEIS dynamisch:
So sieht dann die Formel für die Spaltenüberschriften aus (kann nach rechts ausgefüllt werden, die Bezüge auf die Spalten laufen wie gewünscht mit):
Um die Werte zu dynamisieren, reicht uns sogar die Selektion des Datenschnitts:
Nun noch die Grafik erstellt und samt Datenschnitten auf ein separates Blatt verschoben, damit man das „Arbeitsblatt“ dann ausblenden kann. Jetzt fehlt nur noch der dynamische Titel:
=SlicerValue&“ by „&SlicerLegend.
Fertig ist eine einfache Version einer dynamischen Grafik in Excel: [wpfilebase tag=file id=15 tpl=filebrowser /]
Im nächsten Beitrag zeige ich, wie diese Ergebnisse jetzt noch weiter gefiltert werden können (incl. Crossfiltering).
Weitere Tutorials?
Natürlich haben wir noch mehr wertvolle Tipps rund um Microsoft BI für Sie!