none
Look up values in other datasources RRS feed

  • Question

  • Hmm....should be pretty simple, but can't figure it out....

    Got two datasources:

    1. Building
    BuildingId (int)
    Name (string)
    Location(int)

    2. Location
    Id (int)
    LocationName(string)

    I would like to create a report showing buildings, but instead of showing the int for "location" I would like to look up the name based on the int. How do I do that?


    Ivar


    Monday, November 5, 2007 10:29 AM

All replies

  • I'm not sure how to do this if the data is from two different data sources (i.e. dbs), though perhaps the .Net Dataset builtins provide a way to join such tables once they are in memory. Otherwise, you just have to retrieve the two tables and build up an aggregate table programmatically.

     

    If they are from the same DB, you can do a select based on an appropriate join.

     

    If the tables are in the same DB, try loading SQL Server Management Express Studio and using the query designer...

     

    Monday, November 5, 2007 10:58 PM
  • I do this all the time, using linked servers.

     

    Basically, in other words, one of my servers is the host for all the heterogeneous data.  I define the linked servers there and create heterogeous views or stored procs that reference the linked servers.

     

    Is this an option for you? If not, there are some work arounds with custom code, nested tables, and the like, but nothing is as good as doing it at the source <s>.

     

    >L<

    Tuesday, November 6, 2007 3:24 PM
  • Hi again... just to conclude...

     

    I'm using generated classes made of subsonic (http://www.subsonicproject.com/) as entity classes for my objectdatasources. Since I'm lacy I didn't want to start building from the database again so I ended up making a new class that inherits from my entityclass. I copied all the properties from the baseclass using reflection and added new properties that via helperclasses gave me the textvalues.

     

    Code Block

    public class ReportFoo : Foo

    {

    public ReportFoo(Foo foo)

    {

    // get all the fields in the class

    PropertyInfo[] fields_of_class = building.GetType().GetProperties(

    BindingFlags.NonPublic | BindingFlags.Public | BindingFlags.Instance);

    // copy each value over to 'this'

    foreach (PropertyInfo fi in fields_of_class)

    {

    object obj = fi.GetValue(foo,null);

    if (obj != null && fi.CanWrite)

    fi.SetValue(this, obj, null)

    }

    }

     

    public string TheValueAsString

    {

    get

    {

    return helperclass.GetFooString(base.TheValue);

    }

    }

    }

     

     

    Maybe not very elegant, but it works for me!

     

     

    Ivar

    Friday, November 9, 2007 8:44 AM