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

Gegevensbronnen

De gegevens waarvan 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. De gedetailleerde REGAS uren registratie  gegevens bevatten het AFAS personeelsnummer waarmee de gegevens van beide systemen gecombineerd kunnen worden. 
Voor het initiële rapport met gecumuleerde uren is een beperkte selectie van personeelsleden gebruikt. De uren zijn gekoppeld op basis van naam en niet cliënt gebonden uren zijn gegenereerd om rapportage mogelijk te maken.

Pagina beschrijving:

 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.

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.
De geregistreerde periode is "hard" in de view geprogrammeerd.

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

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. 

 Contract uren gedetailleerd

Voor dit scherm word gebruikt gemaakt van de MS Excel sheets met personeelsgegevens en de gespecificeerde 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 komen uit de MS SQL Server database en worden in Power BI verder uitgewerkt.

Een aantal medewerkers registreren in meerdere gebieden / organisatorische eenheden. In overleg met de opdrachtgever wordt de som van alle geregistreerde uren van een medewerker die registreert in meerdere gebieden, gecalculeerd naar het gebied / organisatorische eenheid met het grootste aantal te verwachte uren voor die medewerker. 

Het gaat om 5 van de  257 geregistreerde medewerkers (< 2%). Deze 5 registreerde ~ 931 uren op een totaal aan ~ 49.004 uren in de periode van 1-1-2018 t/m 18-4-2018.

SQL Server berekeningen

Dit omdat de REGAS tijdregistratie data geen vermelding heeft van het gebied waar de werknemer de werkzaamheden heeft uitgevoerd en een werknemer in meerdere gebieden werkzaam kan zijn. Organisatorische eenheden zonder locatie worden gebundeld naar "Overig" tenzij een postcode voor de locatie aanwezig is. De postcode is wordt gebruikt om de getoonde kaart te koppelen aan de gegevens.

View voor personeelslijst 
REATE VIEW [dbo].[Personeel]
AS
SELECT        
	Naam
	, Roepnaam
	, Voorvoegsel
	, Achternaam
	, VolledigeNaam
	, Geslacht
	, Werkgever
	, Wg
	, (SELECT TOP (1) CASE WHEN [OrganisatorischeEenheid] LIKE 'Team%' THEN 
							CASE WHEN [OrganisatorischeEenheid] NOT IN ('Team Buitenveldert', ' Team Midden-Noord', 'Team Nieuwendam', 'Team Noord-West', 
																		'Team Midden-Noord', 'Team Oud - Zuid', 'Team Rivierenbuurt / De Pijp') THEN 
								'Overig' 
							ELSE 
								[OrganisatorischeEenheid]
							 END 
						ELSE 
							'Overig' END AS OrganisatorischeEenheid
                        FROM dbo.PersoneelExcel
                        WHERE (AfasID = PE.AfasID) AND 
						      (WerkzameUPW = (SELECT MAX(WerkzameUPW) AS Expr1
												FROM dbo.PersoneelExcel AS PersoneelExcel_2
												WHERE (AfasID = PE.AfasID)
											 )
						      )
		) AS OrganisatorischeEenheid
		, AfasID
		, SUM(WerkzameUPW) AS WerkzameUPW
FROM (SELECT
		ID
		, Jaar
		, Maand
		, Naam
		, Roepnaam
		, Voorvoegsel
		, Achternaam
		, VolledigeNaam
		, Geslacht
		, Werkgever
		, Wg
		, OE
		, Functie
		, Soort
		, DPW
		, WerkzameUPW
		, AantalFTE
		, AfasID
		FROM dbo.PersoneelExcel AS PersoneelExcel_1) AS PE
GROUP BY Naam, Roepnaam, Voorvoegsel, Achternaam, VolledigeNaam, Geslacht, Werkgever, Wg, AfasID

Power BI berekeningen

Binnen Power BI worden extra berekeningen uitgevoerd om de data te kunnen tonen. In de diagrammen worden de percentages weergegeven waar de bron data uren bevat.

Percentages per medewerker

In de bovenste diagram worden de percentages per medewerker getoont. Per categorie worden de  geregistreerde uren over de geselecteerde periode gedeeld door te totale te verwachten uren over die periode. In Power BI zijn hier de volgende formules voor gedefinieerd:

 Power BI formules
Power BI formulesVerklaring

ClientTijdPercentage = (SUM(TijdSchrijvenSimple[ClientTijd])/[ExpectedHoursPersoneel])*100

De som van de geschreven client uren over de geselecteerde periode, gedeeld door de verwachte uren over de geselecteeerde periode

Berekend het percentage clientgebonden uren t.o.v. het verwachte aantal uren van de medewerker

TrainingstudieTijdPercentage = (SUM(TijdSchrijvenSimple[TrainingstudieTijd])/[ExpectedHoursPersoneel])*100

De som van de geschreven client uren over de geselecteerde periode, gedeeld door de verwachte uren over de geselecteeerde periode

Berekend het percentage studie uren t.o.v. het aantal verwachte uren van de medewerker

SuperuseractiviteitenTijdPercentage = (SUM(TijdSchrijvenSimple[SuperuseractiviteitenTijd])/[ExpectedHoursPersoneel])*100

De som van de geschreven superuser uren over de geselecteerde periode, gedeeld door de verwachte uren over de geselecteeerde periode

Berekend het percentage superuser uren t.o.v. het aantal verwachte uren van de medewerker

VakgroephouderschapTijdPercentage = (SUM(TijdSchrijvenSimple[VakgroephouderschapTijd])/[ExpectedHoursPersoneel])*100

De som van de geschreven vakgroephouder uren over de geselecteerde periode, gedeeld door de verwachte uren over de geselecteeerde periode

Berekend het percentage vakgroephouder uren t.o.v. het aantal verwachte uren van de medewerker

InternoverlegTijdPercentage = (SUM(TijdSchrijvenSimple[InternoverlegTijd])/[ExpectedHoursPersoneel])*100

De som van de geschreven intern overleg uren over de geselecteerde periode, gedeeld door de verwachte uren over de geselecteeerde periode

Berekend het percentage intern overleg uren t.o.v. het aantal verwachte uren van de medewerker

SponsoringfondsenwerverTijdPercentage = (SUM(TijdSchrijvenSimple[SponsoringfondsenwerverTijd])/[ExpectedHoursPersoneel])*100

De som van de geschreven sponsoring uren over de geselecteerde periode, gedeeld door de verwachte uren over de geselecteeerde periode

Berekend het percentage sponsoring uren t.o.v. het aantal verwachte uren van de medewerker

OndernemingsraadTijdPercentage = (SUM(TijdSchrijvenSimple[OndernemingsraadTijd])/[ExpectedHoursPersoneel])*100

De som van de geschreven ondernemimgsraad uren over de geselecteerde periode, gedeeld door de verwachte uren over de geselecteeerde periode

Berekend het percentage ondernemingsraad uren t.o.v. het aantal verwachte uren van de medewerker

NietgeschrevenUrenNew = IF((AVERAGE(Personeel[WerkzameUPW])/40)* [NumberOfWorkingDays2] * 8 - SUM(TijdSchrijvenSimple[Uren])<0,0,(AVERAGE(Personeel[WerkzameUPW])/40)* [NumberOfWorkingDays2]*8 - SUM(TijdSchrijvenSimple[Uren]))

Het aantal verwachte uren over de geselecteerde periode, minus de geschreven uren in de geselecteerde periode. Indien er minder dan 0 "niet geschreven" uren zijn, wordt dit naar 0 gebracht.

Berekend het percentage niet geschreven uren t.o.v. het aantal verwachte uren van de medewerker, waarbij niet geschreven niet kleiner kan zijn dan 0 t.o.v. het verwacht aantal uren.
Indien het aantal niet geschreven uren negatief is, betekend dit dat de medewerker meer uren dan de verwachte uren over de geselecteerde periode heeft geregistreerd. Niet geschreven uren worden in dat geval gereduceerd naar 0.

Percentages per organisatorische eenheid / gebied

In de onderste diagram worden de percentages per gebied (organisatorische eenheid) getoond. Per categorie worden de gezamelijke, door medewerkers van het gebied geregistreerde uren, over de geselecteerde periode gemiddeld en gedeeld door het gemiddelde te verwachten uren over die periode.

In Power BI zijn hier de volgende formules voor gedefinieerd:

 Power BI formules
Power BI formulesVerklaring

ClientTijdPercentageAvg = (((SUM(TijdSchrijvenSimple[ClientTijd]) / COUNT(Personeel[OrganisatorischeEenheid])) / ((TijdSchrijvenDetail[ExpectedHoursPersoneel])))) *100

Het gemiddelde van de som van de geschreven client uren over de geselecteerde periode gedeelt door het aantal medewerkers, gedeeld door de som van de verwachte uren over de geselecteeerde periode van de medewerkers

Berekend het percentage clientgebonden uren t.o.v. het verwachte aantal uren van een gebied op basis van de gemiddelde uren registratie door medewerkers in de organisatorische eenheid / gebied

TrainingstudieTijdPercentageAvg = (((SUM(TijdSchrijvenSimple[TrainingstudieTijd]) / COUNT(Personeel[OrganisatorischeEenheid])) / ((TijdSchrijvenDetail[ExpectedHoursPersoneel])))) *100

Het gemiddelde van de som van de geschreven training uren over de geselecteerde periode gedeelt door het aantal medewerkers, gedeeld door de som van de verwachte uren over de geselecteeerde periode van de medewerkers

Berekend het percentage studie uren t.o.v. het aantal verwachte uren van een gebied op basis van de gemiddelde uren registratie door medewerkers in de organisatorische eenheid / gebied

SuperuseractiviteitenTijdPercentageAvg = (((SUM(TijdSchrijvenSimple[SuperuseractiviteitenTijd]) / COUNT(Personeel[OrganisatorischeEenheid])) / ((TijdSchrijvenDetail[ExpectedHoursPersoneel])))) *100

Het gemiddelde van de som van de geschreven superuser uren over de geselecteerde periode gedeelt door het aantal medewerkers, gedeeld door de som van de verwachte uren over de geselecteeerde periode van de medewerkers

Berekend het percentage superuser uren t.o.v. het aantal verwachte uren van een gebied op basis van de gemiddelde uren registratie door medewerkers in de organisatorische eenheid / gebied

VakgroephouderschapTijdPercentageAvg = (((SUM(TijdSchrijvenSimple[VakgroephouderschapTijd]) / COUNT(Personeel[OrganisatorischeEenheid])) / ((TijdSchrijvenDetail[ExpectedHoursPersoneel])))) *100

Het gemiddelde van de som van de geschreven vakgroephouder uren over de geselecteerde periode gedeelt door het aantal medewerkers, gedeeld door de som van de verwachte uren over de geselecteeerde periode van de medewerkers

Berekend het percentage vakgroephouder uren t.o.v. het aantal verwachte uren van een gebied op basis van de gemiddelde uren registratie door medewerkers in de organisatorische eenheid / gebied

InternoverlegTijdPercentageAvg = (((SUM(TijdSchrijvenSimple[InternoverlegTijd]) / COUNT(Personeel[OrganisatorischeEenheid])) / ((TijdSchrijvenDetail[ExpectedHoursPersoneel])))) *100

Het gemiddelde van de som van de geschreven intern overleg uren over de geselecteerde periode gedeelt door het aantal medewerkers, gedeeld door de som van de verwachte uren over de geselecteeerde periode van de medewerkers

Berekend het percentage intern overleg uren t.o.v. het aantal verwachte uren van een gebied op basis van de gemiddelde uren registratie door medewerkers in de organisatorische eenheid / gebied

SponsoringfondsenwerverTijdPercentageAvg = (((SUM(TijdSchrijvenSimple[SponsoringfondsenwerverTijd]) / COUNT(Personeel[OrganisatorischeEenheid])) / ((TijdSchrijvenDetail[ExpectedHoursPersoneel])))) *100

Het gemiddelde van de som van de geschreven sponsoring uren over de geselecteerde periode gedeelt door het aantal medewerkers, gedeeld door de som van de verwachte uren over de geselecteeerde periode van de medewerkers

Berekend het percentage sponsoring uren t.o.v. het aantal verwachte uren van een gebied op basis van de gemiddelde uren registratie door medewerkers in de organisatorische eenheid / gebied

OndernemingsraadTijdPercentageAvg = (((SUM(TijdSchrijvenSimple[OndernemingsraadTijd]) / COUNT(Personeel[OrganisatorischeEenheid])) / ((TijdSchrijvenDetail[ExpectedHoursPersoneel])))) *100

Het gemiddelde van de som van de geschreven ondernemingsraad uren over de geselecteerde periode gedeelt door het aantal medewerkers, gedeeld door de som van de verwachte uren over de geselecteeerde periode van de medewerkers

Berekend het percentage ondernemingsraad uren t.o.v. het aantal verwachte uren van een gebied op basis van de gemiddelde uren registratie door medewerkers in de organisatorische eenheid / gebied

NietGeschrevenEenheid = 100 - [ClientTijdPercentageAvg] - [InternoverlegTijdPercentageAvg] - [OndernemingsraadTijdPercentageAvg]- [SuperuseractiviteitenTijdPercentageAvg]- [TrainingstudieTijdPercentageAvg] - [VakgroephouderschapTijdPercentageAvg]  - [SponsoringfondsenwerverTijdPercentageAvg]

100% minus de berekende percentages van de geschreven uren voor de eenheid.

Berekend het percentage niet geschreven uren t.o.v. het aantal verwachte uren van een gebied.
Doordat de gespecificeerde uren worden gemiddeld over de medewerkers van het gebied / organisatorische eenheid kunnen afrondingsverschillen optreden. Door de geschreven uren van de 100% af te trekken wordt ook het gemiddelde 'Niet geschreven' resultaat berekend.




Write a comment…