Max date per group in SSRS

Question

Hi,

How to select the max row/date per group. I am planing to do at report design level(i.e, trying to put filter condition in group properties).

Pl help me in this regard.

Thanks

Aneel

Answer 1

Hi aneelchandra,

It seems that you would like to select the max value in each group, right? If I misunderstand, please let me know.

We can use filter to achieve your target, please take the following steps of my sample to you as reference.

1. Add a row parent group without select “Add group header” and “Add group footer”, and  then a new group display in “Row Groups” pane and it named as Group1.
2. Right-click the “Details” in “Row Groups”, and then select “Group Properties”.
3. Change to “Filters” tab, click “Add” button to add a filter like following:
Expression: [SalesAmount] (select a data field which you would like to choose the max value from it)
Operter: =
Value: =Max(Fields!SalesAmount.Value,"Group1")

After  completer steps above, you could get the result what you need.

If you have any question, please feel free to ask.

Thanks,
Eileen

Answer 2

Hi Eileen,

When I followed these steps I got an error "The FilterValue expression for the grouping 'Details' has a scope parameter that is not valid for an aggregate function. The scope parameter must be set to a string constant that is equal to either the name of ta containing group, the name of a containing data region, or the name of a dataset".

Any suggestions please?

Thanks