Previous

Next

Exercise - Create a dataset for a Power BI report in Business Central

Completed

This exercise is written to use the Cronus sample company from the US version of Business Central. You might need to make adjustments to the steps if you use the sample company from your country or region.

Scenario

You've been asked to create a report where the user can see the current inventory by item. The report also needs to calculate the availability of every item based on current inventory, the number of items on purchase orders, and the number of items on sales orders.

The first step in creating such a report is to create an appropriate data set. Create a dataset for a Power BI report in Business Central.

Steps

1. In the development environment, add a new file and give it the following name: ItemAvailabilityAPI.query.al.

2. Open the .al file, and use the tquery (API Query) snippet to add a query object.

3. Next, change the query so it fetches the following fields from the Item table:

   • No.
   • Description
   • Item Category Code
   • Unit Cost
   • Unit Price
   • Inventory
   • Qty. on Sales Order
   • Qty. on Purch. Order

4. Use the DataAccessIntent property of the query object and set it to ReadOnly.

5. Add a filter to the Item data item to make sure no blocked items are queried. To do so, use the DataItemTableFilter property of the data item as follows: DataItemTableFilter = Blocked = const(false);

6. Your query should now resemble the following example:

   query 50127 ItemAvailabilityAPI
   {
       Caption = 'ItemAvailability_PowerBI';
       APIPublisher = 'contoso';
       APIGroup = 'learnPowerBI';
       APIVersion = 'v2.0';
       EntityName = 'itemAvailability';
       EntitySetName = 'itemAvailabilitys';
       QueryType = API;
       DataAccessIntent = ReadOnly;
   
       elements
       {
           dataitem(Item; Item)
           {
               DataItemTableFilter = Blocked = const(false);
               column(itemNo; "No.")
               {}
               column(itemDescription; Description)
               {}
               column(category; "Item Category Code")
               {}
               column(unitCost; "Unit Cost")
               {}
               column(unitPrice; "Unit Price")
               {}
               column(inventory; Inventory)
               {}
               column(qtyOnSalesOrder; "Qty. on Sales Order")
               {}
               column(qtyOnPurchOrder; "Qty. on Purch. Order")
               {}
           }
       }
   
       trigger OnBeforeOpen();
       begin
       end;
   }
   

7. Your query is now ready. If necessary, you can also add other fields from the Item table, if you need them for the Power BI report.

Continue