Post reply

Using data sources - Best Practise

  • December 5, 2007 at 7:17 am

    #177225

    I have a report project with 1 shared data source which returns data from a particular database and is used across several reports. Other reports in the project need fields from the existing data source and require fields from another database on a different server.

    What is the best way to set this up?

    Thanks

  • December 6, 2007 at 12:28 am

    #757552

    I'd recommend a separate process to go get the data instead of trying to do reporting through a linked server. Setup a staging area to hold the data locally/temporarily.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • December 6, 2007 at 2:18 am

    #757573

    Thanks Jeff,

    In terms of adding a data source though how would I go about this? Firstly I set about creating a new shared data source bringing in the fields I required. I then got stuck at the point of creating the report which uses fields from both data sources.

    How would you handle this?

  • December 7, 2007 at 10:55 pm

    #758266

    Copy only the data you need for the report from the linked server into a local table... then, do the reporting off that and drop the table when you are done. If not of a hughe size, could use a temp table...

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • December 10, 2007 at 3:51 pm

    #758792

    It sounds like Adam is stuck on actually using data from more than one data source in the same report, correct? If so it's easy - define your two (or more) data sources in the data tab, then bind them to your design objects. There are a few limitations - a single table can only have one data source bound to it (though you can hack around this somewhat by embedding other tables inside cells, etc.), but you can happily have multiple tables each bound to a different data source. What you CAN'T do easily is have your data from multiple data sources correlated in the same table - if you want this it'd be better to have a single data source that presents the data from both servers as one result set (using either linked servers or a staging mechanism as Jeff suggested behind the scenes).

    Regards,

    Jacob

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply