Probleme bei der Formatierung von Zellen in Tablix-Objekten in SSRS mit Gruppenbezug für Zeilen und Spalten

Question

Ich möchte einen Report erstellen, der in etwa wie folgt aussehen soll:

Für folgende Anforderungen habe ich aber Probleme, die richtige Lösung in SSRS 2014 zu finden:

• Bei jedem Wechsel der Gruppe2 soll eine schwarze statt graue Trennlinie gezeichnet werden. Mit einer Formel in der Form =iif(Fields!Group2.Value<>Previous(Fields!Group2.Value),"Black","LightGrey") für die jeweils obere Rahmenlinie klappt das für die Zeilenköpfe wie erwartet. Wenn ich diese Formel aber auf den Detailbereich ausweite bekomme ich die Fehlermeldung: "Die Verwendung einer Previous-Aggregatfunktion in einer TablixCell innerhalb der matrix1-Tabelle wird nicht unterstützt", was ja auch logisch ist, da hier nicht mehr eindeutig ist, ob sich Previous() auf die vorhergehende Zeile oder Spalte bezieht. Ich bräuchte also Funktionen wie PreviousRow() oder PreviousColumn() um das zu lösen. Solche Funktionen habe ich aber nicht gefunden. Gibt es hierfür eine Lösung?
• Die hinter dieser Matrix liegende Abfrage liefert für jedes Detail genau einen Wert, sodass es zunächst unerheblich ist, ob ich die Sum(), Min(), Max() oder Avg()-Formel für den Detail-Bereich verwende. Innerhalb der untersten Gruppe3 sollten die Werte für ein Merkmal M1, M2 usw. theoretisch immer identisch sein, aber das ist praktisch nicht immer der Fall. Deshalb sollen gerade die Zellen farblich hervorgehoben werden, wo diese Einheitlichkeit nicht zutrifft. Ich habe dafür zunächst die Formel =iif(me.value <> Avg(Fields!M.Value, "Group2"),"Orange","White") verwendet, aber dabei bildet die Avg()-Formel den Mittelwert über sämtliche Merkmale M1, M2, M3 ... . Ich möchte aber den Mittelwert pro Merkmal haben und müsste somit bei der Avg()-Formel im Detail-Bereich sowohl eine "RowGroup" als auch eine "ColumnGroup" als Bezug angeben. Ist das möglich? Wie lautet dann die Syntax? Oder gibt es eine andere Lösung, um dieses Problem zu lösen?

Ich hoffe, ich habe meine Ideen einigermaßen verständlich beschrieben.

Answer 1

Hallo uwdn

leider können wir nicht sehen wie das Report aussehen soll. Können Sie das Image irgendwo hochladen und dann den link hier hinzufügen

Gruß

Aleksander

---

Bitte haben Sie Verständnis dafür, dass im Rahmen dieses Forums, welches auf dem Community-Prinzip „IT-Pros helfen IT-Pros“ beruht,  kein technischer Support geleistet werden kann oder sonst welche garantierten Maßnahmen seitens Microsoft zugesichert werden können.

Answer 2

Hallo uwdn,

Mit einer Formel in der Form =iif(Fields!Group2.Value<>Previous(Fields!Group2.Value),"Black","LightGrey") für die jeweils obere Rahmenlinie klappt das für die Zeilenköpfe wie erwartet. Wenn ich diese Formel aber auf den Detailbereich ausweite bekomme ich die Fehlermeldung: "Die Verwendung einer Previous-Aggregatfunktion in einer TablixCell innerhalb der matrix1-Tabelle wird nicht unterstützt", was ja auch logisch ist, da hier nicht mehr eindeutig ist, ob sich Previous() auf die vorhergehende Zeile oder Spalte bezieht. Ich bräuchte also Funktionen wie PreviousRow() oder PreviousColumn() um das zu lösen. Solche Funktionen habe ich aber nicht gefunden. Gibt es hierfür eine Lösung?

Da die Previous-Funktion in Zellen nicht unterstützt wird, kannst Du mit einer SQL-Abfrage probieren, auf das vorhergehende Feld zuzugreifen.

Siehe dazu: How to do SQL Server Reporting Services Matrix?

Gruß,
Dimitar

---

Bitte haben Sie Verständnis dafür, dass im Rahmen dieses Forums, welches auf dem Community-Prinzip „IT-Pros helfen IT-Pros“ beruht, kein technischer Support geleistet werden kann oder sonst welche garantierten Maßnahmen seitens Microsoft zugesichert werden können.

Answer 3

Ich versuche es nochmal mit Einbinden des Bildes:

Answer 4

Dann könntest Du es per SQL vorbereiten:

create table #Matrix (Group1 char(1), Group2 char(1), Group3 char(2), M1
int, M2 int, M3 int, M4 int);

Insert into #Matrix(Group1, Group2, Group3, M1, M2, M3, M4) Values
('A','X','T1', 20,31,5,48);
Insert into #Matrix(Group1, Group2, Group3, M1, M2, M3, M4) Values
('A','X','T3', 20,31,6,48);

Insert into #Matrix(Group1, Group2, Group3, M1, M2, M3, M4) Values
('A','Y','T1', 17,27,7,51);
Insert into #Matrix(Group1, Group2, Group3, M1, M2, M3, M4) Values
('A','Y','T2', 17,27,7,51);

Insert into #Matrix(Group1, Group2, Group3, M1, M2, M3, M4) Values
('A','Z','T2', 16,30,9,45);
Insert into #Matrix(Group1, Group2, Group3, M1, M2, M3, M4) Values
('A','Z','T3', 16,30,9,45);
Insert into #Matrix(Group1, Group2, Group3, M1, M2, M3, M4) Values
('A','Z','T5', 17,30,9,45);
Insert into #Matrix(Group1, Group2, Group3, M1, M2, M3, M4) Values
('A','Z','T6', 16,30,9,45);

Insert into #Matrix(Group1, Group2, Group3, M1, M2, M3, M4) Values
('B','X','T1', 20,28,11,62);
Insert into #Matrix(Group1, Group2, Group3, M1, M2, M3, M4) Values
('B','X','T3', 20,28,11,62);

Insert into #Matrix(Group1, Group2, Group3, M1, M2, M3, M4) Values
('B','Y','T1', 15,24,3,57);
Insert into #Matrix(Group1, Group2, Group3, M1, M2, M3, M4) Values
('B','Y','T2', 15,25,3,58);

Select Group1, Group2, Group3, M1, M2, M3, M4,
-- ROW_NUMBER() OVER(PARTITION BY Group1, Group2 ORDER BY Group3) as rn_2,
case when ROW_NUMBER() OVER(PARTITION BY Group1, Group2 ORDER BY Group3) <>
1 then 'LightGrey' else 'Black' end as BorderColor,
-- AVG(M1*1.0) OVER(PARTITION BY Group1, Group2),
Case when AVG(M1*1.0) OVER(PARTITION BY Group1, Group2) <> M1 then 'Orange'
else 'White' end as BackgroundColor_1,
-- AVG(M2*1.0) OVER(PARTITION BY Group1, Group2) as avg_2,
Case when AVG(M2*1.0) OVER(PARTITION BY Group1, Group2) <> M2 then 'Orange'
else 'White' end as BackgroundColor_2,
-- AVG(M3*1.0) OVER(PARTITION BY Group1, Group2) as avg_3,
Case when AVG(M3*1.0) OVER(PARTITION BY Group1, Group2) <> M3 then 'Orange'
else 'White' end as BackgroundColor_3,
-- AVG(M4*1.0) OVER(PARTITION BY Group1, Group2) as avg_4,
Case when AVG(M4*1.0) OVER(PARTITION BY Group1, Group2) <> M4 then 'Orange'
else 'White' end as BackgroundColor_4
from #Matrix
order by Group1, Group2, Group3;

go
Drop Table #Matrix ;

Einen schönen Tag noch,
Christoph
--
Microsoft SQL Server MVP - http://www.insidesql.org/blogs/cmu

Answer 5

Danke für den Hinweis. Ich hatte schon selber angefangen, das Thema mit analytischen Funktionen in der SQL-Abfrage zu lösen, aber so wie Du es vorschlägst ist es noch eleganter. Wobei M1 bis M4 bei mir ja keine separaten Felder in der Datenbank sind, sondern ein weiteres Gruppenfeld. Das Beispiel müsste damit also so aussehen:

create table MyMatrix 
(Group1 varchar2(1), 
 Group2 varchar2(1), 
 Group3 varchar2(2), 
 Group4 varchar2(2), 
 MValue int);

Insert into MyMatrix(Group1, Group2, Group3, Group4, MValue) Values ('A','X','T1','M1',20);
Insert into MyMatrix(Group1, Group2, Group3, Group4, MValue) Values ('A','X','T1','M2',31);
Insert into MyMatrix(Group1, Group2, Group3, Group4, MValue) Values ('A','X','T1','M3', 5);
Insert into MyMatrix(Group1, Group2, Group3, Group4, MValue) Values ('A','X','T1','M4',48);

Insert into MyMatrix(Group1, Group2, Group3, Group4, MValue) Values ('A','X','T3','M1',20);
Insert into MyMatrix(Group1, Group2, Group3, Group4, MValue) Values ('A','X','T3','M2',31);
Insert into MyMatrix(Group1, Group2, Group3, Group4, MValue) Values ('A','X','T3','M3', 6);
Insert into MyMatrix(Group1, Group2, Group3, Group4, MValue) Values ('A','X','T3','M4',48);

Insert into MyMatrix(Group1, Group2, Group3, Group4, MValue) Values ('A','Y','T1','M1',17);
Insert into MyMatrix(Group1, Group2, Group3, Group4, MValue) Values ('A','Y','T1','M2',27);
Insert into MyMatrix(Group1, Group2, Group3, Group4, MValue) Values ('A','Y','T1','M3', 7);
Insert into MyMatrix(Group1, Group2, Group3, Group4, MValue) Values ('A','Y','T1','M4',51);

Insert into MyMatrix(Group1, Group2, Group3, Group4, MValue) Values ('A','Y','T2','M1',17);
Insert into MyMatrix(Group1, Group2, Group3, Group4, MValue) Values ('A','Y','T2','M2',27);
Insert into MyMatrix(Group1, Group2, Group3, Group4, MValue) Values ('A','Y','T2','M3', 7);
Insert into MyMatrix(Group1, Group2, Group3, Group4, MValue) Values ('A','Y','T2','M4',51);

Insert into MyMatrix(Group1, Group2, Group3, Group4, MValue) Values ('A','Y','T2','M1',17);
Insert into MyMatrix(Group1, Group2, Group3, Group4, MValue) Values ('A','Y','T2','M2',27);
Insert into MyMatrix(Group1, Group2, Group3, Group4, MValue) Values ('A','Y','T2','M3', 7);
Insert into MyMatrix(Group1, Group2, Group3, Group4, MValue) Values ('A','Y','T2','M4',51);

Insert into MyMatrix(Group1, Group2, Group3, Group4, MValue) Values ('A','Z','T2','M1',16);
Insert into MyMatrix(Group1, Group2, Group3, Group4, MValue) Values ('A','Z','T2','M2',30);
Insert into MyMatrix(Group1, Group2, Group3, Group4, MValue) Values ('A','Z','T2','M3', 9);
Insert into MyMatrix(Group1, Group2, Group3, Group4, MValue) Values ('A','Z','T2','M4',45);

Insert into MyMatrix(Group1, Group2, Group3, Group4, MValue) Values ('A','Z','T3','M1',16);
Insert into MyMatrix(Group1, Group2, Group3, Group4, MValue) Values ('A','Z','T3','M2',30);
Insert into MyMatrix(Group1, Group2, Group3, Group4, MValue) Values ('A','Z','T3','M3', 9);
Insert into MyMatrix(Group1, Group2, Group3, Group4, MValue) Values ('A','Z','T3','M4',45);

Insert into MyMatrix(Group1, Group2, Group3, Group4, MValue) Values ('A','Z','T5','M1',17);
Insert into MyMatrix(Group1, Group2, Group3, Group4, MValue) Values ('A','Z','T5','M2',30);
Insert into MyMatrix(Group1, Group2, Group3, Group4, MValue) Values ('A','Z','T5','M3', 9);
Insert into MyMatrix(Group1, Group2, Group3, Group4, MValue) Values ('A','Z','T5','M4',45);

Insert into MyMatrix(Group1, Group2, Group3, Group4, MValue) Values ('A','Z','T6','M1',16);
Insert into MyMatrix(Group1, Group2, Group3, Group4, MValue) Values ('A','Z','T6','M2',30);
Insert into MyMatrix(Group1, Group2, Group3, Group4, MValue) Values ('A','Z','T6','M3', 9);
Insert into MyMatrix(Group1, Group2, Group3, Group4, MValue) Values ('A','Z','T6','M4',45);

Insert into MyMatrix(Group1, Group2, Group3, Group4, MValue) Values ('B','X','T1','M1',20);
Insert into MyMatrix(Group1, Group2, Group3, Group4, MValue) Values ('B','X','T1','M2',28);
Insert into MyMatrix(Group1, Group2, Group3, Group4, MValue) Values ('B','X','T1','M3',11);
Insert into MyMatrix(Group1, Group2, Group3, Group4, MValue) Values ('B','X','T1','M4',62);

Insert into MyMatrix(Group1, Group2, Group3, Group4, MValue) Values ('B','X','T3','M1',20);
Insert into MyMatrix(Group1, Group2, Group3, Group4, MValue) Values ('B','X','T3','M2',28);
Insert into MyMatrix(Group1, Group2, Group3, Group4, MValue) Values ('B','X','T3','M3',11);
Insert into MyMatrix(Group1, Group2, Group3, Group4, MValue) Values ('B','X','T3','M4',62);

Insert into MyMatrix(Group1, Group2, Group3, Group4, MValue) Values ('B','Y','T1','M1',15);
Insert into MyMatrix(Group1, Group2, Group3, Group4, MValue) Values ('B','Y','T1','M2',24);
Insert into MyMatrix(Group1, Group2, Group3, Group4, MValue) Values ('B','Y','T1','M3', 3);
Insert into MyMatrix(Group1, Group2, Group3, Group4, MValue) Values ('B','Y','T1','M4',57);

Insert into MyMatrix(Group1, Group2, Group3, Group4, MValue) Values ('B','Y','T2','M1',15);
Insert into MyMatrix(Group1, Group2, Group3, Group4, MValue) Values ('B','Y','T2','M2',25);
Insert into MyMatrix(Group1, Group2, Group3, Group4, MValue) Values ('B','Y','T2','M3', 3);
Insert into MyMatrix(Group1, Group2, Group3, Group4, MValue) Values ('B','Y','T2','M4',58);

und die Abfrage entsprechend:

Select 
  Group1,
  Group2,
  Group3,
  Group4,
  MValue,
  -- ROW_NUMBER() OVER(PARTITION BY Group1, Group2, Group4 ORDER BY Group3) as rn_2,
  case when ROW_NUMBER() OVER(PARTITION BY Group1, Group2, Group4 ORDER BY Group3) <> 1 then 'LightGrey' else 'Black' end as BorderColor,
  -- AVG(MValue) OVER (PARTITION BY Group1, Group2, Group4),
  Case when AVG(MValue) OVER(PARTITION BY Group1, Group2, Group4) <> MValue then 'Orange' else 'White' end as BackgroundColor
from MyMatrix
order by Group1, Group2, Group3, Group4;

Jetzt habe ich noch das erweiterte Problem, dass meine Matrix in der realen Welt nicht vollständig gefüllt ist. Also zum Bespiel ein weiteres Merkmal M5 mit folgenden Werten:

Insert into MyMatrix(Group1, Group2, Group3, Group4, MValue) Values ('A','X','T1','M5',2);
Insert into MyMatrix(Group1, Group2, Group3, Group4, MValue) Values ('A','X','T3','M5',2);
Insert into MyMatrix(Group1, Group2, Group3, Group4, MValue) Values ('A','Y','T1','M5',3);
Insert into MyMatrix(Group1, Group2, Group3, Group4, MValue) Values ('A','Y','T2','M5',3);
Insert into MyMatrix(Group1, Group2, Group3, Group4, MValue) Values ('A','Y','T2','M5',3);
Insert into MyMatrix(Group1, Group2, Group3, Group4, MValue) Values ('B','X','T1','M5',2);
Insert into MyMatrix(Group1, Group2, Group3, Group4, MValue) Values ('B','X','T3','M5',2);
Insert into MyMatrix(Group1, Group2, Group3, Group4, MValue) Values ('B','Y','T1','M5',4);
Insert into MyMatrix(Group1, Group2, Group3, Group4, MValue) Values ('B','Y','T2','M5',4);

In diesen Fällen wird die schwarze Linie nicht mehr durchgezogen:

Jetzt könnte man in der SQL-Abfrage über ein Union noch Null-Werte für die nicht gefüllten Kombinationen ergänzen. So etwas habe ich in der Vergangenheit (mit SSRS 2005) auch schon mal für Auswertungen gemacht. Aber irgendwie hatte ich gedacht, dass es jetzt unter SSRS 2014 mit den (für uns noch recht neuen) Tablix-Elementen einfacher sein sollte, die unterschiedlichen Gruppen-Hierarchien in einer solchen Matrix mit unterschiedlichen Linienarten, Liniendicken oder Linienfarben zu separieren und dass ich den eleganten Weg nur noch nicht gefunden habe.

Answer 6

Meiner Meinung nach reicht dieses für die Linie:

case when ROW_NUMBER() OVER(PARTITION BY Group1, Group2 ORDER BY Group3) <>
1 then 'LightGrey' else 'Black' end as BorderColor,

Ich habe es nicht getestet, aber könnte mir vorstellen, dass dies hier funktioniert.
Ein Konstrukt in der Matrix mit IIF(IsNothing(Fields!MValue.Value) and Fields!rn_2.Value = 1, 'Black', Fields!BorderColor.Value)? Oder sonst irgendwie Bezug nehmen zu der übergeordneten Gruppe (rn_2 ist dort für alle Elemente in dieser Group2 identisch)?

ROW_NUMBER() OVER(PARTITION BY Group1, Group2 ORDER BY Group3) as rn_2,

Einen schönen Tag noch,
Christoph
--
Microsoft SQL Server MVP - http://www.insidesql.org/blogs/cmu

Answer 7

Dein Vorschlag für die Linie war natürlich korrekt.
Ich baue das mal eben in einem Report nach ...
 Einen schönen Tag noch,
Christoph
--
Microsoft SQL Server MVP - http://www.insidesql.org/blogs/cmu

Answer 8

So sollte es aussehen, wobei ich das * 1.1 wieder ergänzt habe, damit alle Zellen Orange werden.

Select
 Group1,
 Group2,
 Group3,
 Group4,
 MValue,
 ROW_NUMBER() OVER(PARTITION BY Group1, Group2 ORDER BY Group3) as rn_2,
 case when ROW_NUMBER() OVER(PARTITION BY Group1, Group2, Group4 ORDER BY
Group3) <> 1 then 'LightGrey' else 'Black' end as BorderColor,
 -- AVG(MValue) OVER (PARTITION BY Group1, Group2, Group4),
 Case when AVG(MValue*1.0) OVER(PARTITION BY Group1, Group2, Group4) <>
MValue then 'Orange' else 'White' end as BackgroundColor
from #MyMatrix
order by Group1, Group2, Group3, Group4;

Im Report kommt dann dieses hier für die BorderColor-Property, wobei meine Gruppen einfach den Namen Group1..3 haben:
=IIF(IsNothing(Fields!MValue.Value) and First(Fields!rn_2.Value, "Group3") = 1, "Black", IIF(IsNothing(Fields!MValue.Value), "LightGrey", Fields!BorderColor.Value))

Einen schönen Tag noch,
Christoph
--
Microsoft SQL Server MVP - http://www.insidesql.org/blogs/cmu

Answer 9

Und wo wir schon gerade beim vereinfachen sind, kannst Du auch die Border-Color-Berechnung komplett aus dem SQL rauslassen, wenn rn_2 enthalten ist.
=IIF(First(Fields!rn_2.Value, "Group3") = 1, "Black", "LightGrey")

Einen schönen Tag noch,
Christoph
--
Microsoft SQL Server MVP - http://www.insidesql.org/blogs/cmu

Answer 10

So sollte es aussehen, wobei ich das * 1.1 wieder ergänzt habe, damit alle Zellen Orange werden.

Soll sicher "*1.0" heißen aber den Sinn davon verstehe ich nicht. Das liegt vielleicht daran, dass meine SQL-Abfrage gegen eine Oracle-DB geht. Verhält sich Oracle an dieser Stelle anders als Microsoft?

Ansonsten habe ich die Formatierung der Ränder mit der von Dir vorgeschlagenen Formel

=IIF(First(Fields!rn_2.Value, "Group3") = 1, "Black", "LightGrey")

in meinem Testbeispiel hinbekommen. In meinem "reale Welt"-Report der dann noch etwas komplexer ist, gibt es noch ein paar Zellen, die immer noch nicht richtig aussehen. Da suche ich gerade, woran das liegt.

Grundsätzlich hätte ich, wie schon geschrieben, gedacht das das einfacher geht. Wenn man mehrere Hierarchie-Ebenen hat, die man unterschiedlich separieren möchte (erst dicke Linien, dann dünne Linien, dann graue Linien, dann gepunktete Linien, ...), dann wird es so schon recht aufwändig. Und wenn man eine Datenquelle hat, die keine analytischen Funktionen versteht, dann ist man mit dieser Lösung auch aufgeschmissen.

Answer 11

Die Multiplikation mit 1.0 ist in diesem Beispiel notwendig, damit der Durchschnitt der Integer-Werte ein Dezimalwert wird und auch alle anderen Integer-Werte der Gruppe dann als Abweichung erkannt werden. Evtl. ist die Spalte auf Oracle ja kein Integer, sondern ein anderes numerisches Format?

Falls Du mal nicht direkt mit Oracle klar kommst, kannst Du immer noch den Weg über einen Verbindungsserver gehen und die Daten per OpenQuery abholen. Darauf kannst Du dann wieder mit SQL Server Mitteln arbeiten. Gleiches gilt für die anderen Datenbanken, die evtl. keine analytischen Funktionen haben. Oracle hat diese ja zum Glück.
 Einen schönen Tag noch,
Christoph
--
Microsoft SQL Server MVP - http://www.insidesql.org/blogs/cmu