Microsoft Dynamics GP: Understanding Reporting Tools


Microsoft Dynamics GP 2010 Reporting

Microsoft Dynamics GP 2010 Reporting

Create and manage business reports with Dynamics GP

        Read more about this book      

(For more resources on Microsoft Dynamics, see here.)

Our goal is to select the reporting tool that has the best capability to meet our required condition. Of course we will consider all of our reporting tools against each individual challenge. In reality, however, we will have to consider the varying conditions we need to meet with each challenge, and accept some trade-offs in our reporting tool's ability to meet all of our conditions.

Intended audience

A report almost always turns out useless when it hasn't been designed and created with the eventual end user in mind. Not only is it likely that the report will not provide the critical data our end user requires, but in many cases, the reporting tool we've selected to design the report may not even contain the functionality we require to meet the requirements of the end user. This can lead to ballooning investments in time and money and is often has the feel of trying to fit a square peg in a round hole!

For the most part, our reporting audiences can be classified as either external or internal consumers of our organization's data. External consumers include auditors who require insight into the financial health of our organization, potential investors, and current stakeholders. On the other hand, internal consumers can range from operations personnel such as a warehouse manager, members of the accounting team, as well as members of the executive team.

For external auditors who require insight into our organization's transaction journals, the default reports that come with tools such as Report Writer and SmartList Builder can easily be modified to provide auditors with the exact information they need. Although these reports are internal to GP, we can convert them to printouts or, in the case of SmartLists, export them to Excel and send the finished document over to the auditor. Other external consumers, such as potential investors and current stakeholders are primarily interested in seeing a summary of our organization's financial statement. This can be provided via numerous types of financial statements, and fortunately, Management Reporter can be used to create a wide variety of professional-looking financial statements that can then be distributed externally.

One thing to keep in mind about most of our internal users is that they are more likely to operate within GP on a daily basis. We can assume that most of these users already have a GP login and are familiar with navigating through the GP environment. By creating reports with a reporting tool that is built-in to GP, we can add a new dimension to the user's GP experience without having to introduce the user to another application and another environment. Tools such as SmartList Builder and Report Writer allow us to develop such reports, and users will more than likely appreciate the ease of access that comes with their new reports.

But, this is not to say that all internal users want to see their new reports in GP. It's entirely likely that members of our executive team will not have access to GP, much less the time or desire to navigate through the raw output that comes from SmartLists or posting journals designed in Report Writer. Instead, our executive team members want to see summarized information in a single, easy-to-reach location. Reporting tools such as the pre-defined SSRS Reports Library, Analysis Cubes for Excel, and Management Reporter are all alike in that they can provide these team members with a central repository of summary reports that do not require a GP user login to view.

For a better, more visual understanding of the relationship between internal and external report consumers and the tools that they would most likely benefit from, take a look at the following image:

Microsoft Dynamics GP 2010 Reporting

Data sources

Since the end result of the kind of reports is to present our end user with data from our ERP solution, we should be concerned with the source of that data. At first glance, the answer seems obvious: if we're trying to provide data from our ERP environment, shouldn't we pull it straight from the underlying company database itself? Unfortunately, the answer is not always that simple. We must be aware of potential performance impacts against our company databases that might result from our reporting processes. Additionally, even though our focus is primarily on GP 2010, we must keep in mind that data can reside in other, non-GP related data marts, as well. These separate data sources may exist due to other proprietary applications. If our end user wants to include information from separate data sources in his or her report, we need to be sure that we have a reporting tool that can access this data and use it in conjunction with what we are pulling from our GP database.

By their very nature, some of the reporting tools can easily be used to combine data from multiple data sources. One tool that lends itself to this kind of reporting is the Analysis Cubes for Excel product. Because this product is built on a data warehouse, we have a ready-made location in which data extracted from multiple data sources can be transformed for consistency purposes and then loaded into the data warehouse. The transformation piece of this is critical, as one of the biggest challenges of using disparate data sources is finding a way to join the data together through some common key value(s). For example, one data source may refer to customers via a unique eight digit numeric customer code, whereas the other data source may refer to the same customer with a ten digit alpha-numeric code. In order to combine these two sets of data, we need to find a way to relate the eight digit codes to their corresponding ten digit codes from the other data source!

Although Analysis Cubes for Excel offers a unique environment in which data from multiple data sources can be staged, other reporting tools we've discussed can also pull data from multiple data sources. This is largely due to the flexibility they offer in the form of using SQL queries to extract data for reports. For example, in SSRS, we can use SQL queries to create the data set from which our report is built. These SQL queries offer us flexibility to pull data from multiple tables, multiple databases, and even multiple servers. Unlike Analysis Cubes, however, the transformation required to create commonality between these different data sources must be done entirely within the SQL query. While this can be achieved, it is usually the domain of those with more advanced technical skills, and it can also cause an unnecessary drain on system resources each time the report is generated. SQL queries can also be used to generate data for SmartList Builder and Excel Report Builder reports, although this does create some limitations that may or may not be an issue for our end users.

The image below shows where each reporting tool falls in the spectrum of reporting against a single data source or multiple data sources:

Microsoft Dynamics GP 2010 Reporting


Latency deals with the idea that, depending on the reporting tool we select, the data in our report may lag a bit behind the actual data that exists in our ERP environment. Although it is likely that our users will profess a need for real-time data in all of our reports, in reality, this is not always easy to achieve. While it may be relatively easy to display up-to-the minute transactional data with certain reporting tools, this is usually only half the battle in reporting. To provide reports capable of analyzing large data sets and offering insight into trends in our data, we cannot rely on simple reporting tools that spit nothing but raw transactional data back at us. Instead, we must use reporting tools that can analyse large amounts of data and return to us a summarized look at the trends and movements within our organization. To do this requires a blend of time, money, and resources.

So, while ERP applications like GP 2010 have long since been able to provide us with reporting tools that can spit raw data back at us, the effort to provide reporting tools that can truly help us aggregate and make sense of our large data sets are increasingly gaining in importance. For example, reporting tools like Management Reporter allow us to create a structure in which bits of data can be summarized into meaningful financial statements that provide both inside and outside stakeholders a look at the health of our organization. With such an analytical tool, we can see the big picture, or the trends, in our organization's data, even though this data originally came to us in the form of individual journal entries. The process of creating a financial statement in Management Reporter is two-fold:

  • Creating the structure, or the building blocks of our report
  • Generating the content of our report

Although this is usually a fairly quick process, made even faster by the fact that we are using only standardized information from our general ledger, it still requires some time and resources to ensure that our reports are up to date. Here, then, we see an example of a great reporting tool that provides up-to-date data without too much of a drain on resources; however, we are limited to viewing this data in the rigid structure of a financial statement.

But what happens when our users tell us that they want the same analytical capabilities provided by Management Reporter, but for other modules in GP, and with the flexibility to quickly and easily modify their reports? This sounds like a job for Analysis Cubes for Excel! The trade-off, however, comes in the form of extraordinarily large time delay (or latency) between the data found in the cubes and what actually exists in our production databases. Because Analysis Cubes stages data in a data warehouse and then processes the data into cubes for faster querying by the end user. The end-result means users will spend less time sorting through copious amounts of transactional data and more time analysing organizational trends that can be used to provide organizational advantage. While this data may not be up-to the minute (more than likely, it won't even be up-to-the hour), this should not scare our report-users away from this tool. Instead, we need to remind them that trends found in our data are often the result of a length of time far greater than the latency of our data; in fact, the cost and impact of reacting to every minute shift in a trend analysis caused by the addition of last minute data to our data set can often have far reaching and devastating consequences on our overall business plan!

And finally, let's not overstate the importance of tools such as SmartList and Excel Reports Builder in the face of far more sophisticated reporting tools like Management Reporter and Analysis Cubes. Tools such as SmartList and Excel Reports Builder do a great job of providing transactional data quickly and accurately. Because these tools don't offer much in the way of analysis, they do the best job of any reporting tool of providing up-to-the minute data without a major drain on costs or performance. These tools also provide search mechanisms that make it easier to navigate to a specific transaction among hundreds and thousands of others. Tools such as these, while probably not much use to an executive team member, can be incredibly valuable to day-to-day operations personnel such as the Customer Service representative who must quickly navigate to a customer's transactions within GP. Using SmartList, for example, our representative can quickly find the right transaction and use the drilldown functionality to actually open the transaction directly in GP. So, while presenting real-time data can be achieved, we must determine from our end user whether the goal is to see purely transactional data that can be retrieved quickly and easily, or if there is a larger goal of using a report for trend analysis or to provide a summary of a large set of data.

As we've seen, the opposing concepts of this reporting challenge consist of real time reporting versus potential data lag. While most of the tools we've discussed tend to lean towards real-time reporting, we can see from the following image that some tools do a better job of providing real-time data. Even though some tools do provide real-time data, some of these tools, like Management Reporter, require the user to go through a more defined report-generation process that can take some extra time to access the data.

Microsoft Dynamics GP 2010 Reporting



        Read more about this book      

(For more resources on Microsoft Dynamics, see here.)

Formatting and presentation

Many times, we don't really put formatting and presentation very high on the priority list when first beginning to develop our reports. In fact, it is usually the last thing we need to consider. With this being said, it actually is a critical piece to consider when developing our reports.

In most cases, our formatting and presentation needs fit in with our intended audience. For example, if this is a financial statement being presented to auditors or our board of directors, it should be professional looking and easy to follow. Functionality such as company logos, graphs, proper page numbering, and reports laid out in sections can all add to the presentation of our reports. Reporting tools such as Management Reporter, Analysis Cubes for Excel, and SSRS are all very good tools to accomplish this type of formatting.

We can easily add logos and graphs, as well as add groupings so that the report has a logic flow and is easy for our audience to follow. For instance, we can use SSRS to send professional looking invoices to our customers with our company logo included. Management Reporter and Analysis Cubes for Excel allow us to lay out our data in a meaningful manner. We can use Management Reporter for creating properly formatted financial statements while in Analysis Cubes for Excel, we can create company dashboards for our executive team.

Tools such as Report Writer, SmartList Builder, and Excel Report Builder, while great at pulling raw data, have very limited formatting options available. These tools are best used when presentation is not necessary and the report will be primarily used by a user who needs to crunch through raw data and analyse things, such as an auditor needing a list of all journal entries posted in a given year.

The challenge of selecting a reporting tool that provides support for graphics, multiple fonts, and the ability to easily modify the appearance of the report can be broken down diametrically into rigid reporting tools versus flexible reporting tools. Flexible reporting tools provide us the ability to easily modify the appearance of a report and can support the inclusion of graphics and logos. On the other hand, rigid reports are difficult to change, and we should not expect them to provide us much in the way of formatting and clean presentation. As the following image shows, the reporting tools we've discussed cross the gamut of rigidity versus flexibility:

Microsoft Dynamics GP 2010 Reporting

Ad hoc queries versus traditional reports

In report design, one of the major challenges is deciding on using an adhoc report or some type of Traditional report. This decision can be made more easily when we learn from our users what the output of the report needs to be and how often that report needs to be generated. We can use the example of an auditor requesting journal entries for the year as something we would consider ad hoc. We are not going to save that report and run it on an on-going basis. However, if our Sales Department needs a list of all open sales orders, that would lend itself more towards some type of Traditional report in that it could be run every day, every week, and so on and give our Sales Department a method of analysing what type of backlog they have or how they are doing with regards demand for certain products.

Most of the reporting tools we have discussed can be used for both of these types of reports and the challenge is figuring out when to use which tool in the right circumstance. While tools such as SmartList Builder and Excel Report Builder can be created and saved and used over and over again, they are typically best used for ad hoc reporting. The same thing can be said for SSRS and Analysis Cubes for Excel. Though they can be used to do some level of ad hoc reporting, typically, they are better fashioned for creating traditional reports that will be used over and over again on some interval.

In addition to this, another thing to consider is the output. Do we need to email our report? Does the reporting tool allow us to export to PDF? Is there an ability for us to subscribe to a report and have it delivered automatically? These are all questions we should answer before deciding on whether to use an ad hoc report or a traditional report.

As the following image shows, we see that some reporting tools such as SmartList and Excel Reports Builder are reporting tools through which temporary reports can quickly and easily be generated. If we want more traditional reports, such as financial statements or sales invoices, we can use tools such as Management Reporter or Report Writer to accomplish this objective.

Microsoft Dynamics GP 2010 Reporting


Many of the reporting tools have some type of security model. In deciding on the most effective tool to use for a particular report, we need to be aware and take care to thoroughly think through what type of security model we will need. Depending on the end user of our report, certain security will need to be set.

Reporting tools such as Report Writer, SmartList Builder, and Excel Report Builder can easily be controlled from within the Dynamics GP application and can therefore be managed by a GP Administrator. SSRS, Analysis Cubes for Excel and Management Reporter have a different security model in that they use the user's network logins or SQL server accounts. This can lend itself to needing the support of an organization's IT department or some other Power user who has the appropriate permissions.

In addition to being able to have the proper user access for the eventual end users of our reports, we also need to think about what permissions are needed for us as report developers utilizing each of these reporting tools so that we can access the necessary data to develop the reports. All of these considerations must be made while keeping in mind the proprietary and sensitive nature of the data.

In general, we can break down security as that which is controlled through the Dynamics GP application and that which is maintained via some separate application. In the case of Report Writer and SmartLists, security is maintained entirely within the GP application. But, as we see in the following image, security for reporting tools such as Analysis Cubes and SSRS is maintained outside of the GP application, even though the data found within each tool is based on Dynamics GP data:

Microsoft Dynamics GP 2010 Reporting

Network access and general IT infrastructure

While determining which tool is best to use, we must ensure that the IT infrastructure can support it. The last thing we want to do as a report developer is design reports that bring an organization's infrastructure to a crawl or even worse bring down a server completely.

Some tools such as SSRS and Analysis Cubes for Excel, although they can work on the same server as the Dynamics GP application or SQL server, usually work much more efficiently when they are on a dedicated reporting server.

Because of the nature of the SmartList Builder and Excel Report Builder, in that they are used from within the Dynamics GP application, if we design reports using these tools that are again mainly used to extract large amounts of raw data, we may find the need to increase resources such as RAM, Disk Space, and/or processors to our servers.

Although the general IT infrastructure may vary from company to company, we can generally break this challenge down between reporting tools that can fit within the existing infrastructure required for Dynamics GP and reporting tools that, by their very nature, may lend themselves to additional resources such as new servers or additional SQL Server components. For example, while the traditional GP environment utilizing only the Financial modules may not have SSAS and SSRS installed, larger organizations that want to take advantage of SSRS and SSAS can expect to need more in terms of additional infrastructure and resources. As the following image shows, some reporting tools fit well within the general GP infrastructure while others will require a greater investment in resources:

Microsoft Dynamics GP 2010 Reporting

Developer resources

The final important challenge we must consider in the selection of a reporting tool to use is what sort of drain it will provide on developers. More than likely, as report developers and consultants, we can consider ourselves under the umbrella of "developer resources." Ideally, we should select a fool-proof tool that anyone can use, thus freeing us up from having to do any work! Actually, this may be a bit unrealistic, but we still do need to consider whether or not our end users will have the technical know-how to work with reports. Additionally, if our IT department is small or underfunded (we bet if you ask everyone in your IT department will probably already agree with you on both of these points!), we don't want to select a reporting tool that adds even more in the way of developer interaction for creating and managing reports. While some developer interaction is still recommended to ensure corporate control over sensitive data and to ensure that new silos of data are being created, we do want to free our developers from devoting extra time to troubleshoot minor problems each time an end user encounters something he or she doesn't understand.

One of the ways that we can minimize the drain on our development staff is to find and train a power user on the use of our selected reporting tool. A power user is someone who possesses a bit of technical knowledge (our tip for finding this person—look for the person in your company who is an Excel guru) and can be a champion for our reporting tool. This power user is someone who has a better grasp of our organization's business processes and, thus, can relate more easily to the end users who will be utilizing our reporting tool. This power user can stand as the gateway to the development team and ensure that only the most pressing questions and concerns are brought to the staff.

Depending on the strength of our GP power user, our development resource(s) may even be comfortable ceding control of report development via SmartList Builder and Report Writer to the power user. Because these tools are contained within GP, our development staff can be rest assured that new silos of data are not being created and maintained elsewhere.

Finally, we have some tools that will require extra attention from the development resources. Some, like creating custom SSRS reports and Analysis Cubes for Excel require developers who are experienced with all aspects of SQL including T-SQL, relational databases, SSAS, SSIS, and SSRS. Without some kind of experience in some of these areas, it will be very hard to deploy, much less maintain, the environments required to support custom SSRS reports and the cubes. While it may be possible to eploy the pre-defined SSRS reports and Analysis Cubes with a modicum of SQL Server knowledge, extending the usefulness of the product will require the kind of knowledge we've just discussed. These two tools, in particular, can also lead us down the slippery slope of creating new silos of information outside of GP. Without attention, our organization may become a bit more lax in ensuring that this external data is safeguarded properly and that it does not give rise to a new silo of information.

Some reporting tools do not require users to possess a great degree of technical skills, while others may require knowledge of technical concepts such as query-writing against relational databases and effective table joining. As the following image shows, some tools are well-suited to the average GP users, while others require the support of a more technical resource such as a developer.

Microsoft Dynamics GP 2010 Reporting


This article gave us a better understanding of how and when each reporting tool can be used most effectively in their organization.

Further resources on this subject:

You've been reading an excerpt of:

Microsoft Dynamics GP 2010 Reporting

Explore Title