Authorization

Basically, the user must be allowed to access statistics. Only those orders are visible for which the user has at least reading rights to the order's main group. For more information, see the article Using evaluations.

Technical information

The SQL query that compiles the data looks like this:

SQL distribution: active orders
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;
 
 
select
a.ANGNR,
cast(cast(a.ERSTDATUM as Date) as varchar) ERSTDATUM,
z.TITEL,
Coalesce(dk.Kennung, '') Kennung,
Coalesce(au.Bezeichnung, '') Auftragsart,
v.AdrName + ' ' + v.AdrName2 Kunde,
v.Strasse,
v.PLZ + ' ' + v.Ort Ort,
Round(Coalesce(a.NETTO, 0), 2) NETTO,
Round(Coalesce(z.DECKUNGSBEITRAG, 0), 2) DECKUNGSBEITRAG,
dk.KNr,
au.AUART,
z.OBERGR OGRNR
 
from
ANGKOPF a left outer join DIM_KENNUNG dk on dk.KNr = a.kennung and dk.Language = @INNO_USER_LANG,
ANGKOPFZ z left outer join AUART au on au.AuArt = z.AUART,
VKUNADR v
 
where
(a.angnr = z.ANGNR and a.AINDEX = z.AINDEX)
and a.art = '01'
and z.STATUS in ('A')
and v.AdrNr = z.ADRNR3
and a.ERSTDATUM >= @date_from and a.ERSTDATUM <= @date_to
and (:user_ogrnr_ISNULL = 1 OR z.OBERGR in (:user_ogrnr)) and (z.OBERGR in (select po.ogrnr from pwogrupp po where po.recht >= 1 and po.name = @INNO_USER))
 
order by a.ANGNR

The display of the evaluation was created with the following entry in the table 'Statistic_UI':

INSERT INTO STATISTIC_UI
INSERT INTO STATISTIC_UI (ID, NAME, STATISTIC_CONFIGURATION, STATISTIC_CONTROLLER, CATEGORY_ID, DELETED, DESCRIPTION, IS_INTERNAL, OVERRIDDEN_BY)
VALUES ('{13540FB3-149E-4ED1-86F9-0A7BD09B06A8}8', 'Aktive Aufträge', '{
"type": "STATISTIK",
"rows": [
{
"type": "ROW",
"rowItems": [
{
"type": "TABLE",
"width": "12",
"uid": "2bc77151-d593-4351-ae3c-876ae9a78810",
"exportTemplate": "",
"dataUrl": "api/dynamicapi/{205C8FC4-03D9-4C75-9634-CE9A6C9B7E72}41/GetData",
"columns": [
{
"header": "{{{Field.Auftrag.usernummer}}}",
"columnName": "ANGNR",
"width": "95px"
},
{
"header": "{{{Field.Zeit.datum}}}",
"columnName": "ERSTDATUM",
"width": "115px"
},
{
"header": "{{{Statistik.Auftrag}}}",
"columnName": "TITEL"
},
{
"header": "{{{Field.Auftrag.knr}}}",
"columnName": "KENNUNG"
},
{
"header": "{{{Projekt.Auftragsart}}}",
"columnName": "AUFTRAGSART"
},
{
"header": "{{{Field.Auftrag.kundenname}}}",
"columnName": "KUNDE"
},
{
"header": "{{{Field.Adresse.strasse}}}",
"columnName": "STRASSE"
},
{
"header": "{{{Field.Adresse.ort}}}",
"columnName": "ORT"
},
{
"header": "{{{Statistik.Netto}}}",
"columnName": "NETTO",
"align": "right",
"width": "100px",
"format": "#0.00"
},
{
"header": "{{{Statistik.Deckung}}}",
"columnName": "DECKUNGSBEITRAG",
"align": "right",
"width": "100px",
"format": "#0.00"
}
],
"titel": "{{{Statistik.AktiveAufträge}}}",
"dataSource": {
"dataUrl": "api/dynamicapi/{205C8FC4-03D9-4C75-9634-CE9A6C9B7E72}41/GetData",
"_Object_Type": "sql"
}
}
],
"uid": "a93d5e0e-9518-4446-b578-dcf152aed6f8"
}
],
"parameters": [
{
"type": "DATE",
"width": "7",
"uid": "35c0f76e-fccd-4266-b092-d224864ca392",
"label": "{{{Standard.Von}}}",
"name": "date_from"
},
{
"type": "DATE",
"width": "7",
"uid": "855cfed2-51c3-43df-bc40-d3f408f11631",
"label": "{{{Standard.Bis}}}",
"name": "date_to"
},
{
"type": "MULTISELECT",
"width": "12",
"uid": "879a6fbb-fc1a-4244-a914-cd17ee14cef0",
"label": "{{{Field.Ressource.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;
', 7, 0, '<h4>Aktive Aufträge</h4>
Alle Aufträge mit Status "Aktiv".
<br><br>
<h4>Filter:</h4>
<ul>
<li>Start - Ende
<li>Obergruppe (unter Berücksichtigung der Benutzerberechtigung)
</ul>', 1, NULL);