Product Tax Rate Search Report (HSN & SAC) in D365 F&O

 📊 Product Tax Rate Search Report (HSN & SAC) in D365 F&O

🔹 1. Introduction

In Microsoft Dynamics 365 Finance & Operations, tax configurations are maintained within complex Tax Engine runtime tables. Although these structures are highly powerful, they are not easily accessible or intuitive for business users.

To overcome this challenge, the Product Tax Rate Search Report is designed to provide a simplified and dynamic approach for retrieving product-wise GST tax details using key business parameters.

🔹 2. Purpose of the Report

This report enables users to fetch tax details based on:

• HSN Code (for Goods)
• SAC Code (for Services)
• Item ID
• Item Group
• Main Account (Posting GL)
• Tax Rate / Tax Rate Type

👉 The report dynamically retrieves GST Tax Rate (IGST) from the Tax Engine, ensuring real-time and accurate tax configuration visibility.

🔹 3. Business Need

In standard D365 F&O:

• Tax configurations are stored in Tax Runtime tables
• These tables are:
  • Complex
  • Highly normalized
  • Not business-friendly

✔ This Report Simplifies:

• 🔍 Product-level tax identification
• 📦 HSN/SAC mapping validation
• 💰 GL Posting verification
• 📊 Tax rate auditing

🔹 4. Solution Overview

The solution is implemented using a Data Provider (DP) class, which:

• Retrieves tax configuration dynamically
• Processes SAC (Services) and HSN (Goods) separately
• Populates a TempDB table for SSRS reporting

🔹 5. Data Provider (DP) Class Structure

[SrsReportParameterAttribute(classStr(TVKProductSearchContract))]
class TVKProductSearchDP extends SrsReportDataProviderPreProcessTempDB
{
TVKProductSearchReportTmp ProductSearchReportTmp;


// Tax runtime tables
TaxRuntimeDocComponent taxRuntimeDocComponent;
TaxRuntimeComponent taxRuntimeComponent;
TaxRuntimeComponentMeasure taxRuntimeComponentMeasure;
TaxRuntimeDocContext taxRuntimeDocContext;
TaxRuntimeDocContextVersion taxRuntimeDocContextVersion;
TaxSolutionScope taxSolutionScope;
TaxRuntimeLookup taxRuntimeLookup;
TaxRuntimeLookupVersion taxRuntimeLookupVersion;
TaxRuntimeLookupCondition taxRuntimeLookupCondition;
TaxRuntimeLookupMeasureResult taxRuntimeLookupMeasureResult;
TaxRuntimeLookupMeasureResultDetail taxRuntimeLookupMeasureResultDetail;


// Master tables
HSNCodeTable_IN hsnCodeTable_IN;
ServiceAccountingCodeTable_IN serviceAccountingCodeTable_IN;
InventTable inventTable;
InventItemGroupItem inventItemGroupItem;
InventPosting inventPosting;
MainAccount mainAccount;


TVKProductSearchContract TVKProductSearchContract;

}

Report Execution Flow

Step 1️⃣: Identify IGST Tax Component

The report first identifies the IGST tax component and rate from the Tax Engine using:

• TaxRuntimeDocComponent
• TaxRuntimeComponentMeasure
• TaxRuntimeLookup

TaxRuntimeLookupVersion


select taxRuntimeDocComponent
where taxRuntimeDocComponent.Name == 'IGST'
join taxRuntimeComponent
join taxRuntimeComponentMeasure
join taxRuntimeDocContext
join taxRuntimeDocContextVersion
join taxSolutionScope
join taxRuntimeLookup
join taxRuntimeLookupVersion;

Step 2️⃣: Fetch Data

The report splits processing into two logical flows:

SAC (Services)-	Fetch service tax data
HSN (Goods)-	Fetch product tax data

🔹 SAC (Service Tax) Logic

✔ Key Highlights

• Uses DimValue3 → SAC Code
• Joins with:
  • ServiceAccountingCodeTable_IN
  • InventTable
  • Item Group
  • Posting (GL)
• Fetches tax rate from:

TaxRuntimeLookupMeasureResultDetail

🧠 Flow Explanation

1. Start from TaxRuntimeLookupCondition
2. Filter using SAC code
3. Join service code table
4. Map to products (InventTable)
5. Link Item Group & Posting
6. Fetch Tax Rate
   
   
   

public void fetchSAC()
{
Query query = new Query();
QueryBuildDataSource qbdsLookupCondition, qbdsSAC, qbdsInventTableSAC;

qbdsLookupCondition = query.addDataSource(tableNum(TaxRuntimeLookupCondition));

qbdsLookupCondition.addRange(fieldNum(TaxRuntimeLookupCondition, LookupVersion))
.value(SysQuery::value(taxRuntimeLookupVersion.RecId));

qbdsSAC = qbdsLookupCondition.addDataSource(tableNum(ServiceAccountingCodeTable_IN));

qbdsInventTableSAC = qbdsSAC.addDataSource(tableNum(InventTable));

QueryRun queryRun = new QueryRun(query);

while (queryRun.next())
{
ProductSearchReportTmp.clear();

ProductSearchReportTmp.ProductCode = inventTable.ItemId;
ProductSearchReportTmp.ProductName = inventTable.NameAlias;
ProductSearchReportTmp.HSCode = serviceAccountingCodeTable_IN.SAC;
ProductSearchReportTmp.TaxRate = taxRuntimeLookupMeasureResultDetail.Value;

ProductSearchReportTmp.insert();
}
}

🔹 HSN (Goods Tax) Logic

✔ Key Highlights

• Uses DimValue2 → HSN Code
• Joins with:
  • HSNCodeTable_IN
  • InventTable
• Similar structure as SAC but tailored for goods

🧠 Flow Explanation

1. Start from TaxRuntimeLookupCondition
2. Filter using HSN code
3. Join HSN master
4. Map to products
5. Fetch tax rate

public void fetchHSN()
{
Query query = new Query();
QueryBuildDataSource qbdsLookupCondition, qbdsHSN, qbdsInventTableHSN;

qbdsLookupCondition = query.addDataSource(tableNum(TaxRuntimeLookupCondition));

qbdsHSN = qbdsLookupCondition.addDataSource(tableNum(HSNCodeTable_IN));

qbdsInventTableHSN = qbdsHSN.addDataSource(tableNum(InventTable));

QueryRun queryRun = new QueryRun(query);

while (queryRun.next())
{
ProductSearchReportTmp.clear();

ProductSearchReportTmp.ProductCode = inventTable.ItemId;
ProductSearchReportTmp.HSCode = hsnCodeTable_IN.Code;
ProductSearchReportTmp.TaxRate = taxRuntimeLookupMeasureResultDetail.Value;

ProductSearchReportTmp.insert();
}
}

🔍 Filters Supported

The report allows dynamic filtering on:

• HSN / SAC Code
• Item ID
• Item Group
• Main Account
• Tax Rate / Tax Type

👉 This ensures flexible and targeted data retrieval.

🚀 Key Technical Highlights

✅ Advanced Tax Engine Usage

• Uses TaxRuntime tables
• Avoids static tax configuration
• Supports real-time tax logic

✅ Dynamic Query-Based Design

• Uses QueryBuildDataSource (QBDs)
• Flexible joins and filtering

✅ Separation of Concerns

• SAC and HSN handled independently
• Improves maintainability and clarity

✅ TempDB-Based Reporting

• Optimized for SSRS performance
• Efficient data handling

🏁 Conclusion

This report acts as a bridge between complex tax engine structures and business-friendly reporting.

🎯 Benefits

• ✔ Accurate GST rate lookup
• ✔ Simplified tax validation
• ✔ Improved audit control
• ✔ Faster product-tax verification