Grouping multiple row and display at bottom

Question

I have created a SSRS report, few record in my report as below:

ID | Currency | Amount
----------------------
1  |   USD    | 100.00
2  |   USD    |  80.00
3  |   USD    | 110.00
4  |   EUR    |  30.00
5  |   EUR    |  50.00

I can group the Amount as total as below:

    ID | Currency | Amount
    ----------------------
    1  |   USD    | 100.00
    2  |   USD    |  80.00
    3  |   USD    | 110.00
    4  |   EUR    |  30.00
    5  |   EUR    |  50.00
  ------------------------
  Total:            370.00

But how do I group the currency with it's Currency type as below:

    ID | Currency | Amount
    ----------------------
    1  |   USD    | 100.00
    2  |   USD    |  80.00
    3  |   USD    | 110.00
    4  |   EUR    |  30.00
    5  |   EUR    |  50.00
  ------------------------
           USD      290.00
  Total:   EUR       80.00

I am group the total by using report wizard, not using sql query. Please help. Thanks

What version of SSRS is this? – Ian Preston Dec 03 '13 at 09:14 — Ian Preston, Dec 03 '13 at 09:14

score 3 · Accepted Answer · answered Dec 03 '13 at 12:00

In SSRS 2008 and above you can use adjacent groups for this.

Create a simple table with a Details row, than right click on the row and choose Add Group -> Adjacent Below:

enter image description here

Set this to group on Currency. It should something like like:

enter image description here

You can also click on the group dropdown and click Add Total -> Before to add table header rows above this new group:

enter image description here

End result looks like your requirement:

enter image description here

As above this is 2008 and above only.

If you have 2005 or below, your only option is to create two tables with the different groupings and place them next to each other in the report to give the appearance of one table.

You can embed the two tables in a Rectangle to try and keep them together as one item.

Grouping multiple row and display at bottom

1 Answers1