creating custom view using Power Query for a report
1.Copy n paste Dr-Ageing Excel n Rename it as Interest Calculation --
to get the custom view option in the report.
2.Copy this excel name in Template Field in the settings(Report).
Now Open the Excel -- Power Query -- Workbook Queries -- Data -- Edit -- View -- Advanced Editor.
3. Write the below Query n Save -- Run
let
SpParameter = Excel.CurrentWorkbook(){[Name = "Parameter"]}[Content],
Source = Sql.Database("192.9.200.195", "ICSoftReportTemp", [Query="SELECT C.CustName + ' | ' + C.CustCode as Customer,[ASOn] ,Ageslab AS Ageing,amount,AgeId AS ID ,IntAmount
FROM [DR-Ageing_ForIntCalc] (NOLOCK)
inner JOIN IcSoft.dbo.Customer C (NOLOCK) on C.CustId = [DR-Ageing_ForIntCalc].custid
where LoginId = " & Number.ToText(SpParameter[LoginID]{0})])
in
Source
