Hello, I need to build a SSRS report where I can generate Historical Trial Balance for a particular Vendor. I was just wondering if anyone has written the SQL query for this.
*This post is locked for comments
Hi Sattu,
Already this report was available in GP, Report > Purchasing > Trail Balance > Historical Aged Trail Balance and if you deployed std SSRS report from reporting tools setup then you can find those report in Your Company > Purchasing > Historical Aged Trail Balance mentioned by Tom & the precise view by harry.
Did you need anything advanced with this report ?
Not your problem - totally mine. I misread your post.
Tom has identified the reports you can use for this purpose.
Victoria Yudin's blog has some really good table information on SQL views for all the financial modules.
victoriayudin.com/.../sql-view-for-current-payables-aging-detail-in-dynamics-gp
There are three basic HITB reports, which deploy with the standard Dynamics GP SSRS reports. Goto SSRS Home > Company > Inventory - The all have HITB in their names.
I am not sure exactly what you need to see for a particular vendor, but here's a foundation query for HITB I happen to have laying around:
--Reports sum by item # for HITB (All HITB Activity)
Declare @ReportStartDate varchar(10)
Declare @ReportStopDate varchar(10)
Set @ReportStartDate = '2012-02-01'
Set @ReportStopDate = '2012-02-29'
select
rtrim(hi.ITEMNMBR) Item_Number,
rtrim(im.ITEMDESC) Item_Description,
sum (hi.TRXQTYInBase) Quantity, --Transaction Quantity for this transaction in Base Units of Measure
sum (hi.EXTDCOST) Extended_Value --The Extended Cost from the HITB table
from dbo.SEE30303 hi (NOLOCK) --HITB Table (holds all transaction date for Historical Aged Trial Balance Reporting)
LEFT JOIN IV00101 im (NOLOCK) ON hi.itemnmbr = im.itemnmbr --Joining this table provides Link to Item Description
where hi.GLPOSTDT between '@ReportStartDate' and @ReportStopDate --Date Range
group by hi.ITEMNMBR, im.ITEMDESC
order by hi.ITEMNMBR
---------
can you please send me the query script please, thanks
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
291,269
Super User 2024 Season 2
#2
Martin Dráb
230,198
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (