Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Business Central forum / Decoding Account Sched...
Business Central forum
Suggested answer

Decoding Account Schedule Line in SQL

(1) ShareShare
ReportReport
Posted on by Peter Jones - BIDA 98
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:
BI and Analytics
Administration and Setup
Financial Management
  • Peter Jones - BIDA Profile Picture
    Peter Jones - BIDA 98 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 98 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 98 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 98 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 54 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 225,864 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

Replay now available! Dynamics 365 Community Call (CRM Edition)

Catch up on the first D365 Community Call held on 7/10

Community Spotlight of the Month

Kudos to Saurav Dhyani!

Congratulations to the June Top 10 community leaders!

These stars go above and beyond . . .

more Community News

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 288,584 Super User

#2
Martin Dráb Profile Picture

Martin Dráb 225,864 Super User

#3
nmaenpaa Profile Picture

nmaenpaa 101,148

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans