MSSQL Analyses related to variables

Variable-Related

This article offers examples for a variable related analyses with the dashboard.

Listing processinstances as well as any amount of processvariables/indexvariables

SELECT
inst.instanceName AS "Instance Name",
  
--For each processvariable a new line must be added
  variables.column_name_variable1,
  variables.column_name_variable2,
  variables.column_name_variable3,
  
--For each indexvariable a new line needs to be added
  COALESCE(inst.index1, '') AS "column_name_index1",
  COALESCE(inst.index2, '') AS "column_name_index2",
  COALESCE(inst.index3, '') AS "column_name_index3"
  
FROM view_instance inst JOIN
(SELECT
  instanceid,

--FOR each processvariable a new line needs to be added
  COALESCE(MAX(CASE WHEN variableName = 'variable1' THEN stringvalue END), '') AS "column_name_variable1",
  COALESCE(MAX(CASE WHEN variableName = 'variable2' THEN stringvalue END), '') AS "column_name_variable2",
  COALESCE(MAX(CASE WHEN variableName = 'variable3' THEN stringvalue END), '') AS "column_name_variable3"
  
--For each processvariable the variable name needs to be added inside the 'IN'-statement
 FROM view_variable WHERE variablename IN ('variable1','variable2','variable3')
  
  GROUP BY instanceid
) AS variables
ON variables.instanceId = inst.id
  ORDER BY inst.definitionName