January 22, 2010 at 4:05 am
Hi
Is it possible to use a xlsx file as a datasource in SSRS 2208 on a Windows Server 2003 x64 ?
I used the following Data Source string but it is not working :
Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Temp\EDC Planning 2010.xlsx;Extended Properties="Excel 12.0 Xml;HDR=YES"
I get the following error message
External table is not in the expected format.
I am able to connect to xls file with the following Data Source
Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\Temp\Book1.xls;Extended Properties="Excel 8.0"
thank you
January 22, 2010 at 9:06 am
egpotus (1/22/2010)
HiIs it possible to use a xlsx file as a datasource in SSRS 2208 on a Windows Server 2003 x64 ?
I used the following Data Source string but it is not working :
Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Temp\EDC Planning 2010.xlsx;Extended Properties="Excel 12.0 Xml;HDR=YES"
I get the following error message
External table is not in the expected format.
I am able to connect to xls file with the following Data Source
Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\Temp\Book1.xls;Extended Properties="Excel 8.0"
thank you
No but in SSRS 2008 you have something close Table can be a datasource so use the import/export wizard to import your Excel and use the table as datasource. And no I have not used it.
Kind regards,
Gift Peddie
January 25, 2010 at 2:34 am
January 25, 2010 at 6:51 am
egpotus (1/25/2010)
using SSRS or any other tools is not an option, I am just wondering why an XLS file can be used as a data source but not an XLSX file? There must be a way.....
I am not aware Excel any version can be used as a datasource, I think it is for export and not a datasource.
Kind regards,
Gift Peddie
January 25, 2010 at 7:49 am
January 25, 2010 at 8:09 am
egpotus (1/25/2010)
well I have been using XLS file as a datasource and it is working well.
I have also seen a user who uploaded saved Excel files in ReportServer and viewed that as reports. Excel from version XP takes Access SQL so I think SSRS 2008 is using it as a table datasource. Why the new one is not working may be related to SSRS current export to Excel uses Excel 2003 and not 2007. You could test drive SQL Server 2008 R2 it may work but it supports Office 2010.
Kind regards,
Gift Peddie
November 24, 2010 at 1:12 am
Hi egpotus,
Could you please let me know how to use Xls as a datasource for SSRS. I've been trying hard to implement this. I'm stuck at a stage where the Datatasource Test Connection is throwing an error. :ermm:
Below are the steps that I've followed,please take a look:
I'm using SSRS 2008 on Windows Server 2008 64-bit.
----I've configured the ODBC datasource from Windows\SysWOW64\odbcad32
----I Opened the ReportBuilder3.0
-> Table or Matrix Wizard
-> in "Choose a dataset screen", I'm selecting Create a dataset - then clicked on Next
-> Clicked New
-> in Datasource properties I'm selecting connection type as ODBC. Now, I'm building the connection
string - I'm selecting the previously configured System DSN for "Use user or system data source
name". Test connection is successful, OK
-> Coming back to Data Source Properties screen, I click on Test Connection it gives the error "ERROR
[IM014] [Microsoft][ODBC Driver Manager] The specified DSN contains an architecture mismatch
between the Driver and Application"
Please help me out, any help is immensely appreciated.:-)
Thanks in advance!
November 24, 2010 at 10:43 am
I am not sure DNS is the reason for your error but you have three choices of using Excel with ReportBuilder 3.0. One open the Excel in BIDS create RDL and consume the RDL in ReportBuilder or use Office Ribbon control and Excel Pivot tables. I would try option one and two because option one will give you valid solution. You know Office 2010 comes in x64 so that is also another option.
http://www.microsoft.com/sqlserver/2008/en/us/report-builder.aspx
Kind regards,
Gift Peddie
November 30, 2010 at 6:10 am
Thanks GP 🙂
Now, I've decided to use the good old SSRS 2005! So, I've created the excel datasource and then I click on ReportBuilder.
Here, on the right handside the first section says, "Select the site or server", which I've done. Below that it says "Select a source of data for your report". In this section I can only see ReportModels and there in no way I can select the excel sheet or its datasource!!
I really don't understand why this is happening. Please enlighten me guys!!
Thanks in advance!
November 30, 2010 at 7:30 am
KingCobra (11/30/2010)
Thanks GP 🙂Now, I've decided to use the good old SSRS 2005! So, I've created the excel datasource and then I click on ReportBuilder.
Here, on the right handside the first section says, "Select the site or server", which I've done. Below that it says "Select a source of data for your report". In this section I can only see ReportModels and there in no way I can select the excel sheet or its datasource!!
I really don't understand why this is happening. Please enlighten me guys!!
Thanks in advance!
If I remember correctly SSRS 2005 Report Builder takes only View and Stored Procs for datasource, so I think you should use SQL Server 2008 Report Builder which is more flexible. I also think you need to create Report Models in Report Builder 2005.
Kind regards,
Gift Peddie
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply