How to create column and row hierarchies in tables?

[livioq]

Hi

I'm having a bit of trouble creating a matrix out of my dataset

I have data in the top format and I want to see it displayed in the bottom format.

ID	Type	Status	Received	Reserved	Amount	Commission
5	M	Received	02/16/2017
4	M	Completed	02/15/2017	02/23/2017	100	20
3	B	Received	02/14/2017
2	M	Cancelled	01/03/2017
1	B	Completed	10/15/2016	01/27/2017	1000	100
		JAN	FEB
M	Count of Received	1	2
M	Count of Reserved	0	1
M	Sum of Amount	0	100
M	Sum of Commission	0	20
B	Count of Received	0	1
B	Count of Reserved	1	0
B	Sum of Amount	1000	0
B	Sum of Commission	100	0

Could anyone help me?  I can only get additional columns added rather than rows and my counts of reserved and received aren't right. 

Thanks

[ImkeF · Accepted solution]

- Unpivot the columns "Received" and "Reserved"

- this creates a column "Value" with the dates: Filter out blanks

- Extract the month from that column

- Group on that column and column "Status" and "Type"

- Create Counts and Sums on "Amount" and "Commission"

[livioq]

A final question.  I now have, in my datasets, the columns: month, type (M or B), attribute (which has sum of amount, sum of commission, count of received and count of reserved) and value.  This allows me, in my report, to have the columns months and with the rows divided into type and attribute i.e. M + sum of amount, M + sum of commission, M + count of received etc.

The problem is the column value contains both counts (whole number) and sums (fixed number).  Is there anyway to display these numbers in different ways in the report? 

[ImkeF]

Having difficulties to follow/image how your table looks like, but maybe you can now pivot on the column that holds Count and Sum. This would produce 2 different columns then.

Otherwise please post picture or sample table - thanks!

[livioq]

Thank you.  

Didn't know about unpivot and group by functions, but they seem to be what I need.  Still getting some rows where I want columns and a hierarchy that I don't want but think if I fiddle around with it a bit more I should be able to get it to work.