Views: 1888
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 . 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] 


CREATE PROCEDURE ecf_GenerateReportingDates
@EndDate DATE
    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()
	--We will add the start date as the day after the max date in ReportingDates
		SET @StartDate = DATEADD(day, 1, @StartDate)

    IF (@EndDate > @StartDate)
        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), 
                 convert(varchar, ReportingDate, 101),
                 MONTH(ReportingDate) / 3,
                 DATEPART(wk, ReportingDate),
                 DATENAME(dw, ReportingDate),
                 DATENAME(month, ReportingDate),
                 DATEPART(dy, ReportingDate),
                 DATENAME(month, ReportingDate) + CAST(DAY(ReportingDate) AS varchar) + ',   
                   ' + CAST(YEAR(ReportingDate) AS varchar),
                 DATEPART(dw, ReportingDate),
                 FROM #ReportingDates

        DROP TABLE #ReportingDates


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

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 11:44:51 AM)

helpful, as always!

( 1/9/2015 1:49:24 PM)

EPiServer's own "millennium bug".

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

Yes, we called it Y2015 bug!

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

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

( 5/18/2015 2: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)


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.