Saturday, August 31, 2013

SSRS - How to create a report using Excel as a data source


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

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---------------------------------------------------

12 comments:

  1. Either the drive can be conveyed to our office situated in Bangalore or it very well may be gotten and dropped to customer's and client's area (Currently accessible assistance in Lab recuperation in Bangalore area) or individual visit to our office area, other city or nation is upheld by means of dispatch office.recovery hdd data

    ReplyDelete
  2. A beaming piece of writing can really enlarge your frame of mind. I wish to read much more articles from you.
    Data Science training in Mumbai
    Data Science course in Mumbai
    SAP training in Mumbai

    ReplyDelete
  3. If you have just a few people to verify, the online option is the better choice. If you need to verify a large quantity, you'll need to create a file and mail it in.this website

    ReplyDelete
  4. Amazing Article ! I would like to thank you for the efforts you had made for writing this awesome article. This article inspired me to read more. keep it up.
    AWS certification course in Chennai

    ReplyDelete
  5. This is a wonderful article, Given so much info in it, These type of articles keeps the users interest in the website, and keep on sharing more ... good luck.
    Reactjs Training in Chennai |
    Best Reactjs Training Institute in Chennai |
    Reactjs course in Chennai

    ReplyDelete
  6. Stupendous blog huge applause to the blogger and hoping you to come up with such an extraordinary content in future. Surely, this post will inspire many aspirants who are very keen in gaining the knowledge. Expecting many more contents with lot more curiosity further.

    Data Science Certification in Bhilai

    ReplyDelete
  7. Extraordinary blog went amazed with the content that they have developed in a very descriptive manner. This type of content surely ensures the participants to explore themselves. Hope you deliver the same near the future as well. Gratitude to the blogger for the efforts.

    Data Science Training

    ReplyDelete
  8. Excellent blog and clear explanation. Thanks for sharing with us. Keep maintain this great work in further blogs.
    AI Patasala Machine Learning Training Hyderabad

    ReplyDelete
  9. Once the recovery is finished, Files are checked for data Validity to guarantee Quality of recovery and arrangement of records recuperated. Melbourne Data recovery

    ReplyDelete
  10. When you contact a data recovery service, usually the first thing they will want to know is how the files you want to recover were lost. hard drive recovery melbourne

    ReplyDelete