1. Background
The purpose of this article is to describe how to create
a report using Excel as a data source for the report. We are going to use ODBC
provider for excel to get the data for the reports.
2. Step by Step procedure to create Excel as Data source for Report:
·
Open the Reporting services
solution in BIDS/SQL Server Data Tools and
·
Go to Shared Data Sources folder then Right click and click on Add New Dataset
·
It will start Data Source
Wizard, give the name of Data Source , give Type as ODBC and Click on Edit to create connection string for
the Excel:

·
Once you click on Edit, It
will open Connection property Window, select use connection string and click on Build.
·
Go to File Data Source and go to the location of the source excel file by
using browse button in Look in:

·
Click ok. It will create the
connection string for the excel

·
Click ok and your dataset
for excel source has been created.

·
In the credential window,
select the credential type as per your requirement. Here I have selected
Windows Integrity security:

·
Now right click on Shared
Dataset and click on New Dataset:

·
Give the name of Dataset and select Data
source as ExcelDataSource that we
have just created. Write the query for the excel:

Note : Check the sheet name in the excel before writing queries
Note : Check the sheet name in the excel before writing queries
3. Steps to create report
·
Now create a report and map this newly
created dataset “ExcelDataSet” to the
report.
·
Add a tablix to the report layout and map the
fields from the attached dataset.

·
Do formatting as per your requirement and run
the report. Below is the resultant report:

4.
Conclusion
By using the above steps, we can create SSRS report using excel as data
source
----------------------------------------------------End
of Article---------------------------------------------------
It is the best information for this topic and kindly updating here...
ReplyDeleteOpenspan Online Training
Openspan Online Course
Matlab Training in Chennai
Leadership Training in Chennai
Highly appreciable regarding the uniqueness of the content. This perhaps makes the readers feels excited to get stick to the subject. Certainly, the learners would thank the blogger to come up with the innovative content which keeps the readers to be up to date to stand by the competition. Once again nice blog keep it up and keep sharing the content as always.
ReplyDeleteData Science training