Description
Microsoft Dynamics GP Business Intelligence
Business Intelligence
Microsoft Dynamics GP
Business Intelligence
WHITE PAPER
Date: February 2006
Table of Contents
Introduction....................................................................................................................... 4
Reporting Tools ................................................................................................................ 5
Report Writer ................................................................................................................... 5
Reports Library................................................................................................................. 5
FRx
®
.................................................................................................................................. 5
Enterprise Reporting........................................................................................................ 7
Original Report ................................................................................................................ 7
Drill Down on the Total Group ....................................................................................... 7
Drill Down on North America ......................................................................................... 8
Crystal Reports ................................................................................................................. 8
Microsoft SQL Server™ Reporting Services .................................................................... 8
Reporting Services Technical Overview.......................................................................... 9
Architecture Overview..................................................................................................... 9
Server Components ......................................................................................................... 9
SQL Reporting Service sample report packs ................................................................10
Analysis Tools .................................................................................................................11
Analytical Accounting....................................................................................................11
Transaction Dimensions ................................................................................................11
Transaction Dimension Codes.......................................................................................12
Valid Code Combinations..............................................................................................13
Multilevel Reporting......................................................................................................13
Ad Hoc Query .................................................................................................................13
Inquiries and Audit Trail Records..................................................................................13
SmartList.........................................................................................................................14
Integrating Microsoft Dynamics GP data from SmartList with Excel .........................14
Export Solutions.............................................................................................................15
Business Portal Query Pages .........................................................................................15
2
MICROSOFT DYNAMICS GP – BUSINESS INTELLIGENCE
Quick Insight ...................................................................................................................16
Business Alerts ...............................................................................................................16
Business Portal Executive Center ..................................................................................17
Key Performance Indicators ..........................................................................................18
Office Web Parts in Business Portal pages ...................................................................18
Excel data source ...........................................................................................................19
Instant access to data using SmartTags........................................................................21
Analysis Cubes for Excel ................................................................................................22
Business Scorecard Manager.........................................................................................24
Web Services ..................................................................................................................26
3
MICROSOFT DYNAMICS GP – BUSINESS INTELLIGENCE
Abstract
Microsoft
®
Dynamics GP Business Intelligence is designed to provide businesses with the tools they need
to help analyze data in order to make faster more informed decisions. This white paper is intended to
provide the reader an overview of the solutions available to address this business need.
Introduction
The ability to extract and present information in a meaningful way is vital for a business management
application like Microsoft Dynamics GP. Business Intelligence enables companies to make better decisions
faster than ever before by providing the right information to the right people at the right time. Employees
increasingly find that they suffer from information overload and need solutions that provide the analysis to
effectively make decisions. Whether they are working on the strategic, the tactical, or the operational level,
Microsoft Dynamics GP provides business intelligence applications to make informed decisions a more
natural part of all employees everyday work experience. Several reporting and analytics solutions are
available for Microsoft Dynamics GP. Some are delivered as part of the core Microsoft Dynamics GP
application, while others are additional products that extend the Microsoft Dynamics GP solution. Key
goals of these solutions are to provide:
• An integrated platform and applications
• A secure and personalized user experience
• A collaborative environment
• A total solution that is cost effective and comprehensive
In order to understand which reporting tool is most appropriate for the task, we have broken the Microsoft
Dynamics GP business intelligence solutions into four categories:
Reporting tools – Allow you to create the operational and financial reports you need to run your
business
Analysis tools – Allow you to analyze, interpret, and create reports based on information tracked
within Microsoft Dynamics GP
Ad-hoc query – Allows you to have interactive information access, including drill down and drill-
around capabilities
Quick insight –Preconfigured tools that allow your executives and operational managers can use to
quickly and proactively view and react on information within Microsoft Dynamics GP
4
MICROSOFT DYNAMICS GP – BUSINESS INTELLIGENCE
Reporting Tools
Report Writer
Core reporting capabilities for Microsoft Dynamics GP are provided by the Report Writer, the reporting
tool that is part of System Manager and is available with every Microsoft Dynamics GP installation.
The Report Writer creates two types of reports: graphical and text. Graphical reports can use a variety of
fonts, shapes, and pictures. They are typically used for reports that should be visually appealing, such as
invoices. Text reports contain only text, and are designed to print quickly. They do this by extensively using
the built-in rendering capabilities of the default printer. Text reports are typically used for reports that can
be lengthy, such as a trial balance.
In addition to creating new reports, end users can use Report Writer to customize most of the more than
2000 standard reports included with Microsoft Dynamics GP. The graphical layout window that’s used to
design reports also can be used to customize their appearance.
The following illustration is an example of the Report Layout window in Report Writer.
Reports Library
Before customizing a report on your own, take a look at the reports available for download from the
Reports Library available to Microsoft Dynamics GP customers. The Reports Library contains modified
versions of reports in addition to samples of the reports included with Microsoft Dynamics GP. Save
valuable time if the modification you require has already been performed by simply downloading the
report.. Reports are categorized by series, version, and report name. Currently there are more than 200
modified versions of reports available for download.
FRx®
The FRx family of reporting and analysis tools is the standard financial reporting tools for Microsoft
Dynamics GP. FRx Reporter makes it easy to manage and distribute critical financial reports that your
business depends on to run effectively. As a part of Microsoft Business Solutions, these tools fully integrate
5
MICROSOFT DYNAMICS GP – BUSINESS INTELLIGENCE
with Microsoft Dynamics GP. Such reports typically built using FRX include Balance Sheet, Income
Statement, Statement of Cash Flows, budget versus actual, or any other financial report or statement.
The key to FRx’s flexibility is a unique and dependable system of three basic building blocks: Row Format,
Columns Layout, and Reporting Trees. Once defined, you can save these building blocks to use again and
again to create business-essential reports. Custom fonts, formatting, and formulas stay with the report—
maintaining the professional, boardroom-quality appearance of your reports.
Row Format Starting here, you can select individual accounts, a range of accounts, or a list of
noncontiguous accounts to be included in a report. You can also define totals, calculations, and if/then
parameters. And by using Wild Cards, you can automatically recognize and include new GL account
numbers, keeping your reports up-to-date. You can even create, name, and reuse account sets across
multiple reports to simplify the report creation and maintenance process.
Column Layout The Column Layout makes it easy for you to include period information like actual,
budget, or other types of data in a report— either from your general ledger or other data source such as
spreadsheets. You can apply calculations across columns, making it easy to identify variances, projections,
or percentages.
Reporting Trees You can create a hierarchical picture of your organization to understand or change your
organizational and reporting structures. A powerful auto-build function constructs Reporting Trees in
seconds—directly from your chart of accounts. Easy drag-and-drop functionality enables you to create
alternative structures and multiple rollups of various accounts without having to make modifications to
your general ledger or chart of accounts. And by using effective dating, you can apply several different
Reporting Trees for different periods of time— all within a single report. Additionally the Drill Down Viewer
allows you to drill down into the detail of the summary numbers calculated by using the reporting tree.
You can drill down into unique account balances that make up the summary totals or you can drill all the
way down into the detailed transactions providing all the necessary data to your report consumers. Finally,
security settings allow you to limit viewing rights at the unit level of your organization, helping ensure that
report consumers can view only the information they are responsible for.
The following illustration is an example of the FRx DrillDown Viewer.
6
MICROSOFT DYNAMICS GP – BUSINESS INTELLIGENCE
Enterprise Reporting
Enterprise Reporting is a product designed for consolidation, financial reporting, budgeting, and analysis
across the enterprise. It has support for multiple currencies, consolidations, and eliminations. The main
components of the product are the rich client, a Web extension that allows you to access reports over the
Web and Windows Services that you can use to generate reports and import data on a scheduled basis.
The product also has support for OLAP for data analysis and an add-in for Excel.
The following illustrations show how you can use the Enterprise Reporting Drill Down Explorer.
Original Report
Drill Down on the Total Group
7
MICROSOFT DYNAMICS GP – BUSINESS INTELLIGENCE
Drill Down on North America
Crystal Reports
Crystal Reports is a stand-alone report writer that is often used to produce reports for applications like
Microsoft Dynamics GP. When used with Microsoft Dynamics GP, Crystal Reports accesses data directly
from the database through ODBC.
The following illustration is an example of the Crystal Reports Layout window.
Microsoft SQL Server™ Reporting Services
SQL Server Reporting Services extends the Microsoft business intelligence platform by making it easy to
get the right information to the right people, in virtually any business environment.
Based on .NET and XML Web services, SQL Server Reporting Services delivers a robust environment for
creating, managing, and delivering traditional, paper-oriented reports, as well as interactive, Web-based
reports. The product’s modular design and extensive Application Programming Interfaces (APIs) allow
8
MICROSOFT DYNAMICS GP – BUSINESS INTELLIGENCE
software developers and enterprises alike to integrate reporting with legacy systems or other integrating
applications.
Reporting Services includes:
• A complete set of tools for creating, managing, and viewing reports
• An engine for hosting and processing reports
• An extensible architecture and open interfaces for embedding reports or integrating the solution in
diverse IT environments.
As an integrated part of the Microsoft business intelligence platform, Reporting Services helps companies
control costs while improving decision making at all organizational levels.
Reporting Services Technical Overview
Microsoft SQL Server Reporting Services is designed with a modular, distributed architecture to ensure
both scalability and flexibility. Processing is distributed across multiple components that can be extended,
as well as integrated into custom solutions.
Architecture Overview
The following diagram illustrates the primary components of Reporting Services.
Server Components
At the core of Reporting Services is a component-based server platform for obtaining, processing, and
rendering data.
9
MICROSOFT DYNAMICS GP – BUSINESS INTELLIGENCE
Server Component Features
Report Server A Web service with multiple components that:
Handles SOAP (Simple Object Access Protocol) and URL requests
Processes reports including executing queries, evaluating expressions, and
generating output formats
Provides report snapshot and cache management
Integrates with SQL Server Agent for scheduled operations
Supports and enforces security policies and authorization
Report Server Database A SQL Server database that stores the information used by the report
server, including report definitions, report metadata, cached reports,
snapshots, and resources. It also stores security settings, account
information, and scheduling and delivery data.
Report Manager A Web-based report access and management tool distributed with
Reporting Services.
Multiple report server instances may be deployed in a Web farm configuration for maximum scalability and
reliability. Also, the report server database may run in a failover cluster.
SQL Reporting Service sample report packs
To make it easy to get started with SQL Reporting Services with Microsoft Dynamics GP, we’ve created the
Microsoft SQL Server Report Pack for Microsoft Business Solutions–Microsoft Dynamics GP, which is a set of
predefined report definition files that work with the Microsoft Dynamics GP sample database. You can
upload and view each report separately and you can use the sample reports as templates for designing
new reports.
With the reports in this pack, you can take the report definition files and, with only some minor
configuration changes, have them work against your existing Microsoft Dynamics GP environment. If you
choose to use these reports against your existing Microsoft Dynamics GP application, be aware that they
bypass the Microsoft Dynamics GP application security layer and will require the user to have SQL access
privileges to all views and tables in the Microsoft SQL Server database that contains the Microsoft
Dynamics GP data. This sample database is based on the sample database that is included with Microsoft
Dynamics GP, with some minor modifications.
The sample reports that are provided in this report pack include:
1. Commissions Report shows sales commissions by territory, salesperson, and year. You can expand a
territory to view sales commissions by a salesperson. You also can expand a year to view quarterly
commissions by territory. Also, there is a sales summary graph that displays annual sales by territory.
2. Customer Profitability Report shows sales, total profit, and gross margin percentage per customer for
the year that you selected. You can expand customer ID to view the sales, total profit, and gross
margin percentage by document number.
3. Inventory Value by Site Report shows a bar graph of the value of inventory by site and the total
inventory value for the company. You can then drill through by clicking an inventory site bar to display
the Inventory Value by Site and Item Number Report to view items and their total value for that site.
4. Item Quantity Back Order Report shows the quantity of items that are back ordered, quantities that are
allocated to purchase orders, and item quantities available for all sites. You can expand each item
10
MICROSOFT DYNAMICS GP – BUSINESS INTELLIGENCE
number to view item quantities for all sites. You can also drill through by clicking the “Qty on PO” link
to display the Quantity on Purchase Order Report to view information about the items allocated to
each purchase order.
5. Open Purchase Orders Report shows purchase orders that have not been closed or canceled per
vendor or for all vendors. The number, status, and date of each purchase order are displayed in a list
view, along with the vendor assigned to the purchase order. You can expand each purchase order to
display the line items and the status of each line item. You can also drill through by clicking on the
“Qty Shipped” link to display the Purchase Order Receipts Report to view the quantity remaining to be
shipped.
6. Open Sales Orders Report shows a list of un-posted sales orders for a range of customers. The sales
order number, the date of each sales order, customer information, the requested ship date, and the
amount remaining on the order is displayed in a list view, along with the salesperson assigned to the
order. You can expand each purchase order to display the line items and specific information about
each line item.
7. Employee Capacity Report shows the available work centers and the employee capacity of each work
center by weeks. You can drill through by clicking on the weekly graph for each work center to display
the Manufacturing Orders for Work Center Report, which displays the manufacturing orders assigned
to the work center. Customer names, customer priority, and manufacturing priority also are displayed.
8. Machine Capacity Report shows the available work centers and the machine capacity of each work
center by weeks. You can drill through by clicking on the weekly graph for each work center to display
the Manufacturing Orders for Work Center Report to view the manufacturing orders assigned to the
work center. Customer names, customer priority, and manufacturing priority also are displayed.
Analysis Tools
Analytical Accounting
Analytical Accounting is a tool that helps you to analyze, interpret, and create reports based on
information tracked outside of your company’s chart of accounts. You also can store information that
cannot be computed in monetary terms, such as labor hours. You can enter detailed analysis information
without resorting to segmental accounting.
With Analytical Accounting, you can:
• Enter analysis information for a group of analysis dimensions
• Set up unlimited analysis dimensions
• Perform comprehensive reporting.
Transaction Dimensions
You can classify, report, and analyze financial transactions based on your specific business needs with an
unlimited number of user-defined transaction dimensions, including Cost Center, Profit Center, Region,
and Hours.
11
MICROSOFT DYNAMICS GP – BUSINESS INTELLIGENCE
You will use the Transaction Dimension Maintenance window, as shown below, to create transaction
dimensions.
Transaction Dimension Codes
You can attach user-defined transaction dimension codes to ledger transactions, eliminating the need to
analyze transactions on the basis of account segments.
12
MICROSOFT DYNAMICS GP – BUSINESS INTELLIGENCE
Valid Code Combinations
Define valid transaction dimension code combinations to prevent combinations that aren’t valid from
being entered for a transaction. For instance, a product group and region always must appear together, or
a project and cost center never can appear together.
Multilevel Reporting
Enable multilevel reporting by grouping any number of transaction dimension codes into an unlimited
number of user-defined levels. Additionally, you can include customers, vendors, items, and sites to
Analytical Accounting reports to further analyze your data.
Wizard-Driven Inquiries Generate multilevel reports more easily with wizard-driven inquiries that
integrate with Microsoft Office Excel and Microsoft Dynamics GP SmartLists.
Reports in Microsoft Office Excel Multilevel reports are run and displayed in Excel for easy manipulation,
integration of non-accounting business data, and enhanced presentation.
Implode/Explode Decide how much detail you need to view throughout your entire report or section by
section with an implode/explode function. If you wish you see additional detail use the explode function.
Integration Analyze transactions across your entire organization with seamless integration across the
following Microsoft Dynamics GP modules: General Ledger, Receivables Management, Payables
Management, Inventory Control, Sales Order Processing, Purchase Order Processing, and Bank
Reconciliation.
Ad Hoc Query
Inquiries and Audit Trail Records
Inquiry windows within Microsoft Dynamics GP allow you to review information for several types of
records. You can select the record or records that you want to view and specify the order that the records
will be displayed in. For example, in the General Ledger Detail Inquiry window, you can select a general
ledger account and a year to view information about the account. You can then click the link on the
13
MICROSOFT DYNAMICS GP – BUSINESS INTELLIGENCE
Account field to view the transaction distributions for the account that have been linked with other
distributions.
Microsoft Dynamics GP also provides an audit trail record so that you can trace a transaction from any
point within the system back to the location where it was originally entered. After you've entered a
transaction, you can trace it from its originating point in Microsoft Dynamics GP to the actual source
document - such as in invoice, check, or receipt - that provided the basis for the transaction, along with a
precise record of where the transaction was posted as it made its way through the Microsoft Dynamics GP
system.
SmartList
SmartList in Microsoft Dynamics GP is another reporting tool that you can use to organize and present
data from the accounting system. You can display lists of records from a specific category, such as
Customers or Purchase Orders. You can apply search criteria to limit the results to only the records of
interest. You can determine which columns should be displayed, as well as the order of the columns.
After you’ve set up a SmartList search, you can add it as a SmartList favorite, so that it can easily be rerun.
The search results can be exported to Excel or Word. These features make SmartList an excellent tool for
creating ad hoc reports.
Integrating Microsoft Dynamics GP data from SmartList with Excel
SmartList is a Microsoft Dynamics GP querying tool that allows you to instantly create sophisticated queries
to find detailed customer, vendor, general ledger, inventory, and employee information. A number of pre-
defined queries are included with Microsoft Dynamics GP.
Exporting to Excel is as easy as opening a SmartList favorite and clicking a single button to export to Excel
or Word. The data is automatically formatted appropriately so no “cleaning up” is necessary. Distributing
data using the world’s most widely used office productivity suite ensures that employees who need access
to back office data can obtain it easily and reliably and analyze it in an environment that they are already
used to. You can include spreadsheets or Word documents as attachments to e-mail messages or embed
the data within the message.
14
MICROSOFT DYNAMICS GP – BUSINESS INTELLIGENCE
Export Solutions
You can use the Export Solutions window, as shown in the following illustration, to export search results to
a template and run Excel or Word macros before or after exporting. For example, perhaps you would like
to chart your customers that are past due. Create a macro in Excel to create the chart and then attach the
macro to the Customer SmartList that contains the applicable data. When you select to export the data to
Excel, it will be presented in chart format.
Business Portal Query Pages
Query pages, found in each of the Business Portal centers, provide access to information stored in your
back office system. For example, the Finance Center includes the Financial Queries page and the Sales
Center includes the Sales Queries page. Business Portal Queries can also be modified to include
information specific to your organizational needs. Query results can also be exported to Excel for further
analysis.
The following illustration is an example of a Business Portal Query Page.
15
MICROSOFT DYNAMICS GP – BUSINESS INTELLIGENCE
Quick Insight
Business Alerts
Business Alerts uses Microsoft SQL Server to monitor your business’s data, delivers fast access to business-
critical information, which enables your organization to make quicker, more informed decisions to improve
your business success.
For example, suppose that you want to be notified when a customer’s credit limit has been exceeded. In
that case, you can use the Business Alert wizard, as shown in the following illustrations, to create a query
that compares the credit limit amounts to the current amounts for your customers.
You can use the wizard to set up the schedule for the alert—that is, when, how often, and for how long
you want Microsoft Dynamics GP to check whether the conditions you’ve defined exist.
16
MICROSOFT DYNAMICS GP – BUSINESS INTELLIGENCE
After you have created and activated a business alert, the conditions will be checked according to the
schedule that you set up. Whenever the alert condition is found, an e-mail message is sent to the people or
groups that you specified.
Business Portal Executive Center
Business Portal gives employees access to the specific information and tasks they need to do their jobs,
based on their roles within your company. Roles are user categories that group users with similar job
functions.
Center pages serve as navigation to groups of role-related items. Use each center page as a starting point
to access role-based information and tasks.
For example, if you’re an Executive, such as CFO or CEO, use the Executive Center to obtain key, high-level
information that is used to make strategic business decisions. Execs can drill down into the details of the
key business metrics when needed.
17
MICROSOFT DYNAMICS GP – BUSINESS INTELLIGENCE
Business Portal Information Pages: Information pages combine a number of parts—mainly result viewer
Web Parts—to present current data from the back office.
For example, the Customers information page displays current customer information.
Key Performance Indicators
Suppose that a company is trying to fine-tune its operations, but needs faster access to business
measurements in a proactive instead of reactive manner. Those measurements enable employees to make
decisions more quickly and base them on more accurate information.
Key performance indicators (KPIs) are standard business metrics that provide your company’s managers
with an easy-to-read assessment of the financial health of the organization. KPIs are available through
Microsoft Business Solutions Business Portal.
KPIs also can indicate when specified values reach a certain point if you’ve defined warning and critical
alert ranges. An alert range is a range of variation—expressed as a percentage—above or below a target
value.
You also can display KPI information as a bar chart, showing a single KPI, and graphically illustrating
company performance for specific time periods.
Office Web Parts in Business Portal pages
Business Portal is a powerful tool for presenting data to users in a variety of business roles. Role-based
access helps provide security to sensitive data because users only see the information that their role is
allowed to see. While it’s easy to see a broad view of your data, individual users also can display more
targeted information that provides the at-a-glance view of the business measurements that are most
important to them for making timely, accurate decisions.
The following table describes how you can access information from within Business Portal and then select
how to display the information.
18
MICROSOFT DYNAMICS GP – BUSINESS INTELLIGENCE
Access from Description
Within Business Portal, by opening the
Virtual Server Gallery of SharePoint
®
Web parts.
You can select from multiple galleries, each that contains a number of Web
parts, such as an Excel pivot chart, that you can add to your Business
Portal pages.
You can select from multiple galleries, each that contains a number of Web parts, such as an Excel pivot
chart, that you can add to your Business Portal pages.
You can add an Excel pivot chart, in the example shown in the following illustration, by dragging and
dropping it from the Virtual Server Gallery to a Business Portal page.
After the Web Part is on the Business Portal page, you can connect it to the data you need, such as
commission amounts as shown in the example in the following illustration. You also can select chart styles
so the data appears in the most effective format.
Excel data source
Suppose that you’d like to explore various scenarios, based on recent sales trends, by considering price
increases, price decreases, and promotions of different durations using your Microsoft Dynamics GP data as
a starting point, but you want to do the analysis with a familiar tool. Excel is a great tool for this task, and
the Excel data source is ready to use without additional setup.
19
MICROSOFT DYNAMICS GP – BUSINESS INTELLIGENCE
The Data Connection wizard in Excel makes it easy to access data stored in an external source, such as
Microsoft SQL Server. In Excel, a data source link to the Microsoft Dynamics GP database is already
included, saving you the effort and time needed to complete this setup.
A wizard interface makes it easy to specify the Business Portal query that will be run in Excel. You can use
query pages and result viewer Web Parts in Business Portal to create customized queries that provide
access to information stored in Microsoft Dynamics GP, including information about customers, vendors,
transactions, and items. For commonly used queries you can save them and rerun them rather than
creating them from scratch each time.
When you’re looking at the data in Excel, you can experiment with different scenarios and apply macros,
filters, and formatting to serve a wide range of purposes. Experienced Excel users should find this a time-
saving time tool for analyzing and presenting Microsoft Dynamics GP data.
20
MICROSOFT DYNAMICS GP – BUSINESS INTELLIGENCE
Instant access to data using SmartTags
In a competitive business environment, employees need to spend their time and energy closing sales and
delivering products and services to customers, rather than searching for information. Using the Microsoft
Office SmartTag Manager, you can have instant access to detailed information in the Microsoft Dynamics
GP database from within Office documents that refer to specific item, customer, vendor, and employee
records stored within Microsoft Dynamics GP. So, for example, if you’re about to send a letter out to a
customer, you can instantly drill back from that letter to see their latest order in real time, then thank them
for that order in the letter.
Pausing over a name or ID, such as the customer name in the following illustration of a customer letter in
Word, displays a SmartTag icon that you can use to access additional information from the Microsoft
Dynamics GP database.
21
MICROSOFT DYNAMICS GP – BUSINESS INTELLIGENCE
You can display a list of actions by clicking the SmartTag icon, as shown in the following illustration.
When you select one of the items in the menu, a window will open that displays data from the Microsoft
Dynamics GP database, which, in the following example, includes current sales and payment summaries for
each accounting period, the current and previous year, and the life-to-date amounts.
Analysis Cubes for Excel
Analysis cubes for Excel allows you to take full advantage of Microsoft SQL Server Analysis Services by
providing out-of-the-box preconfigured OLAP cubes. Analysis Cubes provide virtually unlimited ways to
look at your data with powerful interactive analysis.
Customers with large volumes of data can greatly improve reporting performance by utilizing Analysis
Cubes. Analysis Cubes for Excel is also extensible and data from other systems can be included for analysis.
22
MICROSOFT DYNAMICS GP – BUSINESS INTELLIGENCE
Analysis Services Cubes provide data that can be utilized in a number of different ways (examples to
follow):
• Excel Pivot tables can use a cube as a data source
• The Excel Add-in for Analysis Services can be used to build interactive dashboards as well as tailored
reports.
• Reporting Services can utilize Analysis Cubes as a data source
• Analysis Cubes can provide information for Balanced Scorecards
Leveraging the Excel Add-in for Analysis Services you can build powerful interactive dashboards as seen
below.
Using this functionality a cell within Excel can be set to a value in your accounting system. For example cell
C7 in the dashboard above is equal to sales in January and one or more restrictions like the Company or
Salesperson drop downs in the top left corner of the dashboard above can be used to recalculate that cell.
For example only sales for a specific territory or only sales of a particular item class.
Analysis Cubes also can be used as a data source for pivot tables. Pivot tables can be created using the
Pivot Table Creator window in Microsoft Dynamics GP or they can be created directly in Excel. The
following Pivot Table was configured to automatically open by clicking on the Sales by Item Class graph on
the dashboard above.
23
MICROSOFT DYNAMICS GP – BUSINESS INTELLIGENCE
Analysis Cubes can also be accessed by Reporting Services to provided web based reporting and analysis,
printed reports, or scheduled email distribution of information.
Below is an example of an Accounts Receivable Aging report that could be accessed online or emailed to a
salesperson providing aging information for their customers. In this example by clicking on any of the
customers you can drill down to the detailed transactions that make up their outstanding balance.
Business Scorecard Manager
Business Scorecard Manager 2005 is a comprehensive web-based scorecard and dashboard application
that provides deep contextual insight into business drivers. Business Scorecard Manager helps put
information in context and link corporate strategy to line-of-business (LOB) actions, reporting on
organizational performance in both financial and non-financial terms. Scorecards are designed to
articulate strategy and convert it into specific, measurable goals and to monitor progress towards their
achievement.
Taking a deeper dive into the core value of the interoperability with Microsoft Dynamics GP™ you will see
a highly complementary offering for these customers - in particular when looking at the existing modules
24
MICROSOFT DYNAMICS GP – BUSINESS INTELLIGENCE
available for Microsoft Dynamics GP. First we have the Microsoft Dynamics GP Professional - Business
Intelligence Foundation, which is comprised of components to deepen business insight. One of the
Business Intelligence components is the Analysis Cubes for Excel, which is the piece that truly enables the
out-of-the-box interoperability between Microsoft Dynamics GP and the Business Scorecard Manager.
Thus the Analysis Cubes are to be seen as the Business Scorecard Manager enabler, helping customers
enjoy the benefits of the Scorecard application with a limited level of investment.
Secondly, we have the Microsoft Business Portal, the Microsoft Dynamics GP portal solution, to which the
Business Scorecard Manager should be considered a highly complementary offering.
The two solutions are both portal based business intelligence offerings helping to provide anytime,
anywhere access to business data, and they each address very different business needs. Customers can use
one, the other or both in orchestration, depending upon the business needs that are to be addressed.
Diving one lever deeper into the functionality layer of the Microsoft Business Portal, you will see that this
application also offers KPI functionality. However, keeping in mind that the applications were designed
with two different purposes, quite naturally the KPI’s are designed to address different needs. The KPI’s
offered with the Microsoft Business Portal are designed for measuring data hosted in Microsoft Dynamics
GP. Therefore, if a customer wants to focus on monitoring the data coming from the Enterprise Resource
Planning system only, the Business Portal KPIs are the best choice. The KPI functionality offered with the
Business Scorecard Manager, however, are designed with a broader scope in mind, allowing for creation of
KPI’s based on data consolidated from a variety of data sources. Thus, the KPI functionality available with
the Business Scorecard Manager should be more accommodating when aiming at a more holistic solution.
Here the KPI’s can encounter multiple data types, either when data comes from multiple systems or from
multiple Microsoft Dynamics GP companies.
The deciding factor should be based on what data the customers want to start with and what they want to
achieve. It is important to note that very often you will find situations where the two solutions will be
working in orchestration. Here the Microsoft Dynamics GP solution’s interoperability with Business
Scorecard Manager truly opens the possibilities for a best-of-breed portal experience encompassing a
broader variety of needs compared to the needs covered by the two solutions individually. For example,
the possibility of combining and surfacing the multi-source Business Scorecard Manager KPIs with the
Microsoft Dynamics GP specific KPIs within the Microsoft Business Portal truly help increase the portal user
penetration. The reason being that you are now delivering KPIs and business insight to an even boarder
audience throughout the organization, without jeopardizing the familiar portal look and feel for the
traditional Microsoft Dynamics GP users.
25
MICROSOFT DYNAMICS GP – BUSINESS INTELLIGENCE
Web Services
The Web Service engine includes 160 Web Service access points supporting 20 key business processes. The
adoption of industry-standard Web Services XML Application Programming Interfaces (APIs) allows your
organization to readily customize and adapt Microsoft Dynamics GP to provide deeper integration with
disparate internal and external systems.
26
MICROSOFT DYNAMICS GP – BUSINESS INTELLIGENCE
Microsoft Dynamics is a line of integrated, adaptable business management
solutions that enables you and your people to make business decisions with
greater confidence. Microsoft Dynamics works like and with familiar Microsoft
software, automating and streamlining financial, customer relationship and
supply chain processes in a way that helps you drive business success.
U.S. and Canada Toll Free 1-888-477-7989
Worldwide +1-701-281-6500
www.microsoft.com/dynamics
The information contained in this document represents the current view of Microsoft Corporation on
the issues discussed as of the date of publication. Because Microsoft must respond to changing market
conditions, this document should not be interpreted to be a commitment on the part of Microsoft, and
Microsoft cannot guarantee the accuracy of any information presented after the date of publication.
This White Paper is for informational purposes only. MICROSOFT MAKES NO WARRANTIES, EXPRESS,
IMPLIED, OR STATUTORY, AS TO THE INFORMATION IN THIS DOCUMENT.
Complying with all applicable copyright laws is the responsibility of the user. Without limiting the rights
under copyright, no part of this document may be reproduced, stored in or introduced into a retrieval
system, or transmitted in any form or by any means (electronic, mechanical, photocopying, recording,
or otherwise), or for any purpose, without the express written permission of Microsoft Corporation.
Microsoft may have patents, patent applications, trademarks, copyrights, or other intellectual property
rights covering subject matter in this document. Except as expressly provided in any written license
agreement from Microsoft, the furnishing of this document does not give you any license to these
patents, trademarks, copyrights, or other intellectual property.
© 2006 Microsoft Corporation. All rights reserved.
27
MICROSOFT DYNAMICS GP – BUSINESS INTELLIGENCE
doc_987120861.pdf
Microsoft Dynamics GP Business Intelligence
Business Intelligence
Microsoft Dynamics GP
Business Intelligence
WHITE PAPER
Date: February 2006
Table of Contents
Introduction....................................................................................................................... 4
Reporting Tools ................................................................................................................ 5
Report Writer ................................................................................................................... 5
Reports Library................................................................................................................. 5
FRx
®
.................................................................................................................................. 5
Enterprise Reporting........................................................................................................ 7
Original Report ................................................................................................................ 7
Drill Down on the Total Group ....................................................................................... 7
Drill Down on North America ......................................................................................... 8
Crystal Reports ................................................................................................................. 8
Microsoft SQL Server™ Reporting Services .................................................................... 8
Reporting Services Technical Overview.......................................................................... 9
Architecture Overview..................................................................................................... 9
Server Components ......................................................................................................... 9
SQL Reporting Service sample report packs ................................................................10
Analysis Tools .................................................................................................................11
Analytical Accounting....................................................................................................11
Transaction Dimensions ................................................................................................11
Transaction Dimension Codes.......................................................................................12
Valid Code Combinations..............................................................................................13
Multilevel Reporting......................................................................................................13
Ad Hoc Query .................................................................................................................13
Inquiries and Audit Trail Records..................................................................................13
SmartList.........................................................................................................................14
Integrating Microsoft Dynamics GP data from SmartList with Excel .........................14
Export Solutions.............................................................................................................15
Business Portal Query Pages .........................................................................................15
2
MICROSOFT DYNAMICS GP – BUSINESS INTELLIGENCE
Quick Insight ...................................................................................................................16
Business Alerts ...............................................................................................................16
Business Portal Executive Center ..................................................................................17
Key Performance Indicators ..........................................................................................18
Office Web Parts in Business Portal pages ...................................................................18
Excel data source ...........................................................................................................19
Instant access to data using SmartTags........................................................................21
Analysis Cubes for Excel ................................................................................................22
Business Scorecard Manager.........................................................................................24
Web Services ..................................................................................................................26
3
MICROSOFT DYNAMICS GP – BUSINESS INTELLIGENCE
Abstract
Microsoft
®
Dynamics GP Business Intelligence is designed to provide businesses with the tools they need
to help analyze data in order to make faster more informed decisions. This white paper is intended to
provide the reader an overview of the solutions available to address this business need.
Introduction
The ability to extract and present information in a meaningful way is vital for a business management
application like Microsoft Dynamics GP. Business Intelligence enables companies to make better decisions
faster than ever before by providing the right information to the right people at the right time. Employees
increasingly find that they suffer from information overload and need solutions that provide the analysis to
effectively make decisions. Whether they are working on the strategic, the tactical, or the operational level,
Microsoft Dynamics GP provides business intelligence applications to make informed decisions a more
natural part of all employees everyday work experience. Several reporting and analytics solutions are
available for Microsoft Dynamics GP. Some are delivered as part of the core Microsoft Dynamics GP
application, while others are additional products that extend the Microsoft Dynamics GP solution. Key
goals of these solutions are to provide:
• An integrated platform and applications
• A secure and personalized user experience
• A collaborative environment
• A total solution that is cost effective and comprehensive
In order to understand which reporting tool is most appropriate for the task, we have broken the Microsoft
Dynamics GP business intelligence solutions into four categories:
Reporting tools – Allow you to create the operational and financial reports you need to run your
business
Analysis tools – Allow you to analyze, interpret, and create reports based on information tracked
within Microsoft Dynamics GP
Ad-hoc query – Allows you to have interactive information access, including drill down and drill-
around capabilities
Quick insight –Preconfigured tools that allow your executives and operational managers can use to
quickly and proactively view and react on information within Microsoft Dynamics GP
4
MICROSOFT DYNAMICS GP – BUSINESS INTELLIGENCE
Reporting Tools
Report Writer
Core reporting capabilities for Microsoft Dynamics GP are provided by the Report Writer, the reporting
tool that is part of System Manager and is available with every Microsoft Dynamics GP installation.
The Report Writer creates two types of reports: graphical and text. Graphical reports can use a variety of
fonts, shapes, and pictures. They are typically used for reports that should be visually appealing, such as
invoices. Text reports contain only text, and are designed to print quickly. They do this by extensively using
the built-in rendering capabilities of the default printer. Text reports are typically used for reports that can
be lengthy, such as a trial balance.
In addition to creating new reports, end users can use Report Writer to customize most of the more than
2000 standard reports included with Microsoft Dynamics GP. The graphical layout window that’s used to
design reports also can be used to customize their appearance.
The following illustration is an example of the Report Layout window in Report Writer.
Reports Library
Before customizing a report on your own, take a look at the reports available for download from the
Reports Library available to Microsoft Dynamics GP customers. The Reports Library contains modified
versions of reports in addition to samples of the reports included with Microsoft Dynamics GP. Save
valuable time if the modification you require has already been performed by simply downloading the
report.. Reports are categorized by series, version, and report name. Currently there are more than 200
modified versions of reports available for download.
FRx®
The FRx family of reporting and analysis tools is the standard financial reporting tools for Microsoft
Dynamics GP. FRx Reporter makes it easy to manage and distribute critical financial reports that your
business depends on to run effectively. As a part of Microsoft Business Solutions, these tools fully integrate
5
MICROSOFT DYNAMICS GP – BUSINESS INTELLIGENCE
with Microsoft Dynamics GP. Such reports typically built using FRX include Balance Sheet, Income
Statement, Statement of Cash Flows, budget versus actual, or any other financial report or statement.
The key to FRx’s flexibility is a unique and dependable system of three basic building blocks: Row Format,
Columns Layout, and Reporting Trees. Once defined, you can save these building blocks to use again and
again to create business-essential reports. Custom fonts, formatting, and formulas stay with the report—
maintaining the professional, boardroom-quality appearance of your reports.
Row Format Starting here, you can select individual accounts, a range of accounts, or a list of
noncontiguous accounts to be included in a report. You can also define totals, calculations, and if/then
parameters. And by using Wild Cards, you can automatically recognize and include new GL account
numbers, keeping your reports up-to-date. You can even create, name, and reuse account sets across
multiple reports to simplify the report creation and maintenance process.
Column Layout The Column Layout makes it easy for you to include period information like actual,
budget, or other types of data in a report— either from your general ledger or other data source such as
spreadsheets. You can apply calculations across columns, making it easy to identify variances, projections,
or percentages.
Reporting Trees You can create a hierarchical picture of your organization to understand or change your
organizational and reporting structures. A powerful auto-build function constructs Reporting Trees in
seconds—directly from your chart of accounts. Easy drag-and-drop functionality enables you to create
alternative structures and multiple rollups of various accounts without having to make modifications to
your general ledger or chart of accounts. And by using effective dating, you can apply several different
Reporting Trees for different periods of time— all within a single report. Additionally the Drill Down Viewer
allows you to drill down into the detail of the summary numbers calculated by using the reporting tree.
You can drill down into unique account balances that make up the summary totals or you can drill all the
way down into the detailed transactions providing all the necessary data to your report consumers. Finally,
security settings allow you to limit viewing rights at the unit level of your organization, helping ensure that
report consumers can view only the information they are responsible for.
The following illustration is an example of the FRx DrillDown Viewer.
6
MICROSOFT DYNAMICS GP – BUSINESS INTELLIGENCE
Enterprise Reporting
Enterprise Reporting is a product designed for consolidation, financial reporting, budgeting, and analysis
across the enterprise. It has support for multiple currencies, consolidations, and eliminations. The main
components of the product are the rich client, a Web extension that allows you to access reports over the
Web and Windows Services that you can use to generate reports and import data on a scheduled basis.
The product also has support for OLAP for data analysis and an add-in for Excel.
The following illustrations show how you can use the Enterprise Reporting Drill Down Explorer.
Original Report
Drill Down on the Total Group
7
MICROSOFT DYNAMICS GP – BUSINESS INTELLIGENCE
Drill Down on North America
Crystal Reports
Crystal Reports is a stand-alone report writer that is often used to produce reports for applications like
Microsoft Dynamics GP. When used with Microsoft Dynamics GP, Crystal Reports accesses data directly
from the database through ODBC.
The following illustration is an example of the Crystal Reports Layout window.
Microsoft SQL Server™ Reporting Services
SQL Server Reporting Services extends the Microsoft business intelligence platform by making it easy to
get the right information to the right people, in virtually any business environment.
Based on .NET and XML Web services, SQL Server Reporting Services delivers a robust environment for
creating, managing, and delivering traditional, paper-oriented reports, as well as interactive, Web-based
reports. The product’s modular design and extensive Application Programming Interfaces (APIs) allow
8
MICROSOFT DYNAMICS GP – BUSINESS INTELLIGENCE
software developers and enterprises alike to integrate reporting with legacy systems or other integrating
applications.
Reporting Services includes:
• A complete set of tools for creating, managing, and viewing reports
• An engine for hosting and processing reports
• An extensible architecture and open interfaces for embedding reports or integrating the solution in
diverse IT environments.
As an integrated part of the Microsoft business intelligence platform, Reporting Services helps companies
control costs while improving decision making at all organizational levels.
Reporting Services Technical Overview
Microsoft SQL Server Reporting Services is designed with a modular, distributed architecture to ensure
both scalability and flexibility. Processing is distributed across multiple components that can be extended,
as well as integrated into custom solutions.
Architecture Overview
The following diagram illustrates the primary components of Reporting Services.
Server Components
At the core of Reporting Services is a component-based server platform for obtaining, processing, and
rendering data.
9
MICROSOFT DYNAMICS GP – BUSINESS INTELLIGENCE
Server Component Features
Report Server A Web service with multiple components that:
Handles SOAP (Simple Object Access Protocol) and URL requests
Processes reports including executing queries, evaluating expressions, and
generating output formats
Provides report snapshot and cache management
Integrates with SQL Server Agent for scheduled operations
Supports and enforces security policies and authorization
Report Server Database A SQL Server database that stores the information used by the report
server, including report definitions, report metadata, cached reports,
snapshots, and resources. It also stores security settings, account
information, and scheduling and delivery data.
Report Manager A Web-based report access and management tool distributed with
Reporting Services.
Multiple report server instances may be deployed in a Web farm configuration for maximum scalability and
reliability. Also, the report server database may run in a failover cluster.
SQL Reporting Service sample report packs
To make it easy to get started with SQL Reporting Services with Microsoft Dynamics GP, we’ve created the
Microsoft SQL Server Report Pack for Microsoft Business Solutions–Microsoft Dynamics GP, which is a set of
predefined report definition files that work with the Microsoft Dynamics GP sample database. You can
upload and view each report separately and you can use the sample reports as templates for designing
new reports.
With the reports in this pack, you can take the report definition files and, with only some minor
configuration changes, have them work against your existing Microsoft Dynamics GP environment. If you
choose to use these reports against your existing Microsoft Dynamics GP application, be aware that they
bypass the Microsoft Dynamics GP application security layer and will require the user to have SQL access
privileges to all views and tables in the Microsoft SQL Server database that contains the Microsoft
Dynamics GP data. This sample database is based on the sample database that is included with Microsoft
Dynamics GP, with some minor modifications.
The sample reports that are provided in this report pack include:
1. Commissions Report shows sales commissions by territory, salesperson, and year. You can expand a
territory to view sales commissions by a salesperson. You also can expand a year to view quarterly
commissions by territory. Also, there is a sales summary graph that displays annual sales by territory.
2. Customer Profitability Report shows sales, total profit, and gross margin percentage per customer for
the year that you selected. You can expand customer ID to view the sales, total profit, and gross
margin percentage by document number.
3. Inventory Value by Site Report shows a bar graph of the value of inventory by site and the total
inventory value for the company. You can then drill through by clicking an inventory site bar to display
the Inventory Value by Site and Item Number Report to view items and their total value for that site.
4. Item Quantity Back Order Report shows the quantity of items that are back ordered, quantities that are
allocated to purchase orders, and item quantities available for all sites. You can expand each item
10
MICROSOFT DYNAMICS GP – BUSINESS INTELLIGENCE
number to view item quantities for all sites. You can also drill through by clicking the “Qty on PO” link
to display the Quantity on Purchase Order Report to view information about the items allocated to
each purchase order.
5. Open Purchase Orders Report shows purchase orders that have not been closed or canceled per
vendor or for all vendors. The number, status, and date of each purchase order are displayed in a list
view, along with the vendor assigned to the purchase order. You can expand each purchase order to
display the line items and the status of each line item. You can also drill through by clicking on the
“Qty Shipped” link to display the Purchase Order Receipts Report to view the quantity remaining to be
shipped.
6. Open Sales Orders Report shows a list of un-posted sales orders for a range of customers. The sales
order number, the date of each sales order, customer information, the requested ship date, and the
amount remaining on the order is displayed in a list view, along with the salesperson assigned to the
order. You can expand each purchase order to display the line items and specific information about
each line item.
7. Employee Capacity Report shows the available work centers and the employee capacity of each work
center by weeks. You can drill through by clicking on the weekly graph for each work center to display
the Manufacturing Orders for Work Center Report, which displays the manufacturing orders assigned
to the work center. Customer names, customer priority, and manufacturing priority also are displayed.
8. Machine Capacity Report shows the available work centers and the machine capacity of each work
center by weeks. You can drill through by clicking on the weekly graph for each work center to display
the Manufacturing Orders for Work Center Report to view the manufacturing orders assigned to the
work center. Customer names, customer priority, and manufacturing priority also are displayed.
Analysis Tools
Analytical Accounting
Analytical Accounting is a tool that helps you to analyze, interpret, and create reports based on
information tracked outside of your company’s chart of accounts. You also can store information that
cannot be computed in monetary terms, such as labor hours. You can enter detailed analysis information
without resorting to segmental accounting.
With Analytical Accounting, you can:
• Enter analysis information for a group of analysis dimensions
• Set up unlimited analysis dimensions
• Perform comprehensive reporting.
Transaction Dimensions
You can classify, report, and analyze financial transactions based on your specific business needs with an
unlimited number of user-defined transaction dimensions, including Cost Center, Profit Center, Region,
and Hours.
11
MICROSOFT DYNAMICS GP – BUSINESS INTELLIGENCE
You will use the Transaction Dimension Maintenance window, as shown below, to create transaction
dimensions.
Transaction Dimension Codes
You can attach user-defined transaction dimension codes to ledger transactions, eliminating the need to
analyze transactions on the basis of account segments.
12
MICROSOFT DYNAMICS GP – BUSINESS INTELLIGENCE
Valid Code Combinations
Define valid transaction dimension code combinations to prevent combinations that aren’t valid from
being entered for a transaction. For instance, a product group and region always must appear together, or
a project and cost center never can appear together.
Multilevel Reporting
Enable multilevel reporting by grouping any number of transaction dimension codes into an unlimited
number of user-defined levels. Additionally, you can include customers, vendors, items, and sites to
Analytical Accounting reports to further analyze your data.
Wizard-Driven Inquiries Generate multilevel reports more easily with wizard-driven inquiries that
integrate with Microsoft Office Excel and Microsoft Dynamics GP SmartLists.
Reports in Microsoft Office Excel Multilevel reports are run and displayed in Excel for easy manipulation,
integration of non-accounting business data, and enhanced presentation.
Implode/Explode Decide how much detail you need to view throughout your entire report or section by
section with an implode/explode function. If you wish you see additional detail use the explode function.
Integration Analyze transactions across your entire organization with seamless integration across the
following Microsoft Dynamics GP modules: General Ledger, Receivables Management, Payables
Management, Inventory Control, Sales Order Processing, Purchase Order Processing, and Bank
Reconciliation.
Ad Hoc Query
Inquiries and Audit Trail Records
Inquiry windows within Microsoft Dynamics GP allow you to review information for several types of
records. You can select the record or records that you want to view and specify the order that the records
will be displayed in. For example, in the General Ledger Detail Inquiry window, you can select a general
ledger account and a year to view information about the account. You can then click the link on the
13
MICROSOFT DYNAMICS GP – BUSINESS INTELLIGENCE
Account field to view the transaction distributions for the account that have been linked with other
distributions.
Microsoft Dynamics GP also provides an audit trail record so that you can trace a transaction from any
point within the system back to the location where it was originally entered. After you've entered a
transaction, you can trace it from its originating point in Microsoft Dynamics GP to the actual source
document - such as in invoice, check, or receipt - that provided the basis for the transaction, along with a
precise record of where the transaction was posted as it made its way through the Microsoft Dynamics GP
system.
SmartList
SmartList in Microsoft Dynamics GP is another reporting tool that you can use to organize and present
data from the accounting system. You can display lists of records from a specific category, such as
Customers or Purchase Orders. You can apply search criteria to limit the results to only the records of
interest. You can determine which columns should be displayed, as well as the order of the columns.
After you’ve set up a SmartList search, you can add it as a SmartList favorite, so that it can easily be rerun.
The search results can be exported to Excel or Word. These features make SmartList an excellent tool for
creating ad hoc reports.
Integrating Microsoft Dynamics GP data from SmartList with Excel
SmartList is a Microsoft Dynamics GP querying tool that allows you to instantly create sophisticated queries
to find detailed customer, vendor, general ledger, inventory, and employee information. A number of pre-
defined queries are included with Microsoft Dynamics GP.
Exporting to Excel is as easy as opening a SmartList favorite and clicking a single button to export to Excel
or Word. The data is automatically formatted appropriately so no “cleaning up” is necessary. Distributing
data using the world’s most widely used office productivity suite ensures that employees who need access
to back office data can obtain it easily and reliably and analyze it in an environment that they are already
used to. You can include spreadsheets or Word documents as attachments to e-mail messages or embed
the data within the message.
14
MICROSOFT DYNAMICS GP – BUSINESS INTELLIGENCE
Export Solutions
You can use the Export Solutions window, as shown in the following illustration, to export search results to
a template and run Excel or Word macros before or after exporting. For example, perhaps you would like
to chart your customers that are past due. Create a macro in Excel to create the chart and then attach the
macro to the Customer SmartList that contains the applicable data. When you select to export the data to
Excel, it will be presented in chart format.
Business Portal Query Pages
Query pages, found in each of the Business Portal centers, provide access to information stored in your
back office system. For example, the Finance Center includes the Financial Queries page and the Sales
Center includes the Sales Queries page. Business Portal Queries can also be modified to include
information specific to your organizational needs. Query results can also be exported to Excel for further
analysis.
The following illustration is an example of a Business Portal Query Page.
15
MICROSOFT DYNAMICS GP – BUSINESS INTELLIGENCE
Quick Insight
Business Alerts
Business Alerts uses Microsoft SQL Server to monitor your business’s data, delivers fast access to business-
critical information, which enables your organization to make quicker, more informed decisions to improve
your business success.
For example, suppose that you want to be notified when a customer’s credit limit has been exceeded. In
that case, you can use the Business Alert wizard, as shown in the following illustrations, to create a query
that compares the credit limit amounts to the current amounts for your customers.
You can use the wizard to set up the schedule for the alert—that is, when, how often, and for how long
you want Microsoft Dynamics GP to check whether the conditions you’ve defined exist.
16
MICROSOFT DYNAMICS GP – BUSINESS INTELLIGENCE
After you have created and activated a business alert, the conditions will be checked according to the
schedule that you set up. Whenever the alert condition is found, an e-mail message is sent to the people or
groups that you specified.
Business Portal Executive Center
Business Portal gives employees access to the specific information and tasks they need to do their jobs,
based on their roles within your company. Roles are user categories that group users with similar job
functions.
Center pages serve as navigation to groups of role-related items. Use each center page as a starting point
to access role-based information and tasks.
For example, if you’re an Executive, such as CFO or CEO, use the Executive Center to obtain key, high-level
information that is used to make strategic business decisions. Execs can drill down into the details of the
key business metrics when needed.
17
MICROSOFT DYNAMICS GP – BUSINESS INTELLIGENCE
Business Portal Information Pages: Information pages combine a number of parts—mainly result viewer
Web Parts—to present current data from the back office.
For example, the Customers information page displays current customer information.
Key Performance Indicators
Suppose that a company is trying to fine-tune its operations, but needs faster access to business
measurements in a proactive instead of reactive manner. Those measurements enable employees to make
decisions more quickly and base them on more accurate information.
Key performance indicators (KPIs) are standard business metrics that provide your company’s managers
with an easy-to-read assessment of the financial health of the organization. KPIs are available through
Microsoft Business Solutions Business Portal.
KPIs also can indicate when specified values reach a certain point if you’ve defined warning and critical
alert ranges. An alert range is a range of variation—expressed as a percentage—above or below a target
value.
You also can display KPI information as a bar chart, showing a single KPI, and graphically illustrating
company performance for specific time periods.
Office Web Parts in Business Portal pages
Business Portal is a powerful tool for presenting data to users in a variety of business roles. Role-based
access helps provide security to sensitive data because users only see the information that their role is
allowed to see. While it’s easy to see a broad view of your data, individual users also can display more
targeted information that provides the at-a-glance view of the business measurements that are most
important to them for making timely, accurate decisions.
The following table describes how you can access information from within Business Portal and then select
how to display the information.
18
MICROSOFT DYNAMICS GP – BUSINESS INTELLIGENCE
Access from Description
Within Business Portal, by opening the
Virtual Server Gallery of SharePoint
®
Web parts.
You can select from multiple galleries, each that contains a number of Web
parts, such as an Excel pivot chart, that you can add to your Business
Portal pages.
You can select from multiple galleries, each that contains a number of Web parts, such as an Excel pivot
chart, that you can add to your Business Portal pages.
You can add an Excel pivot chart, in the example shown in the following illustration, by dragging and
dropping it from the Virtual Server Gallery to a Business Portal page.
After the Web Part is on the Business Portal page, you can connect it to the data you need, such as
commission amounts as shown in the example in the following illustration. You also can select chart styles
so the data appears in the most effective format.
Excel data source
Suppose that you’d like to explore various scenarios, based on recent sales trends, by considering price
increases, price decreases, and promotions of different durations using your Microsoft Dynamics GP data as
a starting point, but you want to do the analysis with a familiar tool. Excel is a great tool for this task, and
the Excel data source is ready to use without additional setup.
19
MICROSOFT DYNAMICS GP – BUSINESS INTELLIGENCE
The Data Connection wizard in Excel makes it easy to access data stored in an external source, such as
Microsoft SQL Server. In Excel, a data source link to the Microsoft Dynamics GP database is already
included, saving you the effort and time needed to complete this setup.
A wizard interface makes it easy to specify the Business Portal query that will be run in Excel. You can use
query pages and result viewer Web Parts in Business Portal to create customized queries that provide
access to information stored in Microsoft Dynamics GP, including information about customers, vendors,
transactions, and items. For commonly used queries you can save them and rerun them rather than
creating them from scratch each time.
When you’re looking at the data in Excel, you can experiment with different scenarios and apply macros,
filters, and formatting to serve a wide range of purposes. Experienced Excel users should find this a time-
saving time tool for analyzing and presenting Microsoft Dynamics GP data.
20
MICROSOFT DYNAMICS GP – BUSINESS INTELLIGENCE
Instant access to data using SmartTags
In a competitive business environment, employees need to spend their time and energy closing sales and
delivering products and services to customers, rather than searching for information. Using the Microsoft
Office SmartTag Manager, you can have instant access to detailed information in the Microsoft Dynamics
GP database from within Office documents that refer to specific item, customer, vendor, and employee
records stored within Microsoft Dynamics GP. So, for example, if you’re about to send a letter out to a
customer, you can instantly drill back from that letter to see their latest order in real time, then thank them
for that order in the letter.
Pausing over a name or ID, such as the customer name in the following illustration of a customer letter in
Word, displays a SmartTag icon that you can use to access additional information from the Microsoft
Dynamics GP database.
21
MICROSOFT DYNAMICS GP – BUSINESS INTELLIGENCE
You can display a list of actions by clicking the SmartTag icon, as shown in the following illustration.
When you select one of the items in the menu, a window will open that displays data from the Microsoft
Dynamics GP database, which, in the following example, includes current sales and payment summaries for
each accounting period, the current and previous year, and the life-to-date amounts.
Analysis Cubes for Excel
Analysis cubes for Excel allows you to take full advantage of Microsoft SQL Server Analysis Services by
providing out-of-the-box preconfigured OLAP cubes. Analysis Cubes provide virtually unlimited ways to
look at your data with powerful interactive analysis.
Customers with large volumes of data can greatly improve reporting performance by utilizing Analysis
Cubes. Analysis Cubes for Excel is also extensible and data from other systems can be included for analysis.
22
MICROSOFT DYNAMICS GP – BUSINESS INTELLIGENCE
Analysis Services Cubes provide data that can be utilized in a number of different ways (examples to
follow):
• Excel Pivot tables can use a cube as a data source
• The Excel Add-in for Analysis Services can be used to build interactive dashboards as well as tailored
reports.
• Reporting Services can utilize Analysis Cubes as a data source
• Analysis Cubes can provide information for Balanced Scorecards
Leveraging the Excel Add-in for Analysis Services you can build powerful interactive dashboards as seen
below.
Using this functionality a cell within Excel can be set to a value in your accounting system. For example cell
C7 in the dashboard above is equal to sales in January and one or more restrictions like the Company or
Salesperson drop downs in the top left corner of the dashboard above can be used to recalculate that cell.
For example only sales for a specific territory or only sales of a particular item class.
Analysis Cubes also can be used as a data source for pivot tables. Pivot tables can be created using the
Pivot Table Creator window in Microsoft Dynamics GP or they can be created directly in Excel. The
following Pivot Table was configured to automatically open by clicking on the Sales by Item Class graph on
the dashboard above.
23
MICROSOFT DYNAMICS GP – BUSINESS INTELLIGENCE
Analysis Cubes can also be accessed by Reporting Services to provided web based reporting and analysis,
printed reports, or scheduled email distribution of information.
Below is an example of an Accounts Receivable Aging report that could be accessed online or emailed to a
salesperson providing aging information for their customers. In this example by clicking on any of the
customers you can drill down to the detailed transactions that make up their outstanding balance.
Business Scorecard Manager
Business Scorecard Manager 2005 is a comprehensive web-based scorecard and dashboard application
that provides deep contextual insight into business drivers. Business Scorecard Manager helps put
information in context and link corporate strategy to line-of-business (LOB) actions, reporting on
organizational performance in both financial and non-financial terms. Scorecards are designed to
articulate strategy and convert it into specific, measurable goals and to monitor progress towards their
achievement.
Taking a deeper dive into the core value of the interoperability with Microsoft Dynamics GP™ you will see
a highly complementary offering for these customers - in particular when looking at the existing modules
24
MICROSOFT DYNAMICS GP – BUSINESS INTELLIGENCE
available for Microsoft Dynamics GP. First we have the Microsoft Dynamics GP Professional - Business
Intelligence Foundation, which is comprised of components to deepen business insight. One of the
Business Intelligence components is the Analysis Cubes for Excel, which is the piece that truly enables the
out-of-the-box interoperability between Microsoft Dynamics GP and the Business Scorecard Manager.
Thus the Analysis Cubes are to be seen as the Business Scorecard Manager enabler, helping customers
enjoy the benefits of the Scorecard application with a limited level of investment.
Secondly, we have the Microsoft Business Portal, the Microsoft Dynamics GP portal solution, to which the
Business Scorecard Manager should be considered a highly complementary offering.
The two solutions are both portal based business intelligence offerings helping to provide anytime,
anywhere access to business data, and they each address very different business needs. Customers can use
one, the other or both in orchestration, depending upon the business needs that are to be addressed.
Diving one lever deeper into the functionality layer of the Microsoft Business Portal, you will see that this
application also offers KPI functionality. However, keeping in mind that the applications were designed
with two different purposes, quite naturally the KPI’s are designed to address different needs. The KPI’s
offered with the Microsoft Business Portal are designed for measuring data hosted in Microsoft Dynamics
GP. Therefore, if a customer wants to focus on monitoring the data coming from the Enterprise Resource
Planning system only, the Business Portal KPIs are the best choice. The KPI functionality offered with the
Business Scorecard Manager, however, are designed with a broader scope in mind, allowing for creation of
KPI’s based on data consolidated from a variety of data sources. Thus, the KPI functionality available with
the Business Scorecard Manager should be more accommodating when aiming at a more holistic solution.
Here the KPI’s can encounter multiple data types, either when data comes from multiple systems or from
multiple Microsoft Dynamics GP companies.
The deciding factor should be based on what data the customers want to start with and what they want to
achieve. It is important to note that very often you will find situations where the two solutions will be
working in orchestration. Here the Microsoft Dynamics GP solution’s interoperability with Business
Scorecard Manager truly opens the possibilities for a best-of-breed portal experience encompassing a
broader variety of needs compared to the needs covered by the two solutions individually. For example,
the possibility of combining and surfacing the multi-source Business Scorecard Manager KPIs with the
Microsoft Dynamics GP specific KPIs within the Microsoft Business Portal truly help increase the portal user
penetration. The reason being that you are now delivering KPIs and business insight to an even boarder
audience throughout the organization, without jeopardizing the familiar portal look and feel for the
traditional Microsoft Dynamics GP users.
25
MICROSOFT DYNAMICS GP – BUSINESS INTELLIGENCE
Web Services
The Web Service engine includes 160 Web Service access points supporting 20 key business processes. The
adoption of industry-standard Web Services XML Application Programming Interfaces (APIs) allows your
organization to readily customize and adapt Microsoft Dynamics GP to provide deeper integration with
disparate internal and external systems.
26
MICROSOFT DYNAMICS GP – BUSINESS INTELLIGENCE
Microsoft Dynamics is a line of integrated, adaptable business management
solutions that enables you and your people to make business decisions with
greater confidence. Microsoft Dynamics works like and with familiar Microsoft
software, automating and streamlining financial, customer relationship and
supply chain processes in a way that helps you drive business success.
U.S. and Canada Toll Free 1-888-477-7989
Worldwide +1-701-281-6500
www.microsoft.com/dynamics
The information contained in this document represents the current view of Microsoft Corporation on
the issues discussed as of the date of publication. Because Microsoft must respond to changing market
conditions, this document should not be interpreted to be a commitment on the part of Microsoft, and
Microsoft cannot guarantee the accuracy of any information presented after the date of publication.
This White Paper is for informational purposes only. MICROSOFT MAKES NO WARRANTIES, EXPRESS,
IMPLIED, OR STATUTORY, AS TO THE INFORMATION IN THIS DOCUMENT.
Complying with all applicable copyright laws is the responsibility of the user. Without limiting the rights
under copyright, no part of this document may be reproduced, stored in or introduced into a retrieval
system, or transmitted in any form or by any means (electronic, mechanical, photocopying, recording,
or otherwise), or for any purpose, without the express written permission of Microsoft Corporation.
Microsoft may have patents, patent applications, trademarks, copyrights, or other intellectual property
rights covering subject matter in this document. Except as expressly provided in any written license
agreement from Microsoft, the furnishing of this document does not give you any license to these
patents, trademarks, copyrights, or other intellectual property.
© 2006 Microsoft Corporation. All rights reserved.
27
MICROSOFT DYNAMICS GP – BUSINESS INTELLIGENCE
doc_987120861.pdf