Quan Mai
Jan 8, 2015
  1944
(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
Plug-in manager is back in CMS 12

Plug-in manager is back in the UI, what is it and how can i use it?

Luc Gosso (MVP) | Oct 6, 2022 | Syndicated blog

Display Child Pages in Content Delivery API Response

The below example will implement an instance of IContentConverterProvider to customise the serialisation of PageData and output child pages in the...

Minesh Shah (Netcel) | Oct 4, 2022

Bring the Report Center back in Optimizely CMS 12

The Report Center has been a part of Optimizely CMS since its first debut in version 5R2 in 2008, but in CMS 12, it's removed! Don't despair! Make...

Tomas Hensrud Gulla | Oct 4, 2022 | Syndicated blog

Customizing Property Lists in Optimizely CMS

Generic property lists is a cool editorial feature that has gained a lot of popularity - in spite of still being unsupported (officially). But if y...

Allan Thraen | Oct 2, 2022 | Syndicated blog