By using this website, you agree to our Terms of Use (click here)
Tim & ARC members,
great post http://www.augforums.com/building-your-first-report-in-acumatica-report-designer/
to get me started building my own report.
few things that i'm not understanding about your post, and what I am seeting.
1) DAC - you specifically limit the BAccount DAC to CU for Customers. in the schema. In my case I am using Employee. if BAccount table does not allow duplicates between different TYPES, and I am inner joining to EMPLOYEE table, is DAC filter for 'EP' necessary? Performance?
2) is there some rule / guidance for which fields in BAccount table that are used with different DAC Type. for instance, I need the EP employee's Branch. Branch, which is also in BAccount (but a different type), but then reported for employee in PARENTID field, when TYPE = EP. Schema would go a long way here - if Acumatica is listening.
3) using Report Designer, after including EMPLOYEE table, I then want to include BAccount. Except the only listing I see is for BAccount2, and no BAccount is listed. Likely the same, but...why would there be '2' if it is the same?
4) is there trouble/ limitations if I don't build the entire schema first, before starting with report? it seems there are endless number of tables that might be necessary,
5) any way I can build my own DAC object? like SQL Views - that I can then use for repetitive reporting needs.
Thanks again members, I appreciate your feedback / information.
Hi HappGilmore,
Let me say that the post you linked to was written over 2 years ago. So I had to go re-read it first 🙂
Also, before I get to your questions, a few comments about Report Designer.
First, I try to avoid Report Designer like the plague if at all possible. Although I must say that I'm happy they finally added the Undo button (click here). I mainly try to avoid it because I view Report Designer as a tool for traditional paper reporting which I think is very much on the decline. Here is a slide from my presentation at this past Acumatica Summit on the topic:
Second, I've found that more than 50% of the time, when someone asks for a report, they are really just asking for the data. When I give them a Generic Inquiry, then they are happy. Then they go off and do something with it. This is especially nice with Generic Inquiries because you can connect them directly to Excel with OData. This is "especially especially" nice now that Acumatica 2017 R2 includes over 130 out-of-the-box Generic Inquiries (see post this coming Thursday on the topic).
Third, creating Generic Inquiries is A LOT faster than creating reports in Report Designer because you don't have to deal with formatting. Also, you can type in the fields rather than having to scroll through them. For this reason, it would be cool if we could get rid of the File -> Build schema... window in Report Designer and build reports directly on top of Generic Inquiries. If you think that would be nice, you can vote on the idea (click here).
Ok, enough of my opinions. Let's take a look at your questions:
- No need to filter on "EP" as long as you join on the BAccountID field because BAccountID is the primary key in the BAccount Data Access Class.
- I hear ya on the schema, a very popular request. Click here to vote on it. But it's not all that bad when you combine Inspect Element (click here) with the fact that probably 90% of the time a Data Access Class has a corresponding table name in the database with the same name so you can browse to the table with SQL Management Studio and check the primary keys on it (like I just did to confirm BAccountID in your first question). More specifically to your question though, you can use Inspect Element on the Employees screen to see that you can get the employee's branch in the EPEmployee.ParentBAccountID DAC field.
- What Acumatica build number are you using? I feel like I saw something like this at some point and it was a bug in one of the earlier 6 builds.
- None that I've ever run into. You can always go back and add additional fields or modify the schema. Of course, if you remove some stuff that is being used in the report, you'll get errors when you go try to run the report.
- You can totally do this. Click here for a great tutorial by Doug Johnson, but keep in mind that most new Acumatica customers are on SaaS so you won't have access to the Database. It's a tough transition to the world of not writing SQL queries, but I always recommend trying to do it sooner rather than later. This was my experience (click here).
Tim & team.
to continue with my reporting, I really like the idea of creating client specific DAC because they get used in many various reports.
I did a similar thing in SSRS deployment for same client few years ago by creating stored procedures or views, then just referencing the views / sp when that dimension was needed for reporting.
question is: for Acumatica provided SAAS environment delivery, can i still create / publish DAC? or do i need to incorporate Support case of some type to get done.
Doug seems to show that I can publish, but your comments above indicate otherwise.
Scratch my comments on SaaS. The more I thought about it, the more I thought maybe you could do it in a Customization Project. And it looks like you can, Brandan confirmed it: https://stackoverflow.com/q/46609215