Views: 1789
Number of votes: 2
Average rating:

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.

Jan 08, 2015

K Khan
(By K Khan , 1/8/2015 12:44:51 PM)

helpful, as always!

Petri.isola
(By Petri.isola, 1/9/2015 2:49:24 PM)

EPiServer's own "millennium bug".

Quan Mai
(By Quan Mai, 1/12/2015 5:57:24 PM)

Yes, we called it Y2015 bug!

Quan Mai
(By Quan Mai, 2/24/2015 11:32:05 AM)

A kind reminder is the bug is now fixed and will be released in upcoming 8.9 version.

Guest
(By Guest, 5/18/2015 4:15:53 PM)

This generates some strange values in the DateDefinition column due to how the sp is formatted.

DATENAME(month, ReportingDate) + CAST(DAY(ReportingDate) AS varchar) + ',
' + CAST(YEAR(ReportingDate) AS varchar),

generates (with newline)

"May22,
2015"

DATENAME(month, ReportingDate) + CAST(DAY(ReportingDate) AS varchar) + ', ' + CAST(YEAR(ReportingDate) AS varchar),

would generate

"May22, 2015"

Isn't this the correct value?

Please login to comment.