Create SSRS report from EXCEL DataSource

Step 1:

Go to Report Data and create data source

Step 2:

Provide data source name

Click on edit button and provide data source type as shown in bellow

 

Step 3:

Click on advanced tab

Click on new button

Step 4:

Click on next

Step 5:

Select proper drivers for data source and click on next

Click on finish button

Step 6:

Click on excel source section and select data source file

Step 7:

Go to data set tab, create new data set

Step 8 :

Select data source and provide SQL query as shown in bellow.

select * from [SalesOrder$]

Here [SalesOrder$] is excel sheet name

Click on fields tab and check list of fields

Step 9:

Go to design pane and add table as shown in bellow

Step 10:

Drag and drop required fields

Click on preview