1.2. Parameter in Tabs
Dieser Artikel erklärt die Verwendung von Parametern im Zusammenhang mit verschiedenen Tab-Typen. Der ganze Artikel richtet sich an Fortgeschrittene im Umgang mit SQL.
Wenn man einen Reporting-Tab öffnet, dann werden Datenbestände aus der Datenbank in diesen Tab geladen. Sind die Datenbestände besonders groß, dann kommt es unter Umständen zu Performance-Problemen. Das setzen von Parametern kann den Umfang des Datenbestandes reduzieren, da diese in den Prozeduren im Hintergrund als Filterkriterium dienen (z.B. nur bestimmte Mandanten/Finanzjahre).
Version: Dieser Artikel ist mindestens gültig ab der Version 5.5.
1. Voraussetzungen
Die gewünschten Parameter, als Filterkriterium für den Datenbestand, können zwar an der Programmoberfläche gesetzt werden. Ohne eine korrekte Definition & den korrekten Einsatz in der dahinterliegenden Prozedur, zeigen alle Einstellungen an der GUI keinerlei Wirkung:
SQL-Kenntnisse rund um die Bearbeitung von Prozeduren
Zugriff auf ein SQL Server Managementstudio und die Datenbank
Datenbestände für den Import in den Auswertungs-Tab.
2. Verwendungsanleitung
Einschränkungen
Parameter sind sowohl in der Definition, als auch im Einsatz auf der Programmoberfläche “case-sensitive” - empfindlich für Groß- & Kleinschreibung.
Die Kontextmenüauswahl “Parameter” ist für die Tab-Typen 4.8.3. Flussdiagramm & 4.8.6. HTML Editor nicht verfügbar.
Bereitstellung der Parameter in der Prozedur
Basisparameter
Jeder Tab übergibt der Prozedur beim Aufruf die Basisparameter über sich selbst und Zusatzparameter zur Prozedurausführung, somit:
Cluster - @Username, @ParamterJSON
Factory - @Username, @FactoryID, @ParameterJSON
Productline - @Username, @FactoryID, @ProductlineID, @ParameterJSON
Product - @Username, @FactoryID, @ProductlineID, @ProductID, @ParameterJSON
Die zugrundeliegende Prozedur muss daher die passenden Parameter implementieren, oder mehr Parameter deklarieren und diese als optional kennzeichnen. Eine Prozedur, welche universell von jedem Tab aufgerufen werden soll, müsste so implementieren werden:
CREATE PROCEDURE result.spDemo
(
@Username NVARCHAR (255)
,@FactoryID NVARCHAR (255) = ''
,@ProductlineID NVARCHAR (255) = ''
,@ProductID NVARCHAR (255) = ''
,@ParameterJSON NVARCHAR (MAX) = ''
)
Zusatzparameter
Tabs können Zusatzparameter verwalten und diese an die zugrundeliegende Prozedur übergeben. Die Funktionalität ermöglicht
eine oder mehrere Parameterlisten manuell zu definieren.
eines oder mehrere Elemente jeder Liste als default Element zu kennzeichnen.
Parameterwerte unabhängig von den definierten Listenwerten manuell zu erfassen.
Wird das Tab erstmalig aufgerufen wird, wird die Liste der Default Parameter an die zugrundeliegende Prozedur übermittelt.
Die Liste der Parameter wird an die zugrundeliegende Prozedur als JSON Array im Parameter “@ParameterJSON” übermittelt:
-- Tab hat keine Listen für Zusatzparameter implementiert
SET @ParameterJSON = N'{}'
-- Tab hat Listen implementiert, User hat aber keine Selektion vorgenommmen
SET @ParameterJSON = N'{ "CompanyID":[]
,"Jahre": []
,"Monate": []
}
'
-- User hat Einfachselektion vorgenommmen
SET @ParameterJSON = N'{ "CompanyID":["saxess Germany"]
,"Jahre": [2021]
,"Monate": [1]
}
'
-- User hat Mehrfachselektion vorgenommen
SET @ParameterJSON = N'{ "CompanyID":["saxess Germany","saxess Canada"]
,"Jahre": [2021,2022]
,"Monate": [1,2,3,4,5]
}
'
Die zugrundeliegende Prozedur muss diesen Parameter als (optionalen) Parameter implementiert haben und muss diese selbst im SQL auflösen. Die Prozedur muss damit rechnen, diesen Parameter als leer () übermittelt zu bekommen, falls keine Parameterlisten im Tab implementiert sind.
Testaufruf mit Zusatzparametern
Für den Testaufruf einer Prozedur mit Zusatzparametern muss der Parameter im JSON-Format wie im folgenden Beispiel angegeben werden:
DECLARE @RC INT;
EXEC @RC = result.spVM_BewegungsdatenHistorisch
@Username = 'SQL',
@ParameterJSON = '{ "Loaddate":[20210924] }'
PRINT @RC
Beispiel zur Auflösung des ParameterJSON auf verschiedenen Wegen
DECLARE @ParameterJSON NVARCHAR(MAX);
SET @ParameterJSON = N'{ "CompanyID":["saxess Germany","saxess Canada"]
,"Jahre": [2021,2022]
,"Monate": [1,2,3,4,5]
}
'
-- Test for valid JSON #########################################################################
IF ISJSON(@ParameterJSON) = 0
BEGIN
PRINT 'Exit due to invalid JSON'
--RETURN 403
END
DECLARE
@CompanyID NVARCHAR(255) = N''
,@Jahr INT = 0
,@Monat NVARCHAR(MAX) = 0;
--Option 1 Extract Parameter FROM JSON over picking single values ######################################################################
SET @CompanyID = JSON_Value(@ParameterJSON,N'$."CompanyID"[0]') COLLATE DATABASE_DEFAULT;
SET @Jahr = JSON_Value(@ParameterJSON,N'$."Jahre"[0]') COLLATE DATABASE_DEFAULT;
SET @Monat = JSON_Value(@ParameterJSON,N'$."Monate"[0]') COLLATE DATABASE_DEFAULT;
PRINT @CompanyID
PRINT @Jahr
PRINT @Monat
--Option 2 Extract Parameter into a variable list to use for filtering over IN CLAUSE ######################################################################
DECLARE
@Companyliste NVARCHAR(2000) = N''
,@Jahresliste NVARCHAR(255) = N''
,@Monatsliste NVARCHAR(255) = N'';
SELECT
@Companyliste = CompanyID
,@Jahresliste = Jahre
,@Monatsliste = Monate
FROM OPENJSON(@ParameterJSON)
WITH (
CompanyID NVARCHAR(MAX) AS JSON
,Jahre NVARCHAR(MAX) AS JSON
,Monate NVARCHAR(MAX) AS JSON
)
-- Variable von eckigen Klammern und Textbegrenzern befreien
SET @Companyliste = REPLACE(REPLACE(REPLACE(@Companyliste ,'[',''),']',''),'"','');
SET @Jahresliste = REPLACE(REPLACE(REPLACE(@Jahresliste ,'[',''),']',''),'"','');
SET @Monatsliste = REPLACE(REPLACE(REPLACE(@Monatsliste ,'[',''),']',''),'"','');
PRINT @Companyliste
PRINT @Jahresliste
PRINT @Monatsliste
DROP TABLE IF EXISTS #tDemo;
SELECT * INTO #tDemo
FROM
(
VALUES
('saxess Germany' ,2020 ,1)
,('saxess Germany' ,2020 ,1)
,('saxess Canada' ,2021 ,2)
,('saxess USA' ,2021 ,2)
) tmp (CompanyID, Jahr, Monat);
SELECT
*
FROM #tDemo
WHERE
CompanyID IN
(
SELECT * FROM STRING_SPLIT(@Companyliste,',')
)
AND Jahr IN
(
SELECT * FROM STRING_SPLIT(@Jahresliste,',')
);
Nutzung der bereitgestellten Parameter (Programmoberfläche)
Unterscheidung von Parameter und Tabellenfilter
Parameter begrenzen den Datenbestand, welcher überhaupt in den Tab geladen wird.
Das beeinflusst die Performance, gerade bei großen Datenbeständen und/oder vielen Zeilen/Spalten sehr deutlich.
Tabellenfilter (z.B. Zeilen- oder Spaltenfilter) nehmen den geladenen Datenbestand und blenden, die zur Filterung ausgewählten, Inhalte auf der Programmoberfläche aus.
Anlegen & Bearbeiten von Parametern
Tabs, welche Parameter enthalten können, öffnen deren Bearbeitung über das Kontextmenü des Tabs:
.png?inst-v=37bbb90e-1eb3-468c-aa18-c221e991b1be)
Im Untermenü “Tab ParameterJSON bearbeiten” werden die, in den Prozeduren definierten, Parameter eingetragen & aktiviert.
Die zu hinterlegenden IDs müssen den im Skript im OPENJSON-Befehl angesprochenen Feldern entsprechen.
Die verschiedenen Funktionalitäten an der Oberfläche sind hier beschrieben: https://saxess-software.atlassian.net/wiki/spaces/OH/pages/445646128/5.8.+Tab-Typen#Parameter.
.png?inst-v=37bbb90e-1eb3-468c-aa18-c221e991b1be)
An diesem Beispiel wird der, in den Tab aus der Datenbank geladene, Datenbestand auf die Jahre “2019/2020”, “2020/2021” und “2021/2022” beschränkt.
Die Parameter und deren Werte aktualisieren sich nicht automatisch. Kommt ein Mandant oder ein Finanzjahr in der Datenbank hinzu, und die Prozedur ist nicht zusätzlich auf solch eine Funktion angepasst, dann muss der neue Werte manuell nachgetragen & aktiviert werden.
Speichern & Anwenden
Änderungen im Untermenü “Tab ParameterJSON bearbeiten” können auf folgenden Wegen angewendet werden:
Speichern und Anwenden:
Wendet die Parameter auf den Tab an und speichert diese permanent in der Datenbank ab.
Anwenden:
Wendet die Parameter temporär an.
Bei einem Neuladen der Seite gehen alle getätigten Änderungen verloren.
Das Feature “Anwenden” eignet sich zum temporären Testen der neuen Einstellungen, ohne das vorher eingestellte Werte & Parameter verloren gehen.