SSRS report with excel data source

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.

Answer 1

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

Answer 2

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		
			

Answer 3

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.

Answer 4

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.

Answer 5

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.