How to get a single value in a textbox from a row group in SSRS?

Question

I have the following table row group: Name|Percent| val1|67% | val2|33% | I want to know if is possible to get the percent of the val1 in a textbox? Thanks for the help.

Answer 1

Answer:

In SQL Server Reporting Services (SSRS), you can access values from different rows within the same row group using aggregate functions and expressions. To get the percent value of `val1` within the same row group and display it in a textbox, you can follow these steps:

1. Create a new textbox in your report where you want to display the percent value.

2. Right-click the textbox and select "Expression" from the context menu.

3. In the Expression window, you can use the `Lookup` function along with the `RowNumber` function to achieve this. The `Lookup` function retrieves the value from a specific row in a specified dataset.

Assuming your dataset name is "DataSet1," you can use the following expression to retrieve the `Percent` value corresponding to the `val1` value:

```plaintext

=Lookup("val1", Fields!Name.Value, Fields!Percent.Value, "DataSet1")

```

Here's what the expression does:

- The first parameter `"val1"` specifies the value you want to match (the value in the `Name` column where it equals "val1").

- The second parameter `Fields!Name.Value` specifies the field by which you want to match (`Name` column).

- The third parameter `Fields!Percent.Value` specifies the field whose value you want to retrieve (`Percent` column).

- The fourth parameter `"DataSet1"` specifies the dataset from which to retrieve the values.

4. Click the "OK" button in the Expression window to save the expression.

5. Preview the report to see the calculated percent value displayed in the textbox.

Remember to adjust the dataset name and column names in the expression to match your actual dataset and column names.

Note that the `Lookup` function works within the same row group or scope, so it will retrieve the corresponding `Percent` value for the `val1` row within the same group. If your data structure is more complex or involves multiple row groups, you might need to adjust the expression accordingly.