Home Business & Other Microsoft Dynamics GP 2010 Reporting

Microsoft Dynamics GP 2010 Reporting

By Chris Liley , Christopher 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
Resources such as the book you now hold in your hand are critical to taking the extra step in uncovering the trends locked deep within your data. Not only will this book offer insight into the many reporting tools currently available for GP, it will also offer a unique perspective on how each reporting tool can be used to meet specific challenges faced by your organization" - Errol Schoenfish, a member of the Microsoft Dynamics community for over 24 years Microsoft Dynamics GP 2010 is a sophisticated Enterprise Resource Planning (ERP) system with a multitude of features and options. Microsoft Dynamics GP enables you to create and manage a variety of reports that help small and mid-size businesses effectively manage their financial and operational data. This book will show you how to create and manage reports, know what tools to use and when, how to use them and where to find the data based on how it's being entered into the system with Dynamics GP. This book will empower you with the tools and reports necessary to use Dynamics GP data in making key business decisions. The book addresses the many challenges and frustrations a company may face when preparing to build new reports. Then it moves on to explain how to find your data in the GP system and company databases. The book then dives deep into topics such as SmartLists, SL Builder and Excel Report Builder, Report Writer, SSRS Report Library, and Analysis Cubes Design and Management Reporter amongst others. With this knowledge in hand, you will be capable of selecting the most effective tool for the current reporting environment.
Publication date:
April 2011
Publisher
Packt
Pages
360
ISBN
9781849682183

 

Chapter 1. Meeting the Reporting Challenge

In an increasingly digital world, utilizing proper reporting techniques is essential to making 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 field 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 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 in making key business decisions. We can help our end users unlock the trends found within the data being analysed. 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:

  • Taking advantage of the major trends facing report writers and developers in today's hyper-competitive environments

  • 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 have 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 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 true competitive advantage against other industry laggards. Understanding past trends will provide us 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. 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.

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

Increased access to 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.

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.

 

Challenges in 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 of the report.

In 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 queries vs. traditional reports

  • Security

  • Network access and general IT infrastructure

  • Developer resources

While this is certainly not a comprehensive list of challenges we'll meet while designing reports, it does include some of the most important 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. Oftentimes, it can be a challenge explaining to an end-user why certain environmental challenges, such as lack of developer resources, may prevent you from building a report 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 ultimate "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.

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

Data sources

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:

  • Data may be duplicated across multiple systems.

  • The business logic utilized by the systems that captured 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's updated on a weekly basis.

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.

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

Formatting and presentation

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.

Ad hoc queries versus traditional reports

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:

  • The ease with which a report can be created; or, how easily the report can be modified after initial creation

  • How easily the final version of a report can be distributed to other report viewers

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.

Security

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.

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

Developer resources

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.

 

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. But, 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.

Not surprisingly, 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
  • Christopher Liley

    Chris Liley is a Principal Consultant with Microsoft Partner I.B.I.S., Inc.in Norcross, GA and a Microsoft Certified Information Technology Professional for Dynamics GP.  He is a graduate of Georgia State University with a B.B.A in Accounting.  Chris has worked with Dynamics GP since 2001. Chris’ experience ranges from financial analysis, software implementations, data conversions, integrations to designing and developing customizations in both the functional and technical area of consulting for Dynamics GP.  Chris also has extensive experience designing Business Intelligence solutions.

    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 2010 Reporting
Unlock this book and the full library FREE for 7 days
Start now