Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Analysis Server and Reporting Services (2008)
 SSRS with Excel Data Source

Author  Topic 

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2013-05-27 : 01:06:12
Hi,

I just have created an SSRS report with SQL as data source.
Is it possible to JOIN on the data retrieved from the Excel Source with the data existing in the SQL Database and produce a report? any idea guys.. Thank you..

jov

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-27 : 01:20:29
yep. You can create a second dataset to pull data from excel source. Then inside report you can use LOOKUP or LookupSet functions to merge the data from datasets
Alternatively you could do this in query behind by using distributed query like OPENROWSET and bring all data in one dataset itself. This is my preffered method as doing this at database engine is much better than doing merge at report server end. Report in this case will be simple report which points to the single dataset.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2013-05-27 : 02:13:50
Thanks Visakh.

I dont have the access permission to use the openrowset but temporary i will use the first option.
Do i need to create a seperate datasource for the excel then i will create a dataset? how about a sharepoint datasource it is also feasible to join so meaning i will have 3 different source data.

DataSource1--SQL datasource
Dataset1
Itemid


DataSource2--Excel Datasource
Dataset2
Itemid
OrderQty

Need to get the orderQty of excel datasource
how to use the LookupSet or Lookup


btw, here is my query in my excel datasource. how could i place a where clause condition.
tried this but got an error. need to filter from column1 with the number 1 and filter in column2 with the name that start with HTC.

SELECT *

FROM [Master Parts List$]
Where Column1=1
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-27 : 03:55:41
you need to create a separate datasource for excel. SHarepoint lists also you can use as datasource from SSRS 2008 R2 onwards.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2013-05-27 : 04:18:09
Thank for this very informative input.
btw, the SSRS installed in my PC is not yet 2008 R2. well try to upgrade. thanks.

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-27 : 04:23:06
Ok...Then you should be able to use sharepoint lists as well as source

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
   

- Advertisement -