July 13, 2011 at 9:20 am
Hi forum,
I am using SQL Server 2005, i would like to generate SSRS report from one of my excel file.
I have created database and i am getting results from the sheet with below command but i like to add where clause in to the query. how to add this?
I have created DSN and try to write a query.
select * from [download$]
here i like to add where clause, because i dont want all records of the file.
Can anyone has tried or have idea how to get this?
Thanks.
Chintesh
July 13, 2011 at 12:34 pm
You should be able to just add a simple where clause:
select * from [download$]
where column1 = 'value'
You can use all of the regular sql syntax from here to filter the dataset. Let us know if there is a specific column or set of results you are trying to filter out.
Steve
July 15, 2011 at 12:44 am
sdvoranchik (7/13/2011)
You should be able to just add a simple where clause:select * from [download$]
where column1 = 'value'
You can use all of the regular sql syntax from here to filter the dataset. Let us know if there is a specific column or set of results you are trying to filter out.
Steve
Adding the WHERE clause will work, but using all of the regular SQL syntax won't. A lot of SQL functions can't (unfortunately) be used with the JET provide when connecting to Excel.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
July 15, 2011 at 7:10 am
Here's two additional options that may work. For the data source, instead of using a DSN, create a SQL Server data source. The database you select isn't really important, you could use one of your source dbs or I even tested against Master. Use OPENROWSET to connect to the Excel file. If you have 32 bit you will probabably need to use the Jet Driver. If you use 64 bit you'll probably need the Ace driver.
32 Bit (no where clause)
SELECT *
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\Temp\LGCD.xls',
'SELECT * FROM [LGCD$]')
64 Bit (with where clause, it can go inside the OPENROWSET, or pulled out and put in line with the SELECT * FROM OPENROWSET WHERE ...)
SELECT *
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0;Database=C:\Temp\LGCD.xls',
'select * from [LGCD$] WHERE [Expenditure Type] = ''Subcontractor Amount''')
Now you can use native SQL syntax
The other option is inside your report if you select the table and view table properties there is a Filters tab. In here you can select one of the report fields and apply a filter to reduce the records.
I hope one of these works, good luck,
Steve
July 18, 2011 at 4:33 am
Thanks, I will try and let u know.
I am using it with SSRS. I have to create parameters and validation on that.
Chintesh
July 18, 2011 at 11:36 am
Hi,
When i am trying to use it with parameters it gives error as given below.
TITLE: Microsoft Report Designer
------------------------------
An error occurred while executing the query.
ERROR [07002] [Microsoft][ODBC Excel Driver] Too few parameters. Expected 1.
------------------------------
ADDITIONAL INFORMATION:
ERROR [07002] [Microsoft][ODBC Excel Driver] Too few parameters. Expected 1. (ACEODBC.DLL)
------------------------------
I have used it as below.
SELECT *
FROM [MASTER_LIST$]
WHERE newkid = @kid
@kid is parameter. This parameter will have values with below given query.
select newkid from [MASTER_LIST$]
thanks for your support.
Warm Regards,
Chintesh Soni
July 18, 2011 at 1:16 pm
Assuming you are using the Ace driver, I think this syntax works the best. Pulling the WHERE clause out worked better with SSRS. If you are trying to use something different please let us know.
SELECT *
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0;Database=C:\Temp\LGCD.xls',
'SELECT *
FROM [MASTER_LIST$]')
WHERE newkid = @kid
July 19, 2011 at 4:50 am
Thanks for your help.
I am not using ace but i m using DSN.
Warm REgards,
Chintesh
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply