Btukfyl

I have a report, in which the highest level of detail produces 2 rows of the dataset, which I need to show grouped. The grouping works fine, but a few of the columns still contain different values; how do I tell the report which value it should display?

Example:
The user wants each row of the report to be grouped by Brand name. 1 Brand can have multiple agreement that are relevant for the numbers to be shown in the report. The numbers can be aggregated, but there is also data that has changed with the new agreement, so only the most recent version needs to be shown

Agreement 1: item-A, item-B, value-A
Agreement 2: item-B, item-C, value B

Grouping by Brand should show:
Agreement: item-B, item-C, sum(value-A+B). But I do not know how to tell the report which items to show.

SQL for the dataset shouldn't be modified in order to maintain option in report to show both rows separate as well.

Any help would be much appreciated :)

Thank you

If it is a string you propably can just use the aggregations First() and Last().

But there is a workaround. You have to define a calculated field in your dataset and write your desired expression into it. Think of it like a rating system. The biggest value is the value you will chose in the grouping, because in the grouping you just have to use Max()then.

For example as calculated field (This is a example for strings, you can also convert in double and use > or <, that depens on your variations from the value of ItemA):

'Calculated field name: CustomField

=Switch(Fields!ItemA.Value = Nothing, 1,

        Fields!ItemA.Value = "500", 2, 

        Fields!ItemA.Value = "1000", 3)

Now add your grouping and chose the calculated field in the groupings like this:

Max(Fields!CustomField.Value)

Then hide the Fields!CustomField.Value and show the Fields!ItemA.Value. This way it will display the row value of Fields!ItemA.Value and you custom grouped after Fields!CustomField.Value.