SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Analysis Server and Reporting Services (2008)
 SSRS with Excel Data Source
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Villanuev
Constraint Violating Yak Guru

406 Posts

Posted - 05/27/2013 :  01:06:12  Show Profile  Reply with Quote
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

India
52249 Posts

Posted - 05/27/2013 :  01:20:29  Show Profile  Reply with Quote
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

406 Posts

Posted - 05/27/2013 :  02:13:50  Show Profile  Reply with Quote
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

Edited by - Villanuev on 05/27/2013 02:58:01
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 05/27/2013 :  03:55:41  Show Profile  Reply with Quote
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

406 Posts

Posted - 05/27/2013 :  04:18:09  Show Profile  Reply with Quote
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

India
52249 Posts

Posted - 05/27/2013 :  04:23:06  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000