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 stabilen Version 5.5.8.


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 5.8.3. Flussdiagramm & 5.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 somit implementieren:

CREATE PROCEDURE result.spDemo
  ( 
     @Username        NVARCHAR (255)
    ,@FactoryID       NVARCHAR (255) = ''
    ,@ProductlineID   NVARCHAR (255) = ''
    ,@ProductID       NVARCHAR (255) = ''
    ,@ParameterJSON   NVARCHAR (MAX) = ''
  )
CODE

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]	
			}
			'
CODE

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
CODE

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,',')
				);



CODE

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:

  • Im Untermenü “Tab ParameterJSON bearbeiten” werden die, in den Prozeduren definierten, Parameter eingetragen & aktiviert.

  • Die verschiedenen Funktionalitäten an der Oberfläche sind hier beschrieben: 5.8. Tab-Typen | Parameter.

  • 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.