{"id":1079,"date":"2023-03-08T08:43:10","date_gmt":"2023-03-08T08:43:10","guid":{"rendered":"https:\/\/www.greytrix.com\/blogs\/acumatica\/?p=1079"},"modified":"2024-11-29T06:21:53","modified_gmt":"2024-11-29T06:21:53","slug":"using-a-custom-sql-view-in-acumatica-report","status":"publish","type":"post","link":"https:\/\/www.greytrix.com\/blogs\/acumatica\/2023\/03\/08\/using-a-custom-sql-view-in-acumatica-report\/","title":{"rendered":"Using a custom SQL view in Acumatica Report"},"content":{"rendered":"\n

Acumatica have built in functionality that allow user to add a custom SQL view into Acumatica Reports and GI. Importing SQL views into Acumatica is quite easy. All you need is to create views and create the data access class in Acumatica.<\/p>\n\n\n\n

In this blog we will learn how to add views in Acumatica systems how to create a SQL view and link it to an Acumatica data access class (DAC) so that it may be used by Acumatica reporting tools.<\/p>\n\n\n\n

Link the SQL view in the Acumatica application using Acumatica<\/a> customization tools Generate reports using the SQL view we created.<\/p>\n\n\n\n

Example: Here, we take a customer whose invoice item range is greater than 10 to display total product quantity and total amount.<\/p>\n\n\n\n

Step 1: SQL View creation<\/strong><\/p>\n\n\n\n

The SQL statement below are accustomed select facts that may be to the purpose in producing a Customer Invoice Report text record. It is an honest idea to make SQL view, during this we are able to get the total quantity total amount and we can extract data from multiple tables.<\/p>\n\n\n\n

\"01<\/a><\/center><\/code><\/pre>\n\n\n\n
Step 2: SQL view Link in Acumatica<\/strong><\/h3>\n\n\n\n
1. Create Customization Project: <\/strong>Click the customization button in the upper right c<\/h3>\n\n\n\n
The SQL statement below are accustomed select facts that may be to the purpose in producing a Customer Invoice Report text record. It is an honest idea to make SQL view, during this we are able to get the total quantity total amount and we can extract data from multiple tables.<\/p>\n\n\n\n
2. Open customization project editor<\/p>\n\n\n\n
3.  within the customization project editor, create a new code file using the template New DAC as below:<\/p>\n\n\n\n
\"02<\/a><\/center><\/code><\/pre>\n\n\n\n
Select code click on plus (+) button add a new object<\/p>\n\n\n\n
Select New DAC as the File Template<\/p>\n\n\n\n
Enter the name of your View that you created in the prior step. The class name must match the name of the view you created.<\/p>\n\n\n\n
Check the box to come up with members from the database. this feature will cause Acumatica<\/a> to automatically create a code file that supports the weather within the table you just created.<\/p>\n\n\n\n
The result’s the code that you simply see below: <\/p>\n\n\n\n
\"04<\/a><\/center><\/code><\/pre>\n\n\n\n
After completing your customization, Publish the customization project. To do that, within the main menu of the Customization Project Editor, select<\/p>\n\n\n\n
Publish > Publish Current Project<\/strong><\/p>\n\n\n\n
Example:<\/p>\n\n\n\n
Open generic inquiry (SM208000) and click on plus (+) button to create a record as below enter title, select the new table that you created.<\/p>\n\n\n\n
\"05<\/a><\/center><\/code><\/pre>\n\n\n\n
Click on RESULTS GRID select Object name select Data Field  On the form toolbar, click Save<\/p>\n\n\n\n
Click on view inquiry<\/p>\n\n\n\n
\"05<\/a><\/center><\/code><\/pre>\n\n\n\n
Example 2: include SQL view in report designer<\/p>\n\n\n\n
In Acumatica<\/a> report designer using SQL view<\/p>\n\n\n\n
Open the Report Designer<\/h3>\n\n\n\n
Select the Build Schema Button<\/p>\n\n\n\n
Enter your URL, login, password and click Load Schema<\/p>\n\n\n\n
Select your SQL view in report object.<\/p>\n\n\n\n
\"07<\/a><\/center><\/code><\/pre>\n\n\n\n
This blog makes it easy to include external data into Acumatica reports.<\/p>\n\n\n\n
Hope this blog helps you to feature a SQL view to Acumatica<\/a>.<\/p>\n\n\n\n
<\/p>\n","protected":false},"excerpt":{"rendered":"
Acumatica have built in functionality that allow user to add a custom SQL view into Acumatica Reports and GI. Importing SQL views into Acumatica is quite easy. All you need is to create views and create the data access class in Acumatica. In this blog we will learn how to add views in Acumatica systems\u2026 Read More »<\/a><\/span><\/p>\n","protected":false},"author":2,"featured_media":2552,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[146,3,14],"tags":[4,159,16],"class_list":["post-1079","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-acumatica","category-acumatica-erp","category-acumatica-services","tag-acumatica","tag-acumatica-customization","tag-acumatica-erp"],"yoast_head":"\nUtilizing Custom SQL Views in Acumatica Reports<\/title>\n<meta name=\"description\" content=\"Learn how to leverage custom SQL views in Acumatica reports for enhanced data analysis and reporting efficiency.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.greytrix.com\/blogs\/acumatica\/2023\/03\/08\/using-a-custom-sql-view-in-acumatica-report\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Utilizing Custom SQL Views in Acumatica Reports\" \/>\n<meta property=\"og:description\" content=\"Learn how to leverage custom SQL views in Acumatica reports for enhanced data analysis and reporting efficiency.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.greytrix.com\/blogs\/acumatica\/2023\/03\/08\/using-a-custom-sql-view-in-acumatica-report\/\" \/>\n<meta property=\"og:site_name\" content=\"Acumatica \u2013 Tips, Tricks and Components\" \/>\n<meta property=\"article:published_time\" content=\"2023-03-08T08:43:10+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2024-11-29T06:21:53+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.greytrix.com\/blogs\/acumatica\/wp-content\/uploads\/2023\/03\/05-Generic-Inquiry-2-1-e1721628268425.png\" \/>\n\t<meta property=\"og:image:width\" content=\"500\" \/>\n\t<meta property=\"og:image:height\" content=\"232\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"author\" content=\"greytrix\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"greytrix\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.greytrix.com\/blogs\/acumatica\/2023\/03\/08\/using-a-custom-sql-view-in-acumatica-report\/\",\"url\":\"https:\/\/www.greytrix.com\/blogs\/acumatica\/2023\/03\/08\/using-a-custom-sql-view-in-acumatica-report\/\",\"name\":\"Utilizing Custom SQL Views in Acumatica Reports\",\"isPartOf\":{\"@id\":\"https:\/\/www.greytrix.com\/blogs\/acumatica\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/www.greytrix.com\/blogs\/acumatica\/2023\/03\/08\/using-a-custom-sql-view-in-acumatica-report\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/www.greytrix.com\/blogs\/acumatica\/2023\/03\/08\/using-a-custom-sql-view-in-acumatica-report\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/www.greytrix.com\/blogs\/acumatica\/wp-content\/uploads\/2023\/03\/05-Generic-Inquiry-2-1-e1721628268425.png\",\"datePublished\":\"2023-03-08T08:43:10+00:00\",\"dateModified\":\"2024-11-29T06:21:53+00:00\",\"author\":{\"@id\":\"https:\/\/www.greytrix.com\/blogs\/acumatica\/#\/schema\/person\/c49d53021d39726d820c6fd517b2c2ec\"},\"description\":\"Learn how to leverage custom SQL views in Acumatica reports for enhanced data analysis and reporting efficiency.\",\"breadcrumb\":{\"@id\":\"https:\/\/www.greytrix.com\/blogs\/acumatica\/2023\/03\/08\/using-a-custom-sql-view-in-acumatica-report\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.greytrix.com\/blogs\/acumatica\/2023\/03\/08\/using-a-custom-sql-view-in-acumatica-report\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.greytrix.com\/blogs\/acumatica\/2023\/03\/08\/using-a-custom-sql-view-in-acumatica-report\/#primaryimage\",\"url\":\"https:\/\/www.greytrix.com\/blogs\/acumatica\/wp-content\/uploads\/2023\/03\/05-Generic-Inquiry-2-1-e1721628268425.png\",\"contentUrl\":\"https:\/\/www.greytrix.com\/blogs\/acumatica\/wp-content\/uploads\/2023\/03\/05-Generic-Inquiry-2-1-e1721628268425.png\",\"width\":500,\"height\":232,\"caption\":\"Custom SQL view - Acumatica\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.greytrix.com\/blogs\/acumatica\/2023\/03\/08\/using-a-custom-sql-view-in-acumatica-report\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.greytrix.com\/blogs\/acumatica\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Using a custom SQL view in Acumatica Report\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.greytrix.com\/blogs\/acumatica\/#website\",\"url\":\"https:\/\/www.greytrix.com\/blogs\/acumatica\/\",\"name\":\"Acumatica \u2013 Tips, Tricks and Components\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.greytrix.com\/blogs\/acumatica\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.greytrix.com\/blogs\/acumatica\/#\/schema\/person\/c49d53021d39726d820c6fd517b2c2ec\",\"name\":\"greytrix\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.greytrix.com\/blogs\/acumatica\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/3cdb553d6ca07df0e407d5e092f2410d163e0fd40b27734b345b04c8f0786969?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/3cdb553d6ca07df0e407d5e092f2410d163e0fd40b27734b345b04c8f0786969?s=96&d=mm&r=g\",\"caption\":\"greytrix\"},\"url\":\"https:\/\/www.greytrix.com\/blogs\/acumatica\/author\/greytrix\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Utilizing Custom SQL Views in Acumatica Reports","description":"Learn how to leverage custom SQL views in Acumatica reports for enhanced data analysis and reporting efficiency.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.greytrix.com\/blogs\/acumatica\/2023\/03\/08\/using-a-custom-sql-view-in-acumatica-report\/","og_locale":"en_US","og_type":"article","og_title":"Utilizing Custom SQL Views in Acumatica Reports","og_description":"Learn how to leverage custom SQL views in Acumatica reports for enhanced data analysis and reporting efficiency.","og_url":"https:\/\/www.greytrix.com\/blogs\/acumatica\/2023\/03\/08\/using-a-custom-sql-view-in-acumatica-report\/","og_site_name":"Acumatica \u2013 Tips, Tricks and Components","article_published_time":"2023-03-08T08:43:10+00:00","article_modified_time":"2024-11-29T06:21:53+00:00","og_image":[{"width":500,"height":232,"url":"https:\/\/www.greytrix.com\/blogs\/acumatica\/wp-content\/uploads\/2023\/03\/05-Generic-Inquiry-2-1-e1721628268425.png","type":"image\/png"}],"author":"greytrix","twitter_card":"summary_large_image","twitter_misc":{"Written by":"greytrix","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.greytrix.com\/blogs\/acumatica\/2023\/03\/08\/using-a-custom-sql-view-in-acumatica-report\/","url":"https:\/\/www.greytrix.com\/blogs\/acumatica\/2023\/03\/08\/using-a-custom-sql-view-in-acumatica-report\/","name":"Utilizing Custom SQL Views in Acumatica Reports","isPartOf":{"@id":"https:\/\/www.greytrix.com\/blogs\/acumatica\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.greytrix.com\/blogs\/acumatica\/2023\/03\/08\/using-a-custom-sql-view-in-acumatica-report\/#primaryimage"},"image":{"@id":"https:\/\/www.greytrix.com\/blogs\/acumatica\/2023\/03\/08\/using-a-custom-sql-view-in-acumatica-report\/#primaryimage"},"thumbnailUrl":"https:\/\/www.greytrix.com\/blogs\/acumatica\/wp-content\/uploads\/2023\/03\/05-Generic-Inquiry-2-1-e1721628268425.png","datePublished":"2023-03-08T08:43:10+00:00","dateModified":"2024-11-29T06:21:53+00:00","author":{"@id":"https:\/\/www.greytrix.com\/blogs\/acumatica\/#\/schema\/person\/c49d53021d39726d820c6fd517b2c2ec"},"description":"Learn how to leverage custom SQL views in Acumatica reports for enhanced data analysis and reporting efficiency.","breadcrumb":{"@id":"https:\/\/www.greytrix.com\/blogs\/acumatica\/2023\/03\/08\/using-a-custom-sql-view-in-acumatica-report\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.greytrix.com\/blogs\/acumatica\/2023\/03\/08\/using-a-custom-sql-view-in-acumatica-report\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.greytrix.com\/blogs\/acumatica\/2023\/03\/08\/using-a-custom-sql-view-in-acumatica-report\/#primaryimage","url":"https:\/\/www.greytrix.com\/blogs\/acumatica\/wp-content\/uploads\/2023\/03\/05-Generic-Inquiry-2-1-e1721628268425.png","contentUrl":"https:\/\/www.greytrix.com\/blogs\/acumatica\/wp-content\/uploads\/2023\/03\/05-Generic-Inquiry-2-1-e1721628268425.png","width":500,"height":232,"caption":"Custom SQL view - Acumatica"},{"@type":"BreadcrumbList","@id":"https:\/\/www.greytrix.com\/blogs\/acumatica\/2023\/03\/08\/using-a-custom-sql-view-in-acumatica-report\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.greytrix.com\/blogs\/acumatica\/"},{"@type":"ListItem","position":2,"name":"Using a custom SQL view in Acumatica Report"}]},{"@type":"WebSite","@id":"https:\/\/www.greytrix.com\/blogs\/acumatica\/#website","url":"https:\/\/www.greytrix.com\/blogs\/acumatica\/","name":"Acumatica \u2013 Tips, Tricks and Components","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.greytrix.com\/blogs\/acumatica\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.greytrix.com\/blogs\/acumatica\/#\/schema\/person\/c49d53021d39726d820c6fd517b2c2ec","name":"greytrix","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.greytrix.com\/blogs\/acumatica\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/3cdb553d6ca07df0e407d5e092f2410d163e0fd40b27734b345b04c8f0786969?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/3cdb553d6ca07df0e407d5e092f2410d163e0fd40b27734b345b04c8f0786969?s=96&d=mm&r=g","caption":"greytrix"},"url":"https:\/\/www.greytrix.com\/blogs\/acumatica\/author\/greytrix\/"}]}},"_links":{"self":[{"href":"https:\/\/www.greytrix.com\/blogs\/acumatica\/wp-json\/wp\/v2\/posts\/1079","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.greytrix.com\/blogs\/acumatica\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.greytrix.com\/blogs\/acumatica\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.greytrix.com\/blogs\/acumatica\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.greytrix.com\/blogs\/acumatica\/wp-json\/wp\/v2\/comments?post=1079"}],"version-history":[{"count":9,"href":"https:\/\/www.greytrix.com\/blogs\/acumatica\/wp-json\/wp\/v2\/posts\/1079\/revisions"}],"predecessor-version":[{"id":3030,"href":"https:\/\/www.greytrix.com\/blogs\/acumatica\/wp-json\/wp\/v2\/posts\/1079\/revisions\/3030"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.greytrix.com\/blogs\/acumatica\/wp-json\/wp\/v2\/media\/2552"}],"wp:attachment":[{"href":"https:\/\/www.greytrix.com\/blogs\/acumatica\/wp-json\/wp\/v2\/media?parent=1079"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.greytrix.com\/blogs\/acumatica\/wp-json\/wp\/v2\/categories?post=1079"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.greytrix.com\/blogs\/acumatica\/wp-json\/wp\/v2\/tags?post=1079"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}