locked
Joining two datasets for one report RRS feed

  • Question

  • Any way i can join to datasets together from two differant databases and use information from first in the second

    Thanks 

    Thursday, May 15, 2014 8:19 AM

Answers

  • Yes. Take a look at the Lookup and Join functions.

    http://msdn.microsoft.com/en-us/library/ms157328.aspx


    Brad Syputa, Microsoft Power BI This posting is provided "AS IS" with no warranties.

    Thursday, May 15, 2014 6:22 PM
  • Nope. Join wont merge datasets. What you need is Lookup,LookupSet and MultiLookup functions

    http://www.allaboutmssql.com/2013/08/ssrs-example-for-lookup-lookupset-and.html


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Thursday, May 15, 2014 6:34 PM
  • The Lookup expressions are used for this. They all use a similar syntax:

    =command(LocalKey, ForeignKey, ForeignValue, "OtherDatasetName")

    This is similar to JOINing tables ON LocalKey=ForiegnKey in Transact-SQL. The expression builder tool will give you some limited help with the 3 Lookup expressions . Visakh's link has more extensive help.

    The Join expression mentioned by Brad is often used in conjunction with the LookupSet and MultiLookup expressions, which return an array of values (ForeignValue) rather than a single value like Lookup. Join will concatenate the elements of an array into a string.


    "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.

    Thursday, May 15, 2014 9:15 PM

All replies

  • Yes. Take a look at the Lookup and Join functions.

    http://msdn.microsoft.com/en-us/library/ms157328.aspx


    Brad Syputa, Microsoft Power BI This posting is provided "AS IS" with no warranties.

    Thursday, May 15, 2014 6:22 PM
  • Nope. Join wont merge datasets. What you need is Lookup,LookupSet and MultiLookup functions

    http://www.allaboutmssql.com/2013/08/ssrs-example-for-lookup-lookupset-and.html


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Thursday, May 15, 2014 6:34 PM
  • The Lookup expressions are used for this. They all use a similar syntax:

    =command(LocalKey, ForeignKey, ForeignValue, "OtherDatasetName")

    This is similar to JOINing tables ON LocalKey=ForiegnKey in Transact-SQL. The expression builder tool will give you some limited help with the 3 Lookup expressions . Visakh's link has more extensive help.

    The Join expression mentioned by Brad is often used in conjunction with the LookupSet and MultiLookup expressions, which return an array of values (ForeignValue) rather than a single value like Lookup. Join will concatenate the elements of an array into a string.


    "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.

    Thursday, May 15, 2014 9:15 PM
  • I get this error 

    A Value expression used for the report parameter ‘userid’ refers to a field.  Fields cannot be used in report parameter expressions.

    Friday, May 16, 2014 10:23 AM
  • Fields can only be used in parameters by setting their value using "Get values from query". Please provide more details on what you are trying to accomplish. You hadn't mentioned parameters before. Perhaps there is way to accomplish what you want without using a parameter. Or by using more parameters to insulate the Lookup expression from the dataset.


    "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.

    Friday, May 16, 2014 1:04 PM
  • Can you elaborate on how exactly you're trying to merge the datasets?

    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Friday, May 16, 2014 1:59 PM