Reporting
Space shortcuts
Page tree
Skip to end of metadata
Go to start of metadata

You are viewing an old version of this page. View the current version.

Compare with Current View Page History

« Previous Version 7 Next »

Gegevensbronnen

De gegevens waar de demo de resultaten toont komen uit een drietal MS Excel Spreadsheets:

  • Een sheet met personeelsgegevens uit AFAS
  • Een initieel sheet uit REGAS, met daarin gecumuleerde tijdregistratie gegevens tot en met April 2018
  • Een sheet uit REGAS met gedetailleerde uren registratie over de periode van 1 januari 2018 tot en met 18 april 2018.

De gegevens zijn uit de MS Excel sheets overgebracht naar een MS SQL 2014 database server. Zowel de AFAS als de REGAS gegevens bevatten het AFAS personeelsnummer waarmee de gegevens van beide systemen gecombineerd kunnen worden. 

Tabblad 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. De gegevens zijn deels berekend in MS SQL Server en deels in Power BI.

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 gegeven op naam moeten worden gecombineerd. Dit is handmatig uitgevoerd.

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.

Functie netto werk dagen 
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 .

View berekening percentages 
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
  • No labels