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
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:
Set this to group on
Currency
. It should something like like:You can also click on the group dropdown and click Add Total -> Before to add table header rows above this new group:
End result looks like your requirement:
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.