locked
Can I use lookup function to get a value from one dataset? RRS feed

  • Question

  • Hello, have one dataset with two columns, code and meaning.  I have the code value and like to extract the meaning from dataset.  I was looking at lookup function but it looks like I have to have two datasets and a common key between them.

    Code                      meaning

    1                            description for one

    2                            description for two

    Like to pass 2 and get description for two

    Thanks again

    Thursday, September 6, 2012 3:27 PM

Answers

  • Thanks again but I dont think my question was understood.  Ok, how about this, how can I get the meaning from a dataset when I have the code?  for example I know my code 8765$1234 and now I want to extract the meaning from the dataset.  And forget about the parameters for now.

    using your example data

    =Lookup("8765$1234", Fields!Code.Value, Fields!Meaning.Value, "dataset1")

    where dataset1 is the name of your dataset that holds

    Code                                   meaning

    "1234$Code1"                   my desc one

    "5432$Code2"                   my desc two

    "8765$1234"                   my desc three

    • Marked as answer by rv7a99cv Friday, September 7, 2012 4:51 PM
    Friday, September 7, 2012 10:29 AM

All replies

  • Hi,

    yes you can do that.


    *Regards*
    *Samay Shrivastava*
    *Please mark it as Answered if it answered your question or mark it as Helpful if it helped you solve your problem.*

    Thursday, September 6, 2012 5:59 PM
  • thank you for your reply, but how can I do that?
    Thursday, September 6, 2012 8:43 PM
  • Hi There

    Thanks for your posting. As per your description, do you have one dataset or two dataset? If you have only one dataset why would you need to use lookup function? You can simply drag both code and meaning. However if you have already a dataset bound to your report and you have code inside that report and you would like to get the meaning based on that code from another dataset  then you can use The syntax for lookup function is look like this

    So for example

    =Lookup (Fields!Code.Value, Fields!Code.Value,Fields!meaning.Value,"Dataset1")

    =Lookup (Fields!Code.Value, Fields!Code.Value,Fields!meaning.Value,"Dataset1")

    Where the first Fields! Code.Value is your first dataset field ("Current bound to your report") and Fields! Code.Value is the field in the second dataset ("Dataset1") and both have one to one relationship with each other

    Fields! meaning.Value is the value which you are getting from second dataset ("Dataset1")

    If you have any question please let me know.

    Many Thanks

    Syed Qazafi Anjum

    Please click "Mark as Answer" if this resolves your problem or "Vote as Helpful" if you find it helpful.

    Thursday, September 6, 2012 8:57 PM
  • Thanks so much for your reply Syed,I have one dataset with code and meaning.  Code is combine of two code

    Code                                   meaning

    "1234$Code1"                   my desc one

    "5432$Code2"                   my desc two

    "8765$1234"                   my desc three

    and so on. 

    This dataset is used by a parameter.  For example you pick the third record from dropdown list box ( "8765$1234        my desc three), i want to get the meaning for 1234$Code1 which is "my desc one".  I use split "8765$1234" to get the 1234 value but then I want to lookup the dataset to and find the 1234$Code1 and get its meaning.

    I hope I explained it better this time.

    thanks againe

    Thursday, September 6, 2012 9:54 PM
  • Hi There

    Thanks for your posting again. If I understand you correctly You can simply use

    =Parameters!Code.Label

    I am assuming the parameter name is code please change it to your parameter name

    It will give you the description of your code

    if you have any question please let me know.

    Many Thanks

    Syed Qazafi Anjum

    Please click "Mark as Answer" if this resolves your problem or "Vote as Helpful" if you find it helpful

    Thursday, September 6, 2012 10:08 PM
  • Thanks again but I dont think my question was understood.  Ok, how about this, how can I get the meaning from a dataset when I have the code?  for example I know my code 8765$1234 and now I want to extract the meaning from the dataset.  And forget about the parameters for now.

    Thursday, September 6, 2012 10:16 PM
  • Hi There

    Thanks again. It depends how you want to do. If you want to do this inside the query just use something like this

    Select meaning

    from

    your table

    where

    code=@code

    Select meaning
    from
    your table
    where
    code=@code

    Where I am assuming code is your parameter name which you have

    However if you could not change your query you can simply go to your table properties and add a filter like I have shown in the figure

    Please change it according to your environment

    if you have any question please let me know.

    Many Thanks

    Syed Qazafi Anjum

    Please click "Mark as Answer" if this resolves your problem or "Vote as Helpful" if you find it helpful

    • Proposed as answer by Fanny Liu Friday, September 7, 2012 10:42 AM
    Thursday, September 6, 2012 10:27 PM
  • Hi,

    This how we had used in our reports , we are getting the header Title from dataset after matching with report name.Now note here we had table in database which holds all these mappings.

    =Lookup(Globals!ReportName,Fields!ReportGroupName.Value,Fields!ReportGroupTitle.Value,"DS_ReportsData")

    Hope this will help you.


    *Regards*
    *Samay Shrivastava*
    *Please mark it as Answered if it answered your question or mark it as Helpful if it helped you solve your problem.*

    Friday, September 7, 2012 8:05 AM
  • Thanks again but I dont think my question was understood.  Ok, how about this, how can I get the meaning from a dataset when I have the code?  for example I know my code 8765$1234 and now I want to extract the meaning from the dataset.  And forget about the parameters for now.

    using your example data

    =Lookup("8765$1234", Fields!Code.Value, Fields!Meaning.Value, "dataset1")

    where dataset1 is the name of your dataset that holds

    Code                                   meaning

    "1234$Code1"                   my desc one

    "5432$Code2"                   my desc two

    "8765$1234"                   my desc three

    • Marked as answer by rv7a99cv Friday, September 7, 2012 4:51 PM
    Friday, September 7, 2012 10:29 AM
  • Thank you so much to all of you, it works now.  :)
    Friday, September 7, 2012 4:50 PM