SSRS - Apply Filter to Aggregate Functions

Question

Is there a way in an SSRS report itself, not the database, to filter dataset rows queried in an aggregate function.

For example:

Say I have a simple table:

ID ValueA ValueB
1 A 50
2 B 25
3 B 45
4 C 10
5 C 30

I want the FIRST() or even MIN() of ValueB from the above, but where ValueA = B.
I could use either approach as I'm looking for 25.

Answer 1

Hi @Anonymous

Then you can create 2 parameters ,one for choosing a ValueA, one for calculating the MIN() of ValueB.
Below is my test steps，hope will help you.
Create dataset2 to get all the distinct ValueA

Then create the parameter ValueA

Then create dataset3 to calculate the MIN() of ValueB

Then create parameter minValueB

Then mark the textbox by change the backgroud color

Preview Result

Best Regards,
Isabella

---

If the answer is the right solution, please click "Accept Answer" and upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Hi @Anonymous
You can get the First() of ValueB by using expression ：=First(Fields!ValueB.Value, "DataSet1")

About the MIN() of ValueB，you can add a filter on the dataset，then use expression：=Min(Fields!ValueB.Value, "DataSet1")

Best Regards,
Isabella

---

If the answer is the right solution, please click "Accept Answer" and upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.