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..
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.
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.
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.