SSRS 2008 R2 combine data from multiple datasets

• msgoraya81

  SSC Journeyman

  Points: 90

  More actions

  November 9, 2011 at 10:47 pm

  #245924

  Hi Friends,

  I got a situation where i need to combine data from two datasets and display it in combined fashion...

  Example is

  Dataset1 (from source 1 - SQL Server 2008)

  Year - Scheme - Amount

  2001 - D1 - $1000

  2002 - D2 - $5000

  Dataset2 (from source 2 - Postgres 9)

  Year - Scheme - Amount

  2001 - T1 - $7000

  2003 - P2 - $9000

  And then in SSRS, i need to group by year and display like

  2001

  D1 - $1000

  T1 - $7000

  2002

  D2- $5000

  2003

  P2 - $9000

  Any help is much appreciated...

  Thanks

  Manu

• Daniel Bowlin

  SSC-Dedicated

  Points: 34566

  More actions

  November 10, 2011 at 8:23 am

  #1405810

  I would do this in one dataset with a UNION between the data. I would expect you can use an open query or four part naming to get access to your Postgre data within the same query.

• Koen Verbeeck

  SSC Guru

  Points: 259197

  More actions

  November 14, 2011 at 1:53 am

  #1406973

  Or use SSIS to integrate the data from both sources into one data warehouse (that's where it is for :-)).

  That way you reduce complexity in the visualization layer - aka SSRS- enormously.

  Need an answer? No, you need a question
  My blog at https://sqlkover.com.
  MCSE Business Intelligence - Microsoft Data Platform MVP

• msgoraya81

  SSC Journeyman

  Points: 90

  More actions

  November 14, 2011 at 4:41 pm

  #1407583

  Thanks Daniel and Koen, Much appreciate you help. I used Daniel's solution and it has served the purpose for Now.

  Regards

  Manu