Analyze data using Query Analyzer in Business Central

Inline Query Analyzer is a tool to analyze data in Business Central. This is an open source project intended to help developers for quick retrieval of data for analysis purposes. You can write SQL like queries to retrieve data. Users can view the query results in Grid and JSON views.

How to install?

This is an open source AL Project, source code can be downloaded from https://github.com/msnraju/inline-query. This tool is using Object IDs from 50100 to 50149, you may need to renumber the objects, if that Object ID range is already used.

Want to contribute?

Anyone can contribute to this project by submitting Pull Request at https://github.com/msnraju/inline-query. Submitting Ideas and reporting bugs is also a good way of contributing.

Demo

SELECT c1, c2 FROM t

Query aggregated data in columns c1, c2 from a table

SELECT c1, c2 FROM t

Query data in columns c1, c2 from a table

SELECT * FROM t

Query all rows and columns from a table

SELECT c1, c2 FROM t WHERE condition

Query data and filter rows with a condition from a table

SELECT TOP n c1, c2 FROM t WHERE condition

Query top n filter rows, columns c1, c2 with a condition from a table

SELECT TOP n c1, c2 FROM t ORDER BY c3

Query top n rows sorted by column 3, in columns c1, c2 from a table

Not supported in the current version:

GROUP BY, HAVING, DISTINCT, JOIN, UNION

Conclusion

Inline Query is a library with a small compiler written in AL to support SQL like queries in Business Central. Inline Query Analyzer is a page that uses Inline Query library to execute SQL like queries in Business Central. In the Online / SAAS environment we do not have access to the SQL database. Using this tool, SQL like queries can be executed in SAAS environment without any access to the SQL database.

See my previous post for more details on how to use Inline Query in AL Language.

Source code can be downloaded from GitHub. Issues can be reported at https://github.com/msnraju/inline-query/issues.

#MsDyn365 #MsDyn365BC #BusinessCentral #DynamicsNAV #InlineQuery