Code Compass

  Introduction SQL allows you to write flexible and powerful queries using subqueries and Common Table Expressions (CTEs). Both help break down complex logic, reuse code, and improve readability but they work a bit differently. What is a Subquery? A subquery is a query inside another query. Below is a query which shows customers whose remaining amount is above the average. SELECT    [Customer No_],    [Remaining Amount] FROM [Customer Ledger Entry] WHERE [Remaining Amount] > (   SELECT AVG([Remaining Amount])   FROM [Customer Ledger Entry] ); What is a CTE (Common Table Expression)? A CTE is a temporary result set you can reference in a main query. It starts with the WITH keyword and improves readability, especially with multi-step logic. -- Step 1: Define the CTE WITH AvgBalance AS (   SELECT      AVG([Remaining Amount]) AS avg_balance   FROM [Customer Ledger Entry] ) ...

Introduction Window functions in SQL let you do calculations across rows related to the current row , without combining them into one. Unlike regular aggregate functions, window functions keep each row in the result; they just look at other rows while calculating. For example, you can calculate: the rank of each salesperson within their region, a running total of sales for each customer, or the difference between current and previous month’s revenue. SELECT   [Customer No_],   [Location Code],   [Remaining Amount],   SUM([Remaining Amount]) OVER (     PARTITION BY [Location Code]     ORDER BY [Customer No_]   ) AS LocationOutstandingTotal FROM [Customer Ledger Entry]; We’re calculating the sum of [Remaining Amount] OVER (...) turns it into a window function PARTITION BY [Location Code] groups the rows by location ORDER BY [Customer No_] gives a running total within each location Note: Yo...

Introduction After a new Business Central system goes live, it’s important to know if users are actually using it the way they should (Go-Live is just the beginning). Tracking usage statistics helps us understand how well the system is being adopted and where improvements might be needed. To make this easier, I’ve created a small utility that automatically collects user activity data and builds a report from it. This saves time and gives a clear picture of how the system is being used to the management. Here’s what it does: There’s a setup page where you can choose how stats are collected and skip certain users (like consultants or support team members who create test data). A configuration page lets you pick which tables to track. You can also set how often stats should be gathered using a frequency field; it even sets up the background job for you. A report which summarises the generated data in a neat matrix. With this tool, keeping an eye on system adoption becomes simple and autom...

Introduction Events are notifications that signal something has happened in the system. A record getting created, updated or deleted, a sales order getting posted, etc are all examples of events. An event driven architecture provides asynchrony and decoupled communication between different components of the system supporting flexibility, scalability and modularity. In Business Central, we have two main types of events - Business and Integration. Integration Events are related to things within Business Central. They are use for extending the functionality of the Base Application without modifying it directly. Business Events are related to things outside of Business Central. They are used for notifying external systems (e.g., via Power Automate or Logic Apps) regarding any changes that happen within Business Central. Microsoft allows us to create custom Integration and Business Events based on our requirements. In this blog, we'll see how to create a custom Business Event and...

Introduction Model Context Protocol (MCP) is an open standard that makes it easier for applications to provide information to large language models (LLMs). You can think of MCP like a USB-C port for AI —just as USB-C offers a common way to connect devices, MCP provides a standard way to link AI models with different data sources and tools. MCP uses a client-server setup , where different parts work together to share data: MCP Hosts – Programs like Claude Desktop, IDEs, or AI tools that need access to data. MCP Clients – Connect directly to servers to request and send data. MCP Servers – Small programs that provide specific functions through MCP. For this proof of concept (PoC) , I used two key parts of MCP: Resources – A way for MCP servers to share data, which clients can read and use to give better responses. Tools – Functions that let servers do tasks, such as pulling data from other systems or running commands. I built both the MCP server and client inside Cursor , writing all...

Introduction Power BI is a great tool for turning data into clear, interactive reports and the best part?  It works smoothly with Business Central, right out of the box.  You just need to set it up, and you can start viewing powerful reports right inside within Business Central dashboard.  Microsoft provides several ready-made reports, grouped into different apps, so you can pick and install only what you need.  Once set up, these reports help you track key business insights without switching between systems.  In this blog, we’ll walk you through how to set up and use Power BI reports in Business Central to make smarter decisions. References Introduction to Business Central and Power BI Install Power BI apps for Business Central Configuration Open your Business Central and search for "Assisted Setup". Click on "Connect to Power BI" Once the set up page opens, click on Next. Fiscal : A 12-month calendar that begins in any month and ends 12 months after. Standard ...