Veröffentlicht am: 15. März 2018   Kategorie: Business Intelligence   Author: Wolf Biber

Multi Filter mit Like im SQL Server Reporting Services (SSRS)

Problem
Wenn Sie sehr lange Listen als mehrfach Filter definieren wird es nicht nur sehr unübersichtlich, sondern die Browser kommen auch an Ihre Grenzen solche Listen zu Rendern.
Eine Lösung wäre es eine Liste (Dropdown) mit mehreren Wildcards zu nutzen.
In meinem Beispiel nutze ich die AdventureWorksDW2016 mit FactInternetSales as Faktentabelle, DimDate als OrderDate-Dimension, DimSalesTerritory als Territory-Dimension und die DimProduct als Product-Dimension.
Erstellen Sie für das Beispiel einen neuen SSRS-Report und Verbinden Sie sich mit der gewünschten Datenquelle.
Erstellen Sie 3 DataSets mit folgendem Inhalt:


--DataSet InternetSales
SELECT EnglishProductName, Territory.SalesTerritoryRegion, Orderdate.DateKey, OrderDate.CalendarYear,orderdate.EnglishMonthName,ISales.*
FROM FactInternetSales ISales
inner join DimDate OrderDate on ISales.OrderDateKey = OrderDate.DateKey
inner join DimSalesTerritory Territory on ISales.SalesTerritoryKey = Territory.SalesTerritoryKey
inner join DimProduct Product on ISales.ProductKey = Product.ProductKey
where
OrderDate.CalendarYear = @CalendarYear
and Territory.SalesTerritoryRegion in (@SalesTerritory)

--DataSet CalendarYear
SELECT DISTINCT(CalendarYear) FROM DimDate order by CalendarYear

--Dataset SaleTerritories
SELECT SalesTerritoryKey, SalesTerritoryRegion FROM DimSalesTerritory

Report Layout SSRS Wildcard


Nun müssen wir noch die Parameter einrichten.
Für den Parameter CalendarYear erstellen Sie bitte einen Parameter mit folgenden Einstellungen:

Calendar Parameter


Calendar Parameter

Für das InternetSales Dataset müssen wir nun noch folgenden konfigurieren.


Soweit zu den Vorbereitungen. Ihr Bericht sollte nun folgendes enthalten:

Nach hinzufügen einer einfacher Matrix haben wir nun eine einfache Liste mit 2 Parametern:

Alle Vorbereitungen sind nun getroffen und wir kommen zu dem Multiselect Wildcard Parameter.
Dazu müssen wir erst einmal wieder einen Parameter anlegen.
Achten Sie darauf, das Sie keine Verfügbaren Werte eingeben, Standardwerte stellen dagegen kein Problem dar und können dem Benutzer eine Hilfestellung geben.

Erweitern Sie nun das InternetSales Dataset mit folgenden Script im am Anfang des SQL Statements:
Dieses deklariert eine Tabellen variable und schreibt danach alle eingegebenen Einträge die in im Parameter @Products stehen in die Tabelle

DECLARE @ProductsTable as Table (ProductSearch varchar(max))

SET QUOTED_IDENTIFIER OFF
INSERT INTO @ProductsTable
SELECT rtrim(ltrim(Value)) as ProductSearch FROM
STRING_SPLIT(Replace(Replace(cast(
" @Products "
as varchar(max)),'N''',''),'''','') ,',')

SET QUOTED_IDENTIFIER ON

Damit auch ein Multilike dann mit unserem Bericht funktioniert müssen wir auch noch das eigentlich SQL Statement etwas erweitern:
--DataSet InternetSales
SELECT EnglishProductName, Territory.SalesTerritoryRegion, Orderdate.DateKey, OrderDate.CalendarYear,orderdate.EnglishMonthName,ISales.*
FROM FactInternetSales ISales
inner join DimDate OrderDate on ISales.OrderDateKey = OrderDate.DateKey
inner join DimSalesTerritory Territory on ISales.SalesTerritoryKey = Territory.SalesTerritoryKey
inner join DimProduct Product on ISales.ProductKey = Product.ProductKey
inner join @Products SelectedProducts on Product.EnglishProductName like SelectedProducts.Product
where
OrderDate.CalendarYear = @CalendarYear
and Territory.SalesTerritoryKey in (@SalesTerritory)

bitte beachten Sie folgende Zeile:
inner join @ProductsTable SelectedProducts on Product.EnglishProductName like SelectedProducts.ProductSearch

Unter Parameter im Dataset müssen wir den Parameter nun noch manuell eintragen:

Und fertig ist die Magie.
Testen wir es aus:

Wir kriegen somit alle Products die mit Half-Finger und HL anfangen in der Liste ausgegeben.
Es gibt jedoch ein paar Einschränkungen:
1.) Es ist wohl nicht die performanteste aller Abfragen
2.) Bei nur einem Wert in der Liste muss einmal mit Return bestätigt werden. Wenn am Ende der Liste ein „;“ steht, ist es richtig (Scheint ein Event Fehler bei SSRS zu sein)
Ich wünsche allen viel Erfolg die diesen Filter einmal umsetzen möchten.

Disclaimer:
Der hier geschriebene Artikel soll nur die Funktionsweise erklären. Den Anspruch alle Best Practices und die beste Performance umgesetzt zu haben, ist hier nicht vorhanden.

Ansprechpartner

Christian Busse Geschäftsführer

+4940 - 822 16 332

christian@cube2b.de

Wolf Biber Geschäftsführer

+4940 - 822 16 331

wolf@cube2b.de

Sie wünschen messbare Erfolge?

Dann sprechen Sie uns an, gerne beraten wir Sie unverbindlich.

  • Schreiben Sie uns:Mail: info@cube2b.de
  • Unverb. Beratung:Telefon: 040 / 3290 1199
  • Hier finden Sie uns:Kurze Mühren 1, 20095-HH