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

[Tracy Yaklyvich, MBA]

I have asked this before but can’t find the answer!  How can we produce a trial balance report of non-sequential account numbers in GP?

Thanks again!  Tracy

——————————
Tracy Yaklyvich, MBA
Controller
American Society of Health-System Pharmacists
Bethesda MD
tyaklyvich@ashp.org
——————————

[Kyle Malone, CPA]

,
Can you elaborate on what you mean by non-sequential?

What are your current issues when generating the trial balance either out of GP (financial>>reports>>trial balance) or Management Reporter?

Thanks,
Kyle??

——————————
Kyle Malone, CPA
GP Admin – CGB Enterprises, Inc.
Lead Consultant/Blogger – http://ALaCarteGP.com
Chapter Leader – Louisiana (New Orleans)
——————————
——————————————-

[Tracy Yaklyvich, MBA]

Hi Kyle, we have 5 segments in our general ledger and I am looking for a report on segment 4 that contains the values of 4000, 4200 and 4500.  I cannot use a range because there are other accounts between 4000 and 4500 that I do not want to see.  Thanks!

——————————
Tracy Yaklyvich, MBA
Controller
American Society of Health-System Pharmacists
Bethesda MD
tyaklyvich@ashp.org
——————————
——————————————-

[George Kuntz, MSc]

Hi,

Would a format like this be acceptable to you?

George

——————————
George Kuntz, MSc.
Calgary, AB
——————————
——————————————-

[Tracy Yaklyvich, MBA]

Hi George, yes for some of the accounts this format would work.  For others, we need the detail.  Thanks!

——————————
Tracy Yaklyvich, MBA
Controller
American Society of Health-System Pharmacists
Bethesda MD
tyaklyvich@ashp.org
——————————
——————————————-

[Thaddeus Suter]

Well certainly it is easily done in Management Reporter with a Row Format.

If you were asking for Smartlist that would be done by building a List Restriction in Smartlist Builder where you pick the values for the Segment Pool that you want to report, and don’t  pick the others.
See example below where I selected five values in Segment 1:

——————————
Thaddeus Suter
Retus, Inc
HELOTES TX
——————————
——————————————-

[Jeff Pfershy]

Hi ,

If it’s a regular GP Trial Balance you’re looking for, you can accomplish this with Navigation Lists – here’s how:

1. Open the Financial Area page and click on Accounts in the Navigation Pane to generate your list of GL accounts:

2. Create filters if needed (1 below), mark the accounts you want included in your trial balance (2 below), click on reports and choose ‘Print Trial Balance’ (3 below and following image).

Note that you can only choose summary or detail and the year, not a range of periods.

You can also save the filtered list you created by clicking on the ‘Accounts (read only)’ menu and choosing a name for the list view so you can re-use it later:
 
Hope this gives you another option!

——————————
Jeff Pfershy
Sr Business Analyst
BluJay Solutions Inc
Holland MI
——————————
——————————————-

[George Kuntz, MSc]

Hi Tracy – does your organization have SSRS or do you have access to SQL server?

 

I’m going to send you a sql script if you do.

 

Thanks!

 

——Original Message——

Hi George, yes for some of the accounts this format would work.  For others, we need the detail.  Thanks!

——————————
Tracy Yaklyvich, MBA
Controller
American Society of Health-System Pharmacists
Bethesda MD
tyaklyvich@ashp.org
——————————

[Tracy Yaklyvich, MBA]

Hi George, yes please send me the sql script.  I am sure my IT group can help me with it.  Thanks!

——————————
Tracy Yaklyvich, MBA
Controller
American Society of Health-System Pharmacists
Bethesda MD
tyaklyvich@ashp.org
——————————
——————————————-

[George Kuntz, MSc]

See below. Sorry it took me a while, it has been a bit crazy here.  Enjoy!

— ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
— Created July 9, 2014 by Victoria Yudin
— Flexible Solutions, Inc.
— For other code, please visit http://victoriayudin.com
— Only returns the first open year in GP
— Month names in columns use calendar fiscal year
— ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
— Modified 10.26.2018
select
g.YEAR1 [Year],
a.ACTNUMST Account,
g.ACTDESCR [Description],
c.ACCATDSC Category,
sum(case g.PERIODID when 0
then g.PERDBLNC else 0 end) Beginning_Balance,
sum(case when g.PERIODID <= 1
then g.DEBITAMT-g.CRDTAMNT else 0 end) Jan_Balance,
sum(case when g.PERIODID <= 2
then g.DEBITAMT-g.CRDTAMNT else 0 end) Feb_Balance,
sum(case when g.PERIODID <= 3
then g.DEBITAMT-g.CRDTAMNT else 0 end) Mar_Balance,
sum(case when g.PERIODID <= 4
then g.DEBITAMT-g.CRDTAMNT else 0 end) Apr_Balance,
sum(case when g.PERIODID <= 5
then g.DEBITAMT-g.CRDTAMNT else 0 end) May_Balance,
sum(case when g.PERIODID <= 6
then g.DEBITAMT-g.CRDTAMNT else 0 end) Jun_Balance,
sum(case when g.PERIODID <= 7
then g.DEBITAMT-g.CRDTAMNT else 0 end) Jul_Balance,
sum(case when g.PERIODID <= 8
then g.DEBITAMT-g.CRDTAMNT else 0 end) Aug_Balance,
sum(case when g.PERIODID <= 9
then g.DEBITAMT-g.CRDTAMNT else 0 end) Sep_Balance,
sum(case when g.PERIODID <= 10
then g.DEBITAMT-g.CRDTAMNT else 0 end) Oct_Balance,
sum(case when g.PERIODID <= 11
then g.DEBITAMT-g.CRDTAMNT else 0 end) Nov_Balance,
sum(case when g.PERIODID <= 12
then g.DEBITAMT-g.CRDTAMNT else 0 end) Dec_Balance

from GL11110 g –GL summary data

inner join GL00102 c –categories
on g.ACCATNUM = c.ACCATNUM

inner join GL00105 a –for account number
on g.ACTINDX = a.ACTINDX

where g.ACCTTYPE = 1
and g.YEAR1 = (select min(YEAR1) from GL11110)
and a.ACTNUMST like ‘%4000%’ or a.ACTNUMST like ‘%4200%’ or a.ACTNUMST like ‘%4500%’

group by g.YEAR1, g.ACTDESCR, a.ACTNUMST, c.ACCATDSC

——————————
George Kuntz, MSc
George Kuntz
Calgary
——————————
——————————————-