98
Ok novice question or just clueless, How are the G_L Entries (SQL Table) linked to the G_L Register (Table)?
I have provided GL Entries to Auditor's before but 1st time that this needs to include G_L Register too in the same table.
And I am not sure the JOIN between the two table at the SQL level. I see the From Entry and the To Entry.,
if you've done this before or know, your help is appreciated. (a light bulb moment).
Thanks.
This was my answer or what work for us and I hope it helps you too
---------
So, I bring to the Power BI report two dataset: G/L Entries and G_L Register. This can be through a SQL View or a SQL Script as it is not always necessary to have all the columns from both source (NAV) tables. The me I add a column [Entry No_] from G/L Entries table and this is done by a INNER JOIN of G_L Register table. I need multiple company, but you may not so as a backdrop, I have UNION ALL the individual companies together for each: G/L Entries and G_L Register.
The INNER JOIN is the [Entry No_] BETWEEN the [From Entry No_] AND [To Entry No_]. Again, because I am multicompany I have added a column to both called [Parent] or you could call [Company} and so in the JOIN the two [Parent] columns are joined so that Register entries are unique across all companies. If for you one company just leave this off.
Again I am using a view and not a SQL Script directly in Power BI for the datasets, but a SQL Script could be used as well. This then requires either the Personal Gateway or the On-Premise Gateway so that you can refresh these if you publish to Power BI Services (to a Workspace). Else if you are local and just using Power BI Desktop then the gateway comment is not necessary. Lastly, I add to custom columns before I use merge queries in Power BI Query Editor. It is the same column to each of the datasets. only because I am multicompany. This is the Entry No which is now in both dataset and the [Parent] column which for me is the company. that way they are unique. The join is a LEFT OUTER (all from First and matching from second). If you are not multicompany then you'll just JOIN on the [Entry No_].
If you do this all in Power Query from the Merge Queries it is still complicated, but the JOIN does not give you natively the BETWEEN for both columns in the Register dataset. and you have to insert a custom script.
example (generic): if [Entry No_] >= [From Entry No_] and [Entry No_] <= [To Entry No_] then true else false
I prefer to do most if not all the hard work in SQL either is a script or a SQL view.
ps. I hope this helps. Your partner or your DBA or a data analyst might be able to help you further.
For me the goal was to add these columns from the Register dataset to the G_L Entries dataset.
you can write the below query to join G/L Register table with G/L Entry table.
Thank you so much for the prompt response. I have multiple companies. Is there no way I can build a report in Power Bi or Excel such that I can select the range and it pulls all the entries for me? What I want is the entries that were posted after GL ocked.
the g/l entries has each an entry no. as the below example
from the g/l register page, it shows the from entry and to entry no which correspond to the specific g/l entry as below example. whenever a posting happens that impacts the GL, it creates G/L register and the corresponding entries. you could export both for the auditor for their reference.
thanks for reply. The join for G_L Entries and G_L Register is does the G_L Entrie No_ exist BETWEEN G_L Register Columns From Entry No AND To Entry No.
I don’t know how to that JOIN. Has anyone done that?
Hi, Just recommending a site that hopefully will give you some more hints.
https://dynamicsdocs.com/nav/2018/w1/table/gl-register
Thanks.
ZHU
Thank you and I understand that but I intending to create a SQL View to JOIN the G_L Entries to the G_L Register; and so, needing to craft a JOIN between the two so that to the list (view) of G_L Entries I display, five (5) of the G_L Register columns with the G_L Entry columns.
Example is that G_Entry No 1034789 is in the From Entry No and G_Entry No 1034790 is in the To Entry No
#1
André Arnaud de Cal...
291,391
Super User 2024 Season 2
#2
Martin Dráb
230,445
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (
