Dynamics F/SCM Customer Experience (CRM) Dynamics GP Dynamics Business Central (NAV) Dynamics SL Power Platform Fabric Copilot

[Jamil Azher]

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

 

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.

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

 

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

 

Here the allowance, bonus and deduction fields are added as columns which were in rows of earlier table.

How can I do that? Thanks in advance.

 

 

——————————
Jamil Azher
NUARCA
San Ramon CA
——————————

[Alex Apodaca]

?Hello Jamil,

You can write a query like the one you requested in the following way, and then run it from Excel which connects to the database, submits the query, and returns the results:

SELECT [Employee ID], [Year], [Month], [Basic],

  (
    SELECT COALESCE(SUM([SSDT1].[Amount]), 0)
    FROM [XXXXXX Salary Statement Details Table] AS [SSDT1]
    WHERE [SSDT1].[Employee ID] = [SST1].[Employee ID]
      AND [SSDT1].[Year] = [SST1].[Year]
      AND [SSDT1].[Month] = [SST1].[Month]
      AND [SSDT1].[Detail_Code] = 'House_Rent'
  ) AS [House Rent],

  (
    SELECT COALESCE(SUM([SSDT2].[Amount]), 0)
    FROM [XXXXXX Salary Statement Details Table] AS [SSDT2]
    WHERE [SSDT2].[Employee ID] = [SST1].[Employee ID]
      AND [SSDT2].[Year] = [SST1].[Year]
      AND [SSDT2].[Month] = [SST1].[Month]
      AND [SSDT2].[Detail_Code] = 'Medical_Allowance'
  ) AS [Medical Allowance],

  (
    SELECT COALESCE(SUM([SSDT3].[Amount]), 0)
    FROM [XXXXXX Salary Statement Details Table] AS [SSDT3]
    WHERE [SSDT3].[Employee ID] = [SST1].[Employee ID]
      AND [SSDT3].[Year] = [SST1].[Year]
      AND [SSDT3].[Month] = [SST1].[Month]
      AND [SSDT3].[Detail_Code] = 'Festival_Bonus'
  ) AS [Festival Bonus],

  [Gross Pay], 

  (
    SELECT COALESCE(SUM([SSDT4].[Amount]), 0)
    FROM [XXXXXX Salary Statement Details Table] AS [SSDT4]
    WHERE [SSDT4].[Employee ID] = [SST1].[Employee ID]
      AND [SSDT4].[Year] = [SST1].[Year]
      AND [SSDT4].[Month] = [SST1].[Month]
      AND [SSDT4].[Detail_Code] = 'Loan_Deduction'
  ) AS [Loan Deduction],

  [Total TDS], [Adjustments], [Net Pay]

FROM [XXXXXX Salary Statement Table] AS [SST1]?

Note: You have to update the query where I put the X’s, to suit your environment.

You can then use Excel and connect to a Database to run this or any query safely and retrieve the results: From the network where the SQL Database lives, open Excel and choose New Query=>From Database=>From SQL Server Database.

(Or you can choose ‘From Other Sources’ depending on what you need):

Then enter in your written SQL query, and the server details, to fetch such results. Read-only data access is all that is needed.

Example Only:

Note: You have to update the query where I put the X’s, to suit your environment.
  – Server: is going to be the SQL Server name
  – Database: is going to be the Database name
  – Inside the query language: you have to replace the XXXXXX with the company-name-prefix that appears on all data tables in NAV.

——————————
If my response has helped you please mark the answer.

Best Regards,

Alex A
Sr. NAV Consultant, DXC Technology
Los Angeles, CA, United States
——————————
——————————————-

[Jim Bates]

?Hi Jamil

We have several reports that do what you want. This will work for NAV 2013 or later.  But rather than generating a complex SQL statement that you have to maintain, we use the Matrix object in the reporting tool to group columns as well as rows.  You may need to create an extra field in your dataset that sorts the way you want things to sort for your column groups. For the column grouping, we sort on type of payroll code and then payroll code.  In this report, we also include the tax basis as well as a secondary employee id in the grouping.  But doing things this way means you don’t have rewrite your SQL, if you add a new payroll code.

If you have any questions, feel free to shoot me a note.

——————————
Jim Bates
Samaritan’s Purse
Boone NC
——————————
——————————————-

[Kevin Kueny]

SQL has a PIVOT function (and an UNPIVOT function) that can turn rows into columns or vice-versa.

——————————
Kevin Kueny
Principal Consultant
Velosio
Grayslake, IL
——————————
——————————————-