Skip to main content

Notifications

Announcements

Forum Structure Changes on 11/8 – Update Your Subscriptions!
News and Announcements icon
Dynamics 365 Community / Forums / Small and medium business | Business Central, N... / Decoding Account Sched...
Small and medium business | Business Central, N...
Suggested answer

Decoding Account Schedule Line in SQL

(1) ShareShare
ReportReport
Posted on by Peter Jones - BIDA 104
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.
 
 
Categories:
Business Central
Business Central
Business Central
Business Central
  • Peter Jones - BIDA Profile Picture
    Peter Jones - BIDA 104 on at
    Decoding Account Schedule Line in SQL
    Hi @JM
     
    because you are working with on prem BC accounts.
     
    I would like to let you know that we have just released a suite of free/open source views that create a "pseudo dimensional model" over the top of on prem BC databases.
     
    I used the BC23 Cronus UK database as the example, but you will get the idea.
     
    We are obviously suggesting that these views can be queried with Meta5 to send the data to Excel.

    But being views over BC they can be read by anything.
     
    These views are the "freebie give aways" to earn some credibility in the marketplace. :-) 
     
     
    Best Regards 
     
    Peter 
  • Suggested answer
    Peter Jones - BIDA Profile Picture
    Peter Jones - BIDA 104 on at
    Decoding Account Schedule Line in SQL
    Hi All,
     
    we have completed the creation of our demonstration.
     
    The decoding of the account line and adding up of account entries is not perfect. 
     
    But it gives a very good example of how to do this and just how much can be done using SQL CTEs over the underlying database.
     
    Thank you for all who replied. I can't seem to reply to individual replies. I am not sure where.
     
    Here is the blog post.
     
    All the code is downloadable from the button on the blog post.
     
     
    Best Regards 
     
    Peter 
  • Peter Jones - BIDA Profile Picture
    Peter Jones - BIDA 104 on at
    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 
  • Peter Jones - BIDA Profile Picture
    Peter Jones - BIDA 104 on at
    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 56 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 229,275 Most Valuable Professional 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

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

News and Announcements

Forum Structure Changes on 11/8 – Update Your Subscriptions!

Quick Links

November Spotlight Star - Khushbu Rajvi

Congratulations to a top community star!

Forum Structure Changes Coming on 11/8!

In our never-ending quest to help the Dynamics 365 Community members get answers faster …

Dynamics 365 Community Platform update – Oct 28

Welcome to the next edition of the Community Platform Update. This is a status …

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 290,900 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 229,275 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans