Problem with Date Output in Excel 2007

When we export data mining output including dates through Reporting Services to an Excel spreadsheet, Excel 2007 subtracts 4 years from the dates. Example, the date 11/16/2006 appears in Excel 2007 as 11/16/2002. How should this be handled? Workaround, bug fix, patch,etc.? Thanks, Sam

Sounds very weird to me.

The data renders correctly in Reporting Services, but incorrectly when exported?

Does this only happen with reports built over DMX queries? Do other reports, using SQL or MDX, render correctly in Reporting Services and in Excel?

thanks for any additional clarification you can give.

|||

Thanks, Don. The dates appear correct in the output through Reporting Services that we use as a Front End. When we export to Excel, it comes into Excel 2007 as 4 years earlier. Are the dates in Excel 2007 based on 1900 rather than 1904, and could that be the source of the problem? I will check with the developers on Tuesday with the answer to your other questions about MDX, etc. and will experiment with my SQL queries to see how Excel 2007 handles them so we can figure out if it is an Analysis Services export problem or an Excel 2007 problem.

Sam

|||

Don, Excel 2007 has no trouble bringing in dates from Excel 2003 spreadsheets or from the text output of SQL queries.

I posed your question to our developer. His reply:

Exporting to Excel or any other form is a built-in feature of Microsoft reporting services.I guess it has nothing to do with the way data is generated for the report.Moreover reports are accessing data from SQL server tables and not from the MDX/DMX.

The sequence is data is pumped from analysis server to SQL server table by using the DMX queries .Reports access data from these tables.

I hope this is helpful information--it would be surprising if no one else is having this difficulty witht the built-in export from Microsoft reporting services. Does this provide you with sufficient information to attempt to replicate the problem?

Sam

|||

Thanks. Let's see if we can narrow it down further. (We cannot reproduce the problem here, so I'm sorry but we have to ask you quite a few of these questions to get to the root of the matter.)

Are the results of the DMX queries correct when stored in SQL Server? If yes, and the results are correct when viewed in the report, then I would conclude that the problem is in the Reporting Services Excel export functionality. In that case, we would move this thread to the reporting services forum. (We have already alerted them to issue.) It would not be related to the data mining functionality.

However, if the results stored in the SQL Server are wrong, then it may well be an issue somewhere with the DMX query and we could look into that further.

hth