Run report with Side Panel

Hi everyone, Since version 2018R2, Acumatica has released feature call Side Panel on generic inquiry screen. It is a great feature to enhance the usability. In case that you have missed it, you can also check released notes here and configuration here. With side panel now, you can help user work more effectively by avoiding to many navigation. In one screen, you can do a lot of things for instance: view details of record or linked object, review dashboard, report, etc … Today I would like to share about the configuration. It can help user who need to work with some report or printed form can access it easier. Let… Read more

How To: Count number of prints in Acumatica

Hi All In this post I will explain how to use Acumatica’s Audit feature help to keep count of how many times a invoice report was printed and then with a little bit of Acumatica Report Designer knowledge, you can indicate on the report if it is a copy/duplicate. The objective in this example is to print a invoice with a header “Tax Invoice” and when it is printed a second time to show “Copy of Tax Invoice”. Audit Feature First you have to setup an Audit of the [ARInvoice.Printed] field on the AR301000 screen: On Invoice and Memos screen (AR301000), the checkbox field below is updated to a true value… Read more

Reporting Currency Conversion on the Fly

Hi All, In Acumatica you can use standard currency translation tool to translate base currency to any other reporting currency. However this process is available only for GL Balances. You can really translate AP/AP SO/PO balances and total using the same approach. Here I want to give you the idea of how can you do on the fly currency translation from any currency to any currency on any document in Acumatica for reporting purposes. Problem The biggest problem of this task are Rates. In Acumatica we store rates assigned to effective date. But if you have other dates where are no rates defined, we need to user the closest previous… Read more

Dashboards and Reports on Mobile

Hi All, Do you know that Acumatica Mobile native app has supported dashboards and reports since release of version 2017 R2. Here you can see YouTube video with full presentation of Acumatica Mobile capabilities – Acumatica Mobile App – Functionality and Customization But in this article I want to show you how to configure it by you own. Welcome under the cut. How to Add Dashboard There is not dashboards available out of the box with Acumatica Demo data but we can easily add new one just by modifying MobileSitemap.xml file. MobileSitemap.xml is the definition of the whole mobile UI. Upon login mobile player downloads sitemap definition and draws screens… Read more

Rates per Day in the Report

Hi All, Today I would like to share you my experience in building report that needs to translate currency rate on the fly. The main challenge in this task as you may not have rates for every day. So that mean if your document is for 20th of Jan but there is no rate for that date, you need to find the latest available from list of the rates. Unfortunately this task is solvable just with standard Joins and standard DACs as we need to have little bit more tricky logic. So to fix that I have created an SQL view that can find last available rate for the particular… Read more

Consolidation Reports using Pivot Tables

Hi Everyone, You most probably know that if you need to prepare consolidated reports in Acumatica you should check ARM Reports (Analytical Reports Management) However there is one more option available with last release of Acumatica Create a Generic Inquiry that extracts Account/Subaccount balances for specific period from GL. Here I have a separate article that describes way to extract GL data. Dynamic pivot table that will split data by ledgers as columns and Accounts/Subaccounts as rows. The difference here from my previous post is that here we should change GI to return much more data – for all accounts/subaccounts, multiple ledgers and periods. We need provide more data specially for… Read more

Number to Words Localization

Good Day! Today want to give you an idea on how can you customize amount to words transcription in Acumatica.That might be required for any language localization. Here you can find simple example, but real complexity might be related to your language rules. All the main work around that feature is done by [PX.Objects.AP.ToWordsAttribute] It can be defined on virtual field, and upon accessing by UI or Report will do automatic transcription from number to text. e.ReturnValue = LangEN.ToWords((decimal)((PXDecimalState)DecimalVal).Value, ((PXDecimalState)DecimalVal).Precision); If you need you can apply that attribute to other amounts and reuse transcription for other reports. You can read more about it on StackOverflow. < In the code realization… Read more

Getting Report PDF through Web Servies

Hi All, Today want to show you very simple example of getting printing report and getting it’s PDF representation thought Screen Based API. For doing this we need: Screen-based Endpoint to Report. Import Web services References to our project Fill report parameters using Value command. Request Report to be formatted as PDF (schema.ReportResults.PdfContent). Please note that you also can get a HTML representation of report. Receive report binary data from Acumatica using Submit command from Web Services. Save data somewhere. In my case i’m just saving report as a file to file system, but you can process it however you need. Full Code Sample: View the code on Gist. Have… Read more

Negative Number Representation in Reports

Hi There, Just want to share with you short tip: if you want to have a different representations of negative numbers in the report, it is easy to do using format file in control properties. So if you have negative numbers with “-” sign, like here: You can change format in control Properties. For that example, if you want to show it in brackets (1) instead of -1, than just use following mask: #,##0.00;(#,##0.00) As you can see here, negative representation is specified after “;” sign. Masks are related to standard .NET formatting rules. And than you can check it in browser. Have a fast reporting!

Get PDF file from Report using Code

Hi Everyone, In this article I want to show you the way how you can dynamically generate PDF file from any report of Acumatica and attach if to an entity. I will do that on example of printing AR Invoice Form out of AR Invoice with custom button In general that task can be split in 4 steps: Defining reports parameters Creation and processing report Get report as PDF file Attach report to entity Parameters All reports have different parameter so before writing a code, check what parameters are required in that report. All parameter should be provided as dictionary – name/value. Report To work with reports you need a… Read more

Trial Balance Detailed Generic Inquiry

Hi All, During my last project I have faces a situation that Acumatica does not give us ability to see Trial Balance detailed by subaccount with denominated currency balance. That information might be needed because of integration with 3rd party system. They only things available: Account Summary Inquiry screen – it gives us account balances withing base and denominated currency, but it does not give us information about subaccounts. Trial Balance Detailed report – it gives us detailed information about subaccounts, but there is not balance in denominated currency. But we have a solution under the cut. But fortunately Acumatica has very powerful Generic Inquiries where we can construct such… Read more

Launch Multiple Reports with one Exception

Hi All Today I want to share with you an example how you can launch multiple unrelated reports with one single PXReportRequiredException. PXReportRequiredException has a helper method – CombineReport(..), where you can pass multiple ReportRequiredExceptions and Acumatica will process them together. Acumatica will extract each separate report from extension and can open it with following rules: Print all report as a single PDF file – ex.SeparateWindows = false; Open each separate report in a new tab – ex.SeparateWindows = true; There same rule as with other Pop-ups we can open it with new tab, window or the same window as well – ex.Mode = PXBaseRedirectException.WindowMode.New; Also note that you should pass report parameters… Read more

Multi-Company Reports

Hi All, Today I want to share with you a way how you can print multi-company (multi-tenancy) reports. You know that Acumatica tenants are completely separated and there is no way to make a report or generic inquiry for multiple tenants. So first of all want to share with you the technical details – Acumatica stores all tenants in one database, but separate it by CompanyID column, that is a part of primary key. You can read more about it here. Acumatica Framework automatically detects CompanyID column in the table and adds “WHERE CompanyID = {Something}” to every query. Unfortunately you cannot change anything here. But good thing is –… Read more

Provide Default Value for Report Parameter from Branch

Hi All, Really often you may want to have some default values for report parameters, that may depend on environment. For example different default warehouse depend on branch where are you working now. Or different warehouse depend on current employee. But unfortunately it is not out of the box functionality in Acumatica. Luckily, using powerful Acumatica Customization engine it is quite easy to do. Let me share with you a way to archive it. Scenario: lets assume that we want to have different default warehouse on Inventory Valuation report based on different branch that is selected as current. We will do this task in 3 steps: Adding custom field to… Read more

Get Data from Stored Procedures In Acumatica

Hi There, As you may know, by default Acumatica does not support stored procedures. The reason of this is hidden in multi-tenancy. Acumatica able to host more than one tenant in the same database. To do this, In each table it has a CompanyID column, which is a tenant ID. User from one tenant must not be able to get access to date of other tenant. So to make sure that Acumatica getting data from the right tenant and this date is secured, Acumatica platform automatically adds the special restriction by CompanyID to every select statement ( … where [table].CompanyID in { 1, 2 } ) If you are using stored procedures than platform is not able to make… Read more