Contract uren AFAS vs geschreven in REGAS

Voor dit scherm word gebruikt gemaakt van de MS Excel sheets met personeelsgegevens en de gecumuleerde tijdregistratie gegevens. Op dit scherm zijn 2 staafdiagrammen, selectie criteria voor medewerkers en organisatorische eenheden en een overzichtskaart van de gemeente die de geselecteerde gebieden op basis van postcode weergeeft. De bron gegevens zijn volledig berekend in MS SQL Server.

De gegevens in de medewerkers diagram komen rechtstreeks van de data-bron. Ze worden in Power BI slechts weergegeven.
De gegevens uit de gebieden diagram worden door Power BI automatisch berekend op basis van de gemiddelde waardes van de medewerker gegevens. 

SQL Server berekeningen

Om de gegevens te kunnen weergeven, wordt een deel van de berekeningen uitgevoerd in SQL Server. In een view en een functie worden de uren berekend welke in de rapportage worden gebruikt.

Er zijn in de MS Excel sheet alleen  totaal uren geregistreerd over de gehele periode. Voor de demonstratie zijn deze uren opgedeeld in verschillende categorieën. Deze "opdeling" is in de tabel in SQL Server opgeslagen. Er is een selectie van het personeel gebruikt omdat het REGAS tijdschrijven Excel sheet nog geen AFAS personeelsnummer bevat en gegevens op basis van naam moeten worden gecombineerd. Dit is handmatig uitgevoerd waardoor van een beperkte selectie gebruik is gemaakt.

De periode beslaat een aantal dagen van begin datum tot einddatum. Binnen deze periode vallen een aantal dagen waarin niet gewerkt wordt, zoals bv. weekeinden en officiële vakantiedagen. Hiervoor word de volgende functie gebruikt.

CREATE FUNCTION [dbo].[NetWorkDays](
     @StartDate DATETIME
    ,@EndDate DATETIME
    ) RETURNS INT 
AS 
BEGIN

DECLARE 
     @result INT
    ,@StartDate2 DATETIME
    ,@EndDate2 DATETIME
	,@HolidayCount INT

SET @StartDate2 = DATEADD(d,8-DATEPART(dw, @StartDate), @StartDate)
SET @EndDate2   = DATEADD(d,1-DATEPART(dw ,@EndDate), @EndDate)

SET @result = DATEDIFF(d, @StartDate2, @EndDate2) * 5 / 7
                + DATEDIFF(d, @StartDate, @StartDate2) - 1
                + DATEDIFF(d, @EndDate2, @EndDate)
                - CASE WHEN DATEPART(dw,@StartDate) = 1 THEN 1 ELSE 0 END
                - case when DATEPART(dw,@EndDate) = 7 THEN 1 ELSE 0 END

SET @HolidayCount = (SELECT COUNT(*) FROM Holiday WHERE [Date] BETWEEN @StartDate AND @EndDate)

RETURN @result - @HolidayCount

END

De geregistreerde uren worden in een SQL view naar percentages berekend. Het percentage wordt op medewerker niveau berekend.
De geregistreerde periode is "hard" in de view geprogrammeerd.

CREATE VIEW [dbo].[TijdschrijvenPersoneel]
AS
SELECT 
	T.[VolledigeNaam]
-- UREN
	,[TotaalUren]
	, dbo.NetWorkDays('2018-01-01','2018-04-04') AS NetWorkDays
	, WerkzameUPW/40 * dbo.NetWorkDays('2018-01-01','2018-04-04') * 8 AS ExpectedHours
	, [TotaalUren] - [OverlegUren] - [Training] - [Superuser] - [Vakgroephouder] - [Fondsenwerven] AS ClientUren
	, [OverlegUren]
	, [Training]
	, [Superuser]
	, [Vakgroephouder]
	, [Fondsenwerven] 
	, ((WerkzameUPW/40 * dbo.NetWorkDays('2018-01-01','2018-04-04') * 8) - [TotaalUren]) AS NietGeschreven
-- Percentages
	, ([TotaalUren] - [OverlegUren] - [Training] - [Superuser] - [Vakgroephouder] - [Fondsenwerven])/(WerkzameUPW/40 * dbo.NetWorkDays('2018-01-01','2018-04-04') * 8) * 100 AS ClientPercentage
	, ([OverlegUren])/(WerkzameUPW/40 * dbo.NetWorkDays('2018-01-01','2018-04-04') * 8) * 100 AS OverlegPercentage
	, ([Training])/(WerkzameUPW/40 * dbo.NetWorkDays('2018-01-01','2018-04-04') * 8) * 100 AS TrainingPercentage
	, ([Superuser])/(WerkzameUPW/40 * dbo.NetWorkDays('2018-01-01','2018-04-04') * 8)* 100  AS SuperuserPercentage
	, ([Vakgroephouder])/(WerkzameUPW/40 * dbo.NetWorkDays('2018-01-01','2018-04-04') * 8) * 100 AS VakgroephouderPercentage
	, ([Fondsenwerven])/(WerkzameUPW/40 * dbo.NetWorkDays('2018-01-01','2018-04-04') * 8) * 100 AS FondsenwervenPercentage
	, ((WerkzameUPW/40 * dbo.NetWorkDays('2018-01-01','2018-04-04') * 8) - [TotaalUren]) / (WerkzameUPW/40 * dbo.NetWorkDays('2018-01-01','2018-04-04') * 8) * 100 AS NietGeschrevenPercentage 
	
FROM Personeel P
LEFT JOIN TijdSchrijven T on T.VolledigeNaam = P.VolledigeNaam 
WHERE T.VolledigeNaam IS NOT NULL

Uitwerking view - percentage berekening

Om het percentage te berekenen is het uitgangspunt het aantal contracturen van het betreffende personeelslid. Dit wordt in de view weergegeven in de kolom ExpectedHours. Het zijn de te verwachten uren over de geslecteerde periode. Door de totaal geregistreerde uren te relateren aan de  te verwachten uren, kunnen we bereken hoeveel uren ten opzichte van het contract ontbreken. Dit zijn de niet geregistreerde uren.

Vervolgens kunnen we de percentages berekenen door de gespecificeerde uren te relateren aan de te verwachten uren. De te verwachten uren worden in de view per specificatie percentage opnieuw berekend.

Power BI berekeningen

In dit tabblad zijn geen additionele berekeningen (of measures) opgenomen. Voor de tweede staafdiagram waar de percentages per gebied worden getoond, worden de gemiddelden (AVARAGE) van de medewerkers uit dat gebied berekend door de gegevensbron van de diagram juist te configureren.