Microsoft Dynamics GP 2010 Reporting
Create and manage business reports with Dynamics GP
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:
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:
Latency
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.
