When it comes to business intelligence, anyone who has made the transition from Microsoft Dynamics GP to Business Central begins to miss the eOne Solutions SmartList Builder tool. SmartList Builder allows even the lay user to easily pull data from any GP table. Now, thanks to eOne’s Query Builder tool, users can pull data from any Business Central table with just as much ease.

Although the purchase of eOne’s Popdock is required to utilize Query Builder, this purchase will pay for itself once report designers see that they have a “one-stop shop” for reporting on any of Business Central’s data. The users who do not have access to Business Central can instead be given access to Popdock lists (or reports) created through Query Builder.  Also, without Query Builder, there is no easy means of accessing Business Central tables.

In this hypothetical example, let’s say that you want to access amounts posted to Cost of Goods Sold, along with their sales invoice numbers and customer names.

From within Popdock, navigate to “Connectors”.

In this development environment, we would click the pencil icon to the far right of the connector option labeled “Dynamics 365 Business Central – John Sandbox”.

On the left, under Settings, select “Lists”:

Scroll down to find “Query Builder” and click .

After entering a “Name”, click the tab button on your keyboard to default “Item name”, “Item singular”, and “API endpoint”.  Next, choose the appropriate module for “Group”:

With this information entered, click the “Query builder” selection shown above.

Select “Click here to add a table”.

From the “Table” drop-down list, choose “G/L Account” and click “Add table”.

In the “Edit list – COGS” window, click “G/L Account” to choose any of the fields shown on the right.

*For this example, we will select “No”.

Within the G/L Account table that you added, click on “No.”  On the right, choose “Click here to add a restriction”:

Not only does a drop-down list for “Type” appear in the Restrictions section on the right, but the “No.” field in the table now has a filter icon on its left:

In “Restrictions”, choose Equals for “Type” and enter the Cost of Goods Sold account number from the general ledger for “Value”:

Next, click .

Here, you’ll search for G/L Entry within the “Table” drop-down list.

In “From field” and “To field”, you’ll choose the matching fields between the two tables to join them.

Next, click “Add table”.

(Again, those who know SmartList Builder in GP will be very familiar with this process.)

As you see here, you need to select fields for “G/L Entry”:

Once you click on “G/L Entry”, you can choose fields that appear for selection on the far right.

Choose “Amount”, “Document No”, “Posting Date”, and “User ID”.  Your two tables will appear, as follows:

Now, you need to pull in customer data.  Once again, click .

Here, you’ll search for “Sales Invoice Header” within the “Table” drop-down list.

In “From field” and “To field”, you’ll choose the matching fields between the two tables to join them.

After this, once again click .

Now, in the “Fields” section on the right-hand side of the window, select “Bill-to Customer No.” and “Name”.  Now, here are your three tables:

In “Actions” on the left, click “Publish”:

After waiting for the dialog box shown above to disappear, you should get a message reporting success:

Select “OK”.

Return to the Popdock home page by clicking the logo in the upper left.

In the top middle of the home page, click .

As seen above choose “Find by connector”, update the five fields, and click “Add”.

Now, you’ll be taken to your report layout, where you can add fields.

Begin by selecting “No.” and clicking

Repeat this for the other fields to add:

The following is the result:

Congratulations you are now done. If you have any questions about this process or how to maximize your Business Central environment please reach out to us at Support@BondConsultingServices.com