You’re offline. This is a read only version of the page.
2,095
Hi all
I've got a question and after many attempts at googling, I can find any solutions to this. I've got two queries relating to two different entity data sets. There is no relationship between the two entities (no 1:N, N:1 or N:N relationships) but I've set the fields in each entity to match each other exactly.
What I want to do is bring those two datasets into an SSRS report via Microsoft Visual Studio but I don't want to have it as two different tables but have one overlay their data on top of the other in a union of sorts in 1 table. This is so that we can then sort or group both datasets in a single table. As an example the two queries I've got contain:
Query 1 - Entity 1
Entity 1-name field Entity 1-Subject field Entity 1-Organisation Lookup field
Name value 1 Subject value 1 Organisation lookupvalue 1
Query 2 - Entity 2
Entity 2-name field Entity -Subject field Entity 2-Organisation Lookup field
Name value 2 Subject value 2 Organisation lookupvalue 2
I want to merge/append the two datasets together in SSRS so that it produces a single table via SSRS so the final output in SSRS will be:
Combined-Name field Combined-Subject field Combined-Organisation Lookup field
Name value 1 Subject value 1 Organisation lookupvalue 1
Name value 2 Subject value 2 Organisation lookupvalue 2
As noted there isn't a entity relationship link between the two and I simply need to append/overlay one on top of each other so that I can do a row grouping on a single table as opposed to two.
I understand Fetch does not allow unions and some of the outer join solutions listed require a connecting data point. Is there a easy way of doing this.
Any help will be greatly appreciated and thanks in advanced!
Kind regards,
Mike
*This post is locked for comments
You can amalgamate the information from the link entity to one of the other main entities, retrieving that data in one go and then join the two datasets using lookup from within SSRS
ETA: You might want to investigate using sub-reports
I don't think SSRS has anything that can help you here. Any table / matrix can only be associated with one dataset (for completeness, you can reference other datasets in the expression for a field, but this doesn't help).
You could display 2 tables, one after the other to make it look like a combined table, but you couldn't sort across the tables, for instance.
Or, as both data sets have a lookup to an organisation, you may be able to use on query with outer joins, with the organisation as the connecting data point
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
291,253
Super User 2024 Season 2
#2
Martin Dráb
230,188
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (