Hi guys,

I am trying to create a basic report in SSRS, with 6 columns but then we receive an excel spreadsheet with different account numbers daily and I am requested to create a report based on those account numbers. so how can I create a shared folder for the users to drop their excel spreadsheet  there and make my SSRS only filter the result set based only on those account numbers?

Hi

Please refine your question for better understanding or explain with some example.

Hi Sarang,

Thanks for your reply, I have to create a report that will automate manually imports to SQL  and extract data from different tables. How we do it currently a user sends us an excel spreadsheet with different client codes and ask for certain records in the system so we will import that excel to SQL and join that table with different tables in order to get the results required. e.g i will receive excel spreadsheet via an email  with ID numbers, client codes and account numbers and out of that file i have to extract all the required fields using matching column from both the Client files and  the database. I have created a SSRS now but struggling to accommodate the client file in order to filter my report. Please note the client files they change every time  in terms on of required ID numbers or client codes.

Thank you.

Hi Sql Pros,

You got to to have two data sources  and two datset

1.One datasource will connect to your  excel sheet and another to Sql datbase.

2. This will eventually lead you to create two datasets one with excel columns and another with information from sql tables.

Then you can do lookup function of SSRS to compare data from two datasets and filter data accordingly.

Hi SQL Pros

If you are on SQL Server 2008 onwards, then Change Data Capture Feature of SQL Server will help you to track each record you received through Client in Excel format (Whether the record is new or existing, if existing then whats the change).
You have to activate this feature on database and then on the table level where you dumps Excel data.

Rest all it will take care and You just need to form Select Query with proper Where clause.

Details of this feature is given available here:

https://technet.microsoft.com/en-us/library/bb522489(v=sql.105).aspx

Hi SQL Pros, 

According to your description, you want to extract the required fields from an excel spreadsheet based on the database fields, right? 

In Reporting Services, if we want to display the field based on another dataset field, we can use Lookup() function into the expression. In you scenario, since you want to extract the required data based on the field (like ID numbers or client codes) which stored in a database, please refer to the following expression: 

Lookup(source_expression, destination_expression, result_expression, dataset)

The above expression will return the required field in "dataset". For more detail information, please refer to here. 

For your requirement, the report element has an AutoRefresh property, if we set its value to 60, and then the report will auto-refresh every 60 seconds. 

If you have any other question, please feel free to ask.

Regards,
Shrek Li