Commerce reports not working in 2015? - Keep calm and ...

The first valid phrase to say in my first blog post in 2015 would be “Happy new year!”. I hope you have a great year with EPiServer, together we will create more and more wonderful websites for our customers.

Now back to the bug. Recently we discovered that you now can’t get any reports for date after 1/1/2015, as mentioned here  http://world.episerver.com/support/Bug-list/bug/121273 . This was because the ReportingDates table has no row for the dates after 12/31/2014, which all of our reporting queries rely on.

This bug is considered as highest priority and was fixed shortly. But as any other bug – it must go through release process with intensive tests from QA team to make sure everything is production-ready. This can take 2 or 3 weeks until you got a fixed version via nuget packages.

That’s the point of this blog post. To let the reports work again, simply run this code in the context of your Commerce database:

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id (N'[dbo].[ecf_GenerateReportingDates]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1) DROP PROCEDURE [dbo].[ecf_GenerateReportingDates] 

GO

CREATE PROCEDURE ecf_GenerateReportingDates
@EndDate DATE
AS
BEGIN
    DECLARE @StartDate DATE
    SET @StartDate = (SELECT MAX(DateFull) FROM [dbo].[ReportingDates])

	--If there is no reporting date yet, make sure to add the current date.
	IF (@StartDate IS NULL)
		SET @StartDate = GETDATE()
	ELSE
	--We will add the start date as the day after the max date in ReportingDates
		SET @StartDate = DATEADD(day, 1, @StartDate)

    IF (@EndDate > @StartDate)
    BEGIN
        CREATE TABLE #ReportingDates (ReportingDate Datetime2);
        WITH ReportingDates_CTE(Date) AS        
        ( 
            SELECT @StartDate 
            UNION ALL
            SELECT DateAdd(day,1,ReportingDates_CTE.Date) FROM ReportingDates_CTE WHERE ReportingDates_CTE.Date <= @EndDate
        )
        
        INSERT INTO #ReportingDates
        SELECT * FROM ReportingDates_CTE OPTION (MAXRECURSION 32767);


        INSERT INTO [dbo].[ReportingDates] 
        (DateKey, DateFull, CharacterDate, FullYear, QuarterNumber, WeekNumber, WeekDayName, MonthDay, MonthName, YearDay, 
        DateDefinition, WeekDay, MonthNumber)

        SELECT cast (REPLACE(convert(varchar, ReportingDate, 102), '.', '') as int), 
                 ReportingDate,
                 convert(varchar, ReportingDate, 101),
                 YEAR(ReportingDate),
                 MONTH(ReportingDate) / 3,
                 DATEPART(wk, ReportingDate),
                 DATENAME(dw, ReportingDate),
                 DAY(ReportingDate),
                 DATENAME(month, ReportingDate),
                 DATEPART(dy, ReportingDate),
                 DATENAME(month, ReportingDate) + CAST(DAY(ReportingDate) AS varchar) + ',   
                   ' + CAST(YEAR(ReportingDate) AS varchar),
                 DATEPART(dw, ReportingDate),
                 MONTH(ReportingDate)
                 FROM #ReportingDates

        DROP TABLE #ReportingDates
    END
END

GO

--Generate reporting dates 10 years from now.
DECLARE @EndDate Date
SET @EndDate = DATEADD(year, 10, GETUTCDATE()) 
EXEC ecf_GenerateReportingDates @EndDate
GO

This is basically what we will deliver in the fixed version, more or less. And you don’t have to do anything else, manually, when the official version comes out, just run update-epidatabase as usual. 

I hope this will help you to solve the problem with missing reports. If you have any problems with this, please feel free to contact our support service, or to me directly.