Authorization

The user needs at least the right to view the statistics . In addition, the main group rights are observed for customers with a stored main group.

Technical information

The "Sales per customer and code" report prepares data with the following SQL query:

Sales per customer and code
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.2000';
declare @date_to date = '01.01.2010';
*/
with umsatz as
(
SELECT
VKUNADR.ADRNR,
VKUNADR.ADRNAME,
BFKOPF.PROJNR,
SUM(NETTO) AS UMSATZ,
OPTION_K.KENNUNG AS KENNUNG,
OPTION_K.KNR AS KNR,
VKUNADR.OGRNR,
(SELECT
COALESCE(SUM(BF1.NETTO), 0)
FROM
BFKOPF BF1
WHERE
BF1.ProjNr = BFKOPF.ProjNr
AND BF1.ERSTDATUM >= @DATE_FROM
AND BF1.ERSTDATUM <= DATEADD(day, 1, @DATE_TO)
AND BF1.ART = 'GM'
AND NOT coalesce(BF1.RECHNUNGSART, 0) IN (4,5)
)
AS SUMME_GUTSCHRIFTEN,
COALESCE(SUM(NETTO),0)
-
(SELECT
COALESCE(SUM(BF1.NETTO),0)
FROM
BFKOPF BF1
WHERE
BF1.ProjNr = BFKOPF.ProjNr
AND BF1.ERSTDATUM >= @DATE_FROM
AND BF1.ERSTDATUM <= DATEADD(day, 1, @DATE_TO)
AND BF1.ART = 'GM'
AND NOT coalesce(BF1.RECHNUNGSART, 0) IN (4,5)
)
AS SUMME_GESAMT
FROM
BFKOPF
JOiN VKUNADR ON VKUNADR.ADRNR = BFKOPF.DEBITNR
LEFT JOIN KUNDE ON KUNDE.PROJNR = BFKOPF.PROJNR
LEFT JOIN OPTION_K ON (OPTION_K.KNR = KUNDE.KNR AND OPTION_K.LANGUAGE = @INNO_USER_LANG)
 
 
WHERE
BFKOPF.ERSTDATUM >= @DATE_FROM
AND BFKOPF.ERSTDATUM <= DATEADD(day, 1,@DATE_TO)
AND BFKOPF.ART = 'AM'
AND NOT coalesce(BFKOPF.RECHNUNGSART, 0) IN (4,5)
and (VKUNADR.OGRNR IN (SELECT OGRNR FROM PWOGRUPP WHERE NAME = @INNO_USER AND RECHT >= 1))
AND ((:user_ogrnr_isNull = 1) OR (VKUNADR.OGRNR IN (:user_ogrnr)))
 
 
GROUP BY
VKUNADR.ADRNR,
VKUNADR.ADRNAME,
BFKOPF.PROJNR,
OPTION_K.KENNUNG,
OPTION_K.KNR,
VKUNADR.OGRNR
)
select
u.adrnr,
u.AdrName,
Round(sum(u.Umsatz), 2) SUMME_RNG,
u.kennung,
u.knr,
u.AdrName + '_' + cast(u.knr as varchar) ordernr,
Round(sum(u.SUMME_GUTSCHRIFTEN), 2) SUMME_GUTSCHRIFTEN,
Round(sum(u.SUMME_GESAMT), 2) SUMME_GESAMT
from
umsatz u
group by
u.adrnr,
u.AdrName,
u.kennung,
u.knr
 
union all
 
select
u.adrnr,
u.AdrName,
Round(sum(u.Umsatz), 2) SUMME_RNG,
'' kennung,
0 knr,
u.AdrName + '_999' ordernr,
Round(sum(u.SUMME_GUTSCHRIFTEN), 2) SUMME_GUTSCHRIFTEN,
Round(sum(u.SUMME_GESAMT), 2) SUMME_GESAMT
from
umsatz u
group by
u.adrnr,
u.AdrName
 
ORDER BY
ordernr

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

INSERT Sales per customer and 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}13', 'Umsatz pro Kunde und Kennung', '{
"type": "STATISTIK",
"rows": [
{
"type": "ROW",
"rowItems": [
{
"type": "TABLE",
"width": "12",
"name": "",
"dataUrl": "api/dynamicapi/{205C8FC4-03D9-4C75-9634-CE9A6C9B7E72}17/GetData",
"columns": [
{
"header": "{{{Statistik.Kundennummer}}}",
"columnName": "ADRNR",
"template": "# //style=''font-weight:bold; background-color:red''\n\ts = \"<div>\" +\n\t \"<a href=\"+context.INNO_BASE_PATH +\"Statistic/StatisticWebPage\\\\#/statistic/{13540FB3-149E-4ED1-86F9-0A7BD09B06A8}21?ADRNR=\"+row.ADRNR +\"&_drilldown=true\\>\"+row.ADRNR+\"</a>\" +\n\t \"</div>\";\n\treturn s;\n\t\t\n#\n",
"width": "115px"
},
{
"header": "{{{Standard.Name}}}",
"columnName": "ORDERNR",
"template": "# \nreturn row.ADRNAME; \n#\n",
"width": ""
},
{
"header": "{{{Field.Einsatz.kennung}}}",
"columnName": "ORDERNR",
"template": "# return row.KENNUNG; #\n",
"width": "150px"
},
{
"header": "{{{Statistik.SummeRechnungen}}}",
"columnName": "ORDERNR",
"template": "#\n\nvar value = row.SUMME_RNG;\nvar div;\nvar knr = row.KNR;\nvar f_color = ''black;'';\n\nif (value < 0)\n f_color = ''red;'';\n\nif (knr == ''0'')\n div = ''<div style=\"color:'' + f_color + ''font-weight:bold;font-size:100%;\">'';\nelse\n div = ''<div style=\"color:'' + f_color + ''\">'';\n \nreturn div + value.toFixed(2).toString().replace(/\\B(?=(\\d{3})+(?!\\d))/g, \",\") + ''</div>''; \n\n#",
"align": "right",
"width": "122px",
"format": "#.00"
},
{
"header": "{{{Statistik.SummeGutschriften}}}",
"columnName": "ORDERNR",
"template": "\n# \n\nvar value = row.SUMME_GUTSCHRIFTEN;\nvar div;\nvar knr = row.KNR;\nvar f_color = ''black;'';\n\nif (value > 0)\n f_color = ''red;'';\n\nif (knr == ''0'')\n div = ''<div style=\"color:'' + f_color + ''font-weight:bold;font-size:100%;\">'';\nelse\n div = ''<div style=\"color:'' + f_color + ''\">'';\n \nreturn div + value.toFixed(2).toString().replace(/\\B(?=(\\d{3})+(?!\\d))/g, \",\") + ''</div>''; \n\n#",
"align": "right",
"width": "122px",
"format": "#.00"
},
{
"header": "{{{Statistik.Umsatz}}}",
"columnName": "ORDERNR",
"template": "# \n\nvar value = row.SUMME_GESAMT;\nvar div;\nvar knr = row.KNR;\nvar f_color = ''black;'';\n\nif (value < 0)\n f_color = ''red;'';\n\nif (knr == ''0'')\n div = ''<div style=\"color:'' + f_color + ''font-weight:bold;font-size:115%;\">'';\nelse\n div = ''<div style=\"color:'' + f_color + ''\">'';\n \nreturn div + value.toFixed(2).toString().replace(/\\B(?=(\\d{3})+(?!\\d))/g, \",\") + ''</div>''; \n\n#",
"align": "right",
"width": "115px",
"format": "#0.00"
}
],
"uid": "068a03e4-e109-4bd3-86ec-f62aaa8c5441",
"titel": "Umsatz pro Kunde und Kennung",
"exportTemplate": "",
"dataSource": {
"dataUrl": "api/dynamicapi/{205C8FC4-03D9-4C75-9634-CE9A6C9B7E72}17/GetData",
"_Object_Type": "sql"
}
}
],
"uid": "4b873895-75f3-427a-9ff2-8a6e1321c0f8"
}
],
"parameters": [
{
"type": "DATE",
"width": "7",
"name": "date_from",
"label": "{{{Standard.Von}}}",
"uid": "9013e5fa-7df2-418e-a4dc-3a4a41f4d7f8"
},
{
"type": "DATE",
"width": "7",
"name": "date_to",
"label": "{{{Standard.Bis}}}",
"uid": "a9036f04-a4c6-4e08-a3f8-bc0e691f8dde"
},
{
"type": "MULTISELECT",
"width": "12",
"uid": "6efd5fd6-1b79-459f-b7b6-913d99238bf1",
"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, '<h4>Umsatz pro Kunde unterteilt nach Kennung und Gesamtumsatz pro Kunde.</h4>
 
Per Klick auf die Kundennummer wird auf die kundenspezifische quartalsweise Umsatzübersicht im Zeitraum verwiesen.
<br><br>
<h4>Filter:</h4>
<ul>
<li>Von - Bis
<li>Obergruppe (unter Berücksichtigung der Benutzerberechtigung) des Auftraggebers
</ul>', 1, NULL);

The SQL query for the drilldown evaluation "Sales per customer per quarter" looks like this:

Sales per customer per quarter
WITH
RECHNUNGEN As
(
SELECT DEBITNR,NETTO,DATEPART(year,ERSTDATUM) AS JAHR, DATEPART(quarter,ERSTDATUM) AS QUARTAL
FROM BFKOPF
WHERE ART IN ('AM','GM')
AND DEBITNR = :ADRNR
AND ERSTDATUM BETWEEN :date_from AND :date_to
),
 
QUARTALRESULT As -- Man kann über selbst vergebene Aliase (RECHNUNGEn.QUARTAL) nicht groupen daher noch eine View
(
SELECT DEBITNR,SUM(NETTO) AS UMSATZ,QUARTAL,JAHR
FROM RECHNUNGEN
GROUP BY JAHR,QUARTAL,DEBITNR,NETTO
),
 
COMBINEDRESULT AS -- Um jetzt alle zusammenzufassen, wo QUARTAL,JAHR,DEBITNR in einer Zeile gleich sind
(
SELECT DEBITNR,ROUND(SUM(UMSATZ),2) AS TOTALUMSATZ,QUARTAL,JAHR
FROM QUARTALRESULT
GROUP BY DEBITNR,JAHR,QUARTAL
),
 
ZeitEinheitQuartal As
(
SELECT TOTALUMSATZ, (Cast(QUARTAL as varchar) + '.' + Cast(JAHR as varchar)) AS ZEITEINHEIT
FROM COMBINEDRESULT
),
 
JAHRTABLE As
(
SELECT JAHR,QUARTAL,(Cast(QUARTAL as varchar) + '.' + Cast(JAHR as varchar)) AS ZEITEINHEIT
FROM DIM_DATUM
WHERE DATUMDT BETWEEN :date_from AND :date_to
GROUP BY JAHR,QUARTAL
)
 
SELECT JAHR,QUARTAL,JAHRTABLE.ZEITEINHEIT,TOTALUMSATZ
FROM JAHRTABLE
LEFT JOIN ZEITEINHEITQUARTAL ZQ ON ZQ.ZEITEINHEIT = JAHRTABLE.ZEITEINHEIT
ORDER BY JAHR,QUARTAL

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

INSERT Sales per customer per quarter
INSERT INTO STATISTIC_UI (ID, NAME, STATISTIC_CONFIGURATION, STATISTIC_CONTROLLER, CATEGORY_ID, DELETED, DESCRIPTION, IS_INTERNAL, OVERRIDDEN_BY)
VALUES ('{13540FB3-149E-4ED1-86F9-0A7BD09B06A8}21', 'Umsatz Je Kunde Pro Quartal', '{
"type": "STATISTIK",
"rows": [
{
"type": "ROW",
"rowItems": [
{
"type": "CHART",
"width": "12",
"uid": "40a5d362-596c-45cf-bbf8-e62d05459b1e",
"titel": "{{{Statistik.UmsatzProQuartal}}}",
"dataUrl": "api/dynamicapi/{205C8FC4-03D9-4C75-9634-CE9A6C9B7E72}37/GetData",
"chartSettings": {
"series": [
{
"field": "TOTALUMSATZ",
"type": "column",
"colorField": ""
}
],
"categoryAxis": {
"field": "ZEITEINHEIT"
},
"theme": "bootstrap",
"valueAxis": null
},
"label": "",
"pageSize": "20",
"height": ""
}
],
"uid": "33259864-27cc-4d26-9cd6-8465fc7e9444"
}
],
"parameters": [
{
"type": "DATE",
"width": "7",
"uid": "09ed7b73-b428-4c12-8a95-0d4ef97035e4",
"name": "date_from",
"label": "{{{Standard.Von}}}"
},
{
"type": "DATE",
"width": "7",
"uid": "ebedfb88-c061-4ade-b291-268f9a173cc6",
"name": "date_to",
"label": "{{{Standard.Bis}}}"
},
{
"type": "REACTTEXT",
"width": "7",
"uid": "f5565303-b828-4304-a929-5a24cc6ee5d2",
"name": "ADRNR",
"label": "{{{Statistik.Kundennummer}}}"
}
],
"settings": {
"type": "SETTINGS"
}
}', '
var controller={};
 
controller.getInitialValues = function(paramsFromStorage,params){
debugger;
var initVal = {};
if (paramsFromStorage) {
initVal = paramsFromStorage;
}
if(params && params.ADRNR){
initVal.ADRNR = params.ADRNR;
}
return initVal;
 
var Jahr = new Date().getFullYear();
return {
date_from: ''01.01.'' + Jahr,
date_to: ''31.12.'' + Jahr
};
}
 
controller;
', NULL, 0, '', 1, NULL);