917.848.7284Data for Finance and Accounting

Top

Dynamics GP

I recently posted a seven-part series on SQL for super users. (You’ll find the introduction here.) In this series, I chose to use generic Northwind database (with some enhancements) for my examples. (The posts came out of presentation I made to a mixed group of Dynamics users.) But, as you know, each kind of Dynamics software (i.e. GP, NAV, AX, CRM) has its idiosyncrasies. Therefore, I’m in the process of adapting the generic Northwind examples to specific Dynamics types. And I’m starting with NAV, as I happen to spend quite a lot of time on it. (If you’re interested in getting a copy of the PDF when it’s ready, let me know.) As I prep the NAV series, I realize a basic intro to NAV would be useful before launching into it. Therefore, in this post I’m going to discuss: Finding the right table—the “About This Page” The suboptimal use of special characters Foreign...

In this post, I'll show you how to create a view in SSRS that allows Dynamics GP users to create a trial balance for any time period (or multiple periods) and get the ending balance for that period(s). I was motivated to write this post after seeing a question on the GPUG forum about it. The question was referred to Victoria Yudin, a GP SQL guru, and she has a nice stored procedure to get a 12-month trial balance for the same year. Also, I’ve been developing some generic design patterns for financial reporting in SSRS and as part of this project we've been asked to create trial balances for the last 12-month period—so the topic has been on my mind. Why Views Instead of Stored Procedures? While Victoria’s solution is good, I want to add to it by allowing users to return trial balances for any period, not just the last 12 months....

As consultants, we pride ourselves on both knowing the best way to set up systems AND finding workarounds when client infrastructure is “suboptimal” and unlikely to change. We recently performed such a workaround for a client that had too few SQL server instances—and I'm going to share the experience with you here. (Hopefully, your system is set up more optimally. But even if it is, you can still learn from our example.) A Workaround for Dynamic SQL and BatchMaster Our client was running both Dynamics GP and BatchMaster. Both systems share the same database instance, with different database names. So, we started with six different databases. (This wasn't the exact structure, but it's close.) BMPROD BMTEST BMDEV GPPROD GPTEST GPDEV (Dynamics GP folks know that Dynamics Shared database makes things even complex. But I don’t want to get into that here or we'll lose sight of our main topic.) To make things more interesting, we set up our own databases...

During my recent presentation on SSRS for Dynamics GP at the recent GPUG Summit conference, an attendee asked how to begin. Could he just start with the Report Wizard in Report Builder, he asked? I replied that would work fine if he had a test database (and preferably a test system) so he wouldn’t blow up the production work. Also, rather than try to join tables directly himself, he should work with one of the provided views—this would give him and his team a major head start with names that make sense and joins that are already in place. The challenge: While there are many places to find out about tables, including the application itself and the many blogs on the topic (such as those by Victoria Yudin and Mark Polino), I haven’t been able to find information about the views. But as I was writing an earlier blog post about database queries,...

During a session at the recent GPUG Summit, an attendee asked how to find out which users had access to which database objects. (This was in the context of a conversation about setting up SSRS and using the various database roles that GP provides for reporting.) I responded that you can find this out using the system views which are part of SQL Server. To demonstrate the point, I’ve created two queries below. You can combine them, but for illustrative purposes I've divided the process into two steps. Step 1: Find out which roles have access to which objects The purpose of the first query is to find all objects that the given role has access to. In this example, I'm selecting by role using the "rpt_accounting manager" role. You might also select by permission type (e.g. UPDATE, INSERT OR DELETE permissions)—and thereby tell auditors that only one role has rights to perform...