none
Using Lookup fucntion

    Question

  • Hi,  am trying to use the lookup function to look up a column in a different dataset. The thing is the column I am looking up is only present in dataset2 and not present in dataset1. How can I write the expression so that it looks up the column in dataset2 and there are no common columns in both datasets at all.

    Please If anyone can help , i would be very grateful.

    Thank you very much.


    Ifiok Udoh

    Tuesday, October 22, 2013 9:53 PM

Answers

  • In order to use LOOKUP function it is important to have a key field. Without key field we can not use Lookup function.

    Syntax,

    Lookup(source_expression, destination_expression, result_expression, dataset)
    --------------
    source_expression
    (Variant) An expression that is evaluated in the current scope and that specifies the name or key to look up. For example, =Fields!ProdID.Value.

    destination_expression
    (Variant) An expression that is evaluated for each row in a dataset and that specifies the name or key to match on. For example, =Fields!ProductID.Value.

    result_expression
    (Variant) An expression that is evaluated for the row in the dataset where source_expression = destination_expression, and that specifies the value to retrieve. For example, =Fields!ProductName.Value.

    dataset
    A constant that specifies the name of a dataset in the report. For example, "Products".

    For details, http://technet.microsoft.com/en-us/library/ee210531.aspx

    Regards, RSingh


    Wednesday, October 23, 2013 3:45 AM
  • Hi Ifiok,

    In Reporting Services, we can use Lookup function to retrieve the value from the specified dataset for a name/value pair where there is a 1-to-1 relationship, and it will fetch the first matching value from the other dataset. When joining data across multiple datasets coming from different servers, Lookup function is very useful. Hence, Lookup cannot achieve your goal. For the detailed information of Lookup function, you can refer to RSingh’s post.

    Here, I want to confirm whether you want to obtain the column only present in the dataset2 and not present in the dataset1. If so, I suggest to create a table using T-SQL statement and insert the column only present in the dataset2 and not present in the dataset1 into the table, and then import the table as a new dataset. The T-SQL statement looks like the command as shown below:

    useAdventureWorks2012;

    go

    declare@sql nvarchar(max);

    declare@columns nvarchar(max);

    set@sql= 'select ';

    select@columns=

    stuff((

    select','+c.COLUMN_NAME

    fromINFORMATION_SCHEMA.COLUMNSc

    innerjoinINFORMATION_SCHEMA.tablest

    onc.TABLE_NAME = t.TABLE_NAME

    wheret.table_name = 'Person'

    and

     c.column_name not in

    (

    selectc.COLUMN_NAME

    fromINFORMATION_SCHEMA.COLUMNSc

    innerjoinINFORMATION_SCHEMA.tablest

    onc.TABLE_NAME = t.TABLE_NAME

    wheret.table_name = 'Password'

    )forxmlpath('')),1,1,'');

    set@sql = @sql +

    @columns

    +' from Person.Person'

    exec(@sql);

    insertintoTableName (column1,column2,…)

    exec(@sql);

    Regards,
    Heidi Duan


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time.
    Thanks for helping make community forums a great place.

    Thursday, October 24, 2013 6:17 AM

All replies

  • In order to use LOOKUP function it is important to have a key field. Without key field we can not use Lookup function.

    Syntax,

    Lookup(source_expression, destination_expression, result_expression, dataset)
    --------------
    source_expression
    (Variant) An expression that is evaluated in the current scope and that specifies the name or key to look up. For example, =Fields!ProdID.Value.

    destination_expression
    (Variant) An expression that is evaluated for each row in a dataset and that specifies the name or key to match on. For example, =Fields!ProductID.Value.

    result_expression
    (Variant) An expression that is evaluated for the row in the dataset where source_expression = destination_expression, and that specifies the value to retrieve. For example, =Fields!ProductName.Value.

    dataset
    A constant that specifies the name of a dataset in the report. For example, "Products".

    For details, http://technet.microsoft.com/en-us/library/ee210531.aspx

    Regards, RSingh


    Wednesday, October 23, 2013 3:45 AM
  • Hi Wisdom,

    Am afraid you cannot do a lookup if you dont have common columns in the dataset which has your required column.

    Cheers,

    Amit

    Wednesday, October 23, 2013 5:56 AM
  • Hi Ifiok,

    In Reporting Services, we can use Lookup function to retrieve the value from the specified dataset for a name/value pair where there is a 1-to-1 relationship, and it will fetch the first matching value from the other dataset. When joining data across multiple datasets coming from different servers, Lookup function is very useful. Hence, Lookup cannot achieve your goal. For the detailed information of Lookup function, you can refer to RSingh’s post.

    Here, I want to confirm whether you want to obtain the column only present in the dataset2 and not present in the dataset1. If so, I suggest to create a table using T-SQL statement and insert the column only present in the dataset2 and not present in the dataset1 into the table, and then import the table as a new dataset. The T-SQL statement looks like the command as shown below:

    useAdventureWorks2012;

    go

    declare@sql nvarchar(max);

    declare@columns nvarchar(max);

    set@sql= 'select ';

    select@columns=

    stuff((

    select','+c.COLUMN_NAME

    fromINFORMATION_SCHEMA.COLUMNSc

    innerjoinINFORMATION_SCHEMA.tablest

    onc.TABLE_NAME = t.TABLE_NAME

    wheret.table_name = 'Person'

    and

     c.column_name not in

    (

    selectc.COLUMN_NAME

    fromINFORMATION_SCHEMA.COLUMNSc

    innerjoinINFORMATION_SCHEMA.tablest

    onc.TABLE_NAME = t.TABLE_NAME

    wheret.table_name = 'Password'

    )forxmlpath('')),1,1,'');

    set@sql = @sql +

    @columns

    +' from Person.Person'

    exec(@sql);

    insertintoTableName (column1,column2,…)

    exec(@sql);

    Regards,
    Heidi Duan


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time.
    Thanks for helping make community forums a great place.

    Thursday, October 24, 2013 6:17 AM
  • Hi Heidi,

    Thanks for your Answer, will try it .


    Ifiok Udoh

    Thursday, October 24, 2013 7:29 AM