Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SSRS report with multiple datasets Expand / Collapse
Author
Message
Posted Sunday, October 6, 2013 6:11 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, September 8, 2014 7:11 AM
Points: 189, Visits: 686
I need to create one report with 5 different data sets and from 5 diff data sources. All the 5 data sources are from 5 diff servers and they are not linked servers. So I am creating 5 data sets with 5 diff data sources and executing 5 diff stored procedures and getting 5 diff result sets with same column names and need to add all these 5 result sets into one report.
I need to union the result set of all the 5 Stored procs and use it one report.

How can I do this?

Thanks.
Post #1501975
Posted Sunday, October 6, 2013 6:29 PM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Yesterday @ 10:30 PM
Points: 767, Visits: 4,943
You can't create linked servers and do the union in your stored procedure?
Post #1501976
Posted Sunday, October 6, 2013 7:03 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, September 8, 2014 7:11 AM
Points: 189, Visits: 686
No. I can't create linked server.
Post #1501979
Posted Monday, October 7, 2013 7:49 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 11:50 AM
Points: 58, Visits: 194
Perhaps this might help

http://technet.microsoft.com/en-us/library/ms188386.aspx

if it can be near time, could you also not configure a SSIS package to consolidate the data into one location?
Post #1502141
Posted Monday, October 7, 2013 8:04 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, August 28, 2014 12:38 AM
Points: 201, Visits: 682
Well if all else fails and you don't need to combine the data and sort it, why not create 5 tables and make sure that the tables positioning on the report leave no spaces between the tables, It will look like 1 table but not function as 1 table
Post #1502150
Posted Monday, October 7, 2013 8:54 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, September 3, 2014 11:41 AM
Points: 1, Visits: 16
one way to do it could be to have a master report with 5 subreports each pulling a resultset. If you need to manipulate the resultset I don't know if that single report will allow for it though. Another possibility (that might be overkill) is to create a library in .NET that sets up those connection strings and pulls the results into a single resultset then returns the result to the report.

In either case, you might want to re-examine the workflow that makes this necessary. It might be legitimate, but it sure feels wrong...
Post #1502184
Posted Tuesday, October 8, 2013 6:09 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, October 18, 2013 4:40 PM
Points: 70, Visits: 8
Like one of the other poster says, try using a stored proc.

You can use the OPENROWSET approach to create (in-effect) an on-the-fly linked server for each server connection.
And then UNION all the individual queries together. This sample works for me -- using just 2 servers. Could easily extend it to five.

-- List the databases on several different servers
SELECT A.*
FROM OPENROWSET('SQLOLEDB', 'SERVERA' ; 'sa' ; 'SAPasswordA',
'SELECT * FROM master.dbo.sysdatabases') as A

UNION

SELECT B.*
FROM OPENROWSET('SQLOLEDB', 'SERVERB' ; 'sa' ; 'SAPasswordB',
'SELECT * FROM master.dbo.sysdatabases') as B


A couple of notes here:
1. You may not want to use the "SA" -- and even if you do, you probably should not hard-code the SA passwords in your proc [Bad practice]!
2. You may want to retrieve just the columns you want, and not do the "SELECT *".

-john



Post #1502889
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse