Reporting Tools, Tips, and Best Practices

For the third year in a row, Acumatica held a virtual developer conference. Here are my notes from the Reporting Tools, Tips, and Best Practices session.

I created separate blog posts for each session.

Day 1

09:00am – Welcome & Kick-off – Ajoy & Mark

09:10am – xRP Framework Fundamentals & Best Practices – Sergey Marenich

10:20am – Application Solution Architecture – Dmitry Naumov

10:50am – Source Control & Automation for Acumatica Developers – Brendan Hennelly

11:30am – Reporting Tools, Tips, and Best Practices – Mikhail Reysev (this post)

12:40pm – Implementing Multi-Currency Support for Custom Solutions – Joshua van Hoesen

01:10pm – Unit Testing Framework – Andrew Boulanov

Day 2

09:00am – Database Schema Overview – Dmitry Naumov

09:30am – Application Certification – Ruslan Devyatko

10:10am – Modern Developer Practices – Stan Lesin

11:20am – Updating your Codebase with FBQL – Sharif Ahammad

11:50am – What to Expect from Acumatica’s Developer Community – Joe Jacob & Mark Franks

12:00pm – Developer Roadmap – Mike Chtchelkonogov

Now for the "Reporting Tools, Tips, and Best Practices" session with Mike Rysev. Great to see reporting make its way into this Developer Conference #CloudxRPSummit pic.twitter.com/dRXl4CzqdG
— Tim Rodman (@TimRodman) June 19, 2019

Agenda for this session #CloudxRPSummit pic.twitter.com/Re5iK5kPNk
— Tim Rodman (@TimRodman) June 19, 2019

I'm looking forward to the "Some tricks" section of this session #CloudxRPSummit pic.twitter.com/cvBhXu1pkh
— Tim Rodman (@TimRodman) June 19, 2019

Here is a list of the multiple reporting tools provided by Acumatica #CloudxRPSummit pic.twitter.com/Vw3Ck2llxN
— Tim Rodman (@TimRodman) June 19, 2019

It's nice that Standard Reports with Acumatica Report Designer are stored in XML format so you can edit them with either the Report Designer application or even a text editor like Notepad if needed. #CloudxRPSummit pic.twitter.com/jvKmHSbZqX
— Tim Rodman (@TimRodman) June 19, 2019

A look at the Data Access Class model in Acumatica through the lens of reporting #CloudxRPSummit pic.twitter.com/6woAKV0EyB
— Tim Rodman (@TimRodman) June 19, 2019

Features of both Reports and Generic Inquiries #CloudxRPSummit pic.twitter.com/KPOR81QAWP
— Tim Rodman (@TimRodman) June 19, 2019

It's HUGE that both Reports and Generic Inquiries respect row-level security, unlike 3rd party reporting tools like Crystal Reports #CloudxRPSummit pic.twitter.com/r04PCBUX5l
— Tim Rodman (@TimRodman) June 19, 2019

Looking at differences between Reports and Generic Inquiries #CloudxRPSummit pic.twitter.com/uqfdOinCCZ
— Tim Rodman (@TimRodman) June 19, 2019

The "Additional usage" point is a big reason why I'm building Query Quarry https://t.co/os3cM51T6X #CloudxRPSummit pic.twitter.com/sPlA48EOw6
— Tim Rodman (@TimRodman) June 19, 2019

Both Report Designer and Generic Inquiries use Data Access Classes (DACs), even though the tabs are labeled "Tables" #CloudxRPSummit pic.twitter.com/E5pECwwEYo
— Tim Rodman (@TimRodman) June 19, 2019

First, list the DACs that you want to use #CloudxRPSummit pic.twitter.com/XYkoV7ifuD
— Tim Rodman (@TimRodman) June 19, 2019

When using multiple DACs, be sure to define the relationships between them #CloudxRPSummit pic.twitter.com/DDqjrGGuM7
— Tim Rodman (@TimRodman) June 19, 2019

You can define Parameters to collect from the user that you can apply when running the Report / Generic Inquiry #CloudxRPSummit pic.twitter.com/iqvCu4a9My
— Tim Rodman (@TimRodman) June 19, 2019

Next define Filter Conditions which may utilize Parameters or be hardcoded #CloudxRPSummit pic.twitter.com/eXgIKM5V70
— Tim Rodman (@TimRodman) June 19, 2019

Don't forget to accommodate the Parameter IsNull situation #CloudxRPSummit pic.twitter.com/wuTAMRVFc7
— Tim Rodman (@TimRodman) June 19, 2019

Grouping is next. A big difference between Reports and Generic Inquiries is that Reports apply grouping on the Client while Generic Inquiries apply grouping on the Database Server. Grouping on the Database Server is better for performance. #CloudxRPSummit pic.twitter.com/TjHE42IRjJ
— Tim Rodman (@TimRodman) June 19, 2019

Sorting is next and it can impact performance which is why Generic Inquiries give you a heads up warning #CloudxRPSummit pic.twitter.com/m1N5pUejqk
— Tim Rodman (@TimRodman) June 19, 2019

What I've been looking forward to. Reporting Tips and Tricks. #CloudxRPSummit pic.twitter.com/mWso6tsInt
— Tim Rodman (@TimRodman) June 19, 2019

1. Since Acumatica is a multi-tenant solution, it restricts reports to one Tenant. But you can use a custom SQL View to get around this limitation. #CloudxRPSummit pic.twitter.com/imIMxKTDXJ
— Tim Rodman (@TimRodman) June 19, 2019

#Acumatica data-driven alerts with Business Events introduced in Acumatica 2018 R1 https://t.co/scD1EsW6NC
— Tim Rodman (@TimRodman) June 19, 2019

2. Printing multiple copies of a report. A couple of workarounds: 1. Use empty Report that calls main Report as Subreport multiple times. 2. Use a Cross join in SQL to create copies of the underlying data. #CloudxRPSummit pic.twitter.com/uQ7clhMHzu
— Tim Rodman (@TimRodman) June 19, 2019

3. Transpose Columns to Rows in Generic Inquiries. The "Magic" happens on the "RESULTS GRID" tab: #CloudxRPSummit pic.twitter.com/1jXtkD0ojD
— Tim Rodman (@TimRodman) June 19, 2019

4. Troubleshooting Generic Inquiry performance. First, note that there are yellow automatic warnings about sub queries #CloudxRPSummit pic.twitter.com/Ce50QLBpnC
— Tim Rodman (@TimRodman) June 19, 2019

4. Troubleshooting Generic Inquiry performance. Second, make sure that you are using all Primary Key fields in each join. #CloudxRPSummit pic.twitter.com/8EQjWZSDk7
— Tim Rodman (@TimRodman) June 19, 2019

4. Troubleshooting Generic Inquiry performance. Third, use the Trace to see the actual SQL code run which helps A LOT when investigating a slow-running query. #CloudxRPSummit pic.twitter.com/746Tp7WdMf
— Tim Rodman (@TimRodman) June 19, 2019

4. Troubleshooting Generic Inquiry performance. Fourth, push some of your logic into a SQL View which runs more logic within the Database which is more efficient. #CloudxRPSummit pic.twitter.com/G7vbV1wrwC
— Tim Rodman (@TimRodman) June 19, 2019

Thanks Mike for a great session on Reporting #CloudxRPSummit pic.twitter.com/kMcdsWlAXH
— Tim Rodman (@TimRodman) June 19, 2019

Cool to see the winning team from the Hackathon in January 2019 getting highlighted during this conference with a video of their presentation from January #CloudxRPSummit pic.twitter.com/HGI3BIgwfB
— Tim Rodman (@TimRodman) June 19, 2019

Nice to see that Acumatica Developer Network (ADN) is now included as a full-fledged citizen on the https://t.co/EGE02lyPW9 website. Especially relevant is the Blog https://t.co/nxg3VTE8eW #CloudxRPSummit pic.twitter.com/MKi9QYR8LJ
— Tim Rodman (@TimRodman) June 19, 2019