Authorization

In order to use the analysis, you need the rights to access the statistic "Faults: Causes".

Technical information

The SQL query for the drilldown statistic "Faults: Causes" looks like this:

PMS distribution fault causes by machine type
declare @INNO_USER_LANG int = :INNO_USER_LANG;
declare @INNO_USER varchar(12) = :INNO_USER;
declare @DATE_FROM date = :date_from;
declare @DATE_TO date = :date_to;
declare @STOERUNG_CODE varchar(20) = :stoerung_code;
 
with ursachen as
(
select
distinct(ursache_code),
ursache_code + ' - ' + ursache_text as code_text
from
FACT_TICKET_DLZ p
 
where
cast(p.START_ZEIT as date) >= @DATE_FROM and cast(p.START_ZEIT as date) <= @DATE_TO
and stoerung_code = @STOERUNG_CODE
and (:maschtyp_ISNULL = 1 OR p.maschnr in (select m.maschnr from mapark m where m.maschtyp in (:maschtyp)))
 
)
 
,ursachen_offen as
(
select
count(p.ursache_code) c_offen,
ursache_code + ' - ' + ursache_text as code_text
 
from
FACT_TICKET_DLZ p
 
where
cast(p.START_ZEIT as date) >= @DATE_FROM and cast(p.START_ZEIT as date) <= @DATE_TO
and stoerung_code = @STOERUNG_CODE
and p.AKTIV = 1
and (:maschtyp_ISNULL = 1 OR p.maschnr in (select m.maschnr from mapark m where m.maschtyp in (:maschtyp)))
 
group by
p.ursache_code,
p.ursache_text
)
,ursachen_geschlossen as
(
select
count(p.ursache_code) c_geschlossen,
ursache_code + ' - ' + ursache_text as code_text
 
from
FACT_TICKET_DLZ p
 
where
cast(p.START_ZEIT as date) >= @DATE_FROM and cast(p.START_ZEIT as date) <= @DATE_TO
and stoerung_code = @STOERUNG_CODE
and p.AKTIV = 0
and (:maschtyp_ISNULL = 1 OR p.maschnr in (select m.maschnr from mapark m where m.maschtyp in (:maschtyp)))
 
group by
p.ursache_code,
p.ursache_text
)
 
 
select
u.code_text,
coalesce(uo.c_offen, 0) c_offen,
'red' color_offen,
coalesce(ug.c_geschlossen, 0) c_geschlossen,
'green' color_geschlossen,
coalesce(uo.c_offen, 0) + coalesce(ug.c_geschlossen, 0) c_gesamt
from
ursachen u
left outer join ursachen_offen uo on uo.code_text = u.code_text
left outer join ursachen_geschlossen ug on ug.code_text = u.code_text
 
order by c_gesamt desc

The drilldown statistics interface is saved with the following data set in the "Statistics_UI":

INSERT faults: causes
INSERT INTO STATISTIC_UI (ID, NAME, STATISTIC_CONFIGURATION, STATISTIC_CONTROLLER, CATEGORY_ID, DELETED, DESCRIPTION, IS_INTERNAL, OVERRIDDEN_BY)
VALUES ('{13540FB3-149E-4ED1-86F9-0A7BD09B06A8}18', 'Störungen: Ursachen', '{
"type": "STATISTIK",
"rows": [
{
"type": "ROW",
"rowItems": [
{
"type": "CHART",
"width": "12",
"uid": "5f910acc-28dd-45c9-88f3-e675d6d06b6a",
"titel": "{{{Statistik.StörungenUrsachen}}}",
"dataUrl": "api/dynamicapi/{205C8FC4-03D9-4C75-9634-CE9A6C9B7E72}26/GetData",
"chartSettings": {
"theme": "bootstrap",
"series": [
{
"type": "bar",
"field": "C_OFFEN",
"colorField": "COLOR_OFFEN",
"categoryField": "CODE_TEXT",
"stack": {
"group": "STACK_1"
},
"labels": {
"visible": true
}
},
{
"type": "bar",
"field": "C_GESCHLOSSEN",
"colorField": "COLOR_GESCHLOSSEN",
"categoryField": "CODE_TEXT",
"name": "",
"stack": {
"group": "STACK_1"
},
"labels": {
"visible": true
}
}
],
"valueAxis": null
},
"label": "",
"height": "500"
}
],
"uid": "41be4902-a290-48b3-b4b7-ca9adb92cc27"
}
],
"parameters": [
{
"type": "DATE",
"width": "7",
"uid": "399fc4d5-b2fd-4030-9c96-46cb303838a5",
"name": "date_from",
"label": "{{{Standard.Von}}}"
},
{
"type": "DATE",
"width": "7",
"uid": "495839d7-bf7f-4791-a758-e089750a7dab",
"name": "date_to",
"label": "{{{Standard.Bis}}}"
},
{
"type": "MULTISELECT",
"width": "12",
"uid": "10d84ffc-6596-4592-82d9-44662bef7aa3",
"name": "maschtyp",
"dataUrl": "api/dynamicapi/{205C8FC4-03D9-4C75-9634-CE9A6C9B7E72}21/GetData",
"keyField": "MASCHTYP",
"valueField": "MASCHTYP",
"label": "{{{Field.Maschine.maogrnr}}}"
},
{
"type": "MULTISELECT",
"width": "12",
"uid": "633cc3a9-309f-443e-87b4-2da2f6fac5b3",
"dataUrl": "api/dynamicapi/{205C8FC4-03D9-4C75-9634-CE9A6C9B7E72}27/GetData",
"keyField": "STOERUNG_CODE",
"valueField": "STOERUNG_TEXT",
"name": "stoerung_code",
"label": "{{{WebSMS.Procedure.FaultText}}}"
}
],
"settings": {
"type": "SETTINGS"
}
}', '
var controller={};
 
controller.getInitialValues = function(paramsFromStorage, params, drilldownparams){
 
debugger;
 
/*
if (paramsFromStorage) {
return paramsFromStorage;
}
*/
 
var result = {};
if (paramsFromStorage) {
result.date_from = paramsFromStorage.date_from;
result.date_to = paramsFromStorage.date_to;
result.maschtyp = paramsFromStorage.maschtyp;
result.stoerung_code = drilldownparams.STOERUNG_CODE;
}
 
return result;
 
 
/*
var Jahr = new Date().getFullYear();
return {
date_from: ''01.01.'' + Jahr,
date_to: ''31.12.'' + Jahr
};
*/
 
}
 
controller;
', NULL, 0, 'Anzahl der Tickets, denen im gewählten Zeitraum eine Störung und eine Störungsursache zugewiesen worden ist.
<br>
Rot / Grün: Offene / Geschlossene Tickets.
<br><br>
Filter:
<br>
<ul><li>Start - Ende<li>Maschinentyp<li>Störung</ul>', 1, NULL);