How to group this report

Question

I want to know if there is a way to group this report. This is my report.

This report is grouped by two columns "Merchant" and "ConfirmationID" and ordered by the columns "date" and "time". As you can see the Merchant is repeated twice because of the groupings. I want to know if there a way to group this column "Merchant" to show in this way:

I tried to add one more parent group with the column merchant but the output is different that I want because it groups all together and don't respect the sort of the columns time and date. This is the result if I add a parent group with the column Merchant:

If anyone can help me I would appreciate too much.

Answer 1

Initially group on confirmation id (1st group) and then on merchant (2nd group).

Create two rows for your merchant group.

On the first row put the merchant field and on the second the column headers.

You need to hide these two rows when merchant value doesn't change. In order to do this set the row visibility expression to =(Fields!merchant.Value = Previous(First(Fields!merchant.Value,"merchant")))

Order your detail by date and time.

UPDATE for totals

Create only one group

Add the following custom code to your report

Dim current_merchant
Dim current_confirmation

Public Function CustomGroup( merchant As String, confirmation As Integer) As String

If current_merchant <> merchant Then
    current_merchant = merchant
    current_confirmation = confirmation
End If

Return current_merchant & " " & Cstr(current_confirmation)

End Function

For your group set the expression to =Code.CustomGroup(Fields!merchant.Value, Fields!confirmation_id.Value)

Set the detail sorting on confirmation_id, date, time You don't need any visibility expressions