Forum Discussion
How To Lookup A Specific Value in Report Builder
I have a textbox that I would like to have dynamically update with information from one of my datasets. An example of the layout of the contents of the textbox is as follows:
TITLE
Field1: <<Expr>>, Field2 <<Expr>>, Field3 <<Expr>>
Field4: <<Expr>>, Report Date: <<Expr>>
I want each of the expressions, except for the Report Date (I have that figured out and am using the ExecutionTime), to be a value I "Lookup" from one of the datasets. Let's say my dataset has 4 columns. For each of these expressions, I want to lookup "Specific Value" (for example) in Column1 and return the value corresponding to it in Column4. Can I use the Lookup function? If so, how? When I try to use it like I would in a Tablix, I get the error message stating the Value expression refers to a field without specifying a dataset aggregation. I tried to First() and Max for that aggregation but no value is returned, nor do I get any error messages while previewing the report. If someone could me figure out how to do this, that would be greatly appreciated.
While this question remains unanswered, I ended up taking a piece of a query from one of the data sets in use to create yet another data set. That new data set is laid out in a manner where I can just use the field corresponding to each expression I want to have in my textbox, as opposed to trying to do what I was initially doing. I haven't ruled out that what I was trying to do isn't even possible at this point. While I'm able to resume my developments now, if anyone ever comes up with an answer to his, I would love to hear it, as it might come in handy as I get more into using Power BI (Desktop and/or Report Builder).
3 Replies
While this question remains unanswered, I ended up taking a piece of a query from one of the data sets in use to create yet another data set. That new data set is laid out in a manner where I can just use the field corresponding to each expression I want to have in my textbox, as opposed to trying to do what I was initially doing. I haven't ruled out that what I was trying to do isn't even possible at this point. While I'm able to resume my developments now, if anyone ever comes up with an answer to his, I would love to hear it, as it might come in handy as I get more into using Power BI (Desktop and/or Report Builder).
It's hard to say for sure, but you might be able to do what you are asking using the Lookup function in your text boxes - see https://docs.microsoft.com/en-us/sql/reporting-services/report-design/report-builder-functions-lookup-function?view=sql-server-ver15
d_gosbell lookup function unfortunately does not work inside textboxes.
I have found a solution that involves having your dataset as a value-label pair in Parameters (create a new parameter with the dataset in question as source). Then you will need to write a custom function that will retrieve a label from the parameter value that you can use in a textbox.
This tutorial explains it quite well (on an example of localization): https://www.youtube.com/watch?v=lZYMeOaAd7UIt is in Greek, but you can follow it quite well nonetheless. I hope this helps.
