If you receive errors when attempting to view this white paper, please install the latest version of
Adobe Reader.
"You can create reports in
Microsoft Dynamics AX by using Microsoft Dynamics AX
reporting tools for Visual Studio or by using MorphX reporting tools."
Source : Microsoft Dynamics
Reporting Tools
Microsoft Dynamics NAV is also known as :
dynamics nav,
microsoft analysis services,
microsoft reporting services,
microsoft dynamics nav,
microsoft dynamics nav navision,
microsoft dynamics nav test drive,
microsoft dynamics nav team blog,
microsoft dynamics: business management,
programming microsoft dynamics nav,
microsoft business solutions competency,
microsoft dynamics-navision erp,
microsoft dynamics navision erp software,
microsoft dynamics nav administrator,
erp microsoft dynamics nav,
microsoft dynamics nav demo,
implementing microsoft dynamics nav,
install and configure microsoft dynamics nav,
microsoft dynamics nav developer center,
navision dynamics nav,
focus microsoft dynamics nav,
extend an application using microsoft dynamics nav.
Technical White Paper
This paper presents options for the analysis and
reporting of Microsoft Dynamics NAV 4.00. The benefits
of certain processes and products are discussed as well as relevant issues
partners and users should consider when debating the merits of each option. When
necessary, products and their usages have been described to show the versatility
of Microsoft Dynamics NAV through end reporting.
Introduction
One of the competitive advantages Microsoft Dynamics NAV has in the ERP
market is its ability to provide reporting solutions for users. By combining
Microsoft Dynamics NAV with other options offered by Microsoft or by
building/customizing new solutions, Microsoft Dynamics NAV can provide an
effective ERP solution. The following whitepaper describes how this is done.
Options for analysis and report building, possibilities for customization, and
an outline of various packaged solutions are presented.
What Is Analysis with Microsoft Dynamics NAV?
Analysis and reporting capability provided by an effective IT solution is
essential to the success of any company in the modern business world. Microsoft
Dynamics NAV has worked to ensure that users can perform analysis and reporting
tasks with the aid of other products such as Microsoft Excel, products produced
by other companies, and customized solutions. Valuable qualitative and
quantitative tools, as well as other products that otherwise aid in the transfer
of data, are outlined in this document.
Microsoft Dynamics NAV's Built-in Capabilities
Built into Microsoft Dynamics NAV are some capabilities for analysis and
reporting. These are described in the following sections.
Standard Reports
Standard reports are delivered with Microsoft Dynamics NAV. There are more
than 300 reports covering Sales and Marketing, Finance, Manufacturing, and so
on. In this whitepaper we will not go into detail about which reports are
available in the product, because these vary from country to country and depend
on which granules are in the installation at hand.
Report Wizard and Report Design Tools
- When one of the more than 300 standard reports does not fulfill your
needs, the Report wizard can help. With a Microsoft Dynamics NAV Report
wizard, you can create simple reports based on tables in Microsoft Dynamics
NAV. The wizard will guide you through steps that help them define the
report. There are three wizards in Microsoft Dynamics NAV
- The Form-Type Report wizard helps you create a form-like report. You
start by selecting a table to base the report on. After that, you decide
which fields you want displayed in the report. Separators and column breaks
can be inserted between fields in the report.
- After the content of the report has been defined, you can sort data in
the report by selecting any of the indexed fields. The report has a header
section that contains the company name, report name, date of execution, user
ID, and page numbers.
- The Tabular-Type Report wizard helps you create a tabular report. You
are guided through steps that include selection of fields, sorting of data,
grouping of data, and creation of totals. You can also select a style to
apply to the report: List style gives a table-like report, and document
style uses a page header and gives a document look to the report.
- The Label-Type Report wizard helps you create a report that can be used
to print labels. You are guided through the steps of selecting fields to be
included in the report and specifying the layout of the report (including
specifying how many labels to print across the page as well as the
indentation, distance between labels, and label size)
The wizards cover a variety of user needs and can be modified by adding more
tables to data items, more fields to sections, more sections, and trigger code
to sections in the reports. This means that the wizards can serve either as a
help for creating quick ad-hoc reports or as a starting point for creating more
advanced reports.
Account Schedules and Analysis Reports
These are built-in analysis tools that allow you to slice and dice data from
Salesperson, Customers, Vendors, Items, G/L, Sales, Purchase and Inventory. You
can compare data to budgets across time, departments, project, campaigns, and
other dimensions. The easily defined line and column layout gives you a
comprehensive and tabular form of analysis. You can define lines as the contents
of accounts, text, or freehand calculations across other lines in the view.
Columns are defined in a similar way. They can contain data from accounts
including Net Change from G/L Entries as well as Budgets. They can also be
defined as calculations between other columns in the view. All in all, this
provides you with the ability to tailor the analysis of data.
The results can be presented in print, in a window that allows easy
navigation to original entries and documents, and/or in Microsoft Excel for
further processing. Exporting the data to Excel disconnects it from data in
Microsoft Dynamics NAV, so that changes to a record in Microsoft Dynamics NAV
will not affect the data in the analysis. This also means that the sheet can be
distributed as a regular file, and viewing the data in the sheet does not
require access to Microsoft Dynamics NAV data.
Analysis by Dimensions
This is Microsoft Dynamics NAV's built-in multidimensional analysis tool that
allows you to analyze data from G/L, Purchase, Sales and Warehousing. In this
tool, different analysis views can be created for different purposes. You can
create views as cross tabs between dimensions including time. For example, you
can create an analysis view relating to sales in a particular area for a
particular time period and for a particular group of customers. The views can be
saved for later use and can be sent to Excel at the click of a button, whereby
the automatic creation of relevant pivot tables allows the use of such
functionalities as dragging and dropping fields as well as the use of additional
measures, dimensions, and filter criteria. To reflect changes in the database,
you must update the views. This means that the data in the views are
disconnected from the transactional data. When a record is updated in Microsoft
Dynamics NAV, it remains unchanged in the view and, if it is exported to Excel,
this view is also unchanged in the pivot table. The Excel sheets can be
distributed as regular files. Viewing the data in a sheet does not require
access to Microsoft Dynamics NAV data
Budgets
Working with budgets in Microsoft Dynamics NAV lets you view budgets in a
cross tab view. Budgets can be displayed with dimensions in rows and columns.
The view can be filtered by any dimension and compared with G/L Account Balance
and G/L Balance. Budgets can be copied to new budgets and manipulated and saved.
You can export budgets to Excel, manipulate the data in Excel, and then import
the data back into Microsoft Dynamics NAV.
Extracting Data from Microsoft Dynamics NAV
XMLPorts
XMLPorts are used to create XML documents from Microsoft Dynamics NAV data.
Once it is in XML format, the data can be sent to and read by another
application. New XMLPorts can be created for any documents that you want to
exchange, such as a purchase order or a price list. The procedure has been
simplified using XML input/output (I/O) functionality. Previously, enabling XML
document exchange required a considerable amount of C/AL code, but now XMLPort
objects can be designed effortlessly, making regular document exchange across
different platforms and databases straightforward. XMLPorts can also be used to
create a Microsoft Dynamics NAV database based on an XML document.
With the XMLPort Designer, Microsoft Dynamics NAV data can be placed as
specific elements in the XMLPort during the XMLPort creation process. It is also
possible to map elements and attributes in an XML document, making transferring
data from one format to another uncomplicated and seamless. This functionality
not only makes document exchange more straightforward, it also ensures that
Microsoft Certified Partners can map data between Microsoft Dynamics NAV and a
particular XML document through XML document exchange. This improves the overall
performance of Microsoft Dynamics NAV for you. XML data can also be mapped to
C/SIDE tables, fields, and variables. Developers can use code to manipulate XML
data during the import/export process, ensuring the best result. Using XMLPorts
as a means of exporting data gives the ability to execute code-delivering data
in XML, which can be consumed by Reporting Services 2005 with the XML processing
extension and by Reporting Services 2000 with a custom data processing
extension, DTS/SSIS, and other tools.
NODBC
The Microsoft Dynamics NAV ODBC Driver (NODBC) provides maximum
interoperability between the application and database as a single application.
It can access any ODBC-enabled database by simply being configured to use its
ODBC driver. In turn, an ODBC-enabled application can access a given database
using its ODBC driver. This connection gives access to a specified company, so
at least one connection per company is necessary, and NODBC only works against
the native database. The ODBC driver manager acts as the common interface that
enables this dynamic switching to take place, giving application developers
database-independence. Two of the strong points of NODBC are that it gives
access to flow fields and that it enforces Microsoft Dynamics NAV security. A
downside of NODBC is that it is single-threaded. This means that using it in
conjunction with Reporting Services is not recommended.
SQL Server
If Microsoft Dynamics NAV is running on SQL Server, data can be accessed
directly in SQL. There are some caveats about doing this. First of all, security
will have to be maintained in the SQL database. On top of this, you have no
access to business logic when accessing data residing in SQL Server, and there
is no access to flow fields when doing so, either. That said, this solution
enables the use of Reporting Services and " if running SQL 2005 " the use of
Report Builder. This will be described later in this paper.
Business Analytics
Business Analytics is made up of two separate granules: Basic, which adds the
possibility for Microsoft Dynamics NAV users to define and create cubes in SQL
Server 2000 Analysis Services, and Advanced, which add a comprehensive frontend
to Business Analytics. It is also important to keep in mind that Basic is a
prerequisite for using Advanced since this requires access to cubes built
through the Basic functionality.
Business Analytics Basic
In the Basic setup, you can create cubes in Analysis Services based on data
coming from Microsoft Dynamics NAV. For example, you can create a cube based on
Vendor Ledger Entries:
When the cube has been defined, the configuration can be deployed. If you do
this from inside Microsoft Dynamics NAV, any existing solution will be deleted
and the new one created in its place. It is possible to maintain two active
configurations by working with the SQL tools directly. This is described at
http://blogs.msdn.com/clausba/ (this is not an out-of-the box functionality of
the current release).
To be able to use cubes created in Business Analytics Basic in conjunction
with Business Scorecard Manager, it makes sense to create Measures that cannot
be created directly from Microsoft Dynamics NAV. To do this, it is necessary to
use the tools provided with Analysis Services. This is described at: http://blogs.msdn.com/clausba.
An example of a measure that makes sense in conjunction with Business Scorecard
Manager is Gross Profit Margin. The Multi Dimensional Expression (MDX) for the
measure looks something like this:
(SUM(CROSSJOIN({[G_L Account].&[00016100]},CROSSJOIN( {[G_L Entry Posting
Date].currentmember},
{[Measures].[Balance at Date Amount]})))-
SUM(CROSSJOIN({[G_L Account].&[00017100]},CROSSJOIN( {[G_L Entry Posting Date].currentmember},
{[Measures].[Balance at Date Amount]}))))
/
SUM(CROSSJOIN({[G_L
Account].&[00016100]},CROSSJOIN( {[G_L Entry Posting Date].currentmember},
{[Measures].[Balance at Date Amount]})))
This can be done in the General Ledger cube in the default configuration in
Business Analytics.
Business Analytics Advanced
Targit Business Analytics for Microsoft Dynamics NAV provides a strong
analytical tool for customers. With Business Analytics, you can create a report
and analysis based on data in a cube created in Microsoft Dynamics NAV. The cube
contains measures and dimensions, which provide the skeletal framework for
Business Analytics analysis. From the Business Analytics Overview window, cubes
can be created and configured in an XML format for further analysis.
Once the cube has been created in Microsoft Dynamics NAV, it can then be
configured and utilized in Business Analytics. While analyzing the cube data,
you can choose to create charts, graphs, globes, and maps, as shown in the
following screenshot.
Several analysis views can be created at once, and you can add, edit, or
delete information from the analysis or the analysis view itself at any time.
These graphics can be later added to reports or be used as a stand-alone
analysis, providing customers with a versatile and powerful analytical tool.
It is important to note that whenever a change has been implemented by
executing the configuration from inside Microsoft Dynamics NAV, the installation
program will have to be executed for Business Analytics Advanced to be able to
access the cubes on Analysis Services. This is due to the fact that when
executing the installation program security is set on the cubes and database
allowing Business Analytics Advanced to access them.
Microsoft Business Intelligence Tools
Excel
Microsoft's Excel software provides additional support for analyzing
or reporting data maintained in Microsoft Dynamics NAV, creating a convenient
and flexible resource for analysis and reporting tasks. To access this
functionality, data from Microsoft Dynamics NAV is transferred to Excel for
further analysis or reporting purposes.
The following example shows how
you can take a closer look at a customer's order details. In Microsoft
Dynamics NAV, open the Financial Management menu and then click Receivables -
Reports- Customer - Order Details. On the Options tab, select the Print to
Excel option, indicating that this data will be used for further analysis.
The information is then sent to a Microsoft Excel document for future use.
Excel can also be used in conjunction with Microsoft Dynamics NAV and Business
Analytics. Once data has been created and configured in a Business Analytics
cube, you can create pivot tables so the data can be analyzed in Excel
The strength of Excel reporting with Microsoft Dynamics NAV is its
flexibility " which is useful when tackling ad hoc tasks " coupled with the
well-known user-friendly interface. Excel usability with the Microsoft
Dynamics NAV and Business Analytics cube information provides you with another
option for reporting and analyzing data maintained by Microsoft Dynamics NAV.
These functions allow you to analyze data and review information in a
practical fashion. An add-in, SQL Analysis Services for Excel 2002/2003, is
available at this time. It gives the ability to create free-hand Excel reports
on OLAP data sources and allows for more flexible reporting than pivot
tables.
Reporting Services
SQL Server Reporting Services is a
comprehensive, server-based solution that enables the conception, management,
and delivery of both traditional, paper-oriented reports and interactive,
Web-based reports. Reporting Services provides users with enterprise capable
reporting and an environment for authoring, managing, and delivering reports
to the entire organization. Additions included in Reporting Services in SQL
Server 2005 provide extra enterprise reporting capabilities and allow business
users to utilize data in an ad hoc fashion, generate their own reports from
scratch, and share works with others. As an integrated part of the Microsoft
business intelligence framework, Reporting Services combines the data
management capabilities of SQL Server and Microsoft Windows Server with
well-known and powerful Microsoft Office System applications.
The
functionalities of SQL Reporting Services itself are practical and enable you to
create reports in a convenient manner. Reporting Services' report authoring
functionality lets you create reports for publishing to the Report Server
using Microsoft or other design tools that use Report Definition Language
(RDL). Report definitions, folders, and resources are published and managed as a
Web service with Reporting Services' report management functionality. Managed
reports can be executed on demand or on a specified schedule and are cached
for reliability and performance. Administrators can use the Management Studio
to organize reports and data sources, schedule report execution and delivery,
and track reporting history. SQL Server Reporting Services supports both
on-demand (pull) and event-based (push) delivery of reports. You can view
reports in a Web-based format or in e-mail. Security is also an issue
addressed by SQL Server Reporting Services. Reporting Services implements a
flexible, role-based security model in order to protect reports and reporting
resources.
Reporting Services Architecture
Microsoft SQL Server Reporting
Services is designed with a modular, distributed architecture to help achieve
both scalability and flexibility. Processing is distributed across multiple
components that can be integrated into custom solutions. The following
diagram shows the Reporting Services architecture.
During the report
creation process, a report definition is retrieved from the Report Server
database and used with data from Microsoft Dynamics NAV to create a report.
After the report has been created, it can be reconfigured into another format
such as an Excel spreadsheet or HTML. The following diagram illustrates how a
report is processed.
Reporting Services was designed to meet a vast range
of needs, including those of independent software vendors who may want to
integrate reporting features into their tools and applications. Microsoft
Dynamics NAV and Reporting Services can be easily integrated through modular
design and extensive programming interfaces by software developers and
enterprises so that reporting functionality works seamlessly. It has also
been designed with a number of APIs so that developers can create custom
extensions for reporting services.
The way Reporting Services accesses data
depends on how Microsoft Dynamics NAV data is stored. If Microsoft Dynamics
NAV is running on the native database, Reporting Services can access data using
NODBC. If Microsoft Dynamics NAV is running the SQL option, Reporting Services
can access data directly in SQL Server using the built-in provider. As
mentioned earlier, because of the limitations of NODBC, SQL is the preferred
solution in this scenario. This is due to the fact that NODBC is single
threaded, limiting its use in a client/server setup. Also, as stated earlier,
there are some caveats about accessing data directly in SQL Server. There is
one caveat in accessing Microsoft Dynamics NAV data this way. SQL Server does
not store flowfields in the database. These are computed in Microsoft
Dynamics NAV. For that reason reports that access data directly in SQL Server
cannot access data from flowfields. Data security will have to be handled in
SQL Server as the Microsoft Dynamics NAV security model does not accommodate
accessing SQL Server directly.
To create a report using Reporting Services,
create a Reporting Services project. Add a data source pointing to the
Navision database. Then add a Report and create a SQL query against the Navision
database. When the query has finished running, you can design the actual report.
After you have designed the report, it can be deployed to the Report Server
and viewed in a browser. It is important to note that reports created in
Reporting Services with data residing in the Navision database run directly
on the database. Thus they put a load on the same resources as regular usage of
Microsoft Dynamics NAV.
A report pack with a set of sample reports running
against the Navision database can be found at http://www.microsoft.com/sql/reporting.
This package shows some of the capabilities of Reporting Services.
Report
Builder
Report Builder is a feature that was introduced with SQL Server 2005.
It lets you create reports by dragging and dropping fields from a semantic
model built on top of the physical database. A prerequisite for using Report
Builder is that Microsoft Dynamics NAV is running on SQL Server, because this
is the only database that Report Models can be built on. The following procedure
describes how to create a model on top of the Navision database and build a
report on top of it.
- Create a Report Model project in Business
Intelligence Development Studio.
- Add a data source that connects to the Navision database. Remember to set Security appropriately in the connection.
- Add a Data Source View (DSV). The DSV needs to connect to the data source
that you added in the previous example. In this example, the view is made up
of two tables: Vendor Ledger Entry and Vendor.
- Open the DSV by double-clicking it.
This shows the
relations, if any, between the tables in the DSV.
- Populate the relations
by clicking Database->Alter->Advanced tab.
- To have Microsoft Dynamics NAV
add relations to the database in SQL, select Maintain Relationships. This is
a highly intrusive operation, so you should not be do this unless it is
strictly necessary.
- Add a report model based on the DSV that you have just
created. If you use the default settings, a model is created based on the
relations in the DSV.
- After deploying the model on the Report Server,
navigate to the Report Builder and select the model.
You can now create
reports by navigating the model and dragging and dropping fields onto the
report.
After you have selected the fields, you can add necessary
filters, groups, and prompts to the report. The report can be saved back to
the Report Server for later use.
Business Scorecard Manager
Business
Scorecard Manager is a comprehensive scorecard and dashboard application that
provides knowledge workers with deep contextual insight into business
drivers. Information is delivered in a collaborative environment for
effective business management and action in the performance-driven
organization.
A Microsoft Office product, Business Scorecard Manager empowers
employees to build, manage, and use their own scorecards, reports, and visual
resources using familiar tools. With these tools, employees can analyze
relationships between key performance indicators (KPIs) and tangible business
objectives. Business Scorecard Manager gives companies a broad view of
business opportunities, through which employees can better understand
business challenges, effectively shape solutions, and quickly reach their
objectives. Decision-making happens across all levels of an organization, and
empowering employees with powerful business intelligence (BI) solutions can
help them have greater impact.
There are two ways to integrate Business
Scorecard Manager with Microsoft Dynamics NAV.
- One is to make use of the
ability to build KPIs on top of relational data. This means that KPIs can be
created directly on top of the Navision database. In this solution, it is
important to keep in mind that since the scorecard is connected directly to the
transactional database, there are two caveats. First, all queries will put a
load on the Navision database, which can make the performance worse for the
users of Microsoft Dynamics NAV. Second, data can be updated between
views of the same scorecard, so that people may see different results even
when looking at data at what seems to be the same time.
- Another way of
integrating Business Scorecard Manager to Microsoft Dynamics NAV is to
connect the scorecard to cubes created with Business Analytics Basic. When you
do this, the two caveats mentioned above do not apply. This means that
measures like the KPIs mentioned earlier should be created to enable the
creation of meaningful scorecards. To make this configuration run, the
connection pool running the Scorecard Server must have access to the cubes in
Analysis Services.
Conclusion
Analysis and Reporting services are an
important part of the enterprise resource planning process. Microsoft
Dynamics NAV has been explicitly designed to give the user many options for
optimal analysis and reporting, and to leave room for partners to provide
customized solutions. With the correctly selected reporting tools and
Microsoft Dynamics NAV, dependable and practical analysis and reporting is
available and adaptable to individual users' needs.
Additional Information
For information about Business Intelligence Partner companies, please refer to:
http://www.microsoft.com/sql/partners/dwa/default.mspx
For more information
about the Business Scorecard Manager, please refer to:
http://msdn.microsoft.com/isv/technology/bss/default.aspx
http://www.microsoft.com/office/bsm
For more information about the SQL
Analysis Services add-in for Excel 2002/2003, please refer to:
http://www.microsoft.com/office/solutions/accelerators/exceladdin/default.mspx
For more information about Business Analytics Basic and Advanced please refer to
http://blogs.msdn.com/clausba
About Microsoft Dynamics
Microsoft Dynamics,
a division of Microsoft, offers a wide range of integrated, end-to-end business
applications and services designed to help small, mid-market and corporate
businesses become more connected with customers, employees, partners and
suppliers. Microsoft Dynamics' applications optimize strategic business
processes across financial management, analytics, human resources management,
project management, customer relationship management, field service management,
supply chain management, e-commerce, manufacturing, and retail management. The
applications are designed to provide insight to help customers achieve
business success. More information about Microsoft Dynamics can be found at
www.microsoft.com/BusinessSolutions.
Date: March 2006
* Microsoft Dynamics NAV, formerly Microsoft® Business
Solutions"Navision®
www.microsoft.com/dynamics/nav
Table of Contents
- Introduction.
- What Is Analysis with Microsoft Dynamics NAV?.
- Microsoft Dynamics NAV's Built-in Capabilities
- Standard Reports
- Report Wizard and Report Design Tools
- Account Schedules and Analysis Reports
- Analysis by Dimensions
- Budgets
- Extracting Data from Microsoft Dynamics NAV
- XMLPorts
- NODBC
- SQL Server.
- Business Analytics
- Business Analytics Basic
- Business Analytics Advanced
- Microsoft Business Intelligence Tools
- Excel
- Reporting Services
- Reporting Services Architecture.
- Report
- Builder
- Business Scorecard
- Manager
- Conclusion
- Additional Information
- About Microsoft Dynamics