none
sql report with multiple datasets sorted together

    Question

  • Hi, If i have 3 different databases used as 3 different datasets in a report  how can i
    have them reported mixed together and sorted by animal name (alphabetacally) with different columns/formats for each dataset. see example below.

    Database 1 = dogs
    Database 2 = cats
    Database 3 = lizards

     

    ANIMALS   
       
    Cat   
     Name Clawed 
     Ally Yes 
       
    Lizard   
     Name Tail Location
     Diego Yes TX
    Cat   
     Name Clawed 
     Fluffy No 
    Dog   
     Name Owner Breed
     Rover Max Boxer

    Saturday, June 29, 2013 2:36 AM

Answers

All replies

  • you cannot mix different datasets.

    Ideally when working with different databases, you must build a data mart which contains the data required for analysis and reporting from all the data bases .... and then build reports from the data mart.


    val it: unit=()

    Saturday, June 29, 2013 5:38 PM
  • Hi Nick,

    Generally, we cannot display data from different datasets into one tablix. In SQL Server Reporting Services 2008 R2 or later version, we can use Lookup function to dispaly data from different datasets into one tablix if there have similar column in both of datasets.

    In your scenario, you can use Lookup function to dispaly animal informtion from different dataset into one tablix. Or you can use multiple tablix side by side do display animal informtion.

    Hope this helps.

    Regards,


    Charlie Liao
    TechNet Community Support

    Sunday, June 30, 2013 2:55 AM
    Moderator
  • You can also doing by queering those databases

    SELECT * FROM

    (

    SELECT 0 AS s cats AS animals FROM db1.dbo.cats

    UNION ALL

    SELECT  1 AS s dogs FROM db2.dbo.dogs

    .....

    ) AS Der ORDER BY  s

     

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    Sunday, June 30, 2013 6:32 AM