Home Business & Other Microsoft Dynamics GP 2013 Reporting

Microsoft Dynamics GP 2013 Reporting

By Chris Liley , David Duncan
books-svg-icon Book
eBook $39.99 $27.98
Print $65.99
Subscription $15.99 $10 p/m for three months
$10 p/m for first 3 months. $15.99 p/m after that. Cancel Anytime!
What do you get with a Packt Subscription?
This book & 7000+ ebooks & video courses on 1000+ technologies
60+ curated reading lists for various learning paths
50+ new titles added every month on new and emerging tech
Early Access to eBooks as they are being written
Personalised content suggestions
Customised display settings for better reading experience
50+ new titles added every month on new and emerging tech
Playlists, Notes and Bookmarks to easily manage your learning
Mobile App with offline access
What do you get with a Packt Subscription?
This book & 6500+ ebooks & video courses on 1000+ technologies
60+ curated reading lists for various learning paths
50+ new titles added every month on new and emerging tech
Early Access to eBooks as they are being written
Personalised content suggestions
Customised display settings for better reading experience
50+ new titles added every month on new and emerging tech
Playlists, Notes and Bookmarks to easily manage your learning
Mobile App with offline access
What do you get with eBook + Subscription?
Download this book in EPUB and PDF formats, plus a monthly download credit
This book & 6500+ ebooks & video courses on 1000+ technologies
60+ curated reading lists for various learning paths
50+ new titles added every month on new and emerging tech
Early Access to eBooks as they are being written
Personalised content suggestions
Customised display settings for better reading experience
50+ new titles added every month on new and emerging tech
Playlists, Notes and Bookmarks to easily manage your learning
Mobile App with offline access
What do you get with a Packt Subscription?
This book & 6500+ ebooks & video courses on 1000+ technologies
60+ curated reading lists for various learning paths
50+ new titles added every month on new and emerging tech
Early Access to eBooks as they are being written
Personalised content suggestions
Customised display settings for better reading experience
50+ new titles added every month on new and emerging tech
Playlists, Notes and Bookmarks to easily manage your learning
Mobile App with offline access
What do you get with eBook?
Download this book in EPUB and PDF formats
Access this title in our online reader
DRM FREE - Read whenever, wherever and however you want
Online reader with customised display settings for better reading experience
What do you get with video?
Download this video in MP4 format
Access this title in our online reader
DRM FREE - Watch whenever, wherever and however you want
Online reader with customised display settings for better learning experience
What do you get with video?
Stream this video
Access this title in our online reader
DRM FREE - Watch whenever, wherever and however you want
Online reader with customised display settings for better learning experience
What do you get with Audiobook?
Download a zip folder consisting of audio files (in MP3 Format) along with supplementary PDF
What do you get with Exam Trainer?
Flashcards, Mock exams, Exam Tips, Practice Questions
Access these resources with our interactive certification platform
Mobile compatible-Practice whenever, wherever, however you want
BUY NOW $10 p/m for first 3 months. $15.99 p/m after that. Cancel Anytime!
eBook $39.99 $27.98
Print $65.99
Subscription $15.99 $10 p/m for three months
What do you get with a Packt Subscription?
This book & 7000+ ebooks & video courses on 1000+ technologies
60+ curated reading lists for various learning paths
50+ new titles added every month on new and emerging tech
Early Access to eBooks as they are being written
Personalised content suggestions
Customised display settings for better reading experience
50+ new titles added every month on new and emerging tech
Playlists, Notes and Bookmarks to easily manage your learning
Mobile App with offline access
What do you get with a Packt Subscription?
This book & 6500+ ebooks & video courses on 1000+ technologies
60+ curated reading lists for various learning paths
50+ new titles added every month on new and emerging tech
Early Access to eBooks as they are being written
Personalised content suggestions
Customised display settings for better reading experience
50+ new titles added every month on new and emerging tech
Playlists, Notes and Bookmarks to easily manage your learning
Mobile App with offline access
What do you get with eBook + Subscription?
Download this book in EPUB and PDF formats, plus a monthly download credit
This book & 6500+ ebooks & video courses on 1000+ technologies
60+ curated reading lists for various learning paths
50+ new titles added every month on new and emerging tech
Early Access to eBooks as they are being written
Personalised content suggestions
Customised display settings for better reading experience
50+ new titles added every month on new and emerging tech
Playlists, Notes and Bookmarks to easily manage your learning
Mobile App with offline access
What do you get with a Packt Subscription?
This book & 6500+ ebooks & video courses on 1000+ technologies
60+ curated reading lists for various learning paths
50+ new titles added every month on new and emerging tech
Early Access to eBooks as they are being written
Personalised content suggestions
Customised display settings for better reading experience
50+ new titles added every month on new and emerging tech
Playlists, Notes and Bookmarks to easily manage your learning
Mobile App with offline access
What do you get with eBook?
Download this book in EPUB and PDF formats
Access this title in our online reader
DRM FREE - Read whenever, wherever and however you want
Online reader with customised display settings for better reading experience
What do you get with video?
Download this video in MP4 format
Access this title in our online reader
DRM FREE - Watch whenever, wherever and however you want
Online reader with customised display settings for better learning experience
What do you get with video?
Stream this video
Access this title in our online reader
DRM FREE - Watch whenever, wherever and however you want
Online reader with customised display settings for better learning experience
What do you get with Audiobook?
Download a zip folder consisting of audio files (in MP3 Format) along with supplementary PDF
What do you get with Exam Trainer?
Flashcards, Mock exams, Exam Tips, Practice Questions
Access these resources with our interactive certification platform
Mobile compatible-Practice whenever, wherever, however you want
  1. Free Chapter
    Meeting the Reporting Challenge
About this book
Publication date:
October 2013
Publisher
Packt
Pages
386
ISBN
9781849688925

 

Chapter 1. Meeting the Reporting Challenge

In an increasingly digital world, utilizing proper reporting techniques is essential to make sense of the increasing mountain of data we find stored on our computers.

The task of reporting on and identifying trends in this data is an exciting topic that will only grow in importance as companies continue to take advantage of cheaper disk space and the growing capability of applications and Enterprise Resource Planning (ERP) systems that can track and record every last detail of conducting a business.

As developers and consultants tasked with report writing, our goal is to provide the decision-makers in our organization with the tools and reports necessary to use this data when making key business decisions. We can help our end users unlock the trends found within the data being analyzed. By providing accurate reports that bridge the gap between disparate information systems, we can provide users across the company with accurate, reliable information. In other words, we can provide them with a single version of the truth so that multiple end users can make decisions from the same set of data.

This book is intended for anyone who might be tasked with developing and creating reports, whether they are for personal use or for use by other report consumers. The term "developer" invokes an image of someone who is technically adept and possesses a unique understanding of the inner-workings of a particular piece of software. But, in the context of report-writing (and this book), the term developer has a much wider meaning. Instead, report developers can include those with more application experience such as senior accountants and day-to-day operations personnel. It can also apply to application consultants who are well-experienced with an organization's ERP application.

As we reach the end of this chapter, we should possess a better understanding of the following:

  • Major trends facing report writers and developers in today's hyper-competitive environment

  • The end user-driven challenges or requirements that exist when designing a report according to an end user's specifications

  • Organizational challenges that may help or hinder us from designing a report that meets the end user's requirements

  • Weighing the pros and cons of numerous trends and challenges in light of each other in order to select the most effective report or reporting tool possible

While the focus of this book will primarily rest on reporting tools associated with the Microsoft Dynamics GP ERP application, the concepts required to design and build reports apply to the entire spectrum of ERP applications and various data repositories in general.

 

Trends in reporting


As companies have adapted to new ways of capturing and recording business data, the techniques for reporting and making sense of this data has also changed. The best report developers are those who understand how these techniques have changed and are willing to update their own skill sets to capitalize on this.

Some of the most recent, important trends in the reporting space include:

  • Increased flexibility

  • Reporting through all levels of an organization

  • Increased access to the report generation process

Understanding how reporting techniques have changed in the last few years provides us an opportunity to improve our ability to produce relevant and useful reports. Developers and consultants who understand how to exploit these trends can position their organization's reporting capabilities to provide a true competitive advantage. Understanding past trends will provide us with insight into the future of reporting so that we may begin taking steps to prepare for new tools and techniques.

Increased flexibility

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. Unsurprisingly, 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 becomes 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 a 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.

Reporting through all levels of an organization

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 the 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 at 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 due to 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 on just 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 behavior. 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 reports that are required by these audiences, and the security that is required to ensure that sensitive information is only seen by the right individuals.

Increased access to the report generation process

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.

However, in today's hyper-competitive business environment, 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 equip team members with the knowledge necessary to utilize these tools effectively.

 

Challenges to developing and writing reports


Now that we have identified some of the common trends that exist in today's reporting domain, let's take a look at some of the challenges these trends pose to the developer or consultant setting out to create a well-designed report. As we've seen, each trend leads to a number of different challenges. Careful consideration of these challenges can be the deciding factor in whether or not a report gains acceptance from the intended audience.

From our experience with designing reports, we have identified a list of nine common areas or challenges that we think are important to consider before setting out to design or build the perfect report:

  • Intended audience

  • Data sources

  • Latency

  • Formatting and presentation

  • Ad-hoc reports vs. traditional reports

  • Security

  • Network access and general IT infrastructure

  • Developer resources

While this is certainly not a comprehensive list of all the challenges we will face while designing reports, it does include some of the most important ones to consider when developing and creating reports. The first five challenges relate to how the end user anticipates the report will be designed. Obviously, these challenges should be addressed based on feedback from the end user. The final challenges are less about end user requirements and more about the environment in which we will be designing the report. Often, it can be a challenge to explain to an end user why certain environmental challenges such as lack of developer resources may prevent a particular report from being built to his or her specifications.

Keep in mind that becoming a successful report developer does not always mean delivering a final report to the intended audience. Instead, in many cases, the true "deliverable" that will result from the report writing process will come from equipping the intended audience with the tools necessary for them to create their own reports. As we work our way through the rest of this book, remember that when we discuss creating a successful report, we are really commenting on the overall reporting solution, regardless of what shape or form it may take.

Intended audience

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. An Accounts 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 may also 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, transaction information, or financial position. 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. Most importantly, if it is your boss whom is the one requesting the report, then by all means, you better pay attention!

This is also a good time to warn report developers 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. Often, simply posing the question, "What do you want to accomplish, and how does this report help you accomplish that goal?", will spur a meaningful conversation between the developer and the report consumer. The answer may uncover a need for training users on existing reporting tools—perhaps by sharing this book with the user!—or help the two parties narrow down the focus of the report.

Data sources

While the goal of any ERP system is to provide a single comprehensive location for recording all the functions of a 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. As well as with the increasing move towards cloud-based computing, our data is no longer necessarily found on-premise, it could be found in the form of an RSS feed, Odata feeds from the Windows Azure Marketplace, or some other web-based data source. While a company's reasons for a lack of a single data repository can vary from the 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.

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

  • Data may be duplicated across multiple systems. The fact that some of this data may be stored in cloud-based applications, while other data may be stored in on-premise data stores, only adds to the complexity of this challenge.

  • The business logic utilized by the systems that capture information may differ from system to system, leading to differences in how the data is stored in each silo.

  • Timing differences may exist between silos. Data in one system may be updated on a real-time basis whereas in another system, it might be updated on a weekly basis.

  • Levels of granularity may differ among data sources. For example, one data source might record customer information at the sales order line level, while another data source might only record information at the customer level.

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.

Let's be honest, however, 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. Two common techniques exist for most companies who want to provide reporting tools to users without impacting on production performance:

  • 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).

  • Use SQL Server technologies such as log shipping, database snapshots or, with SQL Server 2012, AlwaysOn Availability Groups, to create copies of the primary user databases. Report solutions can then use these database copies as a data source, thus removing the impact of queries from the primary database(s).

Some companies take the concept of a separate reporting environment one step further by creating a separate reporting server to host reporting related functions including data warehouses or database copies. 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-off with 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 challenges such as duplicate data, business logic, and more.

Latency

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 for a scheduled report generation process to see the impact of this entry on an income statement, 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?", which can and usually does lead to other challenges. Often, users will want everything in real time.

Mostly, 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 to 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.

Another important factor in determining how realistic real-time reporting may be in a particular scenario is how much data is involved. Running real-time reports over significantly large data sets could render our reports out-of-date before we even have a chance to use them. In the worst cases, the underlying data set may actually change during the time it takes to render the report, meaning we are acting on inaccurate information! Sometimes large data sets are unavoidable, and filtering conditions and other techniques must be utilized to trim the delay required to generate the report so that we do not receive obsolete and inaccurate information.

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 figure. As we move from high latency, for example, data provided the next morning, to low latency, real-time data, 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.

Formatting and presentation

Before selecting an appropriate reporting tool for a given situation, the 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 shareholders, customers, and clients other interested parties will, in most cases, require company logos and other colorful visuals that represent the 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 following figure, you can see how much more presentable the balance sheet on the left is when compared to the standard out of the box balance sheet on the right:

Understandably, this reporting challenge often takes a back-seat to other, more important challenges such as latency and security. However, providing a clean, colorful report, can play a critical role in a user's acceptance of a report and its contents. Therefore, be mindful, in selecting a reporting tool with easy control of the report formatting and graphical inserts to ensure that the report viewers are more likely to be influenced by the contents of the report.

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 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 include eXtensible Business Reporting Language (xBRL) 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.

Ad-hoc reports versus traditional reports

In addition to the challenges covered this far, we must also consider the type of report we need to create. Is this a report that our users will create to address a specific, one-time need? Or, is it a report that users will need to produce repeatedly over a period of time? Additionally, the selection we make here 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:

  • Difficulty level of report modification after initial creation

  • Report distribution options to end users, decision makers, and other interested parties

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 level of pre-planning before the initial setup or report creation. These efforts are to determine how they will be structured, the level of detail, generation frequency, and so on. After the report is created, the structure requires very little in the way of modification for 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 structures that will be used again and again over 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 the following screenshot, 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 traditional reporting tools versus ad-hoc tools, we must also determine how and when 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 other application user to generate the report? Will the results need to be emailed, published to a website, or printed for a financial package?

In most, but not all, cases, we will find that the traditional reports are more easily distributed than ad-hoc reports. Also, traditional reports such as financial statements usually contain information that is useful across a diverse user group. This eliminates the need to create multiple stand-alone versions of the same report and reduces the time needed to manage the different types of distribution channels.

Furthermore, 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 also make additional considerations to manage the security of these reports (which we will discuss momentarily), the process by which these reports will be distributed (for example, via e-mail or via a central location like a company intranet), as well as the manner in which reports will be scheduled for distribution.

On the other hand, many 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 specific all journal entries or transactions made by a company for a particular time period. 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 is 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.

Security

Few reports exist that can be distributed to anyone and everyone inside and outside of an organization without considering security. Instead, most reports contain sensitive data that must be tightly controlled to ensure it does not fall into the wrong hands. 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. Consider, as well, reports in the medical field that might contain Protected Health Information (PHI). This information, by law, must be secured in a certain way before distribution to certain parties in order to protect the privacy of the patients represented by the data.

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 the 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 in its ERP and related applications. 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.

Network access and general IT infrastructure

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:

  • How will our intended audience access the reporting tool?

  • How much data is being transmitted across the network?

  • Do we have a dedicated server for reporting purposes?

  • Do we have sufficient disk space to support a data warehouse?

  • Will the reports be memory intensive?

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

As the following figure 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 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 we want to specifically point out the advent of in-memory reporting tools like PowerPivot for Excel. With this tool, data is stored in-memory on whatever computer is hosting the PowerPivot model. Users must be aware that the more data they add to the model, the more memory they will consume. This can quickly cause issues, especially for other applications on the same machine.

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 for these types of reports that we must decide on how frequently the 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.

Developer resources

So far, we have mainly discussed the challenges 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 less technical, but they understand the data they are working with and the output that they need very well. One thing that 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.

Although 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 any time and budget constraints exist. Even if the organization has the required skill set in house, the resources might not necessarily have the time to dedicate to developing the report. This is when companies will often look at outside resources to get the job done. On the other hand, factors such as budget constraints may force an organization to keep these resources in-house.

 

Summary


As we work our way through the rest of this book, always keep in mind that our goal is to provide our end users with accurate and reliable ways to mine the data found in our reporting systems. If we cannot maintain accuracy and reliability, then our end users will quickly seek out another report developer who can meet their needs!

Creating accurate and reliable reports requires the ability to properly identify the end user and corporate requirements. The ability to identify the requirements for a report or reporting tool is a valuable skill to have in today's business environment. Although, this skill doesn't come easily! Instead, developers and consultants must constantly ask challenging questions of end users and the corporate infrastructure to select the most effective reporting tool for the circumstances.

Unsurprisingly, the process of creating a report is not often as easy as creating a data connection to a database and throwing data up onto a computer screen or a PDF file. But, in order to make it easy, we've provided you with information to set you along the way towards identifying the requirements for your report.

As we begin to cover specific reporting tools for Microsoft Dynamics GP, keep the trends and challenges we've discussed in this chapter in the back of your mind. Continue to ask yourself how each reporting tool can help you meet the challenges that we've presented in this chapter.

In the next chapter, we will begin by looking at some tips and tricks to locating our data in the Microsoft Dynamics GP system. Then, we will look at certain tools that can help us in finding the specific data we need to begin writing our reports.

About the Authors
  • Chris Liley

    contacted on 25 may '16

    Browse publications by this author
  • David Duncan

    David Duncan has been a consultant and business analyst for over five years. He has driven organizational success through well-defined reporting solutions that provide valuable and insightful information to key stakeholders. He is the co-author of Microsoft Dynamics GP 2010 Reporting and has also served as a technical reviewer for Building Dashboards with Microsoft Dynamics GP 2013 and Excel 2013, both by Packt Publishing. David, who holds several certifications for Microsoft Dynamics GP and SQL Server, has extensive experience in designing and providing business intelligence and reporting tools for organizations that use Dynamics GP and many other Microsoft SQL Server-based applications. In his current position as a business analyst, David works with end users at all levels of his organization to provide them with reporting solutions to meet their needs. David enjoys sharing insights gained from these solutions, and he is even happier when he can share these insights using Excel PivotTable and Excel Power Pivot functionality! David holds a degree from Clemson University. He resides in Rocky Mount, N.C. with his wife, Mary Kathleen, and their newborn daughter, Mary Eliza.

    Browse publications by this author
Microsoft Dynamics GP 2013 Reporting
Unlock this book and the full library FREE for 7 days
Start now