Microsoft Dynamics GP 2010 Reporting: Create and manage business reports with Dynamics GP

Designing and developing reports has long been the domain of IT departments. If an end user identified a need for a particular report, he or she would typically have to submit a request to the IT department or some other functional group assigned to report development and creation. The process of designing and creating the report would then be assigned to a developer who, more than likely, had a number of other tasks waiting to be completed. Not surprisingly, the end user would not receive his or her report for several days, if not weeks or months! By then, it is entirely possible that the end user's needs may have changed and the anticipated report has become obsolete before it can even be used for the first time.

Is it any wonder then, that this inefficient practice has quickly given way to a more flexible process? While many situations still require the involvement of an experienced technical developer, in today's business environment, end users have a reasonable expectation that they will be provided with the tools necessary to create their own reports. As business software becomes more accessible and easier to use, end users increasingly expect to be equipped with the tools they need to create their own reports. This allows end users to design and create reports that match their specifications and to do this in a much quicker time frame than would be possible if this report were being designed by an individual in another department.

On the surface, this trend offers an extraordinary level of efficiency and flexibility that should be welcomed throughout all organizations. Certain challenges with this model do exist, however, and developers and consultants should be aware of these. Of course, end users who wish to create their own reports cannot have unfettered access to an organization's production database, nor will most of them understand the hardware and network requirements for working with reporting tools that handle large amounts of data. Therefore, while traditional IT departments are no longer the key designers and creators of most reports, they still have a key role to play in providing central oversight for the reporting needs of the entire organization. Additionally, developers and consultants must also expand their focus beyond the IT department to ensure that end users are well-equipped and trained in making the most effective use of their reporting tools.

In years past, access to reports was typically limited to members of the Executive team. As the long-term decisions makers for the company, it was the Executive team that benefitted most from reporting tools that provided a snapshot of company performance over a period of time. Reporting at other levels of the organization was generally an afterthought if it was not ignored entirely. Of course, one reason for this may lie in the fact that ERP systems were generally not as adept in capturing information from all aspects of the business; instead, the focus was on capturing general ledger information, which is generally perceived to be the domain of the Executive and Accounting teams. Additionally, perhaps for reasons of security and trust, Executive team members often did not see a reason to share key metrics and information with lower level team-members.

In recent years, however, as ERP systems have become more adept at capturing a wider variety of information across all functions of business, reporting across all levels of the organization has become more important. Executive teams are no longer the exclusive focus of an organization's reporting efforts. Today, in an effort to gain competitive advantage, all levels of the organization must utilize reporting tools to improve their ability to make decisions on a day-to-day basis. In other words, organizations are now focused on putting their hard-earned business data in the hands of those who can benefit the most from it. They are also discovering that transparency can improve an employee's performance and focus. Employees can quickly and easily see how their individual and team performance is impacting the overall company performance. This leads to greater buy-in and focus from all levels of the organization.

As a developer or a consultant tasked with developing reports, the focus is no longer just on developing balance sheets and income statements for an Executive team. While such reports are still relevant and necessary, additional reports for lower levels of the organization are also important. The Sales manager expects improved insight into customer spending behaviour. The Warehouse manager demands access to reports that identify orders behind schedule. Developers must be aware of the various reporting audiences that exist within an organization, the types of reporting that are required by these audiences, and the security that is required to ensure that sensitive information is only seen by the right individuals.

Successful organizations are the ones that make their data available to all levels of the organization. One reason for this trend lies in the fact that newer, more effective reporting tools have improved the ability of users across all levels of an organization to access company data. In previous years, hardware and resource limitations meant that reports were generated at specific times such as month-end close. In a less-connected business environment, where decisions did not have to be made on a minute-by-minute basis, companies could afford to wait until certain points in time to generate and review their financial statements.

In today's hyper-competitive business environment, however, this is no longer a luxury that organizations can afford. Instead, any report, whether a key financial statement or an aging trial balance, must be capable of being generated at a moment's notice with near-to or real-time company data. Organizations' members cannot afford to wait until the weekly sales meeting or the quarterly Executive team meeting to review key reports; instead, they must be generating and reviewing these reports on a daily basis.

Developers and consultants must be aware of this trend and seek ways to make reports more readily available to an organization. Issues such as latency and hardware must be considered to ensure that reports can be generated at a moment's notice without any negative impact to transactional processing. One of the most effective ways to do this is to make a wide range of reporting tools available to all levels of the organization and then equipping team members with the knowledge necessary to utilize these tools effectively.

The intended audience is any individual or business entity that will use the report as a means to answer questions about specific business functions. A successful reporting solution will be one that is designed with the ultimate end user in mind. It will answer the question, or questions, set forth by the intended audience in a clear, concise manner.

Within an organization, an Executive team may request a cash flow statement in order to view the company's cash position and to understand how much cash is available for further investment. A Payables Coordinator for the same company may need a report that identifies vendor's terms and discounts to determine which vendors should be paid sooner rather than later. Although both audiences may come to the same internal developer for these reports, the developer must be capable of understanding the requirements of the target audience.

A developer also may be tasked with creating a report for an external audience. For example, auditors or external creditors will request reports from an organization as a means to understanding more about an organization's business practices. It is possible that these reports will need to be submitted to external auditors in a specific, pre-determined format.

In all cases, a report writer must be aware of the "Me-Me-Me" syndrome. Requests for reports are usually the result of a need to answer a question specific to the intended audience's immediate function. To an individual who needs a specific report, no report is more important than the one he or she has requested. Not surprisingly, the requesting user will go to great lengths to prove that his or her report should receive top priority over other, yet-to-be created reports. While more clever developers may use this to their advantage and request bribes in the form of cookies or more vacation time. Effective developers will be wary of those who place their reporting needs above all others. And of course, if your boss is the one requesting the report, then by all means, you better pay attention!

Developers may also want to be on the lookout for the "all-knowing" report. Either we have been asked to write a report of this nature or have heard horror stories of it from fellow developers. This is the report that is supposed to give the user every bit of information from the entire system in one report, hence its name "all-knowing". Now, this almost always turns out to be an impossible feat. Sure, it sounds easy enough to the user, but if—and that is a big "if"—the report can actually be created does it provide any true value or is it just a discombobulated mess. If tasked with writing this type of report, don't be afraid to question the need and find out if this really should be a single report or multiple reports that present the data in a meaningful way.

While the goal of any ERP system is to provide a single comprehensive location for recording all functions of business, in reality, we find that most companies, in addition to the production ERP database, utilize a wide variety of different systems. Each system is incorporated with its own set of business logic, for recording data. These silos of information can range from entire database applications down to a static spreadsheet that an end-user keeps on his or her desktop. While a company's reasons for a lack of a single data repository can vary from lack of money required to combine systems to the inherent difficulty in transitioning from an older system to a newer system, the reality is that this kind of environment provides numerous challenges when it comes to accurate and timely reporting.

The challenge of having multiple data sources can be seen in the illustration below. Typically, there are multiple sources of data, being accessed and/or stored on one server or across multiple servers. In many cases, one way to address this challenge is to have a reporting server compiling all this data.

Let's think about what some of these challenges of reporting across multiple data sources might be:

Each of these challenges must be managed to provide accurate and effective reporting. First, developers and consultants must be careful to select a reporting tool that can bridge the gap between these disparate systems without adding another independent silo of information. Once this tool is selected, it must be used effectively to present one version of the truth to the end user. By this, we mean that it must provide users with a consistent and reliable look at the data in the report, regardless of how many data sources were used to generate the data in the report in the first place.

But, let's be honest, accumulating and storing data outside of the ERP database is not always a bad thing. A company cannot and should not expect to conduct all reporting against the production ERP database, which is where the majority of the transactional and statistical information resides. While this method is more likely to allow for real or near-real time reporting, it is also likely that it will cause a decrease in system performance for other users. A common technique for many companies who want to provide reporting tools to users without impacting production performance is to set up a separate data warehouse. This separate data warehouse contains data from one or more enterprise systems and provides a separate location against which users can generate reports and queries. Data in this warehouse is updated at pre-set intervals via extract, transform, and load tools such as SQL Server Integration Services (SSIS). Some companies take the concept of a separate reporting server one step further by creating a separate reporting server. This completely removes the performance impact of reporting away from the ERP system database.

Regardless of the reporting tool we select, we must be aware of the trade-offs in the various sources of data that may be used to generate the report. If we're reporting from a production database, we may encounter additional issues with security and hardware performance. Likewise, if we are extracting data for reporting from a separate data warehouse, we have to be aware that the data may not be in real time. Furthermore, if we plan to utilize multiple sources of data, such as when we want to combine data in an Excel spreadsheet with data stored in our ERP application, to generate our report, we must pay attention to such challenges as duplicate data, business logic, and more.

As we have discussed, trends in reporting have led to more users wanting more data in real time to gain a more competitive business advantage. When we speak of latency, we are referring to the delay between when source data is generated and when it can actually be used in a report. For example, if a journal entry is posted to a revenue account, but the President of our organization must wait until a scheduled report generation process to see the impact of this entry on an income statement for the organization, then we have a period of latency between when the source data was generated and when it was retrieved by a particular report.

As a report developer, one of the first questions that needs to be answered is, "Does the report need to be real-time or can it be generated after some arbitrary delay?" This can and usually does lead to other challenges. Many times users will want everything in real time.

Most often, true real-time reporting is either nearly impossible to achieve or can cause a real strain on the infrastructure. In addition to this, the developer must ask, "Is there really a true need for the report to be in real-time?" Take, for example, an Accounts Receivable manager who is requesting a real-time aging report. Is the benefit of having this run on real-time data going to make a dramatic difference in what is actually collected on these open receivables? Probably not! Now, if we take an example of a Sales Manager requesting a real-time report of open sales orders that have not shipped yet, having this information can make a difference. This will give that Sales Manager a report from which he or she can take immediate action by going to shipping and identifying the hold up on the orders.

It's important to note that the closer we get to real-time reporting, the cost of resources required to provide such real-time reporting will become greater, while the extra increase in added benefit from access to this real-time data will shrink. At the same time, the cost of accessing real-time data usually outweighs the extra benefit provided by having access to such data. When this happens, we face the challenge of convincing our report-users that the data latency they may experience in their reports must be acceptable.

An example of the concept of increasing costs for a decrease in latency is seen in the following image. As we move from high latency, for example, data provided the next morning, to low latency, real time data for instance, the cost of that goes up in terms of both lower performance and higher monetary cost.

So, how does a report designer weigh the pros and cons of providing end users with access to real-time data? The main thing we want to ask ourselves as report developers when determining whether a report truly needs to be real-time is whether or not the data in the report is critical to spurring immediate action or changes in the day-to-day operations of our organization.

Before selecting an appropriate reporting tool for a given situation, requirements for formatting and presentation must be given careful consideration. To some degree, the appropriate solution to this challenge will rely on the report's intended audience. External reports, such as those being sent to customers and clients will, in most cases, require company logos and other colourful visuals that represent your company in a professional manner. On the other hand, reports being designed for internal use, such as for a warehouse manager, may not require extensive formatting and presentation capabilities.

As can be seen in the below diagram, you can see how much more presentable the balance sheet on the right is when compared to the standard out of the box balance sheet on the left:

Understandably, this reporting challenge often takes a back-seat to other, more important challenges such as latency and security. Providing a clean, colourful report, however, can play a critical role in a user acceptance of a report and its contents. By going the extra mile to select a reporting tool that has the ability to easily control report formatting and graphics, report viewers are more likely to be influenced by the contents of the report in a manner of our choosing.

But, this challenge is not just about using fancy fonts or selecting pretty images and logos from a graphics repository. It is also about providing appropriate tools for improving the readability of your report. For example, if we are developing reports that display key financial metrics, we will want our reporting tool to have the ability to display data in straight rows and columns. Viewers of the finished product should be able to follow the logical flow of information presented in the report. Whether this means looking across columns to see how performance has changed over time or down the rows of an income statement to see how revenues compare to expenses over the same period of time, the report should be laid out in logical fashion.

Our report may also be required to meet certain standards for formatting. For example, publically traded companies must submit certain financial statements and these statements must be prepared according to certain principles and standards. A few examples of this includexBRL (eXtensible Business Reporting Language) reporting and GAAP/FASB/IASB regulations. If this is the kind of report we will be developing, then it should play a prominent role in the selection of the reporting tool.

In addition to the challenges covered thus far, we must also consider the kind of report we need to create. Is this a report that our users will create on an as-needed basis? Or, is it a report that users will need to create repeatedly over a period of time? Additionally, the selection we make here may impact how these reports are distributed among the various individuals who need to see the contents of the report.

The concept of ad hoc versus traditional reporting can be broken down into two components:

Every organization has a need for viewing its financial performance at or over a period of time through the use of financial statements. Financial statements are traditional reports that typically require some effort during the initial setup to determine how they will be structured, how often they will be created, and so on. But, usually after this initial setup is done, the structure requires very little in the way of modification during future financial periods. Therefore, while it may be helpful, it is not always critical that the reporting tools we use to generate our financial statements offer us exceptional flexibility in changing or modifying reports.

In addition to reporting requirements that span multiple financial periods, individual users may have a unique reporting need that arises due to a specific business challenge. Rather than go through the effort of setting up a traditional report that can be used over and over again, our user simply needs a reporting tool that can quickly generate a report on an as needed basis in order to answer the specific question at hand. In exchange for this ad hoc capability, ad hoc report viewers may be willing to allow a trade-off in other areas such as formatting and presentation.

In this image, we see a sample of the PivotTable functionality from Microsoft Excel that provides users an opportunity to query their data in an ad hoc fashion:

As we answer the challenges associated with ad hoc versus traditional reporting tools, we must also determine how the report will be distributed to those end users who do not have the ability to generate the reports. Will these users be reliant on a power user or the initial report requestor to generate the report and email the results in PDF format? In most, but not all, cases, we will find that traditional reports are more easily distributed than ad hoc reports. Traditional reports, such as financial statements, usually contain information that is useful across a wide variety of users. These reports contain data presented in a standard format, making the reports useful for users across a wide variety of backgrounds. If a report will be distributed on a regular basis, we must make additional considerations for how to manage security for these reports, the manner in which they will be distributed (for example, via email or via a central location like a company intranet), and how the process of report distribution will be scheduled.

On the other hand, reports generated through ad hoc reporting tools are not usually distributed to a wide variety of users. By their very nature, ad hoc reports are developed quickly, and for a single purpose. For example, an external auditor may request a list of all journal entries made by a company in a particular year. This is a typical request by external auditors as they prepare a company's audit report. However, it isn't really information for which a company will need a traditional report, nor is it a report that will be saved and re-distributed. Rather than expend time and energy developing a report that will be used only once, we may be better off utilizing an ad hoc reporting tool to meet our needs.

A good report developer or consultant will be able to pick up on key requirements from the report requestor and quickly determine whether the information being requested is better pulled in the form of an ad hoc report or some other type of traditional report.

Few reports exist that can be distributed to anyone and everyone inside and outside of an organization without regard for security. Instead, most reports contain sensitive data that must be tightly controlled to ensure it does not fall into the wrong hands. At the organizational level, companies must have control over their data to ensure that it does not fall into the wrong hands outside of the organization. Within an organization, reports developed for one department may not be suitable for employees in another department. For example, consider a report on year-to-date payroll amounts developed for the Director of Human Resources. This type of report is usually considered sensitive information that should not be seen by others in the organization.

When selecting a reporting tool, developers and consultants must consider how effective that reporting tool will be when it comes to managing user access to the data contained in the report. Will entire reports be restricted to certain users? Or, will one format be provided to all users with the select data being displayed based on the user viewing the report? It is also worth considering which attributes will be used to determine security. In some cases, it may be the department that a user belongs to, or it may be that user's functional role within the organization that allows him or her access to certain reports. In other cases, still, it may be that reports will be password protected, and only those with the password will be allowed access to reports.

As developers and consultants tasked with report writing, we often find ourselves with greater access to company data than that of the average end user. With this access comes a high level of responsibility. One of the greatest proprietary advantages a company has over its competitors is the data that it carefully maintains. By selecting reporting tools with security in mind, we take a critical step towards ensuring that hard-earned company data will continue to provide an extra advantage over our competitors.

Access and infrastructure play multiple roles in determining our reporting solution. Not only do we need to determine whether our infrastructure will allow the type of reporting we are going to be utilizing from a performance perspective, but we also need to determine if it will provide the necessary access to our intended audience.

Some of the questions we might ask with regard to this reporting challenge include:

By asking these questions and more, we can make a more accurate selection of a reporting solution.

As the following diagram shows, users in our organization may require external access to the reporting server. This requires taking firewalls and network access into consideration as we select a reporting tool. Additionally, we may have users who will be asking for reports internally, and those users may be able to take advantage of a completely different reporting tool.

With the advent of such technologies such as Citrix and Terminal Server, the ability to give either internal or external access to users on a large scale has been made easier. Many companies that run some type of ERP system will usually already have one of these solutions in place to grant their users remote access. In addition, this type of infrastructure allows companies to centralize their servers, keep maintenance down to a minimum, and keep the systems standardized. In these types of environments, we can easily add on our reporting solutions.

In deciding what the most appropriate reporting solution is, we must also consider how much data is being transmitted across the network. If, for example, we are trying to pull down large amounts of data in the middle of business hours, what kind of effect is that having on our network? Do we need to add bandwidth to our network or can we work within the current bandwidth we have? The answer to this question is usually imperative to deciding on the most appropriate reporting solution.

We have talked about various areas of infrastructure that need to be considered when writing reports. Another important area is available disk space. Whatever reporting solution we ultimately decide on, will, to some degree, require additional disk space. Whether or not the reporting tool requires enough space for an entire data warehouse or if it can rely on something as simple as enough local disk space that contains a report depository or data store, the report developer or consultant needs to be aware of this. We must also determine how much anticipated growth in the data there will be so that disk space can be planned accordingly. One thing we want to avoid is recommending and building a solution that has a short lifespan because it runs out of disk space and crashes the server! As report developers and consultants, it is our job to figure out how long the lifespan of the solution needs to be based on the customers' needs and plan for that growth, ensuring that end solution indeed fills that need.

The last thing we want report developers and consultants to be aware of from an infrastructure standpoint is how memory intensive the reports can be. We have discussed this briefly in terms of dedicated reporting servers, but in addition we want to point out that even with sufficient memory, some reports will just take time to run. A perfect example is a heavy distribution company with thousands of orders. To run an open order report against thousands of orders that might have a large number of line items, we are looking at a potentially long report generation time. It is in these types of reports we must decide on how frequently this report will be generated. Is it something we can schedule to run overnight to be ready first thing in the morning? Or, is it something we can break down into smaller runs, with filters/parameters to spread out the load? These are just some of the things to think about and discuss with the report requestor.

So far, we have mainly discussed challenges having to do with choosing the proper reporting solution. Another piece of the puzzle is the resources that will be tasked with actually developing the reporting solution.

Many times equipping our intended audience with the tools necessary for them to create their own reports is the right course of action. In this model, these "power users" are users who are less technical, but they understand the data they are working with and the output that they need very well. One thing we as report developers and consultants need to take into account is whether this model works for the organization as a whole and that the IT department understands that they are going to have less control over the solution as a whole. We also need to make sure that we don't lose corporate governance over sensitive information.

Though empowering users to be able to generate their own reports has grown more and more acceptable, many IT departments don't want to lose control of the management of the data and are concerned that they will lose corporate oversight and create multiple silos of data. Because of this, report development is usually assigned to a dedicated report developer, be that either an internal resource in the organization, or an outside developer or consultant.

Organizations will often determine whether to develop reporting solutions in house or to outsource it based on many factors. The main factors for our purpose are who is available to develop the reports and whether there are any time and budget constraints. Even if the organization has the required skill set in house, the resource might not necessarily have the time to dedicate to developing the report. This is when companies will often look to outside resources to get the job done. The flip side of this may be if there are budget constraints that prohibit an organization from going outside to get the reporting solution developed, and this is when we typically see organizations keep it in house.