Microsoft Dynamics NAV 2015 Professional Reporting

Table of Contents

Microsoft Dynamics NAV 2015 Professional Reporting

Credits

About the Author

Acknowledgement

About the Reviewers

www.PacktPub.com

Support files, eBooks, discount offers, and more

Why subscribe?

Free access for Packt account holders

Instant updates on new Packt books

Preface

What this book covers

What you need for this book

Who this book is for

Conventions

Reader feedback

Customer support

Downloading the color images of this book

Errata

Piracy

Questions

1. How Do I Start to Create a Report?

What is a report?

The request page

The report viewer

Report development phases

The data model phase

The layout phase

The testing phase

Report development tools

What do I use to develop the data model?

How do I create the report layout?

Built-in and custom layouts

Building the data model

Understanding the report dataset designer

Building the dataset

Data items and columns – fields, variables, and expressions

Including captions and labels

IncludeCaption versus FIELDCAPTION

How is the dataset flattened?

Unrelated tables or multiple data items, without indentation

Related tables or multiple data items with indentation

Report triggers

What happens when a report runs?

The report trigger sequence

What is a ProcessingOnly report?

Creating the layout

Visual Studio versus Report Builder

Creating a simple layout in Report Builder

Report Builder features

Wizards for prototyping

Creating a simple layout in Visual Studio

Visual Studio features

Report formatting, toolbars, and document outline

Building and testing the layout

Testing pagination and layout in different rendering extensions

Testing the report in different clients – Windows, Web, and tablet

Reporting design guidelines

The request page

The report description

The report creation workflow

Summary

2. Getting Started with the Tablix

Report items

Everything is a Tablix

The Document Outline

Changing the name of a Tablix

List versus Table versus Matrix

Filtering and sorting

How can I implement filters?

How can I implement sorting?

Interactive sorting

Grouping

How can I implement grouping?

Adding a parent-child group to a Tablix

How do I implement expand/collapse?

Adding an adjacent group to a Tablix

Formatting report items

Using placeholders

Important properties – CanGrow and CanShrink

Example – create an item dashboard report

Summary

3. Expressions

Using expressions for properties

The expression language

Simple and complex expressions

Symbols used in expression placeholders

Collections

Understanding the scope of an expression

Creating custom functions

Typical expression examples

Working with dates

Working with strings

Decision functions

Generating page breaks in code

Repeating a column header on every page

Example – the green-bar-matrix

Summary

4. Data Visualization Techniques

An introduction to data visualization

Recipes to implement top x filtering

Conditional formatting in a report

Analyzing your data with data bars and indicators

Using Sparklines to visualize trends

Learning how to visualize information with gauges, maps, and charts

Using gauges

Using charts

Using maps

Summary

5. Document Reports

What is a document?

The data model

Implementing multilanguage

Address formatting

Including logos

The No. of Copies option

Totaling and VAT

Logging and No. Printed

InitializeRequest

The layout

Filtering the dataset

Working with headers and footers

GetData and SetData explained

Declaring the global variable and functions

Implementing the Get and Set functions

Alternative solutions – the mini-document

How do I implement page x of y?

Summary

6. Tips and Tricks

Report pagination

Show a footer or header on the last page

Place at the bottom

A fixed number of rows

Trans headers and footers

Creating links

Using a filter

Using a bookmark

Using the GETURL() function

Using internal bookmarks

Printing barcodes

Report templates

Using a report setup table

Report logging

The fixed header problem

Summary

7. Performance Optimization Techniques

Performance recommendations

The dataset

Captions and labels

Remove unused columns

Optimize the usage of BLOB fields

Variables and setup information

Avoid unnecessary rows

Report totals

Number formatting

Applying the correct filters

Recommendations according to the version of Dynamics NAV

The layout

Print layout versus print preview

Avoid conditional visibility on a big dataset

Best practices when visualizing information

Expressions in the page header or footer

Complex grouping and aggregate functions

Optimization for the chosen rendering format

Report design guidelines

Implementing hotfixes and rollup updates

Alternatives for building a faster dataset

Using a temporary table

Using a query object for the dataset

Summary

8. Word Report Layouts

Introducing the Word report layout

Creating a Word report layout

Formatting the Word report layout

Repeating a table header

Using Word templates

Optimizing your dataset for Word reports

Managing report layouts

Custom layouts

Editing a Custom RDLC layout

The report execution flow

The Word report execution flow

At design time

At runtime

Managing layouts in code

Scheduling reports

Summary

9. Power BI

Dynamics NAV web services

Using Excel

Power Pivot

Activating Power Pivot in Excel

Building a Power Pivot data model

Importing data into Power Pivot

Creating relations in the Power Pivot data model

Power View

Power Map

Power Query

Power BI Designer

PowerBI.com

Summary

10. Reporting Services

What are Reporting Services?

Installation and configuration

Creating a report in SSRS

Using SQL Server Data Tools

Publishing a report project

Implementing reusability

Shared data sources and datasets

Shared report parts

Creating functions

Using stored procedures

Calling a Dynamics NAV OData web service

The next step

Caching

Subscribing or scheduling

Summary

11. Charts in Dynamics NAV

The generic chart designer

Text management

Show any list as a chart

Business charts

Creating a business chart

Drill down your business chart

Preserving the user personalization

Implementing cues and colored indicators

A typical activities page

A typical cue table

Colored indicators

Cue style objects in Dynamics NAV

Summary

Index

Microsoft Dynamics NAV 2015 Professional Reporting

Microsoft Dynamics NAV 2015 Professional Reporting

Copyright © 2015 Packt Publishing

All rights reserved. No part of this book may be reproduced, stored in a retrieval system, or transmitted in any form or by any means, without the prior written permission of the publisher, except in the case of brief quotations embedded in critical articles or reviews.

Every effort has been made in the preparation of this book to ensure the accuracy of the information presented. However, the information contained in this book is sold without warranty, either express or implied. Neither the author, nor Packt Publishing, and its dealers and distributors will be held liable for any damages caused or alleged to be caused directly or indirectly by this book.

Packt Publishing has endeavored to provide trademark information about all of the companies and products mentioned in this book by the appropriate use of capitals. However, Packt Publishing cannot guarantee the accuracy of this information.

First published: September 2015

Production reference: 1110915

Published by Packt Publishing Ltd.

Livery Place

35 Livery Street

Birmingham B3 2PB, UK.

ISBN 978-1-78528-473-1

www.packtpub.com

Credits

Author

Steven Renders

Reviewers

Daniela Bozdoc

Alex Chow

Daniel Rimmelzwaan

Matt Traxinger

Commissioning Editor

Dipika Gaonkar

Acquisition Editors

Purav Motiwalla

Richard Brookes-Bland

Content Development Editor

Shweta Pant

Technical Editor

Saurabh Malhotra

Copy Editors

Kevin McGowan

Rashmi Sawant

Project Coordinator

Sanjeet Rao

Proofreader

Safis Editing

Indexer

Priya Sane

Graphics

Abhinash Sahu

Production Coordinator

Nitesh Thakur

Cover Work

Nitesh Thakur

About the Author

Steven Renders is a Microsoft Certified Trainer with skills that span the business and technical domains. He specializes in Microsoft Dynamics NAV and Microsoft SQL Server. He has more than 15 years of both business and technical experience. He provides training and consultancy that focuses on Microsoft Dynamics NAV, Microsoft SQL Server, business intelligence solutions, Microsoft SQL Server Reporting Services, and database performance tuning.

Furthermore, he is also an expert in Microsoft Dynamics NAV, on which he has already delivered many training sessions. He was also the author of the official Microsoft training material on Dynamics NAV reporting, development, upgrading, and SQL Server performance tuning.

He is the author of the books, Microsoft Dynamics NAV 2015 Professional Reporting and Microsoft Dynamics NAV 2009: Professional Reporting and also a reviewer of the books, Programming Microsoft Dynamics NAV 2009, Programming Microsoft Dynamics® NAV 2013, and Implementing Microsoft Dynamics NAV 2013.

He has also presented at various Microsoft MSDN and TechNet conferences, NAV Techdays, communities, events, and the MCT Summit.

In 2011, he started his own company, think about IT, which specializes in training and consultancy, helping companies learn, implement, understand, and solve complex business requirements related to IT, both in Belgium and abroad.

His specialties are Microsoft Dynamics NAV, Microsoft SQL Server, Business Intelligence & Reporting, and Power BI.

You can contact him at <[email protected]> and through his website (www.thinkaboutit.be). You can also view his LinkedIn profile at http://be.linkedin.com/in/stevenrenders, and his Twitter handle is @srenders.

Acknowledgement

There are so many people I would like to thank, who kept me motivated while I was researching and writing this second book.

First of all, a special thanks to my parents, Luc and Martine; my family, Liza, Jan, Ben, Daan, Wout, Lukas and my close friends Merlijn, Vicky, Holbe, Liesbeth, Veerle, Johan, Els, Gita, Niki, and Fynn who always stood behind me and allowed me to spend so much time apart from them.

I would like to thank the team at Packt Publishing, who deserves a lot of gratitude. It was a pleasure working with them, especially Shweta and Saurabh. They helped me a lot and guided the book in the right direction. I'm very thankful and appreciative of their help and guidance.

A big thank you to the team of reviewers (Matt, Daniel, Alex, and Daniela), who volunteered their time, knowledge, and experience by reviewing every chapter and maintaining the quality, accuracy, and flow of the book. You had a very big contribution in making this book a great piece of work that is easy to read and understand.

A special thanks to Vincent and Koen from Plataan. Many years ago, they motivated me to become a Microsoft Certified Trainer and allowed me to deepen my knowledge and experience in the Dynamics community.

Since I started my own company, think about IT, I have been lucky to have worked with a lot of very good and interesting customers, challenging projects, and different types of businesses, which have allowed me to broaden my horizons and expertise, both of which I was able to apply in this book.

I would also like to thank Microsoft and their employees for making fantastic products, such as Dynamics NAV and SQL Server, to come closer together. Both of them are great applications on their own, but combining them has been one of their biggest achievements over the last few years. The way Dynamics NAV is getting more and more integrated with other Microsoft technologies has shaped the future and opened up an almost unlimited window of possibilities and opportunities.

To all the individuals I mentioned earlier and to several colleagues, who have assisted me in one way or the other, especially in challenging me with alternative views, I feel very much indebted to you all (Roel, Steffie, Brecht, Kurt, Luc, Claus, Tarek, Mark, Conny, Frank, Anas, and Aleksandar).

I would like to thank you all!

About the Reviewers

Daniela Bozdoc is an IT professional who has a wide experience as a business analyst with a solid background as a software developer and data and software architect on various technologies. The implementation projects, especially Microsoft Dynamics NAV and Oracle EBS, have brought her excitement, new experiences, and the opportunity to meet and work with interesting people and exceed even the highest expectations.

She is a graduate from the Babes-Bolyai University of Cluj-Napoca, Romania, where she received a bachelor’s degree in computer science.

She lives in Romania, where she enjoys spending time with her family and taking pictures of beautiful landscapes and natural eye-catching pieces.

Alex Chow has been working with Microsoft Dynamics NAV, formerly Navision, since 1999. Over the years, he has conducted hundreds of implementations across multiple industries. The size of businesses he has worked for range from small enterprises that earn $2 million a year to multinational corporations that earn $500 million a year.

Throughout his Dynamics NAV career, he has often been designated as the primary person responsible for the success and failure of a Dynamics NAV implementation. The fact that he is still in the Dynamics NAV business means that he's been pretty lucky so far. His extensive career in the Dynamics NAV business is an evidence of his success rate and expertise.

With a background in implementing all the functions and modules in and out of Microsoft Dynamics NAV, he has encountered and resolved the most practical and complex requirements and business rules. Through these experiences, he has learned that sometimes you have to be a little crazy to have a competitive edge.

He strongly believes that sharing these experiences and knowledge will benefit the Dynamics NAV community. He writes about his journey at www.dynamicsnavconsultant.com. He is also the founder of AP Commerce, Inc. (www.apcommerce.com) in 2005, a fullservice Dynamics NAV service center. In addition, he has written a book on Dynamics NAV titled Getting Started with Dynamics NAV 2013 Application Development.

He lives in Southern California with his beautiful wife and two lovely daughters. He considers himself the luckiest man in the world.

Daniel Rimmelzwaan was born and raised in the Netherlands and moved to the USA at the end of 1999 to be with his new American wife. In Holland, he worked as a Microsoft Access and VBA developer. While looking for a job as a VB developer in the USA, he was introduced to Navision by a VB Recruiter and was intrigued by the simplicity of its development tools. He decided to accept a job offer as a Navision developer with the firm intention to continue looking for a real developer job.

More than 15 years later, after a couple of stints in the Microsoft partner channel and a few years as a freelancer, he currently works as the chief quality officer for KCP Dynamics Group, an international partner serving customers all over the world, and he enjoys his career more than ever.

Ever since he started working with NAV, he has been an active member of the online communities for NAV, such as mibuso.com, dynamicsuser.net, and the online forums managed by Microsoft. For his contributions to these online communities, he received his first of eleven consecutive Microsoft Most Valuable Professional Awards in July 2005, which was just the second year that the MVP Award was given for NAV. Microsoft gives the MVP award to independent members of technology communities around the world and recognizes people who share their knowledge with other members of the community.

He lives with his wife and two kids in Arizona, USA.

Matt Traxinger graduated from the Georgia Institute of Technology in 2005 with a BS in computer science. After college, he took up a job as an add-on developer using a language he was unfamiliar with for a product he had never heard of—Navision. It turned out to be a great decision.

In the years that followed, he learned all the areas of the product and earned certifications in multiple technical and functional areas of Microsoft Dynamics NAV. He currently works as a development manager for ArcherPoint, a Dynamics NAV solutions provider.

In 2012, he was recognized as a Microsoft MVP and continues to be actively involved in the community, working closely with NAVUG and the Association of Dynamics Professionals to educate the next generation of NAV professionals.

www.PacktPub.com

Support files, eBooks, discount offers, and more

For support files and downloads related to your book, please visit www.PacktPub.com.

Did you know that Packt offers eBook versions of every book published, with PDF and ePub files available? You can upgrade to the eBook version at www.PacktPub.com and as a print book customer, you are entitled to a discount on the eBook copy. Get in touch with us at for more details.

At www.PacktPub.com, you can also read a collection of free technical articles, sign up for a range of free newsletters and receive exclusive discounts and offers on Packt books and eBooks.

https://www2.packtpub.com/books/subscription/packtlib

Do you need instant solutions to your IT questions? PacktLib is Packt's online digital book library. Here, you can search, access, and read Packt's entire library of books.

Why subscribe?

Fully searchable across every book published by Packt

Copy and paste, print, and bookmark content

On demand and accessible via a web browser

Free access for Packt account holders

If you have an account with Packt at www.PacktPub.com, you can use this to access PacktLib today and view 9 entirely free books. Simply use your login credentials for immediate access.

Instant updates on new Packt books

Get notified! Find out when new books are published by following @PacktEnterprise on Twitter or the Packt Enterprise Facebook page.

Preface

The goal of this book is to introduce and explain the reporting capabilities of Dynamics NAV in detail. Starting from the beginning, this book will introduce you to the report designers and explain how you can create and customize reports in Dynamics NAV. The book also looks at topics in depth to explain and demonstrate the typical issues you may encounter in your daily life regarding reporting and Dynamics NAV using practical real-life scenarios.

After reading this book, you will understand how to manipulate Dynamics NAV for it to produce the reports and analytical data that you want, when you want it, and in the format you want it.

What this book covers

Chapter 1, How Do I Start to Create a Report?, explains how to create a report in Dynamics NAV. This chapter explains that the report development can be done in two steps: by creating the data model and then the layout. It also explains how to include captions and labels. Then, it dives into Visual Studio, explains how to create the layout, and demonstrates the difference between Visual Studio and Report Builder.

Chapter 2, Getting Started with the Tablix, covers how to use the Tablix control when we create the layout of the report. This chapter explains how the Tablix can be used as a List, Table, or Matrix, and demonstrates the differences between them, and also discusses when to use each. This chapter also covers the different techniques of how to filter, sort, and group information in the report layout. It also introduces you to some important properties.

Chapter 3, Expressions, discusses the expressions and how they can be used to generate values for certain properties.

Chapter 4, Data Visualization Techniques, explains that creating a report is not difficult, but making it easy to understand so that you can spot trends and learn from your data takes some consideration. The main goal of a report is to communicate the information clearly and effectively, for example, through graphical means. A report needs to create insights by communicating its key aspects in an intuitive way. In this chapter, you will learn about the different techniques available in Microsoft Dynamics NAV to visualize the information.

Chapter 5, Document Reports, explains how the RDLC report layout for documents, such as sales invoices, is created. We will explore this in detail with the most important workarounds, how and why they are required, and explore some alternative solutions.

Chapter 6, Tips and Tricks, contains tips, tricks, and useful things to know when developing reports or to speed up report development. It also contains recipes, or report design patterns, on how to show a header or footer on the last page, place it at the bottom, use a query in the dataset, optimize the report performance, create hyperlinks, reusable report components, or templates, report scheduling, and how to upgrade reports.

Chapter 7, Performance Optimization Techniques, contains tips, tricks, and recipes on how to optimize or performance tune a report.

Chapter 8, Word Report Layouts, introduces you to the built-in Word report layouts and explains how to customize them. This chapter also explains how to build a new Word layout reusing an existing Word invoice template, how to refactor and upgrade datasets for Word report layouts, and how to schedule a report to execute on the server side.

Chapter 9, Power BI, introduces you to the world of Power BI. Power BI can be used to extract data from Dynamics NAV, via ODATA web services, so that you can create BI reports in Excel, using simple pivot tables and charts, or you can make use of Power Pivot to create a more complex and optimized data model. It also covers Power View, a tool used to build interactive data visualizations on top of a Power Pivot data model. Last but not least, it also introduces Power BI in Office 365 and Q&A, a feature of Power BI in Office 365 to generate reports by simply typing in a question.

Chapter 10, Reporting Services, introduces you to the Reporting Services of SQL Server. This chapter explains how you can use reporting services, as a free report development tool, as an alternative tool to create reports on top of a Dynamics NAV database in SQL Server.

Chapter 11, Charts in Dynamics NAV, introduces you to the built-in chart designer in Dynamics NAV. It's frequently used by end users to create charts in Role Centers. This chapter also covers the business charts and how to customize them, as a developer.

What you need for this book

Who this book is for

Basically, this book is for everyone who uses Microsoft Dynamics NAV or has an interest in the reporting capabilities of NAV. This book does not have a lot of prerequisites, although it mainly focuses on Dynamics NAV, RDLC, and Business Intelligence.

This does not mean that this book has no technical depth and you don't require any technical skills. On the contrary, many parts of the book will cover the technical aspects, development techniques, and reporting tools for Dynamics NAV in great detail.

If you want to get an impression of what's possible inside and outside the box of Dynamics NAV, then this book will give you a great overview. If you are interested to know how to attach other reporting or business intelligence products to Dynamics NAV, then this book will also give you an overview of these possibilities.

You might be an application developer, a power user, or a technical decision maker. Regardless of your role, I hope that you can use this book to discover the reporting features in Dynamics NAV that are most beneficial to you.

Conventions

In this book, you will find a number of styles of text that distinguish between different kinds of information. Here are some examples of these styles, and an explanation of their meaning.

Code words in text, database table names, folder names, filenames, file extensions, pathnames, dummy URLs, user input, and Twitter handles are shown as follows: The report.rdlc file is imported into the report object.

New terms and important words are shown in bold. Words that you see on the screen, in menus or dialog boxes for example, appear in the text like this: Use the View/Layout menu to open the layout and create the RDLC file.

Note

Warnings or important notes appear in a box like this.

Tip

Tips and tricks appear like this.

Reader feedback

Feedback from our readers is always welcome. Let us know what you think about this book—what you liked or may have disliked. Reader feedback is important for us to develop titles that you really get the most out of.

To send us general feedback, simply send an e-mail to <[email protected]>, and mention the book title via the subject of your message.

If there is a topic that you have expertise in and you are interested in either writing or contributing to a book, see our author guide on www.packtpub.com/authors.

Customer support

Now that you are the proud owner of a Packt book, we have a number of things to help you to get the most from your purchase.

Downloading the color images of this book

We also provide you with a PDF file that has color images of the screenshots/diagrams used in this book. The color images will help you better understand the changes in the output. You can download this file from http://www.packtpub.com/sites/default/files/downloads/4731EN_ColorImages.pdf.

Errata

Although we have taken every care to ensure the accuracy of our content, mistakes do happen. If you find a mistake in one of our books—maybe a mistake in the text or the code—we would be grateful if you would report this to us. By doing so, you can save other readers from frustration and help us improve subsequent versions of this book. If you find any errata, please report them by visiting http://www.packtpub.com/submit-errata, selecting your book, clicking on the errata submission form link, and entering the details of your errata. Once your errata are verified, your submission will be accepted and the errata will be uploaded on our website, or added to any list of existing errata, under the Errata section of that title. Any existing errata can be viewed by selecting your title from http://www.packtpub.com/support.

Piracy

Piracy of copyright material on the Internet is an ongoing problem across all media. At Packt, we take the protection of our copyright and licenses very seriously. If you come across any illegal copies of our works, in any