Skip to main content
Business Central forum
Answered

Is there any way to find and copy the query used by the built in reports?

editSubscribe (0) ShareShare
ReportReport
Posted on by 5

Complete newbie here. 

Starting out in IT and asked to try and modify some reports in BC.

Some stuff is simple, make a column wider, add an image.

The one I am stuck with is to add the posting date to the remittance advice report #400 (which is present in table 25)

There are so many tables and I have no idea where anything is, so trying to build a new query to make a new report from scratch will be nearly impossible.

So, I am hoping I could get the query used to make the dataset in the built-in report and simply add in the extra bit.

Suggestions?

Categories:
  • Hugh_dc Profile Picture
    Hugh_dc 5 on at
    RE: Is there any way to find and copy the query used by the built in reports?

    This is a great answer, but I think it is beyond my current access and ability to implement.

    We will move to the cloud soon, so I think we will wait until then.

  • Verified answer
    YUN ZHU Profile Picture
    YUN ZHU 59,873 Super User on at
    RE: Is there any way to find and copy the query used by the built in reports?

    Yes, ReportExtension is not yet supported in BC17, so you will also need the AL file.

    There are a few simple ways here.

    1. Found from On-Premises' installation.

    For example, 

    Download the installation you needed, https://yzhums.com/24575/

    Dynamics.365.BC.38230.W1.DVD 20.0\Applications\BaseApp\Source\Base Application.Source.zip

    pastedimage1650926965982v1.png

    pastedimage1650926989986v2.png

    2. Find the source code in the Symbol File and copy it into your new AL file.

    pastedimage1650927082259v3.png

    AZ AL Dev Tools/AL Code Outline extension is required.

    pastedimage1650927134111v4.png

    3. Get to the source code of the default apps by Business Central Artifacts

    https://www.powershellgallery.com/packages/BcContainerHelper/3.0.5

    For example, Download-Artifacts -artifactUrl (Get-BCArtifactUrl) -includePlatform

    4. Get the BC Source code from github.

    https://github.com/StefanMaron/MSDyn365BC.Code.History

    Hope this helps.

    Thanks.

    ZHU

  • Hugh_dc Profile Picture
    Hugh_dc 5 on at
    RE: Is there any way to find and copy the query used by the built in reports?

    This is helpful, the bit I am still stuck on is how to get the .ai file out of BC?  It is BC 17, I can export a report, no problem. I can open that report in report builder or in visual code.

    But everything I am seeing indicates that I need a .ai file to come along with that rdl file.

  • Suggested answer
    YUN ZHU Profile Picture
    YUN ZHU 59,873 Super User on at
    RE: Is there any way to find and copy the query used by the built in reports?

    Hi, using page inspection feature you can easily find the information of the report opened.

    https://docs.microsoft.com/en-us/dynamics365/business-central/across-inspect-page#work-with-page-inspection

    For example,

    pastedimage1650847242330v1.png

    If the fields you want to add are not in the standard report, you need to use ReportExtension to add them.

    https://yzhums.com/10723/

    Hope this helps.

    Thanks.

    ZHU

  • Suggested answer
    Inge M. Bruvik Profile Picture
    Inge M. Bruvik 32,714 Super User on at
    RE: Is there any way to find and copy the query used by the built in reports?

    You have to look at the data item table view property in the report.

    And if there are several tables involved in the report you have to look at the way they are linked.

    Typical example is line 173 and 174 of report 400.

    pastedimage1650661115683v1.png

  • Hugh_dc Profile Picture
    Hugh_dc 5 on at
    RE: Is there any way to find and copy the query used by the built in reports?

    Let me change that, I can export a report, same as I have done before. But where do I find the query statement?

  • Hugh_dc Profile Picture
    Hugh_dc 5 on at
    RE: Is there any way to find and copy the query used by the built in reports?

    Ok, got that running, now how do I get the reports out?

  • Suggested answer
    Inge M. Bruvik Profile Picture
    Inge M. Bruvik 32,714 Super User on at
    RE: Is there any way to find and copy the query used by the built in reports?

    There is lots of great information here about setting up VS Code against Business Central here:

    docs.microsoft.com/.../devenv-get-started

  • Hugh_dc Profile Picture
    Hugh_dc 5 on at
    RE: Is there any way to find and copy the query used by the built in reports?

    Awesome, regarding me being a newbie at this, where do I find the source code?

  • Suggested answer
    Inge M. Bruvik Profile Picture
    Inge M. Bruvik 32,714 Super User on at
    RE: Is there any way to find and copy the query used by the built in reports?

    Well the source code for all standard reports are available so i guess the simplest and best approach is to actually look at the source code for that specific report in VS Code and learn from how it is built.  

Helpful resources

Quick Links

What Motivates a Super User?

We know many of you visit the Dynamics 365 Community and Power Platform…

Demystifying Copilot with Georg Glantschnig…

Industry experts answer burning questions directly from our amazing Community…

Setting Up Knowledge Sources for Copilot…

Look at how configuring a comprehensive knowledge base is crucial…

Leaderboard

#1
Andre Arnaud de Calavon Profile Picture

Andre Arnaud de Cal... 283,030 Super User

#2
Martin Dráb Profile Picture

Martin Dráb 222,562 Super User

#3
nmaenpaa Profile Picture

nmaenpaa 101,138

Product updates

Dynamics 365 release plans