I have a request from a user that is similar to the one listed here http://forums.informationbuild...647039336#2647039336

My user would like to use an Excel file that will contain a list of IDs that would be used to filter the report. Due to the nature of the report I am using a SQL passthru at the point where the filtering by this parameter occurs, so I need the parameter in a format that will work in SQL. Ex 'ID_1','ID_2','ID_3'...

Now the Excel file will not be static and as I understand it to use the Excel file as a data source it would require the file having a constant size or any additional rows would not be read in by the adapter. (I have never used an Excel adapter so I may be completely wrong on this.) Either way the user will need to be able to update the file whenever they make any changes.

I am trying to figure out some way that will pull the values from the Excel spreadsheet and put them into a parameter in the proper format for SQL.

I cannot seem to find anything on how to accomplish this. If anyone has done something similar or knows where I could find information that would point me in the right direction I would really appreciate it.

This message has been edited. Last edited by: Nathaniel,

Hi Nathaniel,

I cannot remember whether Excel Direct Retrieval was available in 7.7.03 (might have been 7.7.05 when it became available?). If it is then use that via "upload datafile" from the server console or in Dev Studio.

Otherwise, if I remember correctly, you will need to set the MS Excel file up as a System DSN under ODBC on the reporting server.

The MS Excel data will need to be declared as a NAMEd range that has the bounds of the data required. If the user adds additional rows of data then they will need to resize the named range - I once had some VBA code that did this because you can never rely on a user to remember to do it.

The other thing is that the range should have column names that are unchanging because once you have created the synonym the column names will be those declared within the synonym.

My memory may be playing tricks and some of that may be incorrect.

T

Looks like direct retrieval is available for 7.7.03 - link to documentation

Once you have the data source setup then you can table it out as normal.

T

Perfect! That is exactly what I needed to know. I guess I was searching with the wrong terms haha.

Thank you very much for the help Tony.

You're welcome, glad it helped.

T