Authorization

The user always needs the right to call up the statistics. The corresponding rights must be set for the main groups deposited for the client.

Technical information

The evaluation is prepared by the following SQL query:

FAK Turnover overview
declare @INNO_USER varchar(12) = :INNO_USER;
declare @year int = :year;
 
/*
declare @INNO_USER varchar(12) = 'inno';
declare @INNO_USER_LANG int = 0;
declare @year int = 2006;
*/
 
with umsatz_jahr as
(
select
d.Jahr,
d.Woche,
RIGHT('0' + cast(d.woche as varchar), 2) + '.' + cast(d.jahr as varchar) zeiteinheit,
Round(coalesce(sum(frl.material), 0), 2) material,
Round(coalesce(sum(frl.stunden), 0), 2) stunden,
Round(coalesce(sum(frl.sonstige), 0), 2) sonstige,
Round(coalesce(sum(frl.exnetto), 0), 2) gesamt
from
DIM_DATUM d
left outer join FACT_RGPOS_LA frl on frl.DruckDatum = d.DatumDT AND ((:user_ogrnr_isNull = 1) OR (frl.OGRNR IN (:user_ogrnr)))
where
d.Jahr = @year
 
group by
d.Jahr,
d.Woche
),
umsatz_vorjahr as
(
select
d.Jahr,
d.Woche,
RIGHT('0' + cast(d.woche as varchar), 2) + '.' + cast(d.jahr as varchar) zeiteinheit,
Round(coalesce(sum(frl.material), 0), 2) material,
Round(coalesce(sum(frl.stunden), 0), 2) stunden,
Round(coalesce(sum(frl.sonstige), 0), 2) sonstige,
Round(coalesce(sum(frl.exnetto), 0), 2) gesamt
from
DIM_DATUM d
left outer join FACT_RGPOS_LA frl on frl.DruckDatum = d.DatumDT AND ((:user_ogrnr_isNull = 1) OR (frl.OGRNR IN (:user_ogrnr)))
where
d.Jahr = @year - 1
group by
d.Jahr,
d.Woche
)
 
 
select
j.*, Round(SUM(j.GESAMT) OVER(PARTITION BY j.JAHR ORDER BY j.ZEITEINHEIT), 2) kumuliert,
'Blue' COLOR_KUMULIERT,
'CornflowerBlue' COLOR_MATERIAL,
'LightSkyBlue' COLOR_STUNDEN,
'PowderBlue' COLOR_SONSTIGE
from
umsatz_jahr j
order by
Jahr,
Woche

The interface of the evaluation is stored by the following configuration in the table "Statistic_UI":

INSERT Turnover overview
INSERT INTO STATISTIC_UI (ID, NAME, STATISTIC_CONFIGURATION, STATISTIC_CONTROLLER, CATEGORY_ID, DELETED, DESCRIPTION, IS_INTERNAL, OVERRIDDEN_BY)
VALUES ('{13540FB3-149E-4ED1-86F9-0A7BD09B06A8}7', 'Umsatzübersicht', '{
  "type": "STATISTIK",
  "rows": [
    {
      "type": "ROW",
      "rowItems": [
        {
          "type": "TABLECHART",
          "width": "12",
          "uid": "94a624fb-a960-40db-b266-b86adf10d2cc",
          "titel": "{{{Statistik.Umsatzübersicht}}}",
          "dataUrl": "api/dynamicapi/{205C8FC4-03D9-4C75-9634-CE9A6C9B7E72}47/GetData",
          "chartSettings": {
            "theme": "bootstrap",
            "autoSeries": [
              {
                "type": "line",
                "style": "smooth",
                "field": "KUMULIERT",
                "colorField": "COLOR_KUMULIERT",
                "color": "BLUE",
                "name": "Umsatz"
              },
              {
                "type": "column",
                "style": "step",
                "field": "MATERIAL",
                "colorField": "COLOR_MATERIAL",
                "color": "",
                "name": "Material",
                "stack": {
                  "group": "UMSATZ"
                }
              },
              {
                "type": "column",
                "style": "step",
                "field": "STUNDEN",
                "colorField": "COLOR_STUNDEN",
                "name": "Dienstleistungen",
                "stack": {
                  "group": "UMSATZ"
                }
              },
              {
                "type": "column",
                "style": "step",
                "field": "SONSTIGE",
                "colorField": "COLOR_SONSTIGE",
                "color": "",
                "name": "Sonstige",
                "stack": {
                  "group": "UMSATZ"
                }
              }
            ],
            "tooltip": {
              "visible": true
            },
            "chartArea": {
              "margin": {
                "left": 0
              }
            },
            "valueAxis": {
              "visible": false
            },
            "series": [
              {
                "type": "line",
                "field": "KUMULIERT",
                "colorField": "COLOR_KUMULIERT",
                "color": "BLUE",
                "missingValues": "zero",
                "visibleInLegend": false,
                "name": "Umsatz",
                "style": "smooth"
              },
              {
                "type": "column",
                "field": "MATERIAL",
                "colorField": "COLOR_MATERIAL",
                "color": "",
                "missingValues": "zero",
                "visibleInLegend": false,
                "name": "Material",
                "style": "step",
                "stack": {
                  "group": "UMSATZ"
                }
              },
              {
                "type": "column",
                "field": "STUNDEN",
                "colorField": "COLOR_STUNDEN",
                "missingValues": "zero",
                "visibleInLegend": false,
                "name": "Dienstleistungen",
                "style": "step",
                "stack": {
                  "group": "UMSATZ"
                }
              },
              {
                "type": "column",
                "field": "SONSTIGE",
                "colorField": "COLOR_SONSTIGE",
                "color": "",
                "missingValues": "zero",
                "visibleInLegend": false,
                "name": "Sonstige",
                "style": "step",
                "stack": {
                  "group": "UMSATZ"
                }
              }
            ]
          },
          "categoryAxis": {
            "field": "ZEITEINHEIT"
          },
          "firstColumnWidth": "120",
          "pageSize": "13",
          "exportTemplate": "",
          "chartHeight": "400"
        }
      ],
      "uid": "c12ccd38-3cda-4b6c-948b-d8d206361242"
    }
  ],
  "parameters": [
    {
      "type": "SELECT",
      "width": "6",
      "uid": "18ede7d4-62b2-49dd-9113-2140d61bef0d",
      "name": "year",
      "label": "{{{Statistik.Jahr}}}",
      "dataUrl": "api/dynamicapi/{205C8FC4-03D9-4C75-9634-CE9A6C9B7E72}43/GetData",
      "keyField": "JAHR",
      "valueField": "JAHR"
    },
    {
      "type": "MULTISELECT",
      "width": "12",
      "uid": "7cdc2875-8045-4dd9-827c-95fdbb04362a",
      "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;
', 8, 0, 'Umsatzübersicht für das ausgewählte Jahr pro Kalenderwoche. Der Umsatz wird kumuliert als Kurve dargestellt.
Die einzelnen Wochenumsätze sind als Balkengrafik dargestellt und unterteilen sich in Material, Dienstleistungen und sonstige Positionen.
Die Umsätze werden aus Rechnungen ermittelt, deren Druckdatum im entsprechenden Zeitraum liegt.

Filter:

  • Jahr

  • Obergruppe (unter Berücksichtigung der Benutzerberechtigung) des Auftraggebers

', 1, NULL);