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, I realized that these same queries might help folks get a head start on the correct views.
To illustrate the point, I walk through the process below. Because I don’t want to scare end users (who’re the primary audience for this post), I demonstrate using sample data and leave the actual queries for the end of this post. (You’ll also find a link to a spreadsheet with both the queries and complete sets of data at the end of the post.)
Start with a Role and Find the Views that Role can Access
Dynamics GP sets up many database roles to control data access for reporting. The role names are fairly obvious. Here’s a list of “rpt” roles delivered standard with the TWO database:
(See query 1, below.)
Looking through the list, you can probably guess which role has access to the data you’d like to see.
Once you have the role, you can find what views that role has access to. For example, I checked which views “accounting manager” has access to and received this list:
(See query 2, below.)
Again, the names are pretty straightforward.
One more query. After working with the system, you’ve started to learn a few table names. Or perhaps you found a name through an online tool or a website that covers GP. But still, you don’t want to create all your own table joins. So, I created another extract where you can start with a table name and see which views display its data.
Here’s an example:
(See query 3, below.)
One point on this last query: I didn’t look at every single view in the database, just the views report writers have access to. Also, views are built from other views, not just directly from tables—but I haven’t looped recursively to find every table that supplies information to any given view.
Queries Used in this Post
Download our Excel spreadsheet with the queries and complete sets of data from TWO.
Sign up for our newsletter by entering your email in the box on the top right of the page.