locked
Choosing between two Data Sources in one report. RRS feed

  • Question

  • Hey everybody.  Just a little background, we have two different databases, one for the Calgary, Alberta office, one for the Edmonton, Alberta office.

    I'm looking at creating a report where the user can choose either Calgary or Edmonton to begin, then department number, active or terminated ect to retrieve a complete list from either city.

    How would one go ahead and make it so a single report can access multiple data sources.  I've tried a couple of things but cannot seem to find a way to have them link.  I just need it to either access one or the other from selecting a city. Not any information crossing paths, or retrieving Edmonton information on an already selected Calgary list.  I'd rather be able to do this than have to create two different reports for employees to open! I'm new to this so go easy haha !

    Thanks,

    --

    Chase Myles

    Keystone Excavating

    chasemyles@ourlifeisdirt.com



    • Edited by CMyles Wednesday, June 19, 2013 4:34 PM
    Wednesday, June 19, 2013 4:26 PM

Answers

  • Hi,

    You can achieve this by implementing Dynamic pointing dataset functionality in your reports, so to implement this have a look on this:

    http://www.codeproject.com/Articles/355461/Dynamically-Pointing-to-Shared-Data-Sources-on-SQL


    Live life with joy and happiness! Avanish

    • Proposed as answer by Fanny Liu Thursday, June 20, 2013 6:48 AM
    • Marked as answer by Fanny Liu Thursday, June 27, 2013 1:08 AM
    Wednesday, June 19, 2013 5:42 PM
  • If you do not want to implement a custom solution like that pointed to by Avinash and you want a single report, create the 2 datasets as described by Pradeep and create the report using the first dataset (Calgary). Place all report elements into a single container rectangle. Once it is working correctly, create a parameter with 2 allowed values: Calgary, Edmonton. Now create a copy of the rectangle containing your report elements. Place it immediately below the original rectangle. Assign the dataset for all elements of the second rectangle to the Edmonton dataset. Now right-click each rectangle and set the visibility to show/hide based on a formula:

    =IIf(Parameters!Location.Value = "Calgary", False, True) is for the Calgary rectangle

    =IIf(Parameters!Location.Value = "Edmonton", False, True) is for the Edmonton rectangle

    In this approach, the data is retrieved and the report assembled for both locations but only one or the other is displayed depending on which is selected by the parameter. Alternately, you could allow both to be displayed by adding an allowed value to the parameter: Both. Then alter the visibility formula:

    =IIf(Parameters!Location.Value <> "Edmonton", False, True) is for the Calgary rectangle

    =IIf(Parameters!Location.Value <> "Calgary", False, True) is for the Edmonton rectangle


    "You will find a fortune, though it will not be the one you seek." - Blind Seer, O Brother Where Art Thou
    Please Mark posts as answers or helpful so that others may find the fortune they seek.

    • Proposed as answer by Fanny Liu Thursday, June 20, 2013 6:48 AM
    • Marked as answer by Fanny Liu Thursday, June 27, 2013 1:08 AM
    Wednesday, June 19, 2013 9:11 PM

All replies

  • You can create two data source and when you create dataset then use the data source as per your requirement.

    suppose data source 

    d_Source1 -(connection string using one server)

    d_Source2 -(connection string using second server)

    now create dataset ds1 with using d_Source1 - in this query will fetch data from server1

    create dataset ds2 with using d_Source2 - in this query will fetch data from server2

    Now create a report and place 2 button\link on this report  , on click of this button will navigate to open the report as per user requirement.

    Hope this will help you .....

    Wednesday, June 19, 2013 4:41 PM
  • Hi,

    You can achieve this by implementing Dynamic pointing dataset functionality in your reports, so to implement this have a look on this:

    http://www.codeproject.com/Articles/355461/Dynamically-Pointing-to-Shared-Data-Sources-on-SQL


    Live life with joy and happiness! Avanish

    • Proposed as answer by Fanny Liu Thursday, June 20, 2013 6:48 AM
    • Marked as answer by Fanny Liu Thursday, June 27, 2013 1:08 AM
    Wednesday, June 19, 2013 5:42 PM
  • If you do not want to implement a custom solution like that pointed to by Avinash and you want a single report, create the 2 datasets as described by Pradeep and create the report using the first dataset (Calgary). Place all report elements into a single container rectangle. Once it is working correctly, create a parameter with 2 allowed values: Calgary, Edmonton. Now create a copy of the rectangle containing your report elements. Place it immediately below the original rectangle. Assign the dataset for all elements of the second rectangle to the Edmonton dataset. Now right-click each rectangle and set the visibility to show/hide based on a formula:

    =IIf(Parameters!Location.Value = "Calgary", False, True) is for the Calgary rectangle

    =IIf(Parameters!Location.Value = "Edmonton", False, True) is for the Edmonton rectangle

    In this approach, the data is retrieved and the report assembled for both locations but only one or the other is displayed depending on which is selected by the parameter. Alternately, you could allow both to be displayed by adding an allowed value to the parameter: Both. Then alter the visibility formula:

    =IIf(Parameters!Location.Value <> "Edmonton", False, True) is for the Calgary rectangle

    =IIf(Parameters!Location.Value <> "Calgary", False, True) is for the Edmonton rectangle


    "You will find a fortune, though it will not be the one you seek." - Blind Seer, O Brother Where Art Thou
    Please Mark posts as answers or helpful so that others may find the fortune they seek.

    • Proposed as answer by Fanny Liu Thursday, June 20, 2013 6:48 AM
    • Marked as answer by Fanny Liu Thursday, June 27, 2013 1:08 AM
    Wednesday, June 19, 2013 9:11 PM