locked
Lookup function RRS feed

  • Question

  • I have a report and it follows this structure,

    Resource Name --> Project Name --> Summary Task --> Task Name

    The structure of the task table is like:

    TaskID Task Name ParentTaskID
    1 Training 2
    2 General Tasks 2
    3 Project Management 2
    4 QA 2
    5 Systems Architecture

    2

    As you can see the Summary tasks is General Tasks, I need to group by ParentTaskID, but show the Task Name in the report and I am using the lookup fuction to do it but its not working out for me, it just shows blank any ideas?

    =Lookup(Fields!TaskParentUID.Value,Fields!TaskUID.Value,Fields!TaskName.Value,

    "DataSet1")
    Thursday, May 9, 2013 2:11 PM

Answers

  • Hello,

    Just as Sary post above, Lookup() compare name/value pairs between two datasets.
    Supposing the table is bound to a dataset that includes a field for the parent task identifier "ParentTaskID". A separate dataset called "ParentTask" contains the corresponding  parent task identifier "ID" and parent task "Name". And then you can dispaly the parent task name in the table with following expression:
    =Lookup(Fields!ParentTaskID.Value,Fields!ID.Value,Fields!Name.Value,"ParentTask").

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

    Regards,
    Fanny Liu


    Fanny Liu
    TechNet Community Support

    • Proposed as answer by Fanny Liu Thursday, May 16, 2013 1:22 AM
    • Marked as answer by Fanny Liu Friday, May 17, 2013 1:23 AM
    Monday, May 13, 2013 5:52 AM

All replies

  • Hi,

    For 1 to many relationship use LookupSet function

    http://www.bidn.com/blogs/DustinRyan/bidn-blog/2037/lookup-and-lookupset-functions-new-in-ssrs-2008-r2

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


    sathya --------- Please Mark as answered if my post solved your problem and Vote as helpful if my post was useful.

    Thursday, May 9, 2013 2:26 PM
  • Thanks for the reply, I switched to that function instead. Now I get #Error in the column.

    This warning in the designer: Warning 1 [rsInvalidExpressionDataType] The Value expression used in textrun ‘TaskParentUID.Paragraphs[0].TextRuns[0]’ returned a data type that is not valid. 

    Thursday, May 9, 2013 2:37 PM
  • Is this one data set or two?

    Sary Awwad

    Thursday, May 9, 2013 3:56 PM
  • Hello,

    Just as Sary post above, Lookup() compare name/value pairs between two datasets.
    Supposing the table is bound to a dataset that includes a field for the parent task identifier "ParentTaskID". A separate dataset called "ParentTask" contains the corresponding  parent task identifier "ID" and parent task "Name". And then you can dispaly the parent task name in the table with following expression:
    =Lookup(Fields!ParentTaskID.Value,Fields!ID.Value,Fields!Name.Value,"ParentTask").

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

    Regards,
    Fanny Liu


    Fanny Liu
    TechNet Community Support

    • Proposed as answer by Fanny Liu Thursday, May 16, 2013 1:22 AM
    • Marked as answer by Fanny Liu Friday, May 17, 2013 1:23 AM
    Monday, May 13, 2013 5:52 AM