Het einde van het jaar nadert. Dus is het tijd voor een cadeautje in de vorm van deze stap-voor-stap tutorial.
We hebben er allemaal wel een keer last van gehad. We willen de feestdagen uitsluiten in een berekening of we hebben ontbrekende datums in een grafiek. Soms kun je dit oplossen met de tijdsdimensie die in de laatste versies van SAP BusinessObjects 4.2 zit, maar soms ook niet. In deze blog ga ik uitleggen hoe je dit met Microsoft SQL server en SAP BusinessObjects kunt oplossen kunt oplossen.
De eerste stap is het aanmaken van een aantal functies in Microsoft SQL server. Misschien mag u dit niet zelf, uw Database administrator kan u hier van bij helpen.
Stap1: Functie voor het genereren van een kalender.
Voor het maken van de kalender maak ik gebruik van de zogenaamde “Recursive Common Table Expressions. Het onderstaande state is een voorbeeld om een kalender functie te maken.
CREATE FUNCTION [dbo].[ufnGetKalender](@Year int)
RETURNS @retNLKalender TABLE
(
-- Columns returned by the function
[Date] datetime NOT NULL,
[Dag] int NULL,
[Maandnaam] varchar(25) null,
[jaar] int null,
[dagnaam] varchar(10)
)
AS
BEGIN
DECLARE @YearCnt INT = 1 ; -- Aantal jaren in de kalender, in dit voorbeeld op 1 laten staan.
DECLARE @StartDate DATETIME = DATEFROMPARTS(@Year, '01','01') -- Begindatum kalender
DECLARE @EndDate DATEEIME = DATEADD(DAY, -1, DATEADD(YEAR, @YearCnt, @StartDate)); -- Eindataum kalender
-- Start maken kalender
;WITH Cal(n) AS
(
SELECT 0 UNION ALL SELECT n + 1 FROM Cal
WHERE n < DATEDIFF(DAY, @StartDate, @EndDate) – Aantal recursies wordt bepaald door deze where clause
),
FnlDt(d) AS
(
SELECT DATEADD(DAY, n, @StartDate) FROM Cal -- bepaal volgende datum
),
FinalCte AS -- Voeg wat velden toe
(
SELECT
[Date] = CONVERT(DATEtime,d),
[Day] = DATEPART(DAY, d),
[Month] = DATENAME(MONTH, d),
[Year] = DATEPART(YEAR, d),
[DayName] = DATENAME(WEEKDAY, d)
FROM FnlDt
)
insert into @retNLKalender(date, dag, maandnaam, jaar, dagnaam) select date,day, Month, Year, dayname FROM finalCte -- invoegen in table variabele
ORDER BY [Date]
OPTION (MAXRECURSION 0); -- aantal keren dat recursie uitgevoerd mag worden. 0 s geen maximum, zou ook 366 mogen zijn.
RETURN;
END;
Stap 2: Feestdagen berekenen
Voor het bepalen van de feestdagen heb ik een tweede “table-valued” functie gedefinieerd. In deze functie maakt ik onderscheid in twee soort feestdagen. Vaste feestdagen die altijd op een vaste dag zijn. (bijv. kerstmis, nieuwjaar enz.) en variabele feestdagen. De variabele feestdagen hebben allemaal een relatie met eerste paasdag.
We beginnen met het maken van een functie om eerste paasdag te bepalen. Net als bij de kalender kun je de uitleg van dit algoritme gemakkelijk vinden op internet. Dit zijn de uitgangspunten:
- start met het kerkelijke begin van lente. Die datum heeft de kerk ooit vastgesteld op 21 maart.
- zoek de eerste volle maan op of na 21 maart.
- zoek de eerstvolgende zondag na deze volle maan. Voilà, je hebt Eerste Paasdag te pakken.
Knappe koppen hebben uitgevonden dat je dit zo kunt doen.
CREATE FUNCTION [dbo].[GetEasterSunday] (@Y INT)
RETURNS DATETIME
AS
BEGIN
-- Uitleg van dit algoritme vind je op internet
DECLARE @c INT = @Y / 100
DECLARE @n INT = @Y - 19 * (@Y / 19)
DECLARE @k INT = (@c - 17) / 25
DECLARE @i INT = @c - @c / 4 - (@c - @k) / 3 + 19 * @n + 15
SET @i = @i - 30 * (@i / 30)
SET @i = @i - (@i / 28) * (1 - (@i / 28) * (29 / (@i + 1)) * ((21 - @n) / 11))
DECLARE @j INT = @Y + @Y / 4 + @i + 2 - @c + @c / 4
SET @j = @j - 7 * (@j / 7)
DECLARE @l INT = @i - @j
DECLARE @m INT = 3 + (@l + 40) / 44
DECLARE @d INT = @l + 28 - 31 * (@m / 4)
RETURN
(
SELECT CONVERT
( DATETIME,
RTRIM(@Y)
+ RIGHT('0'+RTRIM(@m), 2)
+ RIGHT('0'+RTRIM(@d), 2)
)
)
END
De overige feestdagen leiden we af van eerste paasdag.
Met de volgende functie bepalen we de feestdagen.
CREATE FUNCTION [dbo].[ufnGetFeestdagen](@Year int)
RETURNS @retNLFeestdagen TABLE
(
-- Columns returned by the function
[DatumFeestDag] datetime NOT NULL,
[NaamFeestdag] [nvarchar](50) NULL,
[SoortFeestdag] [int]
)
AS
BEGIN
declare @Feestdag datetime;
declare @JaarString varchar(4);
set @JaarString = cast(@Year as varchar(4));
-- Vaste feestdagen
-- Nieuwjaarsdag
set @Feestdag = convert( datetime, '01-01-'+@JaarString , 105);
INSERT INTO @retNLFeestdagen values ( @Feestdag ,'Nieuwjaarsdag',1);
--$bevrijdingsdag
set @Feestdag = convert( datetime, '05-05-'+@JaarString , 105);
INSERT INTO @retNLFeestdagen values ( @Feestdag ,'Bevrijdingsdag',4);
--$kerstmis
set @Feestdag = convert( datetime, '25-12-'+@JaarString , 105);
INSERT INTO @retNLFeestdagen values ( @Feestdag ,'Eerste kerstdag',7);
--tweedekerstdag = new \DateTime("$jaar-12-26");
set @Feestdag = convert( datetime, '26-12-'+@JaarString , 105);
INSERT INTO @retNLFeestdagen values ( @Feestdag ,'Tweede kerstdag',7);
-- Koningsdag
set @Feestdag = convert( datetime, '27-04-'+@JaarString , 105);
IF DATEPART(dw,@feestdag) = 1
BEGIN
set @feestdag = dateadd(day,-1,@Feestdag)
END
INSERT INTO @retNLFeestdagen values ( @Feestdag ,'Koningsdag',3);
-- Variabele Feestdagen
-- Paaszondag
SELECT @Feestdag = dbo.GetEasterSunday(@Year);
INSERT INTO @retNLFeestdagen values ( @Feestdag ,'Eerste Paasdag',2);
-- Tweede paasdag
INSERT INTO @retNLFeestdagen values ( dateadd(day,1,@Feestdag) ,'Tweede paasdag',2);
-- Hemelvaart
INSERT INTO @retNLFeestdagen values ( dateadd(day,39,@Feestdag) ,'Hemelvaart',5);
-- Eerste Pinksterdag
INSERT INTO @retNLFeestdagen values ( dateadd(day,49,@Feestdag) ,'Eerste pinksterdag',6);
-- Eerste Pinksterdag
INSERT INTO @retNLFeestdagen values ( dateadd(day,50,@Feestdag) ,'Tweede pinksterdag',6);
RETURN;
END;
STAP 3: Invoegen in de SAP BusinessObjects universe
Eindelijk gaan we aan de slag met BusinessObjects. Voor het maken van de universe heb ik gebruik gemaakt van de Information Design Tool. (Kan ook de universe design tool zijn). Met BusinessObjects kunnen we niet rechtstreeks op deze functies een query bouwen. We maken daarom gebruik van een “derived table”.
SELECT k.*, NaamFeestdag, soortfeestdag FROM [dbo].[ufnGetKalender] (
(@Prompt('Kies jaar:','N','{'2020','2021'}',MONO,FREE,,))) k left
outer join [dbo].[ufnGetFeestdagen] (@Prompt('Kies jaar:','N','{'2020','2021'}',MONO,FREE,,)) f on date = DatumFeestDag
In deze “derived table” kalender combineer ik de kalender met de feestdagen. De connectie van de datafoundation moet wijzen naar de database waar de functies in staan.
Nu, nog de “business layer”
Ik heb er voor gekozen om in de business layer ook een object isoweek op te nemen. Hiervoor is in de database nog een “scalar function” gemaakt.
create FUNCTION [dbo].[Isoweek] (@DATE DATETIME)
RETURNS INT
AS
BEGIN
DECLARE @week int;
DECLARE @year int;
--Bepalen isoweek
SET @week = cast(datepart(isowk, @date) as int)
--Correctie iso jaar
set @year =
CASE
WHEN DATEPART(ISO_WEEK, @Date) > 50 AND MONTH(@Date) = 1 THEN YEAR(@Date) - 1
WHEN DATEPART(ISO_WEEK, @Date) = 1 AND MONTH(@Date) = 12 THEN YEAR(@Date) + 1
ELSE YEAR(@Date) END;
return
(select @year*100+@week );
END
Nu nog de objecten aanmaken. Ik heb er twee extra aangemaakt voor het maken van de kalender. I
Datum(m): De kalender is een crosstab. Dit object is in de body hiervan gebruikt.
Week(iso): Met dit object kun je de kalender eenvoudig sorteren.
Stap 4: Rapport bouwen in WebIntelligence
De laatste stap is maken van de kalender. Je kunt zelf de lay-out bepalen . Ik heb gekozen voor een maandkalender. In het onderstaande plaatje staat de structuur van de maand kalender.
Nu nog de kalender personaliseren om je eigen touch eraan te geven.
De kalender is een leuke gimmick. De componenten die in deze blog zitten kun je in tal van rapporten gebruiken.
Veel succes!
Meer inspiratie en kennis
Gerelateerde blogs
