{"id":104181,"date":"2024-11-07T21:25:02","date_gmt":"2024-11-07T21:25:02","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=104181"},"modified":"2024-12-12T20:15:30","modified_gmt":"2024-12-12T20:15:30","slug":"frappe-and-erpnext-leveraging-erp-capabilities-for-business-solutions-part-ii","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/development\/other-development\/frappe-and-erpnext-leveraging-erp-capabilities-for-business-solutions-part-ii\/","title":{"rendered":"Frappe and ERPNext: Leveraging ERP Capabilities for Business Solutions (Part II)"},"content":{"rendered":"\n<p>In the previous article (<a href=\"https:\/\/www.red-gate.com\/simple-talk\/development\/other-development\/frappe-and-erpnext-leveraging-erp-capabilities-for-business-solutions-part-i\/\">here<\/a>) touched on the overview of Frappe and ERPNext and how they\u2019re connected. We also learnt that ERPNext can be leveraged to solve challenges in business with a suite of tools at our disposal.<\/p>\n\n\n\n<p>We\u2019ll continue with using Frappe and ERPNext to carry out more functions for business solutions like customisation, data management in ERPNext, etc.<\/p>\n\n\n\n<p>Dashboards in ERPNext offer a powerful solution for visualising data and tracking key performance indicators (KPIs) in real-time. By customising these dashboards, businesses can tailor the display to meet their specific needs, ensuring that important metrics are easily accessible and relevant. Whether you&#8217;re monitoring sales performance, project progress, or inventory levels, a well-designed dashboard can provide valuable insights with just a glance.<\/p>\n\n\n\n<p>In addition to its dashboard functionality, ERPNext excels in data management. Effective data organisation is the backbone of any ERP system, and ERPNext provides a comprehensive suite of tools to manage customer records, inventory data, financial transactions, and employee details efficiently. With ERPNext, all data is captured, structured, and readily accessible, enabling organisations to make informed decisions based on real-time information.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Using Frappe Framework for Customization<\/h2>\n\n\n\n<p>Here\u2019s a detailed guide on how to use the Frappe framework for customization.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Setting Up Development Environment<\/h3>\n\n\n\n<p>These are the prerequisites you will need to set up your development environment. Versions are current as of this publication, and are subject to change.<\/p>\n\n\n<div class=\"block-core-list\">\n<ul class=\"wp-block-list\">\n<li><a href=\"https:\/\/www.python.org\/\">Python<\/a> 3.6+<\/li>\n\n\n\n<li><a href=\"https:\/\/nodejs.org\/en\">Node.js<\/a> 14+<\/li>\n\n\n\n<li><a href=\"https:\/\/redis.io\/\">Redis<\/a> 5 (caching and real time updates)<\/li>\n\n\n\n<li><a href=\"https:\/\/mariadb.org\/\">MariaDB<\/a> 10.3.x \/ Postgres 9.5.x (to run database driven apps)<\/li>\n\n\n\n<li><a href=\"https:\/\/classic.yarnpkg.com\/lang\/en\/\">yarn<\/a> 1.12+ (js dependency manager)<\/li>\n\n\n\n<li><a href=\"https:\/\/pip.pypa.io\/en\/stable\/\">pip<\/a> 20+ (py dependency manager)<\/li>\n\n\n\n<li><a href=\"https:\/\/wkhtmltopdf.org\/\">wkhtmltopdf<\/a> (version 0.12.5 with patched qt) (for pdf generation)<\/li>\n\n\n\n<li>Cron (bench&#8217;s scheduled jobs: automated certificate renewal, scheduled backups)<\/li>\n\n\n\n<li><a href=\"https:\/\/nginx.org\/en\/\">NGINX<\/a> (proxying multitenant sites in production)<\/li>\n<\/ul>\n<\/div>\n\n\n<h3 class=\"wp-block-heading\">Installation<\/h3>\n\n\n\n<p>Here I\u2019m using <a href=\"https:\/\/releases.ubuntu.com\/jammy\/\">Linux (ubuntu 22.04)<\/a> to install frappe.<\/p>\n\n\n\n<h4 class=\"wp-block-heading\">Install Bench:<\/h4>\n\n\n\n<figure class=\"wp-block-table\"><table class=\"has-fixed-layout\"><tbody><tr><td>\n<p><code>pip install frappe-bench<\/code><\/p>\n<figure><img loading=\"lazy\" decoding=\"async\" width=\"790\" height=\"578\" class=\"wp-image-104182\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2024\/10\/word-image-104181-1.png\"><\/figure><p><\/p>\n<p>To check if installation was successful:<\/p>\n<p><code>bench \u2013-version<\/code> <br><img loading=\"lazy\" decoding=\"async\" width=\"790\" height=\"67\" class=\"wp-image-104183\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2024\/10\/word-image-104181-2.png\"> <\/p>\n<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<h4 class=\"wp-block-heading\">Initialise a Bench Directory:<\/h4>\n\n\n\n<p>To initialise a frappe bench you\u2019ll need to use the following command<\/p>\n\n\n\n<p>Syntax (<em>note that the version name parameter is optional, but if you use that parameter, a version name value is required<\/em>):<\/p>\n\n\n\n<p>bench init &lt;bench_name&gt; [version_name(optional)]<\/p>\n\n\n\n<p>So to set this up, you will use:<\/p>\n\n\n\n<p>bench init my-bench<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"796\" height=\"859\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2024\/10\/word-image-104181-3.png\" alt=\"\" class=\"wp-image-104184\"\/><\/figure>\n\n\n\n<p><\/p>\n\n\n\n<h4 class=\"wp-block-heading\">Create a New Site<\/h4>\n\n\n\n<pre class=\"wp-block-preformatted\">cd my-bench\nbench new-site my-site.local<\/pre>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"941\" height=\"177\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2024\/10\/word-image-104181-4.png\" alt=\"\" class=\"wp-image-104185\"\/><\/figure>\n\n\n\n<p><\/p>\n\n\n\n<p>Run<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">Bench start<\/pre>\n\n\n\n<p>Then go to <code>https:\/\/127.0.0.1:8000\/#login<\/code>, and you should see the Frappe login screen:<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"1600\" height=\"860\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2024\/10\/word-image-104181-5.png\" alt=\"\" class=\"wp-image-104186\"\/><\/figure>\n\n\n\n<p><\/p>\n\n\n\n<p>If you get a 404 error such as this below<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"1198\" height=\"352\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2024\/10\/word-image-104181-6.png\" alt=\"\" class=\"wp-image-104187\"\/><\/figure>\n\n\n\n<p><\/p>\n\n\n\n<p>Use the command below to set the newly created site to default (of course this assumes the site has been properly configured. If this does not remedy the issue, you will need to look at the other settings and configuration that you have used.)<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">bench use my_site.local\nGet ERPNext:\nbench get-app erpnext<\/pre>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"793\" height=\"201\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2024\/10\/word-image-104181-7.png\" alt=\"\" class=\"wp-image-104188\"\/><\/figure>\n\n\n\n<p><\/p>\n\n\n\n<h4 class=\"wp-block-heading\">Install ERPNext on Your Site:<\/h4>\n\n\n\n<figure class=\"wp-block-table\"><table class=\"has-fixed-layout\"><tbody><tr><td>\n<p><code>bench install-app erpnext<\/code><\/p>\n<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"793\" height=\"131\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2024\/10\/word-image-104181-8.png\" alt=\"\" class=\"wp-image-104189\"\/><\/figure>\n\n\n\n<p><\/p>\n\n\n\n<p><strong>Start the Bench:<\/strong><\/p>\n\n\n\n<figure class=\"wp-block-table\"><table class=\"has-fixed-layout\"><tbody><tr><td>\n<p><code>bench start<\/code><\/p>\n<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<h3 class=\"wp-block-heading\"><a id=\"post-104181-_3j2qqm3\"><\/a>Creating a New App<\/h3>\n\n\n\n<p><strong>Create a New App:<\/strong><\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">bench new-app my_custom_app<\/pre>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"789\" height=\"571\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2024\/10\/word-image-104181-9.png\" alt=\"\" class=\"wp-image-104190\"\/><\/figure>\n\n\n\n<p><\/p>\n\n\n\n<h4 class=\"wp-block-heading\">Install the App on Your Site<\/h4>\n\n\n\n<pre class=\"wp-block-preformatted\">bench --site my-site.local install-app my_custom_app<\/pre>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"789\" height=\"97\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2024\/10\/word-image-104181-10.png\" alt=\"\" class=\"wp-image-104191\"\/><\/figure>\n\n\n\n<p><\/p>\n\n\n\n<h4 class=\"wp-block-heading\">Add Your App to Bench<\/h4>\n\n\n\n<pre class=\"wp-block-preformatted\">bench --site my-site.local set-config apps [\"erpnext\", \"my_custom_app\"]<\/pre>\n\n\n\n<h3 class=\"wp-block-heading\"><a id=\"post-104181-_1y810tw\"><\/a>Customizing DocTypes<\/h3>\n\n\n\n<p>DocTypes in ERPNext are the fundamental building blocks that define the structure and behaviour of the system&#8217;s various forms, reports, and data tables. Customizing DocTypes allows you to attune the data model to fit your business processes better. This can involve modifying existing DocTypes or creating new ones to capture unique data specific to your operations.<\/p>\n\n\n\n<p>Customizing DocTypes is essential when the default fields and behaviours provided by ERPNext do not fully meet your business requirements. By customising DocTypes, you can:<\/p>\n\n\n<div class=\"block-core-list\">\n<ul class=\"wp-block-list\">\n<li>Add fields specific to your business operations that are unavailable in the standard DocTypes.<\/li>\n\n\n\n<li>Implement custom validations, default values, and triggers that align with your operational workflows.<\/li>\n\n\n\n<li><a id=\"post-104181-_6lq9hwennwxx\"><\/a> Customise the layout and structure of your forms to ensure that data is organised and displayed in a way that makes sense for your users.<\/li>\n<\/ul>\n<\/div>\n\n\n<h3 class=\"wp-block-heading\">Creating a Custom Doctype<\/h3>\n\n\n\n<p>In this section I will cover creating a custom Doctype.<\/p>\n\n\n\n<h4 class=\"wp-block-heading\">Create a New Doctype<\/h4>\n\n\n\n<p>To create a new Doctype simply go to the <strong><em>build<\/em><\/strong> section<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"1539\" height=\"816\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2024\/10\/word-image-104181-11.png\" alt=\"\" class=\"wp-image-104192\"\/><\/figure>\n\n\n\n<p><\/p>\n\n\n\n<p>Click on the <em>Doctype<\/em> to navigate to the <em>Doctype<\/em> section<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"1539\" height=\"816\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2024\/10\/word-image-104181-12.png\" alt=\"\" class=\"wp-image-104193\"\/><\/figure>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"1539\" height=\"816\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2024\/10\/word-image-104181-13.png\" alt=\"\" class=\"wp-image-104194\"\/><\/figure>\n\n\n\n<p> <br><br><\/p>\n\n\n\n<p>Click on the <em>add Doctype<\/em> button to create your custom <em>Doctype<\/em><\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"1539\" height=\"816\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2024\/10\/word-image-104181-14.png\" alt=\"\" class=\"wp-image-104195\"\/><\/figure>\n\n\n\n<p><\/p>\n\n\n\n<h4 class=\"wp-block-heading\">Define Fields in the DocType<\/h4>\n\n\n\n<p>Open the generated:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">my_custom_app\/my_custom_app\/doctype\/my_custom_doctype\/my_custom_doctype.json file.<\/pre>\n\n\n\n<p>Add fields as needed.<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">{\n \"fields\": [\n   {\n     \"fieldname\": \"field1\",\n     \"fieldtype\": \"Data\",\n     \"label\": \"Field 1\"\n   },\n   {\n     \"fieldname\": \"field2\",\n     \"fieldtype\": \"Int\",\n     \"label\": \"Field 2\"\n   }\n ]\n}<\/pre>\n\n\n\n<h4 class=\"wp-block-heading\">Migrate Changes<\/h4>\n\n\n\n<pre class=\"wp-block-preformatted\">bench --site my-site.local migrate<\/pre>\n\n\n\n<h3 class=\"wp-block-heading\"><a id=\"post-104181-_2xcytpi\"><\/a>Creating Custom Scripts<\/h3>\n\n\n\n<p>Client-Side Scripts<\/p>\n\n\n\n<p>Client-side scripts allow you to enhance the UI and add custom behaviours.<\/p>\n\n\n\n<h4 class=\"wp-block-heading\">Add a Custom Script<\/h4>\n\n\n\n<p>Go to <strong>Custom Script<\/strong> in the ERPNext UI.<\/p>\n\n\n\n<p>Create a new script for the desired DocType.<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">frappe.ui.form.on('My Custom DocType', {\n   refresh: function(frm) {\n       \/\/ Custom behavior\n       frm.add_custom_button(__('Click Me'), function() {\n           frappe.msgprint(__('Button Clicked'));\n       });\n   }\n});<\/pre>\n\n\n\n<h3 class=\"wp-block-heading\">Server-Side Scripts<\/h3>\n\n\n\n<p>Server-side scripts allow you to extend backend functionality using Python.<\/p>\n\n\n\n<h4 class=\"wp-block-heading\">Add Server Script<\/h4>\n\n\n\n<p>Create a new Python file in your app, e.g.,<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">my_custom_app\/my_custom_app\/doctype\/my_custom_doctype\/my_custom_doctype.py.\nimport frappe\ndef before_save(doc, method):\n   # Custom logic before saving the document\n   doc.custom_field = \"Value set in before_save\"<\/pre>\n\n\n\n<h4 class=\"wp-block-heading\">Hook the Script to the DocType<\/h4>\n\n\n\n<p>Add the hook in <code>my_custom_app\/hooks.py<\/code>.<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">doc_events = {\n   \"My Custom DocType\": {\n       \"before_save\": \"my_custom_app.my_custom_app.doctype.my_custom_doctype.my_custom_doctype.before_save\"\n   }\n}<\/pre>\n\n\n\n<h2 class=\"wp-block-heading\"><a id=\"post-104181-_3whwml4\"><\/a>Managing Data within ERPNext<\/h2>\n\n\n\n<p>ERPNext provides robust tools for managing data, generating reports, and performing analytics to help businesses make informed decisions. Here\u2019s a detailed guide on managing data and generating reports and analytics using ERPNext.<\/p>\n\n\n\n<p><a id=\"post-104181-_2bn6wsx\"><\/a><a id=\"post-104181-_ju6p5kwrxq4\"><\/a> Effective data management is crucial for any ERP system, and ERPNext provides a comprehensive suite of tools to help organisations manage their data efficiently. Whether you&#8217;re dealing with customer records, inventory data, financial transactions, or employee details, ERPNext ensures that all data is captured, organised, and accessible in a structured way.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\"><a id=\"post-104181-_qsh70q\"><\/a>Importing Data<\/h3>\n\n\n\n<p>ERPNext allows you to import data from various sources, such as CSV files, directly into the system.<\/p>\n\n\n\n<h4 class=\"wp-block-heading\">Prepare Data for Import<\/h4>\n\n\n\n<p>Create a CSV file with the data to be imported. Ensure that the columns match the fields in the target DocType.<\/p>\n\n\n\n<h4 class=\"wp-block-heading\">Use the Data Import Tool<\/h4>\n\n\n\n<p>Go to <strong>Data Import<\/strong> in the ERPNext UI.<\/p>\n\n\n\n<p>Select the DocType you want to import data into.<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"1549\" height=\"820\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2024\/10\/word-image-104181-15.png\" alt=\"\" class=\"wp-image-104196\"\/><\/figure>\n\n\n\n<p><\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"1549\" height=\"820\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2024\/10\/word-image-104181-16.png\" alt=\"\" class=\"wp-image-104197\"\/><\/figure>\n\n\n\n<p><\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"1549\" height=\"820\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2024\/10\/word-image-104181-17.png\" alt=\"\" class=\"wp-image-104198\"\/><\/figure>\n\n\n\n<p><\/p>\n\n\n\n<h4 class=\"wp-block-heading\">Upload the CSV file<\/h4>\n\n\n\n<p>Map the CSV columns to the DocType fields.<\/p>\n\n\n\n<p>Validate the data to check for errors.<\/p>\n\n\n\n<p>Import the data.<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"1549\" height=\"820\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2024\/10\/word-image-104181-18.png\" alt=\"\" class=\"wp-image-104199\"\/><\/figure>\n\n\n\n<p><\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"1549\" height=\"820\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2024\/10\/word-image-104181-19.png\" alt=\"\" class=\"wp-image-104200\"\/><\/figure>\n\n\n\n<p><\/p>\n\n\n\n<h3 class=\"wp-block-heading\"><a id=\"post-104181-_3as4poj\"><\/a>Managing Records<\/h3>\n\n\n\n<p>ERPNext provides comprehensive forms and views to manage records for each DocType.<\/p>\n\n\n\n<h4 class=\"wp-block-heading\">List View<\/h4>\n\n\n\n<p>The list view displays all records for a DocType. You can filter, search, and sort records.<\/p>\n\n\n\n<h4 class=\"wp-block-heading\">Form View<\/h4>\n\n\n\n<p>The form view allows you to create, read, update, and delete individual records. Each form is tailored to the specific DocType.<\/p>\n\n\n\n<h4 class=\"wp-block-heading\">Bulk Operations<\/h4>\n\n\n\n<p>Perform bulk operations such as mass updates, deletions, and email notifications directly from the list view.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><a id=\"post-104181-_1pxezwc\"><\/a>Generating Reports and Analytics<\/h2>\n\n\n\n<p>ERPNext offers built-in tools for generating various types of reports and performing analytics.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\"><a id=\"post-104181-_49x2ik5\"><\/a>Standard Reports<\/h3>\n\n\n\n<p>ERPNext comes with a set of standard reports for common business needs, such as sales, purchases, inventory, and financials.<\/p>\n\n\n\n<h4 class=\"wp-block-heading\">Access Standard Reports<\/h4>\n\n\n\n<p>Go to the module (e.g., Sales, Accounting) and select <strong>Reports<\/strong>.<\/p>\n\n\n\n<p>Choose from the list of available reports (e.g., Sales Order Summary, General Ledger).<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"1549\" height=\"820\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2024\/10\/word-image-104181-20.png\" alt=\"\" class=\"wp-image-104201\"\/><\/figure>\n\n\n\n<p><\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"1549\" height=\"820\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2024\/10\/word-image-104181-21.png\" alt=\"\" class=\"wp-image-104202\"\/><\/figure>\n\n\n\n<p><\/p>\n\n\n\n<h4 class=\"wp-block-heading\">Customise Standard Reports<\/h4>\n\n\n\n<p>Use filters to customise the report view (e.g., date range, customer, status).<\/p>\n\n\n\n<p>Export reports to CSV or Excel for further analysis.<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"1549\" height=\"820\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2024\/10\/word-image-104181-22.png\" alt=\"\" class=\"wp-image-104203\"\/><\/figure>\n\n\n\n<p><\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"1549\" height=\"820\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2024\/10\/word-image-104181-23.png\" alt=\"\" class=\"wp-image-104204\"\/><\/figure>\n\n\n\n<p><\/p>\n\n\n\n<h3 class=\"wp-block-heading\"><a id=\"post-104181-_2p2csry\"><\/a>Custom Reports<\/h3>\n\n\n\n<p>Create custom reports to meet specific business requirements.<\/p>\n\n\n<div class=\"block-core-list\">\n<ul class=\"wp-block-list\">\n<li>Create a Custom Report:<\/li>\n\n\n\n<li>Go to Custom Reports.<\/li>\n\n\n\n<li>Click New Report.<\/li>\n\n\n\n<li>Choose the DocType and the type of report (e.g., Query Report, Script Report).<\/li>\n<\/ul>\n<\/div>\n\n\n<h4 class=\"wp-block-heading\">Define Report Parameters<\/h4>\n\n\n<div class=\"block-core-list\">\n<ul class=\"wp-block-list\">\n<li>Select fields to include in the report.<\/li>\n\n\n\n<li>Apply filters and conditions.<\/li>\n<\/ul>\n<\/div>\n\n\n<h4 class=\"wp-block-heading\">Write SQL Query or Script<\/h4>\n\n\n<div class=\"block-core-list\">\n<ul class=\"wp-block-list\">\n<li>For Query Reports, write SQL queries to fetch data.<\/li>\n\n\n\n<li>For Script Reports, write Python scripts to generate data.<\/li>\n<\/ul>\n<\/div>\n\n\n<h4 class=\"wp-block-heading\">Example SQL Query for a Report<\/h4>\n\n\n\n<pre class=\"wp-block-preformatted\">SELECT name, customer, grand_total\nFROM `tabSales Order`\nWHERE transaction_date BETWEEN %(from_date)s AND %(to_date)s<\/pre>\n\n\n\n<p><strong>Example Python Script Report:<\/strong><\/p>\n\n\n\n<p>Then you use the query in code like this:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">import frappe\ndef execute(filters=None):\n   columns = [\n       {\"fieldname\": \"name\", \"label\": \"Name\", \"fieldtype\": \"Data\", \"width\": 100},\n       {\"fieldname\": \"customer\", \"label\": \"Customer\", \"fieldtype\": \"Data\", \"width\": 100},\n       {\"fieldname\": \"grand_total\", \"label\": \"Grand Total\", \"fieldtype\": \"Currency\", \"width\": 100}\n   ]\n   data = frappe.db.sql(\"\"\"\n       SELECT name, customer, grand_total\n       FROM `tabSales Order`\n       WHERE transaction_date BETWEEN %(from_date)s AND %(to_date)s\n   \"\"\", filters)\n   return columns, data<\/pre>\n\n\n\n<h3 class=\"wp-block-heading\"><a id=\"post-104181-_147n2zr\"><\/a>Dashboards<\/h3>\n\n\n\n<p>ERPNext provides dashboards for a visual representation of key metrics and KPIs.<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"1539\" height=\"816\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2024\/10\/word-image-104181-24.png\" alt=\"\" class=\"wp-image-104205\"\/><\/figure>\n\n\n\n<p><\/p>\n\n\n\n<h4 class=\"wp-block-heading\">Access Dashboards<\/h4>\n\n\n\n<p>Location in ERPNext UI: Dashboards can be accessed from the main menu in the ERPNext user interface. Look for the <strong><em>Dashboard<\/em><\/strong> option under the relevant module or section (e.g., Sales, Accounts, or Projects).<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"1549\" height=\"814\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2024\/10\/word-image-104181-25.png\" alt=\"\" class=\"wp-image-104206\"\/><\/figure>\n\n\n\n<p><\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"1549\" height=\"814\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2024\/10\/word-image-104181-26.png\" alt=\"\" class=\"wp-image-104207\"\/><\/figure>\n\n\n\n<p><\/p>\n\n\n\n<p>Dashboard Types: ERPNext provides both default dashboards for various modules (like Sales, Accounting, or Human Resources) and the ability to create custom dashboards tailored to your specific needs.<\/p>\n\n\n\n<h4 class=\"wp-block-heading\">Exploring Default Dashboards<\/h4>\n\n\n\n<p>Once you open a dashboard, you&#8217;ll see a collection of pre-configured charts, graphs, and data summaries. These dashboards are designed to give you an immediate overview of the most critical data for that module, such as sales performance, financial health, or project status.<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"1549\" height=\"814\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2024\/10\/word-image-104181-27.png\" alt=\"\" class=\"wp-image-104208\"\/><\/figure>\n\n\n\n<p><\/p>\n\n\n\n<h4 class=\"wp-block-heading\">Customise Dashboards<\/h4>\n\n\n\n<p>Thes are the steps to customize your dashboards.<\/p>\n\n\n\n<p>1. <strong>Adding New Components:<\/strong><\/p>\n\n\n<div class=\"block-core-list\">\n<ul class=\"wp-block-list\">\n<li>You can add various visualisations to your dashboard, such as bar charts, line graphs, and pie charts. To do this, select &#8220;Dashboard Settings&#8221; and &#8220;Add Chart.&#8221; You&#8217;ll need to choose a data source (such as a specific DocType) and define what the chart should display (e.g., sales by month or top-selling products).<\/li>\n\n\n\n<li>Add key performance indicators (KPIs) or summary metrics to the dashboard, like total revenue, number of new customers, or outstanding invoices. These metrics provide quick, actionable insights.<\/li>\n<\/ul>\n<\/div>\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"1549\" height=\"814\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2024\/10\/word-image-104181-28.png\" alt=\"\" class=\"wp-image-104209\"\/><\/figure>\n\n\n\n<p><\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"1547\" height=\"820\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2024\/10\/word-image-104181-29.png\" alt=\"\" class=\"wp-image-104210\"\/><\/figure>\n\n\n\n<p><\/p>\n\n\n\n<p>2. <strong>Configuring Data Sources and Filters:<\/strong><\/p>\n\n\n<div class=\"block-core-list\">\n<ul class=\"wp-block-list\">\n<li><strong>Data Sources:<\/strong> Each chart or metric on your dashboard pulls data from specific sources within ERPNext, such as DocTypes, reports, or custom queries. When adding a new component, you&#8217;ll specify the source, ensuring the data displayed is accurate and up-to-date.<\/li>\n\n\n\n<li><strong>Filters:<\/strong> Apply filters to narrow down the data shown on your dashboard. For example, you can filter sales data by region, product category, or period. This allows you to focus on specific areas of interest without cluttering your dashboard with irrelevant information.<\/li>\n<\/ul>\n<\/div>\n\n\n<h3 class=\"wp-block-heading\"><a id=\"post-104181-_3o7alnk\"><\/a>Analytics<\/h3>\n\n\n\n<p>ERPNext provides different built-in analytics tools that allow you to extract valuable insights from your data. These tools are designed to help you visualise trends, monitor key performance indicators (KPIs), and make data-driven decisions without complex setups or additional software. While ERPNext\u2019s analytics tools cover many standard use cases, more advanced analytics might require integration with external platforms like Tableau or PowerBI.<\/p>\n\n\n\n<p>Using Built-In Analytics Tools in ERPNext<\/p>\n\n\n\n<h4 class=\"wp-block-heading\">Pivot Table<\/h4>\n\n\n\n<p>Pivot tables in ERPNext are powerful tools that allow you to summarise and analyse large datasets by organising data into a matrix format. This feature is similar to pivot tables in Excel, but it\u2019s integrated directly into the ERPNext interface, allowing you to work with your data without needing to export it.<\/p>\n\n\n\n<p>Create pivot tables to summarise and analyse data.<\/p>\n\n\n\n<p>Go to <strong>Pivot Table<\/strong> in the ERPNext UI.<\/p>\n\n\n\n<p>Choose the DocType (which represents the data type you want to analyse, such as Sales Orders, Invoices, or Employee Records).<\/p>\n\n\n\n<p>Select the fields you want to include in the pivot table. For instance, you might choose &#8220;Sales Region&#8221; as rows, &#8220;Product Category&#8221; as columns, and &#8220;Total Sales&#8221; as the values to aggregate.<\/p>\n\n\n\n<p>Customise the layout to display data highlighting the necessary insights, such as sorting, filtering, and applying calculated fields.<\/p>\n\n\n\n<h4 class=\"wp-block-heading\">Chart Builder<\/h4>\n\n\n\n<p>The Chart Builder in ERPNext enables you to create custom charts that visualise your data in various formats, such as bar charts, line graphs, or pie charts. These visualisations are crucial for identifying patterns, trends, and outliers in your data.<\/p>\n\n\n\n<p>Build custom charts to visualise data.<\/p>\n\n\n\n<h4 class=\"wp-block-heading\">Go to Chart Builder<\/h4>\n\n\n\n<p>Select the DocType, fields, and chart type<\/p>\n\n\n\n<p>Choose the relevant DocType and fields to visualise. For example, you might want to create a line chart showing monthly sales revenue. (bar, line, pie).<\/p>\n\n\n\n<p>Select the proper chart type based on the data you want to represent. Bar charts are excellent for comparing quantities, line charts for styles over time, and pie charts for proportions.<\/p>\n\n\n\n<p>Customise the chart by adding labels, adjusting colours, and setting filters to focus on specific data segments.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Advanced Analytics<\/h3>\n\n\n\n<p>While ERPNext&#8217;s built-in tools are robust, some organisations may require more advanced analytics capabilities. Integration with tools like Tableau or PowerBI might be necessary for complex data analysis.<\/p>\n\n\n\n<p>Why Use External Tools?<\/p>\n\n\n<div class=\"block-core-list\">\n<ul class=\"wp-block-list\">\n<li>Tools like Tableau or PowerBI can handle more complex data models and offer advanced features like machine learning integrations, predictive analytics, and interactive dashboards.<\/li>\n\n\n\n<li>These platforms can scale to analyse massive datasets across various sources, providing deeper insights and more detailed visualisations.<\/li>\n\n\n\n<li>Advanced tools allow for more granular customization of reports and dashboards, enabling a more tailored analysis experience.<\/li>\n<\/ul>\n<\/div>\n\n\n<p><strong>Integration with ERPNext:<\/strong> ERPNext can be integrated with these tools through APIs or by exporting data to be imported into the analytics platform. This allows you to sustain an efficient workflow while leveraging powerful external analytics capabilities.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><a id=\"post-104181-_23ckvvd\"><\/a>Conclusion<\/h2>\n\n\n\n<p>ERPNext is a versatile platform you can leverage to solve various business challenges, from improving operational efficiency to enhancing customer relationships. By integrating all business processes into a single platform, ERPNext provides businesses the tools they need to develop, innovate, and stay competitive in today\u2019s fast-paced market. The analytics tools in ERPNext are designed to be user-friendly yet powerful, enabling you to gain insights from your data with minimal setup. Whether you&#8217;re using pivot tables to summarise data, building custom charts for visual insights, or integrating with advanced analytics platforms, ERPNext offers the flexibility to meet your analytical needs. For organisations with more complex requirements, tools like Tableau or PowerBI can be integrated to provide even deeper data analysis and visualisation capabilities.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>In the previous article (here) touched on the overview of Frappe and ERPNext and how they\u2019re connected. We also learnt that ERPNext can be leveraged to solve challenges in business with a suite of tools at our disposal. We\u2019ll continue with using Frappe and ERPNext to carry out more functions for business solutions like customisation,&#8230;&hellip;<\/p>\n","protected":false},"author":339480,"featured_media":104213,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[147591],"tags":[159202,159233],"coauthors":[145948],"class_list":["post-104181","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-other-development","tag-frappe","tag-goodnesswoke_frappe"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/104181","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/339480"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=104181"}],"version-history":[{"count":3,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/104181\/revisions"}],"predecessor-version":[{"id":104214,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/104181\/revisions\/104214"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media\/104213"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=104181"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=104181"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=104181"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=104181"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}