Authorization

The user needs at least the right to call up these statistics. In addition, at least read rights to the groups and main groups of the displayed resources are required.

Technical information

The data of the evaluation is prepared with the following SQL query:

PM Hours / Utilization
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 @INNO_USER varchar(12) = 'inno';
declare @INNO_USER_LANG int = 0;
declare @DATE_FROM Date = '01.01.2016';
declare @DATE_TO Date = '31.12.2016';
--declare @USER_GRNR int = 7;
--declare @USER_OGRNR int = 1;
*/
 
--FEHL vorbereiten
IF OBJECT_ID('tempdb..#fehl', 'U') IS NOT NULL
drop table #fehl;
create table #fehl
(
grnr int not null,
ogrnr int not null,
datumdt date not null,
fehl float not null,
primary key (grnr, ogrnr, datumdt)
)
insert into #fehl
select
fet.fact_grnr grnr,
fet.fact_ogrnr ogrnr,
fet.DatumDT,
coalesce(sum(fet.ARBEITSZEIT), 0) 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 ((:user_grnr_ISNULL = 1) or (fet.Fact_GrNr in (:user_grnr)))
and fet.fact_ogrnr in (select pog.ogrnr from pwogrupp pog where pog.NAME = @INNO_USER and pog.Recht >= 1)
and ((:user_ogrnr_ISNULL = 1) or (fet.Fact_OGrNr in (:user_ogrnr)))
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))
group by
fet.fact_grnr,
fet.fact_ogrnr,
fet.DatumDT;
 
--KAPA vorbereiten
IF OBJECT_ID('tempdb..#kapa', 'U') IS NOT NULL
drop table #kapa;
create table #kapa
(
grnr int not null,
ogrnr int not null,
datumdt date not null,
kapa float not null,
primary key (grnr, ogrnr, datumdt)
)
insert into #kapa
select
frk.grnr,
frk.ogrnr,
frk.DatumDT,
coalesce(sum(frk.KAPA_RESSOURCE), 0) 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 ((:user_grnr_ISNULL = 1) or (frk.GrNr in (:user_grnr)))
and frk.ogrnr in (select pog.ogrnr from pwogrupp pog where pog.NAME = @INNO_USER and pog.Recht >= 1)
and ((:user_ogrnr_ISNULL = 1) or (frk.OGrNr in (:user_ogrnr)))
and frk.DatumDT >= @DATE_FROM and frk.DatumDT <= @DATE_TO
group by
frk.grnr,
frk.ogrnr,
frk.DatumDT;
 
--EP-Einsätze ohne PM vorbereiten
IF OBJECT_ID('tempdb..#ep_plan', 'U') IS NOT NULL
drop table #ep_plan;
create table #ep_plan
(
grnr int not null,
ogrnr int not null,
datumdt date not null,
kapa float not null,
primary key (grnr, ogrnr, datumdt)
)
insert into #ep_plan
select
fet.fact_grnr,
fet.fact_ogrnr,
fet.DatumDT,
coalesce(sum(fet.ARBEITSZEIT), 0) Kapa
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 ((:user_grnr_ISNULL = 1) or (fet.Fact_GrNr in (:user_grnr)))
and fet.fact_ogrnr in (select pog.ogrnr from pwogrupp pog where pog.NAME = @INNO_USER and pog.Recht >= 1)
and ((:user_ogrnr_ISNULL = 1) or (fet.Fact_OGrNr in (:user_ogrnr)))
and fet.DatumDT >= @DATE_FROM and fet.DatumDT <= @DATE_TO
and fet.knr in (select distinct(dk.knr) from DIM_KENNUNG dk where dk.Typ in (4,5,6,7))
and (fet.PROJNR not in (select distinct(usernummer) from AUFUEBER where USERNUMMER is not null))
group by
fet.fact_grnr,
fet.fact_ogrnr,
fet.DatumDT;
 
with datum_go as
(
select
dd.*,
g.GrNr,
o.OGrNr
from
DIM_DATUM dd,
option_g g,
option_o o
where
dd.DatumDT >= @DATE_FROM and dd.DatumDT <= @DATE_TO
and g.Language = 0
and o.Language = 0
and g.grnr in (select pg.grnr from pwgrupp pg where pg.NAME = @INNO_USER and pg.RECHT >= 3)
and ((:user_grnr_ISNULL = 1) or (g.GrNr in (:user_grnr)))
and o.OGrNr in (select pog.ogrnr from pwogrupp pog where pog.NAME = @INNO_USER and pog.Recht >= 1)
and ((:user_ogrnr_ISNULL = 1) or (o.OGrNr in (:user_ogrnr)))
 
), vorgaenge as
(
select
v.datumdt,
v.freigabe_ep,
v.freigabe_rueck,
v.ogrnr,
v.grnr,
v.STUNDEN_PLAN_TAG,
case when (((v.STUNDEN_SOLL_TAG - v.STUNDEN_PLAN_TAG) > 0) and (v.freigabe_ep = 1)) then v.STUNDEN_SOLL_TAG - v.STUNDEN_PLAN_TAG else 0 end STUNDEN_NOT_PLAN_TAG,
case when ((v.Freigabe_EP = 0) and (v.Freigabe_Rueck = 1)) then coalesce(v.STUNDEN_SOLL_TAG, 0) else 0 end STUNDEN_SOLL_TAG,
v.STUNDEN_IST_TAG
from
FACT_VORGAENGE_TAGE v
), vorgaenge2 as
(
select
v.datumdt,
v.ogrnr,
v.grnr,
sum(v.STUNDEN_PLAN_TAG) STUNDEN_PLAN_TAG,
sum(v.STUNDEN_NOT_PLAN_TAG) STUNDEN_NOT_PLAN_TAG,
sum(v.STUNDEN_SOLL_TAG) STUNDEN_SOLL_TAG,
sum(v.STUNDEN_IST_TAG) STUNDEN_IST_TAG
from
vorgaenge v
group by
v.datumdt,
v.ogrnr,
v.grnr
 
)
 
select
d.Jahr,
d.Woche,
Round(sum(coalesce(k.kapa, 0) - coalesce(f.fehl, 0)), 2) Verfuegbar,
'GREEN' as Verfuegbar_Color,
Round(sum(coalesce(ep.kapa, 0)), 2) EP_Plan,
'Crimson' as EP_Plan_Color,
Round(sum(coalesce(fvt.STUNDEN_PLAN_TAG, 0)), 2) PM_Plan,
'RoyalBlue' as PM_Plan_Color,
Round(sum(coalesce(fvt.STUNDEN_NOT_PLAN_TAG, 0)), 2) PM_Not_Plan,
'MediumBlue' as PM_Not_Plan_Color,
Round(sum(coalesce(fvt.STUNDEN_SOLL_TAG, 0)), 2) PM_Soll,
'ORANGE' as PM_Soll_Color,
Round(sum(coalesce(fvt.STUNDEN_IST_TAG, 0)), 2) PM_Ist,
'GreenYellow' as PM_Ist_Color,
RIGHT('0' + cast(d.Woche as varchar), 2) + '.' + cast(d.Jahr as varchar) as Zeiteinheit
from
datum_go d
left outer join #fehl f on f.datumdt = d.DatumDT and f.grnr = d.GrNr and f.ogrnr = d.OGrNr
left outer join #kapa k on k.datumdt = d.DatumDT and k.grnr = d.GrNr and k.ogrnr = d.OGrNr
left outer join #ep_plan ep on ep.datumdt = d.DatumDT and ep.grnr = d.GrNr and ep.ogrnr = d.OGrNr
left outer join vorgaenge2 fvt on fvt.datumdt = d.DatumDT and fvt.grnr = d.GrNr and fvt.ogrnr = d.OGrNr
group by
d.Jahr,
d.Woche
 
order by
d.Jahr,
d.Woche

The configuration for the evaluation is stored in the following data set in the table "Statistic_UI":

INSERT Comparison of target, planned, actual hours
INSERT INTO STATISTIC_UI (ID, NAME, STATISTIC_CONFIGURATION, STATISTIC_CONTROLLER, CATEGORY_ID, DELETED, DESCRIPTION, IS_INTERNAL, OVERRIDDEN_BY)
VALUES ('{13540FB3-149E-4ED1-86F9-0A7BD09B06A8}5', 'Vergleich Soll-, Plan-, Iststunden', '{
  "type": "STATISTIK",
  "rows": [
    {
      "type": "ROW",
      "rowItems": [
        {
          "type": "TABLECHART",
          "width": "12",
          "uid": "732052f1-8353-467f-92ef-a557a139e7ad",
          "dataUrl": "api/dynamicapi/{205C8FC4-03D9-4C75-9634-CE9A6C9B7E72}39/GetData",
          "chartSettings": {
            "theme": "bootstrap",
            "autoSeries": [
              {
                "type": "line",
                "style": "step",
                "field": "VERFUEGBAR",
                "colorField": "VERFUEGBAR_COLOR",
                "color": "GREEN",
                "name": "Verfügbar",
                "stack": {
                  "group": ""
                }
              },
              {
                "type": "column",
                "field": "PM_PLAN",
                "colorField": "PM_PLAN_COLOR",
                "color": "BLUE",
                "style": "step",
                "name": "Geplant (PM)",
                "stack": {
                  "group": "PM"
                }
              },
              {
                "type": "column",
                "style": "step",
                "field": "PM_NOT_PLAN",
                "colorField": "PM_NOT_PLAN_COLOR",
                "name": "Ungeplant (PM)",
                "stack": {
                  "group": "PM"
                },
                "color": ""
              },
              {
                "type": "column",
                "colorField": "EP_PLAN_COLOR",
                "style": "step",
                "field": "EP_PLAN",
                "color": "RED",
                "name": "Geplant (EP)",
                "stack": {
                  "group": "PM"
                }
              },
              {
                "type": "column",
                "style": "step",
                "field": "PM_SOLL",
                "colorField": "PM_SOLL_COLOR",
                "stack": {
                  "group": "PM"
                },
                "name": "Bedarf (PM)"
              },
              {
                "type": "column",
                "style": "step",
                "field": "PM_IST",
                "colorField": "PM_IST_COLOR",
                "color": "",
                "name": "IST (PM)",
                "stack": {
                  "group": "PM2"
                }
              }
            ],
            "chartArea": {
              "margin": {
                "left": 0
              }
            },
            "valueAxis": {
              "visible": false
            },
            "series": [
              {
                "type": "line",
                "field": "VERFUEGBAR",
                "colorField": "VERFUEGBAR_COLOR",
                "color": "GREEN",
                "missingValues": "zero",
                "visibleInLegend": false,
                "name": "Verfügbar",
                "style": "step"
              },
              {
                "type": "column",
                "field": "PM_PLAN",
                "colorField": "PM_PLAN_COLOR",
                "color": "BLUE",
                "missingValues": "zero",
                "visibleInLegend": false,
                "name": "Geplant (PM)",
                "style": "step",
                "stack": {
                  "group": "PM"
                }
              },
              {
                "type": "column",
                "field": "PM_NOT_PLAN",
                "colorField": "PM_NOT_PLAN_COLOR",
                "color": "",
                "missingValues": "zero",
                "visibleInLegend": false,
                "name": "Ungeplant (PM)",
                "style": "step",
                "stack": {
                  "group": "PM"
                }
              },
              {
                "type": "column",
                "field": "EP_PLAN",
                "colorField": "EP_PLAN_COLOR",
                "color": "RED",
                "missingValues": "zero",
                "visibleInLegend": false,
                "name": "Geplant (EP)",
                "style": "step",
                "stack": {
                  "group": "PM"
                }
              },
              {
                "type": "column",
                "field": "PM_SOLL",
                "colorField": "PM_SOLL_COLOR",
                "missingValues": "zero",
                "visibleInLegend": false,
                "name": "Bedarf (PM)",
                "style": "step",
                "stack": {
                  "group": "PM"
                }
              },
              {
                "type": "column",
                "field": "PM_IST",
                "colorField": "PM_IST_COLOR",
                "color": "",
                "missingValues": "zero",
                "visibleInLegend": false,
                "name": "IST (PM)",
                "style": "step",
                "stack": {
                  "group": "PM2"
                }
              }
            ]
          },
          "firstColumnWidth": "115",
          "titel": "{{{Statistik.VergleichSollPlanIststunden}}}",
          "pageSize": "13",
          "categoryAxis": {
            "field": "ZEITEINHEIT"
          },
          "exportTemplate": "",
          "chartHeight": "400"
        }
      ],
      "uid": "7a9f67d9-5081-496a-944e-aa2a91ba50a5"
    }
  ],
  "parameters": [
    {
      "type": "DATE",
      "width": "7",
      "uid": "b5729a43-369f-48af-903b-27e8ce3aab4b",
      "name": "date_from",
      "label": "{{{Standard.Von}}}"
    },
    {
      "type": "DATE",
      "width": "7",
      "uid": "90ee843c-8bff-4f37-9ba4-39589c7250b2",
      "name": "date_to",
      "label": "{{{Standard.Bis}}}"
    },
    {
      "type": "MULTISELECT",
      "width": "12",
      "uid": "2d19cb1c-aaa2-424f-910f-34f13603b3a5",
      "name": "user_grnr",
      "label": "{{{Field.Ressource.gruppe}}}",
      "dataUrl": "api/dynamicapi/{205C8FC4-03D9-4C75-9634-CE9A6C9B7E72}2/GetData",
      "keyField": "GRNR",
      "valueField": "GRUPPE"
    },
    {
      "type": "MULTISELECT",
      "width": "12",
      "uid": "04a7ffcb-4dc7-463a-92ba-e535be95bb41",
      "name": "user_ogrnr",
      "label": "{{{Statistik.Obergruppe}}}",
      "dataUrl": "api/dynamicapi/{205C8FC4-03D9-4C75-9634-CE9A6C9B7E72}1/GetData",
      "keyField": "OGRNR",
      "valueField": "OBERGRUPPE"
    }
  ],
  "settings": {
    "type": "SETTINGS"
  }
}', '
var controller={};

controller.getInitialValues = function(paramsFromStorage){
    

  if (paramsFromStorage) {
      return paramsFromStorage;
  }

  var Jahr = new Date().getFullYear();
  return {
        date_from: ''01.01.'' + Jahr,
        date_to: ''31.12.'' + Jahr
    };
    
}

controller;
', 2, 0, '

Planzeiten (wochenweise):

  • Verfügbar: Verfügbare Kapazität in Stunden errechnet aus der bei der Ressource hinterlegten oder der systemweit eingestellten Arbeitszeit / Tag. Fremd-Ressourcen werden berücksichtigt, sobald ihnen ein Balken zugewiesen ist. Die Soll-Kapazität erhöht sich dann für die Länge des Balkens um die im Balken erfasste Arbeitszeit / Tag. Fehlzeiten sind abgezogen.

  • Geplant (PM): Freigegebene Zeiten (EP-Freigabe) aus dem Projektmanagement, denen bereits ein Einsatz in der Einsatzplanung zugewiesen worden ist.

  • Ungeplant (PM): Freigegebene Zeiten (EP-Freigabe) aus dem Projektmanagement, für die noch nicht alle Stunden verplant wurden ((PM.BedarfSoll - EP.BedarfPlan) > 0).

  • Geplant (EP): Einsätze ohne Bezug zum Projektmanagement, z.B. Serviceeinsätze, interne oder adressbezogene Einsätze

  • Bedarf (PM): Soll-Zeiten aus dem Projektmanagement, die keine Freigabe für die Einsatzplanung besitzen. Diese Zeiten werden nur berücksichtigt, wenn in den entsprechenden Projekten die Freigabe Rückmeldung gesetzt ist.

  • IST (PM): Rückgemeldete Ist-Zeiten


Filter:

  • Start - Ende

  • Gruppe (unter Berücksichtigung der Benutzerberechtigung)

  • Obergruppe (unter Berücksichtigung der Benutzerberechtigung)

', 1, NULL);