July 22, 2014 at 12:07 pm
I have a query where in I Union two separate SQL database queries. It works fine. Now I need to use to this query in Report builder . But I have always used one datasource in one dataset.
How can we achieve using two datasources in one dataset.
Since the two databases involves dates /months for chart purposes , I cant use lookup here.
What will be the best way to approach this?
July 22, 2014 at 5:19 pm
When you run your query, I assume you are attached to either database X or Y, and the code tells it to call out to the other DB for that part of the union. If so, make whichever DB you connect to in SQL your datasource for the dataset you create in SSRS. The query will handle calling out to the other DB, just as it does when you run it in SQL. Just make sure the code contains the full DB.dbo.table.paths like the made-up example below. In this example, you could make either DatabaseX or DatabaseY your datasource.
SELECT Column1
FROM databaseX.dbo.tableA
WHERE SomeStuff = OtherStuff
UNION
SELECT Column2
FROM databaseY.dbo.tableB
WHERE widgets = whosits
July 23, 2014 at 12:47 am
Right..
UNION works in this scenario.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply