Artikel: AN0002463Aktualisiert: 01.06.2021
The solution will be maintained by the role Administrator of cost allocation that will use two following dashboards:
- Dashboard containing menu (outpost to settings and to particular data) and then various checks of data quality
- Dashboard with final allocation to countries, teams and organizational units
Dashboard menu and check of data quality
We will use webpart Menu, into which we will insert all the necessary objects.
Furthermore, we will use webparts Grid, into which we will insert queries that we will prepare and that will identify various incorrect settings.
Example: Users without allocation
SELECT 'cs-CZ::Uživatel z Napojení Poplatku ke Službě~de-DE::Benutzer von Lastschrift - Dienst Verbindung~en-US::User from Link charge to service' AS Type, su.id AS service_user___service_user, lchts.period AS period___period, lchts.code AS Object
FROM {{:class.link_charge_to_service:}} lchts
LEFT JOIN {{:class.service_user:}} su ON lchts.service_user = su.id
LEFT JOIN {{:class.service_user_allocation:}} sua ON sua.service_user = su.id AND lchts.period = sua.period
WHERE sua.service_user IS NULL AND lchts.service_user IS NOT NULL AND lchts.deleted IS NULL AND sua.deleted IS NULL
UNION
SELECT 'cs-CZ::Uživatel z Modelu úložiště~de-DE::Benuzer von Speicher-Modell~en-US::User from Storage model', ms.service_user, per.id, NULL AS Object
FROM {{:class.model_storage:}} ms
INNER JOIN {{: class.period :}} per ON ms.date >= per.[from] AND ms.date <= per.[to]
LEFT JOIN {{:class.service_user_allocation:}} sua ON sua.service_user = ms.service_user AND per.id = sua.period
WHERE sua.service_user IS NULL AND ms.deleted IS NULL AND sua.deleted IS NULL
UNION
SELECT 'cs-CZ::Uživatel z Modelu tisku~de-DE::Benuzer von Druck-Modell~en-US::User from Print model', mp.service_user, mp.period, NULL AS Object
FROM {{:class.model_print:}} mp
LEFT JOIN {{:class.service_user_allocation:}} sua ON sua.service_user = mp.service_user AND sua.period = mp.period
WHERE sua.service_user IS NULL AND mp.deleted IS NULL AND sua.deleted IS NULL
UNION
SELECT 'cs-CZ::Uživatel z Členové MS AD skupiny~de-DE::Benutzer von Mitglieder von MS AD Gruppen~en-US::User from Members of MS AD groups', grm.service_user, grm.period, gr.name AS Object
FROM {{:class.ms_ad_group_members:}} grm
LEFT JOIN {{:class.ms_ad_group:}} gr ON grm.ms_ad_group = gr.id
LEFT JOIN {{:class.service_user_allocation:}} sua ON sua.service_user = grm.service_user AND sua.period = grm.period
WHERE sua.service_user IS NULL AND grm.deleted IS NULL AND sua.deleted IS NULL
UNION
SELECT 'cs-CZ::Chybějící země nebo období~de-DE::Fehlendes Land oder Period~en-US::Missing country or period', sua.service_user, sua.period, Null AS Object
FROM {{:class.service_user_allocation:}} sua
WHERE sua.country IS NULL OR sua.period IS NULL
Dashboard with resulting allocation
We will use webparts Report and webpart Grid.
![](ImagesData/Help-cs-CZ/0333_en.png)