Get Vendor Ledger Entries for all the companies in Sandbox using AL DataSet

Hi all,

I am new to Dynamics 365 Business Central Cloud. I have to create a report using AL, in which i have to get Vendor Ledger Entries of all the companies in Sandbox and create report in RDL. So far i was able to achieve this in Power BI where i connected to my sandbox and got Vendor Ledger Entries for each Company and merged them to show on single company(consolidated) but in VSC and AL i am unable to do so.

Please point to right direction

Thanks

you need to use loop and CHANGECOMANY for the list of companies and do loop inside for VLE (Vendor Ledger Entry). It is hard to say deeper because you need to write more about this report.

I am wornidering how i can loop and changecompany and select VLE. So far i was able to select multiple companies but change company is not happening and i am getting result of MY COMPANY. Here is the code of what i have done so far

This is repeating Amount of original MyCompany in other companies

dataset
{
    dataitem(Company; Company)
    {
        column(Name;Name){}
        dataitem("Vendor Ledger Entry"; "Vendor Ledger Entry")
        {
            column(Vendor_No_; Vendor_No_){}
            column(Amount; Amount){}
        }
    }
}

I can’t see the loop for companies in your report and CHANGECOMPANY usage.

And what do you want to see in this report? Do you need to have data only or to see it on the page?

I dont know how to loop and change company and i want to see data only. Thanks

Hello

It should be basic knowledge that if you want ot read some data from a specific company then you have to either change company in the client or do some code that makes that switch for you. If you run a local NAV/BC database and opens up SQL-management studio then you will se that the Vender Ledger Entries are not in one table but several, and the tableid is named as ‘Companyname’$Vendor Ledger Entry.

So basically you have to do a loop where you for each company finds the records you need. Depending on how you want to do the printing you probably have to transfer the data to a temporary table and then print the data on the report using that table.

I have no idea about your NAV/BC development skills, but if you are not an experienced developer this might be above your league of of skills - and you will probably get more than a few difficult issues in the steps that follows after the CHANGECOMPANY function.

Thanks for response Palle. I am using BC. I have already done this in PowerBI but have trouble in understanding CHANGECOMPANY in AL, because of it i am unable to set dataset for report

Well you cannot just make a dataset on a report - you have to create some code that creates the mixed dataset you need. Remember data is coming from different tables in SQL.

Exactly, data is coming from different tables from SQL, We can append/join/merge in SQL. But in AL i dont find anyway to achieve this.

The steps are

  1. Create two variables linke to the tables Company and Vendor Ledger Entry

  2. Create a new variable with the Vendor Ledger Entry as tempoary

  3. Create a Loop of Companies

  4. For every Loop of Company copy the Vendor Ledger Entries into the temporary table

  5. Use that dataset for whatever you need