Quan Mai
Jan 8, 2015
  2271
(2 votes)

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

Comments

K Khan
K Khan Jan 8, 2015 11:44 AM

helpful, as always!

Jan 9, 2015 01:49 PM

EPiServer's own "millennium bug".

Quan Mai
Quan Mai Jan 12, 2015 04:57 PM

Yes, we called it Y2015 bug!

Quan Mai
Quan Mai Feb 24, 2015 10:32 AM

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

May 18, 2015 02:15 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.
Latest blogs
How to fix scheduled job 'Remove Abandoned BLOBs' if it keeps failing

Optimizely inlcudes a job named 'Remove Abandoned BLOBs'. This post will help you fix it if it's no longer working.

Henning Sjørbotten | Sep 26, 2023 | Syndicated blog

Optimizely Web Experimentation Metrics

Within the domain of Optimizely Web Experimentation Metrics, the emphasis is on objective key performance indicators (KPIs) selected to assess an...

Matthew Dunn | Sep 23, 2023 | Syndicated blog

Optimizely For you Intranet

Having been at Optimizely and in the CMS industry for nearly 16 years I have seen clients’ intranet requirements go from a simple site just to hous...

Robert Folan | Sep 22, 2023

Vulnerability in EPiServer.GoogleAnalytics v3 and v4

Introduction A potential security vulnerability was detected for Optimizely Google Analytics addon (including EPiServer.GoogleAnalytics and...

Bien Nguyen | Sep 20, 2023