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

15 comments:

  1. The post is well written by the author. Daisy

    ReplyDelete
  2. It requires broad business demonstrating and may take a very long time to structure and fabricate. Data Analytics Courses

    ReplyDelete
  3. Business Analytics Certification Course Overview
    The Professional Certification in Business Analytics is a foundation course for students and professionals who want to develop niche data skills for their chosen industry domain or function area. Become a Business Intelligence and Data Visualisation expert and surge ahead in your career. The nine-day Business Analytics certification course covers all the essential Analytical and Statistical techniques for effective business decision making. This programme introduces the student to the basic concepts of Python language.
    business analytics course

    ReplyDelete
  4. It has fully emerged to crown Singapore's southern shores and undoubtedly placed her on the global map of residential landmarks. I still scored the more points than I ever have in a season for GS. I think you would be hard pressed to find somebody with the same consistency I have had over the years so I am happy with that.
    360DigiTMG data science course in kompally

    ReplyDelete
  5. Wow i can say that this is another great article as expected of this blog.Bookmarked this site.. download

    ReplyDelete
  6. I was surfing the Internet for information and came across your blog. I am impressed by the information you have on this blog. It shows how well you understand this subject. hikedatabase.com/united-states/hiking-in-rhode-island/

    ReplyDelete
  7. 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.
    Correlation vs Covariance
    Simple Linear Regression
    data science interview questions
    KNN Algorithm
    Logistic Regression explained

    ReplyDelete
  8. Positive site, where did u come up with the information on this posting?I have read a few of the articles on your website now, and I really like your style. Thanks a million and please keep up the effective work. excel budget spreadsheets

    ReplyDelete
  9. With so much overstated negative criticism of the corporate culture in the media, it is indeed bracing to have an upbeat, positive report on the good things that are happening. Wish to read some more from you!
    Data Science training in Mumbai
    Data Science course in Mumbai
    SAP training in Mumbai

    ReplyDelete
  10. very well explained. 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.
    Correlation vs Covariance
    Simple Linear Regression
    data science interview questions
    KNN Algorithm
    Logistic Regression explained

    ReplyDelete
  11. This blog is so nice to me. I will keep on coming here again and again. Visit my link as well.. Personal CRM

    ReplyDelete