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.
| 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 datasetsAlternatively 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 MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
 |
|
|
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 datasourcehow 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 |
|
|
|
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 MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
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. |
|
|
|
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 MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
|
|
|
SSRS with Excel Data Source