Microsoft Dynamics GP and Microsoft Dynamics SL ERP solutions both provide predefined SQL Views that can be used for simplifying your report development. A SQL “View” is essentially a virtual table or predefined SQL “Select” statement that is stored in the database as an object.
Some of the benefits of using a SQL View in your report writing effort
- Table relationships in a multi-table query can be defined in the View, including the type of join (inner, outer, full) that is being employed. If you don’t fully understand how to create the relationships, you can frequently get a more technical resource to create a View for you, which you can then consume in your report.
- Obscure field and table names can be changed to more meaningful terms. For example, in Dynamics GP, the main customer master table is “RM00101”. Fields in this table are significantly abbreviated (GP used to have an eight character field name limit). Once you create (or use an existing) View, you may just call this “Customer” or “Customers” and rename fields like “CNTCPRSN” to something like “ContactPerson”.
- You can make use of some of the useful SQL functions that exist to alter the database information to a more useful format. For example, Dynamics GP has many pre-defined “Scalar-valued Functions” that translate numeric values for options chosen in GP into useful textual values. For example, in the function DYN_FUNC_Document_Type_RM_Trx, when the integer value for the receivables document type is supplied, it will return a descriptive name of the document type.
- You can restrict the fields available to the reporting user. Instead of having access to the base table in SQL, you can grant permissions to the SQL Views, which may only show a limited number of columns. An example of this would be sensitive data like payroll information. You may want to create a report that lists employees, but you don’t want the Social Security Number to be available. You could create a View of the employee master table (“UPR00100”), selecting only the columns needed:Base table:
New View:
Pre-defined SQL Views (from Microsoft)
Both Dynamics GP and Dynamics SL ship from Microsoft with some useful pre-defined SQL Views. You can find these by browsing in SQL Server Management Studio to the “Views” folder within the appropriate database.
NOTE: In Dynamics GP, many of the most useful pre-defined Views are going to be the ones with the easy-to-read names. For example, the “Accounts” View is a useful View that joins together the several GL account-related tables and also uses some of the “DYN” functions above to provide meaningful descriptions for things like “Typical Balance” and “Posting Type”. Dynamics SL tends to, in many cases, have table and view names that are user friendly – so you may not do as much renaming.
Creating your Own Views
Often, you may find it is necessary or beneficial to create your own Views. I find creating my own View is helpful because I know the intent of the View (and don’t have to worry about its other uses). I also don’t always need the complexity or quantity of fields that exist in some of the pre-defined Microsoft Views.
At Boyer, we make sure to name any custom SQL objects with a standard prefix (we use “bav” for Boyer & Associates Views). This makes it easier for us to identify Views created by our team.
SQL Permissions to the View(s)
Remember that for accessing any SQL object (Views are objects) you need to assign permissions to roles or users. In the case of SSRS reports, all report access is controlled through “Integrated Security” or Active Directory-based user accounts. Dynamics SL offers “Integrated Security” for its interface, but Dynamics SL users do not actually have SQL permissions assigned to the individual AD accounts in SQL. Dynamics GP only uses SQL logins, but again, those users/logins are not intended (and won’t work) for access outside of the GP interface. You may find that you will want to set up Active Directory groups and assign those groups permissions to the various Views that you use or create in SQL. To grant permissions on a single View to a user, group, or role, you simply run the “GRANT” statement in SQL Management Studio.
Using the View in SSRS
Now that you have found or created your new SQL View, you can consume (or use) it in a report. Report Builder will only show you the SQL objects that your Windows account has permissions to see. This is nice for the typical user in that they won’t see the hundreds of Views and tables that they don’t need to see.
You will see your new View (if you remembered to grant permissions to it for a role that you have access to or to your AD account) in the Report Builder’s Query Designer.
Conclusion
While the use of SQL Views is not required, you may find them to be very useful in your report building efforts. They certainly can help users that don’t have a deep understanding of the underlying data model in your Dynamics ERP system.
For additional tips and tricks regarding Microsoft Dynamics ERP products, visit our blog.
This post was reviewed in July 2017 to make sure it still offers the most up-to-date information.
