3,015
I've done some digging around and I can't figure it out. How can I get a full list of all the fixed allocation accounts and their percentages? I need to be able to export it to an excel file.
Thanks!
*This post is locked for comments
If you wanted to use a SQL query, I believe this should do it:
select
an.ACTNUMST Fixed_Allocation_Account,
a.ACTDESCR Fixed_Allocation_Account_Name,
da.ACTNUMST Distribution_Account,
dn.ACTDESCR Distribution_Account_Name,
f.PRCNTAGE Distribution_Percentage
from GL00100 a
inner join GL00105 an on a.ACTINDX = an.ACTINDX
inner join GL00103 f on a.ACTINDX = f.ACTINDX
inner join GL00105 da on f.DSTINDX = da.ACTINDX
inner join GL00100 dn on f.DSTINDX = dn.ACTINDX
where a.ACCTTYPE = 3 and a.ACTIVE = 1
and a.ACTINDX not in (select ACTINDX from GL00104)
Hi,
I don't think that using report writer would be the best option?
Pls - you don't need to build an export report - you could just build a custom report - but this requires knowledge of the underlying tables etc.
The best bet would be a SQL query?
But remember - Fixed Allocation Accounts do not have percentages in their own right - only the Distribution Accounts associated with each Fixed Alloc. Account.
If I was using the Report Writer - I would start with the report I detailed above and modify it to suit.
Ian.
Thank you Ian, that definitely put me in the right direction.
I am still at a dead end though because I am not at all familiar with report writer. I have been reading over the manual at download.microsoft.com/.../ReportWriter.pdf and it is teaching me quite a bit.
When I get down to the section about creating reports for exporting it is very vague. I am wondering if you or someone out there might be able to walk me through these steps more carefully than the guide does...
Below is a screenshot of their instructions.
Hi.
There are no perentages allocated to Fixed Allocation accounts in themselves. The percentages are allocated to the various distribution accounts associated with a Fixed Allocation account.
There is a standard GP report that lists all Fixed Allocation accounts along with the distribution accounts and percentages associated. You could modify this report in report writer to be XL friendly. The report is in Reports >> Financial >> Account >> Fixed Allocation.
Or you could run a SQL query (maybe make it a View, or a stored procedure if you wanted to create an SSRS report).
GL00103 is the Fixed Allocation master. This lists the relationships between Fixed Allocation accounts (ACTINDX) and distribution accounts (DSTINDX)...and the percentages (PRCNTAGE). You will need to link it to the GL Account Master table (GL00100) to get the account numbers / descriptions.
Ian.
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,280
Super User 2024 Season 2
#2
Martin Dráb
230,214
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (