The user must be authorised to access these statistics and must also have at least reading rights to the selected group. Further information can be found in the article Using Evaluations.

Technical information

The evaluation is generated with the following SQL query:

EP capacity per group and week

declare @INNO_USER varchar(12) = :INNO_USER;
declare @INNO_USER_LANG int = :INNO_USER_LANG;
declare @DATE_FROM Date = :date_from;
declare @DATE_TO Date = :date_to;
declare @USER_GRNR int = :user_grnr;
 
--declare @INNO_USER varchar(12) = 'inno';
--declare @INNO_USER_LANG int = 0;
--declare @DATE_FROM Date = '29.08.2016';
--declare @DATE_TO Date = '02.02.2018';
--declare @USER_GRNR int = 1;
 
--ARBEIT vorbereiten
IF OBJECT_ID('tempdb..#arbeiten', 'U') IS NOT NULL
drop table #arbeiten;
create table #arbeiten
(
grnr int not null,
datumdt date not null,
arbeit float not null,
primary key (grnr, datumdt)
)
insert into #arbeiten
select
fet.fact_grnr grnr,
fet.DatumDT,
sum(fet.ARBEITSZEIT) as arbeit
from
FACT_EINSAETZE_TAGE fet
where
fet.fact_grnr in (select pg.grnr from pwgrupp pg where pg.NAME = @INNO_USER and pg.RECHT >= 3)
and fet.fact_ogrnr in (select pog.ogrnr from pwogrupp pog where pog.NAME = @INNO_USER and pog.Recht >= 1)
and fet.DatumDT >= @DATE_FROM and fet.DatumDT <= @DATE_TO
and fet.knr in (select distinct(knr) from option_k where typ in (4, 5, 6, 7))
and fet.Fact_GrNr = @USER_GRNR
group by
fet.fact_grnr,
fet.DatumDT;
 
--FEHL vorbereiten
IF OBJECT_ID('tempdb..#fehl', 'U') IS NOT NULL
drop table #fehl;
create table #fehl
(
grnr int not null,
datumdt date not null,
fehl float not null,
primary key (grnr, datumdt)
)
insert into #fehl
select
fet.fact_grnr grnr,
fet.DatumDT,
sum(fet.ARBEITSZEIT) as fehl
from
FACT_EINSAETZE_TAGE fet
where
fet.fact_grnr in (select pg.grnr from pwgrupp pg where pg.NAME = @INNO_USER and pg.RECHT >= 3)
and fet.fact_ogrnr in (select pog.ogrnr from pwogrupp pog where pog.NAME = @INNO_USER and pog.Recht >= 1)
and fet.DatumDT >= @DATE_FROM and fet.DatumDT <= @DATE_TO
and fet.knr in (select distinct(knr) from option_k where typ in (1,2))
and fet.Fact_GrNr = @USER_GRNR
group by
fet.fact_grnr,
fet.DatumDT;
 
--KAPA vorbereiten
IF OBJECT_ID('tempdb..#kapa', 'U') IS NOT NULL
drop table #kapa;
create table #kapa
(
grnr int not null,
datumdt date not null,
kapa float not null,
primary key (grnr, datumdt)
)
insert into #kapa
select
frk.grnr,
frk.DatumDT,
sum(frk.KAPA_RESSOURCE) kapa
from
FACT_RESS_KAPA frk
where
frk.grnr in (select pg.grnr from pwgrupp pg where pg.NAME = @INNO_USER and pg.RECHT >= 3)
and frk.ogrnr in (select pog.ogrnr from pwogrupp pog where pog.NAME = @INNO_USER and pog.Recht >= 1)
and frk.DatumDT >= @DATE_FROM and frk.DatumDT <= @DATE_TO
and frk.GrNr = @USER_GRNR
group by
frk.grnr,
frk.DatumDT;
 
select
dd.Woche,
dd.Jahr,
RIGHT('0' + cast(dd.Woche as varchar), 2) + '.' + cast(dd.Jahr as varchar) as Zeiteinheit,
round(sum(coalesce(a.arbeit, 0)), 2) arbeit,
'RED' as arbeit_color,
round(sum(coalesce(k.kapa, 0)), 2) kapa_soll,
'ORANGE' as kapa_soll_color,
round(sum(coalesce(f.fehl, 0)), 2) fehl,
round(sum(coalesce(k.kapa, 0)) - sum(coalesce(f.fehl, 0)), 2) kapa_ist,
'GREEN' as kapa_ist_color,
round(sum(coalesce(k.kapa, 0)) - sum(coalesce(f.fehl, 0)) - sum(coalesce(a.arbeit, 0)), 2) kapa_frei
 
from
DIM_DATUM dd
left outer join #kapa k on k.DatumDT = dd.DatumDT
left outer join #fehl f on f.DatumDT = dd.DatumDT
left outer join #arbeiten a on a.DatumDT = dd.DatumDT
where
dd.DatumDT >= @DATE_FROM and dd.DatumDT <= @DATE_TO
 
group by dd.Woche,dd.Jahr
order by dd.jahr, dd.woche

The interface is stored in the table "Statistic_UI" with the following data set:

INSERT capacity per group and CW

INSERT INTO STATISTIC_UI (ID, NAME, STATISTIC_CONFIGURATION, STATISTIC_CONTROLLER, CATEGORY_ID, DELETED, DESCRIPTION, IS_INTERNAL, OVERRIDDEN_BY)
VALUES ('{13540FB3-149E-4ED1-86F9-0A7BD09B06A8}19', 'Auslastung pro Gruppe und KW', '{
"type": "STATISTIK",
"rows": [
{
"type": "ROW",
"rowItems": [
{
"type": "TABLECHART",
"width": "12",
"uid": "4a4c0dbb-ec1c-4b87-8ba2-e71900852e11",
"dataUrl": "api/dynamicapi/{205C8FC4-03D9-4C75-9634-CE9A6C9B7E72}36/GetData",
"titel": "{{{Statistik.VerfügbareKapazitätSollFehlBedarfPlanzeiten}}}",
"chartSettings": {
"theme": "bootstrap",
"autoSeries": [
{
"type": "line",
"style": "step",
"field": "KAPA_SOLL",
"colorField": "KAPA_SOLL_COLOR",
"color": "ORANGE",
"name": "{{{Statistik.SollKapazität}}}"
},
{
"type": "line",
"style": "step",
"field": "KAPA_IST",
"colorField": "KAPA_IST_COLOR",
"color": "GREEN",
"name": "{{{Statistik.VerfügbarSollFehl}}}"
},
{
"type": "column",
"field": "ARBEIT",
"colorField": "ARBEIT_COLOR",
"color": "RED",
"name": "{{{Statistik.Bedarf}}}"
}
],
"tooltip": {
"visible": true,
"template": ""
},
"chartArea": {
"margin": {
"left": 0
}
},
"valueAxis": {
"visible": false
},
"series": [
{
"type": "line",
"field": "KAPA_SOLL",
"colorField": "KAPA_SOLL_COLOR",
"color": "ORANGE",
"missingValues": "zero",
"visibleInLegend": false,
"name": "{{{Statistik.SollKapazität}}}",
"style": "step"
},
{
"type": "line",
"field": "KAPA_IST",
"colorField": "KAPA_IST_COLOR",
"color": "GREEN",
"missingValues": "zero",
"visibleInLegend": false,
"name": "{{{Statistik.VerfügbarSollFehl}}}",
"style": "step"
},
{
"type": "column",
"field": "ARBEIT",
"colorField": "ARBEIT_COLOR",
"color": "RED",
"missingValues": "zero",
"visibleInLegend": false,
"name": "{{{Statistik.Bedarf}}}"
}
]
},
"categoryAxis": {
"field": "ZEITEINHEIT"
},
"pageSize": "13",
"firstColumnWidth": "150",
"exportTemplate": "",
"chartHeight": "400"
}
],
"uid": "8bdfc3f5-61d8-476c-8038-aec51811a28a"
}
],
"parameters": [
{
"type": "DATE",
"width": "7",
"uid": "92bc1b28-ec61-4cf0-9163-5d091e39e524",
"name": "date_from",
"label": "{{{Standard.Von}}}"
},
{
"type": "DATE",
"width": "7",
"uid": "1509f2ba-a765-4870-9487-93f0aca4ae41",
"name": "date_to",
"label": "{{{Standard.Bis}}}"
},
{
"type": "SELECT",
"width": "12",
"uid": "d8440e15-e9ab-4add-9d6b-2e48db62cb05",
"label": "{{{Field.Ressource.gruppe}}}",
"dataUrl": "api/dynamicapi/{205C8FC4-03D9-4C75-9634-CE9A6C9B7E72}2/GetData",
"keyField": "GRNR",
"valueField": "GRUPPE",
"name": "user_grnr"
}
],
"settings": {
"type": "SETTINGS"
}
}', '
var controller={};
 
controller.getInitialValues = function(paramsFromStorage, params, drilldownparams){
 
debugger;
 
if (drilldownparams) {
 
var Jahr = new Date().getFullYear();
var result = {};
 
result.date_from = drilldownparams.START_OF_CURRENT_WEEK;
result.date_to = ''31.12.'' + Jahr;
result.user_grnr = drilldownparams.GRNR;
 
return result;
 
}
 
//if (params)
//return params;
 
if (paramsFromStorage)
return paramsFromStorage;
 
}
 
 
controller;
', 0, 0, '<h4>Kapazitäten für die gewählte Gruppe im gewählten Zeitraum:</h4>
 
<ul>
<li>Soll-Kapazität: Errechnet aus der bei der Ressource hinterlegten oder der systemweit eingestellten Arbeitszeit / Tag.
<br>Fremd-Ressourcen werden berücksichtigt, sobald ihnen ein Balken zugewiesen ist.
Die Soll-Kapazität erhöht sich dadurch für die Länge des Balkens um die im Balken erfasste Arbeitszeit / Tag.
<li>Verfügbar: Soll-Kapazität abzüglich der Fehlzeiten.
<li>Bedarf: Plankapazität aus Einsätzen vom Typ Adresse, Intern, Auftrag.
</ul>
<br>
<h4>Filter:</h4>
 
<ul><li>Start - Ende<li>Gruppe (unter Berücksichtigung der Benutzerberechtigung)</ul>', 1, NULL);