Case study Cost allocation: Dashboards

The solution will be maintained by the role Administrator of cost allocation that will use two following dashboards:

1. Dashboard containing menu (outpost to settings and to particular data) and then various checks of data quality
2. 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.