Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Business Central forum / Report using multiple ...
Business Central forum
Suggested answer

Report using multiple tables in AL

(0) ShareShare
ReportReport
Posted on by matthewjd24 75

Hello all,

I'm working on an AL extension that generates a report and it's going pretty well. I'm following Microsoft's walkthrough. However, I've come to an issue where it's not pulling the related record from the Reservation Entry table. Only the first embedded dataitem (in this case, "Prod. Order Line") is retrieving values. The second embedded dataitem ("Reservation Entry") has blank values in the report. The same is true if I add more dataitems within the Production Order dataitem, they will just be blank. It's only the first. I'm confused about why that is.

report 50003 CertificateOfConformance
{
    DefaultLayout = RDLC;
    RDLCLayout = 'MyRDLReport.rdl';

    dataset
    {
        dataitem("Production Order"; "Production Order")
        {
            column(RmsNumber; "No.") { }
            column(PartNumber; "Source No.") { }
            column(Description; Description) { }
            dataitem("Prod. Order Line"; "Prod. Order Line")
            {
                DataItemTableView = sorting("Prod. Order No.");
                DataItemLink = "Prod. Order No." = field("No.");

                column(Quantity; "Quantity") { }
            }
            dataitem("Reservation Entry"; "Reservation Entry")
            {
                DataItemTableView = sorting("Source ID");
                DataItemLink = "Source ID" = field("No.");

                column(SalesOrderNumber; "Source ID") { }
                column(EntryNumber; "Entry No.") { }
            }
        }
    }
}

Categories:
Reports and Business Intelligence
Other
  • Suggested answer
    Inge M. Bruvik Profile Picture
    Inge M. Bruvik 32,744 Moderator on at
    RE: Report using multiple tables in AL

    Very weird that you get the error running the same code as me.

    You can raise a support ticket with Microsoft through your CSP partner.

  • matthewjd24 Profile Picture
    matthewjd24 75 on at
    RE: Report using multiple tables in AL

    I haven't really modified this sandbox and we have another sandbox that I just tested it on, same result. Regarding that error when trying to send it to excel (data only), is there somewhere I can raise that issue with Microsoft?

  • Suggested answer
    Inge M. Bruvik Profile Picture
    Inge M. Bruvik 32,744 Moderator on at
    RE: Report using multiple tables in AL

    Maybe you can create a new clean sandbox and deploy the report there just to check?

  • matthewjd24 Profile Picture
    matthewjd24 75 on at
    RE: Report using multiple tables in AL

    I am using online sandbox as well.

  • Suggested answer
    Inge M. Bruvik Profile Picture
    Inge M. Bruvik 32,744 Moderator on at
    RE: Report using multiple tables in AL

    Are you on prem or running BC online?

    I am running on an online sandbox.

  • matthewjd24 Profile Picture
    matthewjd24 75 on at
    RE: Report using multiple tables in AL

    This is the error I get from debugging, in Document Service Management.dal.

    The error says <Out of Scope>. I feel like this stuff is way over my head, ugh

    pastedimage1650665017149v1.png

  • Suggested answer
    Inge M. Bruvik Profile Picture
    Inge M. Bruvik 32,744 Moderator on at
    RE: Report using multiple tables in AL

    But you did not get ant excel output?

    You have any other customization in your system that can cause the error?

    You should run it with debug from VS code and see where it runs into the error.

  • matthewjd24 Profile Picture
    matthewjd24 75 on at
    RE: Report using multiple tables in AL

    I just tried your code and I got the two pop-up messages, they had the right content, and after I closed them I got the error :(

  • Suggested answer
    Inge M. Bruvik Profile Picture
    Inge M. Bruvik 32,744 Moderator on at
    RE: Report using multiple tables in AL

    report 50149 CertificateOfConformance
{
    DefaultLayout = RDLC;
    RDLCLayout = 'MyRDLReport.rdl';
    ApplicationArea = all;
    UsageCategory = Lists;

    dataset
    {
        dataitem("Production Order"; "Production Order")
        {
            column(RmsNumber; "No.") { }
            column(PartNumber; "Source No.") { }
            column(Description; Description) { }
            dataitem("Prod. Order Line"; "Prod. Order Line")
            {
                DataItemTableView = sorting("Prod. Order No.");
                // Set a filter on the child data item, **CustLedgerEntry** to select only the records where the 
                // value of `Customer."No."` field and the `"Customer Ledger Entry"."Customer No."` field matches.
                DataItemLink = "Prod. Order No." = field("No.");

                column(Quantity; "Quantity") { }
            }
            dataitem("Reservation Entry"; "Reservation Entry")
            {
                DataItemTableView = sorting("Source ID");
                DataItemLink = "Source ID" = field("No.");

                column(SalesOrderNumber; "Source ID") { }
                column(EntryNumber; "Entry No.") { }
                trigger OnPreDataItem()
                begin
                    Message(format("Reservation Entry".Count));
                end;

                trigger OnAfterGetRecord()
                begin
                    Message(format("Reservation Entry"));
                end;
            }
        }
    }
}

    Here is my code for the report now.

    I have added some messages for debug help so i can see how many reservation lines the report read and the data on these lines. 
    You can comment those if you don't want them.

  • matthewjd24 Profile Picture
    matthewjd24 75 on at
    RE: Report using multiple tables in AL

    Do you have the same code for the report as I've posted? If so I don't know what to make of it.

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

Quick Links

Anton Venter – Community Spotlight

Kudos to our October Community Star of the month!

Announcing Our 2024 Season 2 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Dynamics 365 Community Newsletter - September 2024

Check out the latest community news

more Community News

Leaderboard

#1
André Arnaud de Calavon Profile Picture

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

#2
Martin Dráb Profile Picture

Martin Dráb 228,588 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,148

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans