Skip to main content

Notifications

Business Central forum
Suggested answer

Decoding Account Schedule Line in SQL

Posted on by 64
Hi All,

we are creating the ability to perform direct query against the underlying BC database for on premise installations.
 
We have provided easier to use names for all fields. We have decoded totaling in the GL.
 
However, the totaling field in the account schedule line is a bit more complex.
 
So I was wondering if anyone has published the code to perform totaling for account schedule line in SQL to the public.
 
I am guessing it is something that someone has published somewhere.
 
Thanks.
 
Peter
 
PS. Here is the link for the freebie to rename all tables and columns in BC023 demo database.
 
 
  • Decoding Account Schedule Line in SQL
    Hi JM 
    we have now got the code working to about 80% and we will publish it on our BIDA Brains web site in the next few days.
     
    If you would like to let me know about your company feel free to use our "contact us" page on BIDA Brains.
     
    We are happy to talk with  people in the BC area. We are pretty clear on what we are working on to sell into the marketplace. 
     
    We are doing software development for BI on BC and other systems.

    Best Regards 

    Peter 
  • Decoding Account Schedule Line in SQL
     
    my apologies. I thought that because this is a very specific finance question this would be best put on the finance forum.
     
    Best Regards 
     
    Peter Jones
  • Suggested answer
    JM Profile Picture
    JM 52 on at
    Decoding Account Schedule Line in SQL
    Hi there!
     
    The script you've used to use custom names is just a creation of views for those tables. Whether you want to summarize using the view or the source table, the approach in SQL would be the same.

    I work in a company where we have this type of reports through SQL ETL processes. We are also working with on-prem BC (version 19) for 16 different locations.
     
    Without entering in the query details (there are multiple factors to consider, as customizations on top), the approach to summarize is straightforward.
     
    You should parse the "Totaling" field from Acc. Schedule Lines in a first step to extract the account ranges you want to look for. Then you can create a CTE to calculate total amounts for each line number, based on the parsed account ranges.

    Finally you should join parsed ranges with their corresponding totals and display the results.
     
    In some cases you would need to match your Totalings with the GL Entries using a mapping in the CoA to your Acc. Schedules.

    Hope this provide you a general overview on how the query is like.
     
    Cheers!
    JM
  • Martin Dráb Profile Picture
    Martin Dráb 224,528 Super User on at
    Decoding Account Schedule Line in SQL
    Moved from Dynamics 365 Finance forum.
  • Hana Xue Profile Picture
    Hana Xue Microsoft Employee on at
    Decoding Account Schedule Line in SQL
    Hi,
    Your problem seems to be related to BC. Point you to the correct forum: Dynamics Community Forum Thread
    Best Regards,
    Hana

Helpful resources

Quick Links

Can you answer this forum question?

You could make someone's day!

Community Newsletter - May 2024

Kudos to our community stars!

Community Spotlight of the Month

Kudos to Mohamed Amine Mahmoudi!

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 283,632 Super User

#2
Martin Dráb Profile Picture

Martin Dráb 224,528 Super User

#3
nmaenpaa Profile Picture

nmaenpaa 101,146

Featured topics

Product updates

Dynamics 365 release plans