1. Case study Cost allocation: Dashboards

    Artikel: AN0002463Aktualisiert: 01.06.2021
    Die vorgegebene Sprachenversion vom Artikeltext wird angezeigt, weil es kein Text von der ausgewählten Sprache und Version gibt.

    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.

×