Last week we covered how to login to Acumatica Report Designer (click here) so you can retrieve a report directly from the Acumatica ERP application.
This week we’ll cover how to build a simple report from scratch that will display the list of our customers and run it from the Acumatica application.
Step 1: Open the Acumatica Report Designer application
After it opens, you should see a blank page like this.
Go outside and take a breath of fresh air. Here you are, staring at a blank canvas with infinite possibilities. Building a report is an art and the report designer program is your canvas. Feel free to think of yourself as an artist. You might even want to go out and buy a beret so you can look like an artist like this guy here:
Step 2: Build the schema
In order to build a report, you need to connect to the Acumatica data objects by going to File –> Build schema… on the menu.
Use the same URL and login credentials that you used last week to connect to an existing report. After you enter the information, click the Load schema button and you should see something like this:
Note: The @Demo in the screenshot above is only needed if you have multiple companies. In my screenshot above, admin is the user and Demo is the company. If there was only one company, I would only have had to put admin, not admin@Demo.
The items listed on the left-hand side are called Data Access Classes (DAC). You can choose more than one to combine them in your report, but for the purposes of our simple customer list example, let’s just choose one. Click the BAccount DAC, click the arrow icon to select it, and then move to the Filters tab on the top.
Now we need to filter the DAC. Because BAccount has more than just customers in it, we need to add a filter so that our report will only return customers. We can do this by filtering to only show records where Type equals “CU”. After filling out the fields, click the OK button.
Step 3: Add fields to the report
Now that we are done building the schema, we can add our fields to the report. For our example, we’ll just grab one field, AcctName and add it to the report. Just click the Fields tab on the right-hand side and then drag the AcctName field onto the detailSection1 area of the report.
After you drag the field to the detailSection1 area of the report, shrink the section so it looks something like this:
Step 4: Save the report into Acumatica
Use the File –> Save To Server… option to save the report into the server:
Pick a unique report name like MyFirstTestReport.rpx and click the OK button.
Step 5: Add the report to the Acumatica menu
I’m going to add it to the Audit folder in the Accounts Receivable module and use a ScreenID that hasn’t been used yet like this:
Step 6: Run the report
Find the report on the menu and click the Run Report button:
And, voila, there’s your first report in Acumatica Report Designer:
Good day, Tim, How can I include the new fields added to an AR Aging report to be exported via excel? I added 4 new columns, and when I export it into PDF file, the columns are there but when exporting the report in excel, the export file does not include the new fields or columns.
Hi Jorge,
Can you post this over in the Acumatica User Group? https://timrodman.com/augforums
Hi Tim,
How can group item in the invoice report ? Can you give me some sample video ?
like
1 A 1 1$ 1$
2 A 2 1$ 2$
To
1 A 3 1$ 3$
Thanks..! I am a beginner of acumatica. If there are more videos related to application developer, please share me .
Hi Vita,
Can you start a discussion Topic in this forum and upload what you have so far:
https://www.timrodman.com/augforums/acumatica-report-designer/
Then, if you could mockup an example in Excel of what you want it to look like and post a screenshot to your discussion Topic, we can go from there.
HI Tim,
I have got solution of it by specifying more fields for grouping it was easy by the way 🙂 . But further i have one more question on accumatica.
I have added a new column in database of existing table in accumatica now i want to update it on the accumatica code behind. How should i do it ?
Thanks
You just added the field to the table in the database or you added the field through the Acumatica Framework?
If you want to interact with the field in Acumatica, you have to add it through the Acumatica Framework. See this post for an idea of how to do it, although things have changed a little bit in Acumatica 5.
Hi Tim,
Thanks for your answer.
I have used grouping but it just make records together(duplicates records). But I need to compress them in single line. So that duplicate records will show in a single line. How to distinct the records ?
Thanks
You’ll need to include more fields in the Group to make it unique. What fields are you currently including? What tables are you using in the underlying data?
Hi Tim,
Thanks for sharing this knowledge.
I have a question regarding accumatica report. I am getting duplicate records in the report . If it was possible in SQL then i will use Groupby to resolve this issue. But how can i do in accumatica report designer. Please help.
Thanks,
Vikas
Hi Vikas,
You can use Group sections to show your data and hide the Details section.
How can we make a new section that always go to new page?
Hi Aggyey,
Have you explored the
PageBreakoption on the properties of the section?Very helpful, thanks
Hi there, do you know a way how to open a report in a new window, for example in the SO screen, when going into the reports menu and select Print Sales Orders/Quotes it opens a new window or tab on the screen?
Hi Matt,
Unfortunately, I don’t know how to do this yet.
However, I suspect that this is something that you would have to do with a screen customization, not in Report Designer, because the launching of a separate window would need to take place in the screen before the report definition even gets involved.
There might be a way to define this in the Automation Steps (AR301000) screen, but I looked briefly and couldn’t figure it out.
This is great!! So glad you are doing this Tim. I look forward to reading your blog this year.
Thanks Russ!