Dynamics Community Forum Thread Details

NAV - Convert Rows of one table to Columns of another table

Here’s the scenario:

I have two tables, one is Salary Statement and another one is Salary Statement Details.

Salary Statement Table

Year	Month	Employee ID	Basic	Total Allowances	Total Bonus	Gross Pay	Total Deductions	Total TDS	Adjustments	Net Pay
2018	November	221344	40000	20000	5000	65000	6000	1000	0	58000

Salary Statement Details Table

Employee ID	Year	Month	Detail_Code	Detail_Type	Amount
221344	2018	November	Medical_Allowance	Allowance	5000
221344	2018	November	House_Rent	Allowance	15000
221344	2018	November	Festival_Bonus	Bonus	5000
221344	2018	November	Loan_Deduction	Deduction	6000

Total Allowances is a calculated column in Salary Statement table. Total Allowances column value comes from Salary Statement Details table. Salary Statement Details table have all the allowances as a row.

Now, I want to create a report in NAV to Show all the Salary Statement table’s fields and all the related rows from Salary Statement Details table as Column and ultimately it will look like:

Employee ID	Year	Month	Basic	House Rent	Medical Allowance	Festival Bonus	Gross Pay	Loan Deduction	TDS	Adjustments	Net Pay
221344	2018	November	40000	15000	5000	5000	65000	6000	1000	0	580000