Authorization

The logged-in user must always have access rights to the called statistics, for more information see the article Using evaluations. In addition, the main group rights are taken into account and when applying the "Main group" parameter, it is checked whether the user has at least read rights to the corresponding main group.

Technical information

The following SQL query was used to generate the first view (distribution of project hours by identifiers):

EP ProjectAZ grouped by identifiers in the time period
--Kennungen vorbereiten
IF OBJECT_ID('tempdb..#kennungen', 'U') IS NOT NULL
drop table #kennungen;
create table #kennungen
(
knr int not null,
kennung varchar(12) not null,
color_html varchar(7) not null,
primary key (knr)
)
insert into #kennungen select knr, Kennung, color_html from DIM_KENNUNG where language = :INNO_USER_LANG and typ = 5;
 
select
round(coalesce(sum(fet.Arbeitszeit), 0), 2) AZ,
dk.knr,
dk.kennung,
dk.color_html
from
dbo.FACT_EINSAETZE_TAGE fet,
dbo.kunde k,
#kennungen dk
 
where
k.projnr = fet.projnr
and dk.knr = k.knr
and fet.datumdt >= :date_from and fet.datumdt <= :date_to
and (:user_ogrnr_ISNULL = 1 OR fet.fact_OGrNr in (:user_ogrnr)) and (fet.Fact_OGrNr in (select po.ogrnr from pwogrupp po where po.recht >= 1 and po.name = :INNO_USER))
 
group by
dk.knr,
dk.kennung,
dk.color_html
 
order by
az desc

This SQL query generates the second view, distribution of project hours to identifiers, grouped per month:

EP ProjectAZ grouped by identifiers and months in the time period
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;
 
--Kennungen vorbereiten
IF OBJECT_ID('tempdb..#kennungen', 'U') IS NOT NULL
drop table #kennungen;
create table #kennungen
(
knr int not null,
kennung varchar(12) not null,
color_html varchar(7) not null,
primary key (knr)
)
insert into #kennungen select knr, Kennung, color_html from DIM_KENNUNG where language = @INNO_USER_LANG and typ = 5;
 
--Zeiten vorbereiten
IF OBJECT_ID('tempdb..#zeiten', 'U') IS NOT NULL
drop table #zeiten;
create table #zeiten
(
jahr int not null,
monat int not null,
knr int not null,
Arbeitszeit float not null,
primary key (jahr, monat, knr)
)
insert into #zeiten select fet.jahr, fet.monat, fet.KNR, round(coalesce(sum(fet.Arbeitszeit), 0), 2) from dbo.FACT_EINSAETZE_TAGE fet where fet.datumdt >= @DATE_FROM and fet.datumdt <= @DATE_TO group by fet.Jahr, fet.Monat, fet.KNR;
 
with kennungen_datum as
(
select
dk.knr,
dk.kennung,
dk.color_html,
dd.Jahr,
dd.Monat,
RIGHT('0' + cast(dd.Monat as Varchar), 2) + '.' + cast(dd.Jahr as Varchar) as MJ
 
from
#kennungen dk, dbo.DIM_DATUM dd
where
dd.datumdt >= @DATE_FROM and dd.datumdt <= @DATE_TO
 
group by
dk.knr,
dk.kennung,
dk.color_html,
dd.Jahr,
dd.Monat
 
)
 
select
coalesce(z.Arbeitszeit, 0) AZ,
kd.kennung,
kd.knr,
kd.color_html,
kd.MJ,
kd.Jahr,
kd.Monat
 
from
kennungen_datum kd left outer join #zeiten z on z.Monat = kd.Monat and z.Jahr = kd.Jahr and z.knr = kd.knr
order by kd.mj

The SQL query for the third view, projects in period, looks like this:

EP ProjectAZ in the time period
--Kennungen vorbereiten
IF OBJECT_ID('tempdb..#kennungen', 'U') IS NOT NULL
drop table #kennungen;
create table #kennungen
(
knr int not null,
kennung varchar(12) not null,
color_html varchar(7) not null,
primary key (knr)
)
insert into #kennungen select knr, Kennung, color_html from DIM_KENNUNG where language = :INNO_USER_LANG and typ = 5;
 
 
select
k.projnr,
k.kennwort,
k.AdrName,
k.knr,
k.Vom,
k.Durch,
k.StartSoll,
k.EndeSoll,
k.OGRNR,
min(fet.datumdt) planung_start,
min(fet.datumdt) planung_ende,
coalesce(sum(fet.Arbeitszeit), 0) Arbeitszeit,
dk.kennung,
dk.color_html
 
from
dbo.FACT_EINSAETZE_TAGE fet,
dbo.kunde k,
#kennungen dk
 
where
dk.knr = k.KNr
and k.projnr = fet.projnr
and fet.DatumDT >= :date_from and fet.DatumDT <= :date_to
and (:user_ogrnr_ISNULL = 1 OR fet.Fact_OGrNr in (:user_ogrnr)) and (fet.Fact_OGrNr in (select po.ogrnr from pwogrupp po where po.recht >= 1 and po.name = :INNO_USER))
 
group by
k.projnr,
k.kennwort,
k.AdrName,
k.knr,
k.Vom,
k.Durch,
k.StartSoll,
k.EndeSoll,
k.OGRNR,
dk.kennung,
dk.color_html

To create the interface, the following entry was stored in the "Statistic_UI" table:

INSERT Project evaluation
INSERT INTO STATISTIC_UI (ID, NAME, STATISTIC_CONFIGURATION, STATISTIC_CONTROLLER, CATEGORY_ID, DELETED, DESCRIPTION, IS_INTERNAL, OVERRIDDEN_BY)
VALUES ('{13540FB3-149E-4ED1-86F9-0A7BD09B06A8}3', 'Projektauswertungen', '{
"type": "STATISTIK",
"rows": [
{
"type": "ROW",
"rowItems": [
{
"type": "REACTTABCONTROL",
"width": "12",
"uid": "64dee376-731a-47a1-afa3-4564fa53fa36",
"tabs": [
{
"type": "REACTTAB",
"header": "{{{Statistik.PlanstundenGesamt}}}",
"uid": "0df5b77d-30a5-4263-ae59-6d02d6a248c0",
"content": [
{
"type": "ROW",
"rowItems": [
{
"type": "CHART",
"width": "12",
"uid": "dda0e746-4dfb-43bf-8985-88fabc1bffdf",
"label": "",
"chartSettings": {
"theme": "bootstrap",
"series": [
{
"type": "bar",
"field": "AZ",
"categoryField": "KENNUNG",
"name": "",
"color": "",
"colorField": "COLOR_HTML",
"labels": {
"visible": true
}
}
],
"legend": {
"visible": false,
"position": "bottom"
},
"valueAxis": null
},
"dataUrl": "api/dynamicapi/{205C8FC4-03D9-4C75-9634-CE9A6C9B7E72}13/GetData",
"pageSize": "10",
"titel": "{{{Statistik.VerteilungProjektstundenKennungenZeitraum}}}",
"exportTemplate": "",
"height": "",
"dataSource": {
"dataUrl": "api/dynamicapi/{205C8FC4-03D9-4C75-9634-CE9A6C9B7E72}13/GetData",
"_Object_Type": "sql"
}
}
],
"uid": "ef0bcb21-deb5-4e88-8837-a141d2d71868"
}
],
"name": ""
},
{
"type": "REACTTAB",
"header": "{{{Statistik.PlanstundenMonat}}}",
"uid": "763d127b-25cd-436d-af08-5aa9899fcb44",
"content": [
{
"type": "ROW",
"rowItems": [
{
"type": "TABLECHART",
"width": "12",
"uid": "6ecf3520-5df2-471b-840d-3d667f81c86c",
"titel": "{{{Statistik.MonatsweiseProjektstundenKennungenZeitraum}}}",
"dataUrl": "api/dynamicapi/{205C8FC4-03D9-4C75-9634-CE9A6C9B7E72}14/GetData",
"firstColumnWidth": "150",
"chartSettings": {
"theme": "bootstrap",
"pivotProperties": {
"pivotField": "KNR",
"groupingFields": [
{
"field": "JAHR"
},
{
"field": "MONAT"
}
],
"sortFields": [
{
"field": "MONAT"
},
{
"field": "JAHR"
}
]
},
"autoSeries": [
{
"type": "column",
"style": "",
"field": "AZ*",
"colorField": "COLOR_HTML",
"color": "",
"name": "KENNUNG",
"stack": {
"group": "1"
}
}
],
"seriesDefaults": {
"stack": false,
"_Object_Type": "default"
}
},
"categoryAxis": {
"field": "MJ"
},
"pageSize": "12",
"exportTemplate": "",
"name": "",
"chartHeight": "500"
}
],
"uid": "911cea88-b54c-4b1d-b19d-62beb4d5cdec"
}
],
"name": ""
},
{
"type": "REACTTAB",
"header": "{{{Navigation.Projekte}}}",
"uid": "0763707b-0fe7-48b9-9e52-e272496811db",
"content": [
{
"type": "ROW",
"rowItems": [
{
"type": "TABLE",
"width": "12",
"uid": "0ff567bb-fb8b-4787-b8c5-58f642fdd49c",
"dataUrl": "api/dynamicapi/{205C8FC4-03D9-4C75-9634-CE9A6C9B7E72}12/GetData",
"exportTemplate": "",
"columns": [
{
"header": "Proj.-Nr.",
"columnName": "PROJNR"
},
{
"header": "Projekt",
"columnName": "KENNWORT"
},
{
"header": "Kunde",
"columnName": "ADRNAME"
},
{
"header": "Kennung",
"columnName": "KENNUNG"
},
{
"header": "Start (Planung)",
"columnName": "PLANUNG_START",
"width": "",
"format": "d"
},
{
"header": "Ende (Planung)",
"columnName": "PLANUNG_ENDE",
"format": "d"
},
{
"header": "Stunden",
"columnName": "ARBEITSZEIT",
"width": "80px",
"format": ""
},
{
"header": "Verantwortl.",
"columnName": "DURCH"
}
],
"titel": "{{{Statistik.ProjekteimZeitraum}}}",
"dataSource": {
"dataUrl": "api/dynamicapi/{205C8FC4-03D9-4C75-9634-CE9A6C9B7E72}12/GetData",
"_Object_Type": "sql"
}
}
],
"uid": "cb660e4d-e527-4814-8cb3-b0e5319c37bc"
}
],
"name": ""
}
]
}
],
"uid": "870e5135-7e0a-47ba-8628-060824edf07a"
}
],
"parameters": [
{
"type": "DATE",
"width": "7",
"uid": "b7c394b0-d555-4c50-b8a0-9e51976e6a44",
"name": "date_from",
"label": "{{{Standard.Von}}}"
},
{
"type": "DATE",
"width": "7",
"uid": "39ecc3c1-25a2-4962-a9fb-f77c6be53969",
"name": "date_to",
"label": "{{{Standard.Bis}}}"
},
{
"type": "MULTISELECT",
"width": "12",
"uid": "0223d0de-5aa9-4810-8b43-1313a143affd",
"name": "user_ogrnr",
"label": "{{{Field.Ressource.obergruppe}}}",
"dataUrl": "api/dynamicapi/{205C8FC4-03D9-4C75-9634-CE9A6C9B7E72}1/GetData",
"keyField": "OGRNR",
"valueField": "OBERGRUPPE"
}
],
"settings": {
"type": "SETTINGS",
"disableLoadParamsFromStorage": false
}
}', '
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;
', 0, 0, 'Alle projektbezogenen Planzeiten für den gewählten Zeitraum, aufgeschlüsselt nach Kennung, die Farbe der Kennung entspricht der Balkenfarbe aus der Einsatzplanung.

Filter:

  • Start - Ende

  • Obergruppe (unter Berücksichtigung der Benutzerberechtigung)

', 1, NULL);