Skip to main content

Notifications

Announcements

Join us in the Community for an AMA: December 12th Register Today
News and Announcements icon
Dynamics 365 Community / Forums / Small and medium business | Business Central, N... / Re: How to get values ...
Small and medium business | Business Central, N...
Suggested answer

Re: How to get values from multiple tables in Excel Report for Business Central

(0) ShareShare
ReportReport
Posted on by Revathi.T 76
Hi, 
 My requirement is to create an excel report with many columns from different tables. So i wrote a code like this:
report 50100 /GSTR1 Report/
{
    ApplicationArea = All;
    Caption = 'GSTR1 Excel';
    UsageCategory = /ReportsAndAnalysis/;
    ExcelLayout = 'GSTR1 Excel.xlsx';
    DefaultLayout = Excel;
    dataset
    {
        dataitem(/Sales Invoice Header/; /Sales Invoice Header/)
        {     
 
            column(Receiver_Name; /Bill-to Name/)
            {
            }
            column(Invoice_Number; /No./)
            {
            }
            column(Posting_Date; /Posting Date/)
            {
            }
            column(Place_of_Supply; /Location State Code/)
            {
            }
            dataitem(/Sales Invoice Line/; /Sales Invoice Line/)
            {
                DataItemLink = /Document No./ = field(/No./);
                DataItemLinkReference = /Sales Invoice Header/;
                DataItemTableView = sorting(/Document No./);
                column(Taxable_Value; Amount)
                {
                }
                column(Rate; Rate)
                {
                }
                column(CGST; CGST)
                {
                }
                column(SGST; SGST)
                {
                }
                column(IGST; IGST)
                {
                }
            }
            dataitem(/Cust. Ledger Entry/; /Cust. Ledger Entry/)
            {
                DataItemLink = /Document No./ = field(/No./);
                DataItemLinkReference = /Sales Invoice Header/;
                DataItemTableView = sorting(/Document No./);
                column(Invoice_Value; Amount)
                {
 
                }
            }           
            dataitem(Customer; Customer)
            {
                DataItemLink = /No./ = field(/Sell-to Customer No./);
                DataItemLinkReference = /Sales Invoice Header/;
                DataItemTableView = sorting(/No./);
                column(GST_Registration_No_; /GST Registration No./)
                {
 
                }
            }
            trigger OnAfterGetRecord()
            var
            begin
                DGST.Reset();
                DGST.SetRange(/Document No./, /No./);
                CGST := 0;
                SGST := 0;
                IGST := 0;
 
                if DGST.FindFirst() then
                    repeat
 
                        if DGST./GST Component Code/ = 'CGST' then begin
                            CGST += -DGST./GST Amount/;
                            SGST += -DGST./GST Amount/;
                            IGST := 0;
                            Rate := DGST./GST %/;
                        end; 
                        
                        if DGST./GST Component Code/ = 'IGST' then begin
                            IGST += -DGST./GST Amount/;
                            Rate := DGST./GST %/;
                            CGST := 0;
                            SGST := 0;
                        end;
 
                    until DGST.Next() = 0
 
                else begin
                    CGST := 0;
                    SGST := 0;
                    IGST := 0;
                    Rate := 0;
 
                end;
            end;
 
        }
    }
    var
        CGST: Decimal;
        SGST: Decimal;
        IGST: Decimal;
        Rate: Decimal;
        CustLedEnt: Record /Cust. Ledger Entry/;
        DGST: Record /Detailed GST Ledger Entry/;
        Cust: Record Customer;
        CustGSTRegNo: Code[20];
        InvoiceValue: Decimal;
        GST_Registration_No_: Code[20];
}
 
If I run the above code I get output as 
 
My requirement is to get values in columns in One line. but now getting Invoice value in one line, GST Registration No. in one line and all other field values in another line for the same document no. Please suggest me to all values in single line. 
Categories:
Business Central
Business Central
  • Suggested answer
    YUN ZHU Profile Picture
    YUN ZHU 73,339 Super User 2024 Season 2 on at
    Re: How to get values from multiple tables in Excel Report for Business Central
    If you insist on using Excel Layout, you need to use Excel formulas to do some processing in Excel. Here is a simple example, hoping to give you some hints.
     
    Thanks.
    ZHU
  • Suggested answer
    Inge M. Bruvik Profile Picture
    Inge M. Bruvik 32,748 Super User 2024 Season 1 on at
    Re: How to get values from multiple tables in Excel Report for Business Central
    You will get one line for each data item that is nested in your report.
    So if you want to have it all in one line you need to use only one data item., Then in the OnAfterGetRecord trigger for that data item you need to collect all the other data you want to report, put those data into variables that you then again use on your report layout.

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

Join us in the Community for an AMA: December 12th Register Today

Quick Links

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Community AMA December 12th

Join us as we continue to demystify the Dynamics 365 Contact Center

New! Quick response templatesâš¡

Save time with the new custom templates!

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,228 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,056 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans