Authorizations

The logged-in user must always have access rights to the called statistics. For more information, see the article Using evaluations.

In addition, when applying the parameters "Group" and "Main Group", it is checked whether the user has at least reading rights to the corresponding group.

Technical information

The following SQL query was used to generate the evaluation per quarter:

EP_Times_Quartal_CodeType
with Fehlzeiten as
(
select
round(sum(coalesce(fet.arbeitszeit, 0)), 0, 0) arbeitszeit,
fet.jahr,
fet.quartal zeiteinheit
from
FACT_EINSAETZE_TAGE fet,
option_k ok
where
fet.DatumDT >= :date_from and fet.DatumDT <= :date_to
and ok.knr = fet.knr
and ok.Language = :INNO_USER_LANG
and ok.typ in (1,2)
and (:user_grnr_ISNULL = 1 OR fet.Fact_GrNr in (:user_grnr)) and (fet.Fact_GrNr in (select pg.grnr from pwgrupp pg where pg.recht >= 3 and pg.name = :INNO_USER))
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 fet.jahr, fet.quartal
), Adresszeiten as
(
select
round(sum(coalesce(fet.arbeitszeit, 0)), 0, 0) arbeitszeit,
fet.jahr,
fet.quartal zeiteinheit
from
FACT_EINSAETZE_TAGE fet,
option_k ok
where
fet.DatumDT >= :date_from and fet.DatumDT <= :date_to
and ok.knr = fet.knr
and ok.Language = :INNO_USER_LANG
and ok.typ in (4)
and (:user_grnr_ISNULL = 1 OR fet.Fact_GrNr in (:user_grnr)) and (fet.Fact_GrNr in (select pg.grnr from pwgrupp pg where pg.recht >= 3 and pg.name = :INNO_USER))
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 fet.jahr, fet.quartal
), Projektzeiten as
(
select
round(sum(coalesce(fet.arbeitszeit, 0)), 0, 0) arbeitszeit,
fet.jahr,
fet.quartal zeiteinheit
from
FACT_EINSAETZE_TAGE fet,
option_k ok
where
fet.DatumDT >= :date_from and fet.DatumDT <= :date_to
and ok.knr = fet.knr
and ok.Language = :INNO_USER_LANG
and ok.typ in (5)
and (:user_grnr_ISNULL = 1 OR fet.Fact_GrNr in (:user_grnr)) and (fet.Fact_GrNr in (select pg.grnr from pwgrupp pg where pg.recht >= 3 and pg.name = :INNO_USER))
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 fet.jahr, fet.quartal
), Internzeiten as
(
select
round(sum(coalesce(fet.arbeitszeit, 0)), 0, 0) arbeitszeit,
fet.jahr,
fet.quartal zeiteinheit
from
FACT_EINSAETZE_TAGE fet,
option_k ok
where
fet.DatumDT >= :date_from and fet.DatumDT <= :date_to
and ok.knr = fet.knr
and ok.Language = :INNO_USER_LANG
and ok.typ in (7)
and (:user_grnr_ISNULL = 1 OR fet.Fact_GrNr in (:user_grnr)) and (fet.Fact_GrNr in (select pg.grnr from pwgrupp pg where pg.recht >= 3 and pg.name = :INNO_USER))
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 fet.jahr, fet.quartal
), datum as
(
select d.jahr, d.quartal zeiteinheit, MIN(d.DatumDt) date_from, max(d.DatumDt) date_to
from dim_datum d
where d.DatumDT >= :date_from and d.DatumDT <= :date_to
group by d.jahr, d.quartal
)
select d.jahr, RIGHT('0' + cast(d.zeiteinheit as varchar), 2) + '.' + cast(d.jahr as varchar) zeiteinheit, d.date_from, d.date_to,
(coalesce(f.arbeitszeit, 0)) as FEHLZEIT,
(coalesce(a.arbeitszeit, 0)) as ADRESSZEIT,
(coalesce(p.arbeitszeit, 0)) as PROJEKTZEIT,
(coalesce(i.arbeitszeit, 0)) as INTERNZEIT
from datum d
left outer join fehlzeiten f on f.jahr = d.jahr and f.zeiteinheit = d.zeiteinheit
left outer join adresszeiten a on a.jahr = d.jahr and a.zeiteinheit = d.zeiteinheit
left outer join projektzeiten p on p.jahr = d.jahr and p.zeiteinheit = d.zeiteinheit
left outer join internzeiten i on i.jahr = d.jahr and i.zeiteinheit = d.zeiteinheit
order by d.jahr, d.zeiteinheit

The following query was used to create the evaluation per month:

EP_Times_Months_CodeType
with Fehlzeiten as
(
select
round(sum(coalesce(fet.arbeitszeit, 0)), 0, 0) arbeitszeit,
fet.jahr,
fet.Monat zeiteinheit
from
FACT_EINSAETZE_TAGE fet,
option_k ok
where
fet.DatumDT >= :date_from and fet.DatumDT <= :date_to
and ok.knr = fet.knr
and ok.Language = :INNO_USER_LANG
and ok.typ in (1,2)
and (:user_grnr_ISNULL = 1 OR fet.Fact_GrNr in (:user_grnr)) and (fet.Fact_GrNr in (select pg.grnr from pwgrupp pg where pg.recht >= 3 and pg.name = :INNO_USER))
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 fet.jahr, fet.Monat
), Adresszeiten as
(
select
round(sum(coalesce(fet.arbeitszeit, 0)), 0, 0) arbeitszeit,
fet.jahr,
fet.Monat zeiteinheit
from
FACT_EINSAETZE_TAGE fet,
option_k ok
where
fet.DatumDT >= :date_from and fet.DatumDT <= :date_to
and ok.knr = fet.knr
and ok.Language = :INNO_USER_LANG
and ok.typ in (4)
and (:user_grnr_ISNULL = 1 OR fet.Fact_GrNr in (:user_grnr)) and (fet.Fact_GrNr in (select pg.grnr from pwgrupp pg where pg.recht >= 3 and pg.name = :INNO_USER))
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 fet.jahr, fet.Monat
), Projektzeiten as
(
select
round(sum(coalesce(fet.arbeitszeit, 0)), 0, 0) arbeitszeit,
fet.jahr,
fet.Monat zeiteinheit
from
FACT_EINSAETZE_TAGE fet,
option_k ok
where
fet.DatumDT >= :date_from and fet.DatumDT <= :date_to
and ok.knr = fet.knr
and ok.Language = :INNO_USER_LANG
and ok.typ in (5)
and (:user_grnr_ISNULL = 1 OR fet.Fact_GrNr in (:user_grnr)) and (fet.Fact_GrNr in (select pg.grnr from pwgrupp pg where pg.recht >= 3 and pg.name = :INNO_USER))
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 fet.jahr, fet.Monat
), Internzeiten as
(
select
round(sum(coalesce(fet.arbeitszeit, 0)), 0, 0) arbeitszeit,
fet.jahr,
fet.Monat zeiteinheit
from
FACT_EINSAETZE_TAGE fet,
option_k ok
where
fet.DatumDT >= :date_from and fet.DatumDT <= :date_to
and ok.knr = fet.knr
and ok.Language = :INNO_USER_LANG
and ok.typ in (7)
and (:user_grnr_ISNULL = 1 OR fet.Fact_GrNr in (:user_grnr)) and (fet.Fact_GrNr in (select pg.grnr from pwgrupp pg where pg.recht >= 3 and pg.name = :INNO_USER))
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 fet.jahr, fet.Monat
), datum as
(
select d.jahr, d.Monat zeiteinheit, MIN(d.DatumDt) date_from, max(d.DatumDt) date_to
from dim_datum d
where d.DatumDT >= :date_from and d.DatumDT <= :date_to
group by d.jahr, d.Monat
)
select d.jahr, RIGHT('0' + cast(d.zeiteinheit as varchar), 2) + '.' + cast(d.jahr as varchar) zeiteinheit, d.date_from, d.date_to,
(coalesce(f.arbeitszeit, 0)) as FEHLZEIT,
(coalesce(a.arbeitszeit, 0)) as ADRESSZEIT,
(coalesce(p.arbeitszeit, 0)) as PROJEKTZEIT,
(coalesce(i.arbeitszeit, 0)) as INTERNZEIT
from datum d
left outer join fehlzeiten f on f.jahr = d.jahr and f.zeiteinheit = d.zeiteinheit
left outer join adresszeiten a on a.jahr = d.jahr and a.zeiteinheit = d.zeiteinheit
left outer join projektzeiten p on p.jahr = d.jahr and p.zeiteinheit = d.zeiteinheit
left outer join internzeiten i on i.jahr = d.jahr and i.zeiteinheit = d.zeiteinheit
order by d.jahr, d.zeiteinheit

The following SQL query was used to create the evaluation per calendar week:

EP_Times_Weeks_CodeType
with Fehlzeiten as
(
select
round(sum(coalesce(fet.arbeitszeit, 0)), 0, 0) arbeitszeit,
fet.jahr,
fet.Woche zeiteinheit
from
FACT_EINSAETZE_TAGE fet,
option_k ok
where
fet.DatumDT >= :date_from and fet.DatumDT <= :date_to
and ok.knr = fet.knr
and ok.Language = :INNO_USER_LANG
and ok.typ in (1,2)
and (:user_grnr_ISNULL = 1 OR fet.Fact_GrNr in (:user_grnr)) and (fet.Fact_GrNr in (select pg.grnr from pwgrupp pg where pg.recht >= 3 and pg.name = :INNO_USER))
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 fet.jahr, fet.Woche
), Adresszeiten as
(
select
round(sum(coalesce(fet.arbeitszeit, 0)), 0, 0) arbeitszeit,
fet.jahr,
fet.Woche zeiteinheit
from
FACT_EINSAETZE_TAGE fet,
option_k ok
where
fet.DatumDT >= :date_from and fet.DatumDT <= :date_to
and ok.knr = fet.knr
and ok.Language = :INNO_USER_LANG
and ok.typ in (4)
and (:user_grnr_ISNULL = 1 OR fet.Fact_GrNr in (:user_grnr)) and (fet.Fact_GrNr in (select pg.grnr from pwgrupp pg where pg.recht >= 3 and pg.name = :INNO_USER))
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 fet.jahr, fet.Woche
), Projektzeiten as
(
select
round(sum(coalesce(fet.arbeitszeit, 0)), 0, 0) arbeitszeit,
fet.jahr,
fet.Woche zeiteinheit
from
FACT_EINSAETZE_TAGE fet,
option_k ok
where
fet.DatumDT >= :date_from and fet.DatumDT <= :date_to
and ok.knr = fet.knr
and ok.Language = :INNO_USER_LANG
and ok.typ in (5)
and (:user_grnr_ISNULL = 1 OR fet.Fact_GrNr in (:user_grnr)) and (fet.Fact_GrNr in (select pg.grnr from pwgrupp pg where pg.recht >= 3 and pg.name = :INNO_USER))
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 fet.jahr, fet.Woche
), Internzeiten as
(
select
round(sum(coalesce(fet.arbeitszeit, 0)), 0, 0) arbeitszeit,
fet.jahr,
fet.Woche zeiteinheit
from
FACT_EINSAETZE_TAGE fet,
option_k ok
where
fet.DatumDT >= :date_from and fet.DatumDT <= :date_to
and ok.knr = fet.knr
and ok.Language = :INNO_USER_LANG
and ok.typ in (7)
and (:user_grnr_ISNULL = 1 OR fet.Fact_GrNr in (:user_grnr)) and (fet.Fact_GrNr in (select pg.grnr from pwgrupp pg where pg.recht >= 3 and pg.name = :INNO_USER))
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 fet.jahr, fet.Woche
), datum as
(
select d.jahr, d.woche zeiteinheit, MIN(d.DatumDt) date_from, max(d.DatumDt) date_to
from dim_datum d
where d.DatumDT >= :date_from and d.DatumDT <= :date_to
group by d.jahr, d.woche
)
select d.jahr, RIGHT('0' + cast(d.zeiteinheit as varchar), 2) + '.' + cast(d.jahr as varchar) zeiteinheit, cast(d.date_from as varchar) + ';' + cast(d.date_to as varchar) zeitraum, d.date_from, d.date_to,
(coalesce(f.arbeitszeit, 0)) as FEHLZEIT,
(coalesce(a.arbeitszeit, 0)) as ADRESSZEIT,
(coalesce(p.arbeitszeit, 0)) as PROJEKTZEIT,
(coalesce(i.arbeitszeit, 0)) as INTERNZEIT
from datum d
left outer join fehlzeiten f on f.jahr = d.jahr and f.zeiteinheit = d.zeiteinheit
left outer join adresszeiten a on a.jahr = d.jahr and a.zeiteinheit = d.zeiteinheit
left outer join projektzeiten p on p.jahr = d.jahr and p.zeiteinheit = d.zeiteinheit
left outer join internzeiten i on i.jahr = d.jahr and i.zeiteinheit = d.zeiteinheit
order by d.jahr, d.zeiteinheit

To create the interface, the following configuration was entered in the table "Statistic_UI":

INSERT Script Analysis Assignment Times
INSERT INTO STATISTIC_UI (ID, NAME, STATISTIC_CONFIGURATION, STATISTIC_CONTROLLER, CATEGORY_ID, DELETED, DESCRIPTION, IS_INTERNAL, OVERRIDDEN_BY)
VALUES ('{13540FB3-149E-4ED1-86F9-0A7BD09B06A8}2', 'Analyse Einsatzzeiten', '{
"type": "STATISTIK",
"rows": [
{
"type": "ROW",
"rowItems": [
{
"type": "REACTTABCONTROL",
"width": "12",
"uid": "185d7bcf-6034-49bd-bdbe-f38ccbfd5a09",
"tabs": [
{
"type": "REACTTAB",
"header": "{{{Standard.Woche}}}",
"uid": "ede826cc-e66f-49b9-a1c2-ced9e86b0386",
"content": [
{
"type": "ROW",
"rowItems": [
{
"type": "CHART",
"width": "12",
"uid": "d1c88382-bcc8-43c9-8e53-2ca40f653350",
"titel": "{{{Statistik.PlanzeitenKalenderwoche}}}",
"label": "",
"name": "",
"dataUrl": "api/dynamicapi/{205C8FC4-03D9-4C75-9634-CE9A6C9B7E72}3/GetData",
"exportTemplate": "",
"chartSettings": {
"theme": "bootstrap",
"series": [
{
"type": "column",
"field": "FEHLZEIT",
"categoryField": "",
"name": "{{{Standard.Fehlzeit}}}",
"colorField": "",
"color": "Crimson"
},
{
"type": "column",
"field": "ADRESSZEIT",
"categoryField": "",
"name": "{{{Adresse.Adressen}}}",
"color": "RoyalBlue",
"colorField": ""
},
{
"type": "column",
"field": "PROJEKTZEIT",
"name": "{{{Navigation.Projekte}}}",
"color": "LimeGreen",
"colorField": "",
"categoryField": ""
},
{
"type": "column",
"field": "INTERNZEIT",
"name": "{{{WebSMS.ResourcePlanning.KennungIntern}}}",
"color": "Gold"
}
],
"categoryAxis": {
"field": "ZEITEINHEIT",
"labels": {
"rotation": "60",
"format": ""
}
},
"legend": {
"visible": true,
"position": "bottom"
},
"tooltip": {
"visible": true
},
"valueAxis": null
},
"pageSize": "20",
"drilldown": {
"statisticId": "{13540FB3-149E-4ED1-86F9-0A7BD09B06A8}10",
"destField": "",
"sourceField": "",
"hideInputControls": "false"
},
"height": "600px"
}
],
"uid": "9fa537d2-32d6-4651-bf01-efbf21e3aed4"
}
],
"name": "tab_week"
},
{
"type": "REACTTAB",
"header": "{{{Standard.Monat}}}",
"uid": "c54dc062-dc41-4abe-b50a-6efd56e571bb",
"content": [
{
"type": "ROW",
"rowItems": [
{
"type": "CHART",
"width": "12",
"uid": "49514dd8-61a9-4b89-9ea0-6ac058ac2750",
"titel": "{{{Statistik.PlanzeitenMonat}}}",
"label": "",
"name": "",
"dataUrl": "api/dynamicapi/{205C8FC4-03D9-4C75-9634-CE9A6C9B7E72}4/GetData",
"exportTemplate": "",
"chartSettings": {
"theme": "bootstrap",
"series": [
{
"type": "column",
"field": "FEHLZEIT",
"categoryField": "",
"name": "{{{Standard.Fehlzeit}}}",
"colorField": "",
"color": "Crimson"
},
{
"type": "column",
"field": "ADRESSZEIT",
"categoryField": "",
"name": "{{{Adresse.Adressen}}}",
"color": "RoyalBlue",
"colorField": ""
},
{
"type": "column",
"field": "PROJEKTZEIT",
"name": "{{{Navigation.Projekte}}}",
"color": "LimeGreen",
"colorField": ""
},
{
"type": "column",
"field": "INTERNZEIT",
"name": "{{{WebSMS.ResourcePlanning.KennungIntern}}}",
"color": "Gold"
}
],
"categoryAxis": {
"field": "ZEITEINHEIT",
"labels": {
"rotation": "60",
"format": ""
}
},
"legend": {
"visible": true,
"position": "bottom"
},
"tooltip": {
"visible": true
}
},
"pageSize": "20",
"drilldown": {
"statisticId": "{13540FB3-149E-4ED1-86F9-0A7BD09B06A8}10",
"hideInputControls": "false"
},
"height": "600px"
}
],
"uid": "f274cb2c-7981-4957-b4d8-8fb70abfec5c"
}
]
},
{
"type": "REACTTAB",
"header": "{{{Standard.Quartale}}}",
"uid": "62b8b9a1-c0ed-42fd-9c9a-b80d355f9b1c",
"content": [
{
"type": "ROW",
"rowItems": [
{
"type": "CHART",
"width": "12",
"uid": "775f6323-9615-4aa0-9fd2-6e8ed9f52143",
"titel": "{{{Statistik.PlanzeitenQuartal}}}",
"label": "",
"name": "",
"dataUrl": "api/dynamicapi/{205C8FC4-03D9-4C75-9634-CE9A6C9B7E72}5/GetData",
"exportTemplate": "",
"chartSettings": {
"theme": "bootstrap",
"series": [
{
"type": "column",
"field": "FEHLZEIT",
"categoryField": "",
"name": "{{{Standard.Fehlzeit}}}",
"colorField": "",
"color": "Crimson"
},
{
"type": "column",
"field": "ADRESSZEIT",
"categoryField": "",
"name": "{{{Adresse.Adressen}}}",
"color": "RoyalBlue",
"colorField": ""
},
{
"type": "column",
"field": "PROJEKTZEIT",
"name": "{{{Navigation.Projekte}}}",
"color": "LimeGreen",
"colorField": ""
},
{
"type": "column",
"field": "INTERNZEIT",
"name": "{{{WebSMS.ResourcePlanning.KennungIntern}}}",
"color": "Gold"
}
],
"categoryAxis": {
"field": "ZEITEINHEIT",
"labels": {
"rotation": "60",
"format": ""
}
},
"legend": {
"visible": true,
"position": "bottom"
},
"tooltip": {
"visible": true
}
},
"pageSize": "20",
"drilldown": {
"statisticId": "{13540FB3-149E-4ED1-86F9-0A7BD09B06A8}10",
"hideInputControls": "false"
},
"height": "600px"
}
],
"uid": "bf27b2d0-5e5a-428c-a9a0-a7d9cdb4a130"
}
],
"name": ""
}
],
"selectedIndex": ""
}
],
"uid": "a4293711-ac3d-4aee-ab7a-cb2c57435104"
}
],
"parameters": [
{
"type": "DATE",
"width": "7",
"uid": "5a01c52c-2004-4a47-a03c-d5b90990e810",
"name": "date_from",
"label": "{{{Standard.Von}}}"
},
{
"type": "DATE",
"width": "7",
"uid": "7df40333-d62d-4eb0-8467-868a843a4d3f",
"name": "date_to",
"label": "{{{Standard.Bis}}}"
},
{
"type": "MULTISELECT",
"width": "12",
"uid": "79e7410f-8079-4ea0-af5d-4c4436f73feb",
"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": "cfaee7ad-a712-49f5-8805-23d0bde3e3df",
"label": "{{{Statistik.Obergruppe}}}",
"name": "user_ogrnr",
"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;
', 0, 0, '

Alle Planzeiten für die Zeitabschnitte Quartal, Monat, Woche, unterschieden nach den Kennungstypen:

  • Fehlzeiten

  • interne Zeiten

  • adressbezogene Zeiten

  • Projektzeiten

Dargestellt als Balkengrafik, pro Kennungstyp ein Balken in der Periode des gewählten Zeitabschnitts. Per Drilldown über den Kennungstyp-Balken wird die gleiche Statistisk für den jeweiligen Kennungstyp über die enthaltenen Kennungen ausgeführt.

Filter:

  • Start - Ende

  • Gruppe (unter Berücksichtigung der Benutzerberechtigung)

  • Obergruppe (unter Berücksichtigung der Benutzerberechtigung)

', 1, NULL);

The SQL query for the drill-down view "Percentage distribution of scheduled assignment times by code type" looks like this:

Percentage distribution of scheduled assignment times by code type
declare @INNO_USER_LANG int = :INNO_USER_LANG;
declare @INNO_USER varchar(12) = :INNO_USER;
declare @DATE_FROM date = :date_from_10;
declare @DATE_TO date = :date_to_10;
with zeiten as
(
select round(sum(coalesce(arbeitszeit, 0)), 0) as stunden_plan,
0 MIN, 100 MAX,
ok.typ,
case
when ok.Typ = 1 then 'Urlaub'
when ok.Typ = 2 then 'Fehlzeit'
when ok.Typ = 3 then 'Unbekannt'
when ok.Typ = 4 then 'Adressbezogen'
when ok.Typ = 5 then 'Projektbezogen'
when ok.Typ = 6 then 'Wartung'
when ok.Typ = 7 then 'Interne Zeiten'
end as typ_str,
case
when ok.Typ = 1 then 'LightCoral'
when ok.Typ = 2 then 'IndianRed'
when ok.Typ = 3 then ''
when ok.Typ = 4 then 'RoyalBlue'
when ok.Typ = 5 then 'LimeGreen'
when ok.Typ = 6 then 'Lime'
when ok.Typ = 7 then 'Gold'
end as typ_color,
@DATE_FROM as DATE_FROM,
@DATE_TO as DATE_TO
from
FACT_EINSAETZE_TAGE fet, option_k ok
where
fet.DatumDT >= @DATE_FROM
and fet.DatumDT <= @DATE_TO
and ok.knr = fet.KNR
and ok.knr = fet.KNR and ok.Language = @INNO_USER_LANG
and (:user_grnr_ISNULL = 1 OR fet.Fact_GrNr in (:user_grnr)) and (fet.Fact_GrNr in (select pg.grnr from pwgrupp pg where pg.recht >= 3 and pg.name = @INNO_USER))
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
ok.typ
), sum_zeiten as
(
select sum(stunden_plan) Gesamt from zeiten
)
select
sz.Gesamt,
0 MIN,
100 MAX,
z1.stunden_plan, round(z1.stunden_plan / (sz.Gesamt / 100), 2) as PROZ,
z1.typ_str,
z1.typ_color,
z1.Typ,
DATE_FROM,
DATE_TO
from
zeiten z1,
sum_zeiten sz
where z1.stunden_plan > 0
order by
z1.stunden_plan desc

The interface of the drilldown view is saved with the following configuration in the table "Statistic_UI":

INSERT Percentage distribution of scheduled assignment times by code type
INSERT INTO STATISTIC_UI (ID, NAME, STATISTIC_CONFIGURATION, STATISTIC_CONTROLLER, CATEGORY_ID, DELETED, DESCRIPTION, IS_INTERNAL, OVERRIDDEN_BY)
VALUES ('{13540FB3-149E-4ED1-86F9-0A7BD09B06A8}10', 'Prozentuale Verteilung der verplanten Einsatzzeiten nach Kennungstyp', '{
  "type": "STATISTIK",
  "rows": [
    {
      "type": "ROW",
      "rowItems": [
        {
          "type": "CHART",
          "width": "6",
          "titel": "",
          "dataUrl": "api/dynamicapi/{205C8FC4-03D9-4C75-9634-CE9A6C9B7E72}7/GetData",
          "chartSettings": {
            "theme": "bootstrap",
            "categoryAxis": {
              "field": ""
            },
            "series": [
              {
                "type": "pie",
                "field": "STUNDEN_PLAN",
                "colorField": "TYP_COLOR",
                "name": "",
                "color": "",
                "categoryField": "TYP_STR",
                "labels": {
                  "visible": true
                }
              }
            ],
            "seriesDefaults": {
              "labels": {
                "visible": true
              }
            },
            "tooltip": {
              "visible": false
            },
            "legend": {
              "position": "left"
            },
            "valueAxis": null
          },
          "drilldown": {
            "statisticId": "{13540FB3-149E-4ED1-86F9-0A7BD09B06A8}11",
            "sourceField": "",
            "hideInputControls": "false",
            "destField": ""
          },
          "uid": "d75f2a0b-2af5-4088-abef-dbe1fb2c70a6",
          "label": ""
        },
        {
          "type": "BULLETCHART",
          "width": "6",
          "uid": "7bf63ae2-8a04-427d-8e8d-46ddcab9998e",
          "dataUrl": "api/dynamicapi/{205C8FC4-03D9-4C75-9634-CE9A6C9B7E72}7/GetData",
          "minField": "MIN",
          "maxField": "MAX",
          "currentValueField": "PROZ",
          "descriptionField": "TYP_STR",
          "color": "",
          "pageSize": "",
          "titel": "",
          "plotBands": [
            {
              "from": "MIN",
              "to": "MAX",
              "color": "TYP_COLOR",
              "opacity": ""
            }
          ]
        }
      ],
      "uid": "c05e8468-af99-4e38-bef8-8fcd9bfb8664"
    },
    {
      "type": "ROW",
      "rowItems": [
        {
          "type": "TABLE",
          "width": "12",
          "exportUrl": "api/dynamicapi/7/GetExportFile",
          "dataUrl": "api/dynamicapi/{205C8FC4-03D9-4C75-9634-CE9A6C9B7E72}7/GetData",
          "columns": [
            {
              "header": "{{{WebSMS.Reminder.Fields.Type}}}",
              "columnName": "TYP_STR",
              "template": "",
              "align": "left"
            },
            {
              "header": "{{{WebSMS.ServiceReport.Stunden}}}",
              "columnName": "STUNDEN_PLAN",
              "template": "",
              "align": "right"
            },
            {
              "header": "{{{Statistik.Prozent}}}",
              "columnName": "PROZ",
              "align": "right"
            }
          ],
          "uid": "829e8ac7-40a4-4d17-a2d2-d713de16c1b8",
          "dataSource": {
            "dataUrl": "api/dynamicapi/{205C8FC4-03D9-4C75-9634-CE9A6C9B7E72}7/GetData",
            "_Object_Type": "sql"
          }
        }
      ],
      "uid": "a220a56a-4cd0-4b8e-8f60-10cdd1957a2f"
    }
  ],
  "parameters": [
    {
      "type": "DATE",
      "width": "7",
      "uid": "7a58bb61-8cfa-4330-b54f-515e4343a437",
      "label": "{{{Standard.Von}}}",
      "name": "date_from_10"
    },
    {
      "type": "DATE",
      "width": "7",
      "uid": "abf54362-5961-4983-92d4-67b7d0918977",
      "name": "date_to_10",
      "label": "{{{Standard.Bis}}}"
    },
    {
      "type": "MULTISELECT",
      "width": "12",
      "name": "user_grnr",
      "label": "{{{Field.Ressource.gruppe}}}",
      "dataUrl": "api/dynamicapi/{205C8FC4-03D9-4C75-9634-CE9A6C9B7E72}2/GetData",
      "keyField": "GRNR",
      "valueField": "GRUPPE",
      "uid": "ad50a613-9783-4455-befe-0dd0bd2e6bce"
    },
    {
      "type": "MULTISELECT",
      "width": "12",
      "name": "user_ogrnr",
      "label": "{{{Statistik.Obergruppe}}}",
      "keyField": "OGRNR",
      "valueField": "OBERGRUPPE",
      "dataUrl": "api/dynamicapi/{205C8FC4-03D9-4C75-9634-CE9A6C9B7E72}1/GetData",
      "uid": "e52ef744-2f1d-4b7a-a22f-a623456a9b43"
    }
  ],
  "settings": {
    "type": "SETTINGS"
  }
}', '
var controller={};
 
controller.getInitialValues = function(paramsFromStorage, params, drilldownparams){
     
debugger;
     
  if ((paramsFromStorage)  && (params)) {
       
    var result = {};
       
    result.date_from_10 = drilldownparams.DATE_FROM;
    result.date_to_10 = drilldownparams.DATE_TO;
    result.user_grnr = paramsFromStorage.user_grnr;
    result.user_ogrnr = paramsFromStorage.user_ogrnr;
 
    return result;
       
  }
 
  var Jahr = new Date().getFullYear();
  return {
        date_from: ''01.01.'' + Jahr,
        date_to: ''31.12.'' + Jahr
    };
     
}
 
 
controller;
', NULL, 0, '', 1, NULL);

The SQL query for the second drilldown view "Percentage distribution of scheduled assignment times by code" looks like this:

Percentage distribution of scheduled assignment times per code
declare @INNO_USER_LANG int = :INNO_USER_LANG;
declare @INNO_USER varchar(12) = :INNO_USER;
declare @DATE_FROM date = :date_from_11;
declare @DATE_TO date = :date_to_11;
declare @KNRTYP int = :knrtyp;
with zeiten as
(
select round(sum(coalesce(arbeitszeit, 0)), 0) as stunden_plan,
0 MIN, 100 MAX, ok.knr, ok.kennung, ok.color_html
from
FACT_EINSAETZE_TAGE fet, DIM_KENNUNG ok
where
fet.DatumDT >= @DATE_FROM
and fet.DatumDT <= @DATE_TO
and ok.knr = fet.KNR
and ok.Language = @INNO_USER_LANG
and ok.Typ = @KNRTYP
and (:user_grnr_ISNULL = 1 OR fet.Fact_GrNr in (:user_grnr))
and (fet.Fact_GrNr in (select pg.grnr from pwgrupp pg where pg.recht >= 3 and pg.name = @INNO_USER))
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
ok.knr,
ok.kennung,
ok.color_html
)
, sum_zeiten as
(
select sum(stunden_plan) Gesamt from zeiten
)
select
sz.Gesamt,
0 MIN,
100 MAX,
z1.stunden_plan, round(z1.stunden_plan / (sz.Gesamt / 100), 2) as PROZ,
z1.knr,
z1.kennung,
z1.color_html
from
zeiten z1,
sum_zeiten sz
where z1.stunden_plan > 0
order by
z1.stunden_plan desc

The interface of the second drilldown view is stored in the Statistic_UI table with the following configuration:

INSERT Percentage distribution of scheduled assignment times by code
INSERT INTO STATISTIC_UI (ID, NAME, STATISTIC_CONFIGURATION, STATISTIC_CONTROLLER, CATEGORY_ID, DELETED, DESCRIPTION, IS_INTERNAL, OVERRIDDEN_BY)
VALUES ('{13540FB3-149E-4ED1-86F9-0A7BD09B06A8}11', 'Prozentuale Verteilung der verplanten Einsatzzeiten nach Kennung', '{
  "type": "STATISTIK",
  "rows": [
    {
      "type": "ROW",
      "rowItems": [
        {
          "type": "CHART",
          "width": "6",
          "titel": "",
          "dataUrl": "api/dynamicapi/{205C8FC4-03D9-4C75-9634-CE9A6C9B7E72}8/GetData",
          "chartSettings": {
            "theme": "bootstrap",
            "categoryAxis": {
              "field": ""
            },
            "series": [
              {
                "type": "pie",
                "field": "PROZ",
                "colorField": "COLOR_HTML",
                "name": "",
                "color": "",
                "categoryField": "KENNUNG"
              }
            ],
            "seriesDefaults": {
              "labels": {
                "visible": true
              }
            },
            "tooltip": {
              "visible": true
            },
            "legend": {
              "position": "left"
            }
          },
          "pageSize": "0",
          "drilldown": {
            "statisticId": "",
            "destField": "",
            "hideInputControls": ""
          },
          "uid": "9d1995c6-3375-45db-946f-310c2056673e"
        },
        {
          "type": "BULLETCHART",
          "width": "6",
          "minField": "MIN",
          "dataUrl": "api/dynamicapi/{205C8FC4-03D9-4C75-9634-CE9A6C9B7E72}8/GetData",
          "maxField": "MAX",
          "currentValueField": "PROZ",
          "descriptionField": "KENNUNG",
          "pageSize": "6",
          "titel": "",
          "uid": "39e5a571-1dca-4697-a992-12b139642232"
        }
      ],
      "uid": "8ebaac73-5609-4fe8-b470-6179dc9848f9"
    },
    {
      "type": "ROW",
      "rowItems": [
        {
          "type": "TABLE",
          "width": "12",
          "exportUrl": "api/dynamicapi/15/GetExportFile",
          "dataUrl": "api/dynamicapi/{205C8FC4-03D9-4C75-9634-CE9A6C9B7E72}8/GetData",
          "columns": [
            {
              "header": "{{{Standard.Typ}}}",
              "columnName": "KENNUNG",
              "template": ""
            },
            {
              "header": "{{{Field.BelegAusgeführteArbeiten.stunden}}}",
              "columnName": "STUNDEN_PLAN",
              "template": ""
            },
            {
              "header": "{{{Statistik.Prozent}}}",
              "columnName": "PROZ",
              "template": ""
            }
          ],
          "template": [
            {
              "type": "ROW",
              "rowItems": [
                {
                  "type": "REACTTABCONTROL",
                  "width": "12",
                  "tabs": [
                    {
                      "type": "REACTTAB",
                      "header": "tab",
                      "content": []
                    }
                  ],
                  "selectedIndex": ""
                }
              ]
            }
          ],
          "rows": [
            {
              "type": "ROW",
              "rowItems": [
                {
                  "type": "REACTTABCONTROL",
                  "width": "12",
                  "tabs": [
                    {
                      "type": "REACTTAB",
                      "header": "tab",
                      "content": [
                        {
                          "type": "ROW",
                          "rowItems": [
                            {
                              "type": "TABLE",
                              "width": "12",
                              "rows": [],
                              "dataUrl": "api/dynamicapi/{205C8FC4-03D9-4C75-9634-CE9A6C9B7E72}1/GetData",
                              "columns": [
                                {
                                  "header": "GRNR",
                                  "columnName": "GRNR",
                                  "template": ""
                                }
                              ]
                            }
                          ]
                        }
                      ]
                    },
                    {
                      "type": "REACTTAB",
                      "header": "tab2",
                      "content": []
                    }
                  ],
                  "selectedIndex": ""
                }
              ]
            }
          ],
          "uid": "e58c2909-3ab5-4ece-b0b1-632485ef4e42"
        }
      ],
      "uid": "be0e33c6-f168-42c9-98b7-1e3494d92b7c"
    }
  ],
  "parameters": [
    {
      "type": "DATE",
      "width": "6",
      "name": "date_from_11",
      "label": "{{{Standard.Von}}}",
      "uid": "796890aa-a7b5-4a98-b5ff-c81cfe93311f"
    },
    {
      "type": "DATE",
      "width": "6",
      "name": "date_to_11",
      "label": "{{{Standard.Bis}}}",
      "uid": "32e0b343-61e8-49af-8015-79eee3821ff4"
    },
    {
      "type": "MULTISELECT",
      "width": "12",
      "name": "user_grnr",
      "label": "{{{Field.Ressource.gruppe}}}",
      "dataUrl": "api/dynamicapi/{205C8FC4-03D9-4C75-9634-CE9A6C9B7E72}2/GetData",
      "keyField": "GRNR",
      "valueField": "GRUPPE",
      "uid": "1bf795ac-5e6e-441b-87f2-acdeb727062d"
    },
    {
      "type": "MULTISELECT",
      "width": "12",
      "name": "user_ogrnr",
      "label": "{{{Statistik.Obergruppe}}}",
      "keyField": "OGRNR",
      "valueField": "OBERGRUPPE",
      "dataUrl": "api/dynamicapi/{205C8FC4-03D9-4C75-9634-CE9A6C9B7E72}1/GetData",
      "uid": "8af72abf-de86-48d8-bab1-110f5be44aba"
    },
    {
      "type": "MULTISELECT",
      "width": "12",
      "name": "knrtyp",
      "label": "{{{Statistik.Kennungstyp}}}",
      "dataUrl": "api/dynamicapi/{205C8FC4-03D9-4C75-9634-CE9A6C9B7E72}28/GetData",
      "keyField": "TYP",
      "valueField": "BEZEICHNUNG",
      "uid": "821cc28e-c487-41c1-9d84-9751d99a4bba"
    }
  ],
  "settings": {
    "type": "SETTINGS"
  }
}', '
var controller={};
 
controller.getInitialValues = function(paramsFromStorage, params, drilldownparams){
     
debugger;
     
  if ((paramsFromStorage)  && (params)) {
       
    var result = {};
       
    result.date_from_11 = drilldownparams.DATE_FROM;
    result.date_to_11 = drilldownparams.DATE_TO;
    result.user_grnr = paramsFromStorage.user_grnr;
    result.user_ogrnr = paramsFromStorage.user_ogrnr;
    result.knrtyp = drilldownparams.TYP;
 
    return result;
       
  }
 
  var Jahr = new Date().getFullYear();
  return {
        date_from: ''01.01.'' + Jahr,
        date_to: ''31.12.'' + Jahr
    };
     
}
 
 
controller;
', NULL, 0, '', NULL, NULL);