Imagine creating charts in Business Central simply by describing your requirements. Perhaps not everyone is familiar with the standard BusinessChart tool, a Control Add-In that allows for the creation of custom charts directly in AL. Using this tool, I set out to develop a Copilot that enables users to request charts in natural language—whether it’s bar charts, line graphs, or other types—generating them instantly. In this article, I’ll share how this idea can be developed.

Technical requirements:

• Microsoft Dynamics 365 Business Central and technical knowledge of the platform
• Azure subscription
• Azure OpenAI Service resource with a deployed model

Before we dive into the implementation details, let’s first watch a video demonstrating the final outcome:

You can find the source code on my GitHub.

Charts in Business Central

Before diving into the details of the AI component, it’s important to understand how the standard Business Chart add-in works, as used on various standard pages (for example, page 1392, “Help and Chart Wrapper”). This add-in is designed to create charts like the one shown below:

To create a page like this in Business Central, you need to define the BusinessChart control add-in within a dedicated page and implement a procedure to load and update the data within the chart. Below is the structure of the page used in our case.

This page contains two essential procedures:

1. SetParameters: This procedure sets the input for chart creation. The parameters include:
   • Table Id: Specifies the table from which the data will be sourced.
   • Chart Type: Indicates the type of chart to be generated (e.g., pie, line, column, etc.).
   • X Axis: The field from the table to be represented on the X-axis.
   • Y Axis: The field from the table to be represented on the Y-axis.
2. Load: This procedure calculates the data to be inserted into the chart. It utilizes the standard “Business Chart Buffer” table, which is essential for chart creation.

Below are the details of these two procedures:

Charting with AI

How can we integrate AI into this context? The goal is to allow users to describe the chart they want based on the page they are currently on. For example, if the user is on the item page, they could request a chart that displays the inventory value for each item in a column format.

Considering the parameters needed for chart generation (as outlined in the SetParameters procedure above), the Table Id will be derived from the current page the user is on. For the remaining parameters—such as chart type, X-axis, and Y-axis—we rely on the AI to interpret the user’s input and generate them accordingly based on the request.

AI in Action

Now is the time to leverage AI. Specifically, we will use Azure OpenAI, Microsoft’s cloud service that provides access to OpenAI’s powerful language models. We need to have an API endpoint that can be called from Business Central, allowing us to interact with the AI models. To create an Azure OpenAI endpoint, you need to provision an Azure OpenAI resource, configure its access permissions, and obtain the API key and endpoint URL for integrating it into your application. For more details, follow this guide.

Once this phase is completed, necessary references for the Azure OpenAI API should be input on a setup page in Business Central previously created..

The user will be able to input their requests through a PromptDialog page. 

This page will also contain the necessary logic to query the Azure OpenAI API using the standard “Azure OpenAI” codeunit and other supporting codeunits. You can find the complete code directly on GitHub.

Here are the three most important procedures of this app. 

The first procedure, called GetData, takes the user prompt and the page from which the user invoked the Copilot (parameters PageNo and UserPrompt) as input, and returns all the parameters required for generating the chart, including the chart type, x-axis, and y-axis.

The second procedure is Generate and it is internal to GetData and is responsible for managing the calls to the Azure OpenAI APIs.

The third procedure called GetJsonData, also internal to GetData, is responsible for “extracting” the information we need from the response received from Azure OpenAI.

As you can see, we will extract this information from a JSON, as we will enforce a response in this format, as we will see later.

The Prompt

Now we are at the core part: the definition of the prompt. A prompt is an input provided to an AI model to guide its response or action. We also make a distinction between system prompt and user prompt:

• System Prompt: This is an internal instruction or context provided to the model that defines its behavior, rules, and capabilities. It is used to establish the working environment of the AI.
• User Prompt: This is the specific input provided by the user, containing a question or request. It represents what the user wishes to know or obtain from the model.

In summary, the system prompt determines how the AI operates, while the user prompt is the active interaction of the user with the AI.

In our case, I have created a system prompt that allows the AI to generate a result with the following characteristics:

• The response must contain exclusively the three necessary pieces of information: the type of chart, the field for the x-axis, and the field for the y-axis.
• It is crucial that the provided values are accurate and correspond to the terminology used in Business Central. For example, the ID of the field for the x-axis must be returned exactly as it appears in Business Central.
• To facilitate the extraction of values, the response must be structured in JSON format.

With these premises, I have generated the following system prompt:

Determine the appropriate x-axis, y-axis fields and chart type for a Chart based on user input. 

Your goal is not to generate the chart but only to extract the x-axis, y-axis, and chart type fields from the user input. 

Possible Fields in CSV format:

{Field_list_here}

Possible Chart Types with description between bracket:

Point (Uses points to represent data points.); Bubble (A variation of the Point chart type, where the data points are replaced by bubbles of different sizes.); Line (Illustrates trends in data with the passing of time.); StepLine (Similar to the Line chart type, but uses vertical and horizontal lines to connect the data points in a series forming a step-like progression.); Column (Uses a sequence of columns to compare values across categories.); StackedColumn (Used to compare the contribution of each value to a total across categories.); StackedColumn100 (Displays multiple series of data as stacked columns. The cumulative proportion of each stacked element is always 100% of the Y axis.); Area (Emphasizes the degree of change over time and shows the relationship of the parts to a whole.); StackedArea (An Area chart that stacks two or more data series on top of one another.); StackedArea100 (Displays multiple series of data as stacked areas. The cumulative proportion of each stacked element is always 100% of the Y axis.); Pie (Shows how proportions of data, shown as pie-shaped pieces, contribute to the data as a whole.); Doughnut (Similar to the Pie chart type, except that it has a hole in the center.); Range (Displays a range of data by plotting two Y values per data point, with each Y value being drawn as a line chart.); Radar (A circular chart that is used primarily as a data comparison tool.); Funnel (Displays in a funnel shape data that equals 100% when totaled.).

Output Requirements: the result must always be in JSON format without any additional text. 

Use the following structure for the JSON output:

{
    “chartType”: “Chart_type”,
    “x_axis”: FieldId_for_x_axis,
    “y_axis”: FieldId_for_y_axis
}

The response MUST BE just the json as per the example without any other text or sentences.

In the system prompt, in the section marked with {Field_list_here}, a procedure has been implemented to retrieve all the IDs and names of the fields present on the page currently displayed to the user, incorporating them into the prompt.

The prompt may seem lengthy. However, considering a page with 50 fields (a scenario that is not common), I estimate that the prompt could consume about 800 tokens. Given that the limit of the model used is 120,000 tokens per minute, this usage remains acceptable. Under these conditions, the service would be able to handle up to 150 API calls per minute.

Below, I present the procedure used to extract the fields from the page where the user initiated the Copilot:

The information extracted from the model’s response will be used later for creating the chart through the standard Business Central functions described above.

Conclusions

In this concluding section, I would like to examine some limitations of the proposed solution and suggest possible remedies. Below, I outline the main limitations encountered:

• Prompt Length Issue: If you require a longer prompt that exceeds the token limits, you might consider using the embedding technique. This link illustrates a potential solution to a similar problem in a more general context, not specific to Business Central.
• Limited Access to ToolTip Metadata: In Business Central, it is possible to define the ToolTip property for each table field, which gives useful details for field usage. This information would be valuable for making the prompt even more precise. However, ToolTips are not accessible through AL, as the virtual tables “Page Table Field” and “Page Info And Fields” have limited access and are not available in the cloud.
• JSON Response Structure: As noted, we specified in the prompt that the response must be provided in JSON format. Recently, both OpenAI and Microsoft have released updates that improve the ability to enforce structured output. Please refer to this article for more details. In Business Central, it is possible to use the SetJsonMode function from the “AOAI Chat Completion Params” codeunit, but this is not compatible with all models available in Azure OpenAI.

I hope this article has been helpful. For more similar content, follow me on LinkedIn to stay updated.

‍

About the Author:

Mario Longo

I am currently working as Head of Development of a team that develops apps for Microsoft Dynamics 365 Business Central. When I’m not working I enjoy making music, reading about news, science and technology.

Reference:

Longo, M (2024). Create Charts with Business Central and Azure OpenAI. Available at: Create Charts with Business Central and Azure OpenAI – ML blog [Accessed: 18th October 2024].