Summary:
This How-to goes over how to utilize CreatePeriods Excel Template from (https://support.solverglobal.com/staff/index.php?/Knowledgebase/Article/Edit/2088) to add prior years within Private Host setups. Because Private Host customers have access to SQL, creating a business rule is not necessary, and the merge statement from the Excel template can be used directly into the database via SSMS.
To review, when looking at the Excel file template distributed by QA (and available in the referenced KB above), the fields highlighted yellow are the fields to be edited when creating different Period Configurations. Once you have updated the fields and the merge statement is generated in column T:
You will then select the entire merge statement and add the following:
) s
on (t.MemberId = s.Memberid)
when matched then update set
t.FyPeriod=s.FYPeriod
,t.FYPeriodInYear=s.FYPERiodINYear
,t.PeriodStart=s.PeriodStart
,t.PeriodEnd=s.PeriodEnd
,t.FyYear=s.FYYear
,t.FyYearLabel=s.FYYearLabel
,t.FYQuarter=s.FYQuarter
,t.FYQuarterLabel=s.FYQuarterLabel
,t.FYMonthLabel=s.FYMonthLabel
,t.FYWeek=s.FYWeek
,t.FYWeekLabel=s.FYWeekLabel
,t.[Day]=s.[Day]
,t.CalendarYear=s.CalendarYear
,t.CalendarMonth=s.CalendarMonth
,t.CalendarDay=s.CalendarDay
,t.Entity=s.Entity
when not matched by target then insert (Memberid, FYperiod, FYPeriodInYear, PeriodStart,PeriodEnd,FyYear,FyYearLabel,FYQuarter, FYQuarterLabel, FYMonthLabel, FYWeek, FYWeekLabel,[Day], CalendarYear, CalendarMonth,CalendarDay, Entity)
Values (s.Memberid, s.FYPeriod, s.FYPeriodInyear, s.PeriodStart, s.PeriodEnd, s.FYYear, s.FYYearLabel, s.FYQuarter, s.FYQuarterLabel, s.FYMonthLabel, s.FYWeek, s.FYWeekLabel, s.[Day], s.CalendarYear, s.CalendarMonth, s.CalendarDay, s.Entity);
The piece of the script is how to handle the data from the merge statement generated in the Excel template. The "s" after the first ")" is needed as an alias for the data from the merge statement. This is needed for the "matches" that is happening after.
So, in the end you should have a SQL query that looks something like (obviously more extensive than this):
merge into d_Time t using (
select [...]
) s
on (t.MemberId = s.Memberid)
when matched then update set
t.FyPeriod=s.FYPeriod
,t.FYPeriodInYear=s.FYPERiodINYear
,t.PeriodStart=s.PeriodStart
,t.PeriodEnd=s.PeriodEnd
,t.FyYear=s.FYYear
,t.FyYearLabel=s.FYYearLabel
,t.FYQuarter=s.FYQuarter
,t.FYQuarterLabel=s.FYQuarterLabel
,t.FYMonthLabel=s.FYMonthLabel
,t.FYWeek=s.FYWeek
,t.FYWeekLabel=s.FYWeekLabel
,t.[Day]=s.[Day]
,t.CalendarYear=s.CalendarYear
,t.CalendarMonth=s.CalendarMonth
,t.CalendarDay=s.CalendarDay
,t.Entity=s.Entity
when not matched by target then insert (Memberid, FYperiod, FYPeriodInYear, PeriodStart,PeriodEnd,FyYear,FyYearLabel,FYQuarter, FYQuarterLabel, FYMonthLabel, FYWeek, FYWeekLabel,[Day], CalendarYear, CalendarMonth,CalendarDay, Entity)
Values (s.Memberid, s.FYPeriod, s.FYPeriodInyear, s.PeriodStart, s.PeriodEnd, s.FYYear, s.FYYearLabel, s.FYQuarter, s.FYQuarterLabel, s.FYMonthLabel, s.FYWeek, s.FYWeekLabel, s.[Day], s.CalendarYear, s.CalendarMonth, s.CalendarDay, s.Entity);
Properties:
Applies To:
- Solver Private Host version 5.0+ Period Configuration
Keywords: kb2148 Private Host Modifying Period Configuration Add Prior Years createperiods