locked
SSRS report with excel data source RRS feed

  • Question

  • Hi Guys,

    I have a requirements to create a report in SSRS using SQL server and excel file as data source.. also sharepoint data. have already created the SSRS report with SQL as data source. i would like to get records from excel file and sharepoint data incorporate to my created ssrs report. any idea guys if this is possible? thanks you.

    Monday, May 27, 2013 2:46 AM

Answers

  • Hi Lenoj,

    Yes, you can do it easily .

    Have a look at below links :

    http://sqlserverpedia.com/blog/sql-server-bloggers/using-excel-as-a-reporting-services-datasource-2/

    http://weblogs.sqlteam.com/joew/archive/2008/08/22/60695.aspx

    http://msdn.microsoft.com/en-us/library/ee633650.aspx

    http://www.mssqltips.com/sqlservertip/2068/using-a-sharepoint-list-as-a-data-source-in-sql-server-reporting-services-2008-r2/

    Let me know in case of any issue.


    Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread. Regards, Randhir

    • Proposed as answer by V Karthik Monday, May 27, 2013 5:30 AM
    • Marked as answer by Lenoj Monday, May 27, 2013 7:58 AM
    Monday, May 27, 2013 5:22 AM
  • check this link. Basically you need a common column to lookup on , which in this case is ItemId and expression would be like this -

    =Lookup(Fields!ItemId.Value,FieldsItemId.Value,Fields!OrderQty.Value,"Dataset2")

    You would need to put that expression when you are using DataSet1 for populating data.

    -http://blog.hoegaerden.be/2010/05/16/looking-up-data-on-different-sources/


    Regards,
    Karthik

    Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread.

    • Marked as answer by Lenoj Monday, May 27, 2013 7:58 AM
    Monday, May 27, 2013 7:00 AM

All replies

  • Hi Lenoj,

    Yes, you can do it easily .

    Have a look at below links :

    http://sqlserverpedia.com/blog/sql-server-bloggers/using-excel-as-a-reporting-services-datasource-2/

    http://weblogs.sqlteam.com/joew/archive/2008/08/22/60695.aspx

    http://msdn.microsoft.com/en-us/library/ee633650.aspx

    http://www.mssqltips.com/sqlservertip/2068/using-a-sharepoint-list-as-a-data-source-in-sql-server-reporting-services-2008-r2/

    Let me know in case of any issue.


    Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread. Regards, Randhir

    • Proposed as answer by V Karthik Monday, May 27, 2013 5:30 AM
    • Marked as answer by Lenoj Monday, May 27, 2013 7:58 AM
    Monday, May 27, 2013 5:22 AM
  • thanks for your reply.

    Just created the report with 2 datasource one from SQL and the other is Excel.

    Please see blow my requirements. need to get the data from excel file..

    DataSource1--SQL datasource
    	Dataset1
    	Itemid--field
    		
    		
    DataSource2--Excel Datasource
    	Dataset2
    		Itemid--field
    		OrderQty--field
    		
    Need to get the orderQty of excel datasource
    how to use the LookupSet or Lookup		
    			

    Monday, May 27, 2013 6:41 AM
  • check this link. Basically you need a common column to lookup on , which in this case is ItemId and expression would be like this -

    =Lookup(Fields!ItemId.Value,FieldsItemId.Value,Fields!OrderQty.Value,"Dataset2")

    You would need to put that expression when you are using DataSet1 for populating data.

    -http://blog.hoegaerden.be/2010/05/16/looking-up-data-on-different-sources/


    Regards,
    Karthik

    Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread.

    • Marked as answer by Lenoj Monday, May 27, 2013 7:58 AM
    Monday, May 27, 2013 7:00 AM
  • thanks for this very informative info.

    By the way, I may not be able to use this because my SQL software installed is only 2008 not R2.

    it is applicable to 2008?

    Is this the same process if my datasource is sharepoint data? thanks.

    Monday, May 27, 2013 8:02 AM
  • Yes, it is applicable to 2008 version as well. You would need to follow the same process.


    Regards,
    Karthik

    Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread.

    Monday, May 27, 2013 8:49 AM