Home Dashboard Directory Help
Search

Merging / Linking datasets on report level (SSRS 2008) by Frank van Zuilen


Status: 

Active


224
1
Sign in
to vote
Type: Suggestion
ID: 419819
Opened: 3/2/2009 12:18:42 AM
Access Restriction: Public
Duplicates: 473573
2
Workaround(s)
view

Description

At this moment it is not possible to merge/link multiple datasets data (from different datasources) into a single “data region”. Most common workarounds are rewriting the different queries into one single query which introduce unnecessary complexity. Simple example:

Dataset 1 (Actual), Sales Region/Country Actuals:
Europe, Netherlands -> 10
Europe, Germany -> 20
America, USA -> 20
Africa, Egypt -> 10

Dataset 2 (Budget), Sales Region Budgets:
Europe -> 35
America -> 25
Asia -> 30

By having the possibility to merge/link the data sets on common dimensions, in this case the “Sales Region” it should be possible to set up a data region for variance analysis like:
Sales Region    Actual         Budget        Variance
Europe                    30        35        5
America                 20        25        5
Africa                 10        -        -10
Asia        -        30        30

Most elegant solution would be to “introduce” a new dataset which merges the two datasets into a “virtual” dataset. Other solution could be to introduce new functions which allows to aggregate data from another dataset with some kind of where clause: sum(<Budget>,”Dataset2”, dataset2.SalesRegion= SalesRegion). In this last option it should be possible to have some kind of “outer join” option or “union all” option to determine the set of sales regions: All regions in both datasets (union all, like above), All common regions (intersect, Europe / America), Only Sales Regions from dataset1 etc.

Business Objects provides this kind of functionality as well. So it would be a "plus" for SSRS in selection projects as well.
Details
Sign in to post a comment.
Posted by RCC, Inc on 3/5/2013 at 8:14 AM
The new lookup function is a great feature, however; it doesn't solve the issue when each data set is needed to return more than a single record. For example, a Hardware detail report with a single Hardware record of fields for the first data set and a second data set with multiple warranty records and a third data set with multiple maintenance records.
Posted by Andrew781 on 2/14/2012 at 12:45 PM
This issue is a constant source of problems for me in SSRS given that I am dealing with replicated data environments across multiple servers.

In my case I might have 2 or 3 datasets which are identical except that they have different data sources. If I want to combine these sets in a report, it's a problem; if I want to combine them as the source for a parameter? Big problem.

I don't see it being too difficult to provide the ability for SSRS users to create a DataSet (x) that is the result of DataSet(Y) Union DataSet(Z).
Posted by Nishantg on 9/27/2011 at 1:07 AM
I haven't found the feature of combing datasets which come from different datasources in SSRS 2008 R2. SSRS team please consider this feature in your future release as its a very usefull.
Posted by Ojas Maru on 10/21/2010 at 11:21 AM
Lookupset is a possible option....

Guess wat, there is no Sum function on top of LookupSet. LookupSet returns a array and you cant Sum a array.
Posted by Greg Low - Australia on 10/13/2010 at 3:56 AM
Don't the lookup functions added in SQL Server 2008 R2 address the basic issue here? You can iterate over one dataset and lookup values from another dataset.
Posted by SQL-BI-MCTS on 7/19/2010 at 7:15 PM
definitely a needed functionality.. I thought R2 will atleast have this. SSRS still far behind compared to BO & Crystal reports. Its hard to convince clients without basic features like these. They say they would spend more money and go for BO or Cognos when implementing on large scale :(
Posted by Bill Mittenzwey on 6/22/2010 at 8:38 AM
The lookup functions added in R2 are a definate step in the right direction. One of the, "lookup," is spot on, but the lookupset still leaves something to be desired. The problem is not in the function itself, but in the lack of anything useful that you can do with the resultset. For this function to be useful, we will need to be able to bind the results to a group or detail band. I have come across many situations where there is still a little data sitting in a foxpro table or some other legacy format that needs to be UNION'd in with newer data in SQL. In those cases, I still need the ability to merge in data and bind it to the report. The only other choice is to use linked servers to an oledb or odbc source (a practice which was BANED from my office sever years ago after SQL server processors got hung waiting for an invisible fox dialog box to be clicked)
Posted by Valentino Vranken on 5/23/2010 at 8:03 AM
What Markos is referring to are probably the new lookup functions. I wrote an article about them a little wile ago: http://blog.hoegaerden.be/2010/05/16/looking-up-data-on-different-sources/
Posted by mekabthegreat on 4/29/2010 at 6:45 AM
Re: _Markos_

"This feature is availible with SSRS 2008 R2!"

Is this legit? I am trying to find out for certain if this works in 2008 R2... I have to make a strong case for the company to upgrade. Can anyone show me where this additional feature is documented in the new release?
Posted by P Wong on 4/24/2010 at 8:10 AM
Hi Markos:

You mention the function is available in R2, is it in Report Builder 3.0 as well.

Thanks
Posted by _Markos_ on 4/23/2010 at 3:21 PM
This feature is availible with SSRS 2008 R2!
Posted by wesgoad on 4/16/2010 at 5:27 AM
This is a standard feature in Crystal reports and I use it all the time. I don't understand why something like this is even being questioned as an addition. It should be a standard option.
Posted by Josh5 on 3/8/2010 at 2:15 PM
This is a basic function of Business Objects and has been since very early versions. As a consultant having a client where they have BO and SSRS, having this limitation is hard for me to tell them to drop BO and go all SQL Server. Without this simple function I need to create a stored procedure that is either one very large query with lots of joins or several in line queries both of which result in poor performance. In BO multiple queries run concurrently and join on the report with far better performance.
Posted by Sumo [GB] on 2/11/2010 at 11:52 PM
I'm a business (not very tech savvy) user. Having just upgraded from Report Builder 1.0, I thought my every dream was about to be fulfilled. Close...but not quite a cigar. It's nowhere near as easy to use as the last version, but the increased functionality means I'm happy to go through the pain of a steep learning curve. If I could just join 2 datasets, then I'd be a very happy camper indeed. I thought I'd found a work around - in a table using dataset 1, I inserted another column and manually entered a query. If you do that, then you can reference other datasets used in the same report. I've got some data to display, but not quite got it right so far (I think I may need to learn SQL, to understand the syntax of the query better, and get the data right). Ho hum...time to start another day of frustration, hopefully leading to success. Microsoft - listen to us & remove this frustration. Just let us link things...please.
Posted by R Lancaster on 1/27/2010 at 6:42 AM
This would be extremely helpful, significantly extending the usability of SSRS to OLE DB data sources where the source system is not able to provide the data in a single query.
Posted by Melissa Field on 1/25/2010 at 9:48 AM
I completely agree, to faciliate ad hoc reporting. We need a graphical interface to "connect" the two data sets. Business users need an intuitive way to do this that doesn't require the technical knowledge.
Posted by rohitkumar1 on 10/30/2009 at 7:08 AM
Agree, this would be a very helpful feature, I have an SP that returns data for a particular date, to compare variations over a period, I can call the same SP with two different dates and link them in the report.
Posted by Tuan Nguyen on 10/27/2009 at 4:51 AM
Hi, I have seen you have added the Lookup, LookupSet and MultiLookup functions to the RDL expression language but the way they work is not ideal. Especially when you want to use them in Report Builder as these are business users creating thes reports. I would like to see

1. There should be a graphical interface to "connect" the two data sets
2. These functions should be allowed to be used in a expression for a calculated field, allowing the user to perform a inner, left, right or full outer join between the two data sets so that you can utilize the lookups in all data regions.

This way users don't have to use the Join function to display the values in a textbox for LookupSet and MultiLookup.
Posted by colin leversuch-roberts on 7/8/2009 at 3:37 AM
It's not so much you can't do this - it's just it would be better if it could be done within SSRS.
e.g. If I want to merge the results of the same query sent to three servers ( data sources ) I can only mange this by setting linked servers from one of the servers and then union the results in a proc or query ( or I coul set lots of linked servers from SSRS ) I'd just prefer to be able to do this without linked servers.
I have the same issue with generating a union statement to show two datasets ina graph, say this week vs last week.
It would be better to be able to do this with SSRS - hope you're still monitoring SSRS Team ?
Posted by Valentino Vranken on 5/29/2009 at 4:19 AM
I agree! Right now I am even having an issue getting other developers (who work on a DB2 database where the data is coming from) convinced that I really need all the data in the same query (which results in a slow query due to many joins and a not-so-optimal optimizer). Voted 5.
Posted by John Lynn on 5/27/2009 at 2:14 PM
This is a VERY important item! Please vote for this item by moving mouse over the stars under Rating.
Posted by Microsoft on 3/27/2009 at 9:52 AM
Thank you for your suggestion.

We are indeed considering adding this kind of functionality in a future release of Reporting Services. We are also monitoring the customer vote count on this particular suggestion to gauge the relative community demand compared to other suggestions.

Thanks,
Reporting Services Team
Sign in to post a workaround.
Posted by MarkGStacey on 5/11/2012 at 1:41 PM
Hi

There is a workaround I tried, which seemed to work in my limited example:
http://www.bidn.com/blogs/MarkGStacey/bidn-blog/2714/aggregated-lookups-in-ssrs-2008r2

Basically, use a code piece to do the aggregation
Posted by Ashish Khade on 3/15/2012 at 11:50 PM
There could be 3 ways as I see -
1. Join two datasets using merge join in SSIS and use this package as a datasource to SSRS.
2. Write a stored procedure using linked server to combine 2 datasets or
3. Go for .Net assembly where we can connect to different datasources and then join it. I'm not much aware on this 3rd option for now.