Advanced Reporting in MS Dynamics AX/365: Excel and SSRS

Category: Stories Post Date: 23.11.2020

Financial reports demonstrate the current financial situation of businesses and help plan out further strategies for corporate development. If you are using Microsoft Dynamics AX/365, you should definitely be interested in the available reporting tools at your disposal.

Initially, companies used paper reports to exchange information or provide it to management or regulatory authorities. That’s where the SSRS tool came to the rescue out of the software package of Dynamics AX 2012. Starting with AX 2012, the integration with MS Office products and, in particular, with Excel, became available. This enabled users to upload data sorted out in lists without any programming skills.

Let’s review these methods in detail.

Advanced reporting

Excel Reporting

Excel is a popular application used by organizations to compile data reports. With powerful formulas, easy-to-use pivot tables, and flexible charts, Excel has been considered the perfect reporting tool for the longest time.

You can use Excel formulas in an Excel template to perform complex calculations and summarize data. You can also use pivot tables and charts to display data in an easy to read way. This function acts like a template, which means that you can use the same Excel file to report data from any of your views. For example, an organization can create an Excel template to export a weekly sales funnel.

Then, there are pivot tables – one of the most effective tools in MS Excel. They can be used to transform a million rows of data into a concise report in seconds. Pivot tables allow changing the analysis scheme by dragging and dropping fields from one area of the report to another.

Another tool – Excel Templates – allows you to create Excel files based on Dynamics 365 CE, like the Excel export described earlier, but takes it even further.

All these features are available through integration with MS Dynamics (starting from the MS Office AX 2012 version) and can be used as part of your internal corporate data management and processing system. Here’s a list of particular Excel features useful for creating reports:

  • auto-complete formulas;
  • counting calendar days;
  • sorting data;
  • working with long tables;
  • comparison of indicators for different periods;
  • search for indicators based on conditions;
  • search for errors;
  • pivot tables;
  • selection of indicators within the specified limits;
  • determining the impact of changing data on the final calculation;
  • IF function and drop-down list;
  • hotkeys.

Is Excel worth using?

One of the main advantages of Excel is the instant formulas’ recalculation whenever the input data changes. Accordingly, it provides forecasting, planning, and analysis capabilities without the need for programming code and complex mathematical calculations.

Excel’s next pro is the ability to create custom functions and scripts in the Visual Basic for Application language (VBA). In this case, the code is written in a third-party editor and then run in the table, processing the input data. In fact, the table becomes the interface to the VBA code. However, to create such functions, you need certain programming skills.

In general, today many business owners, financiers, and marketers cannot imagine their work without Microsoft Excel. It is almost impossible to calculate profits, plan company activities, and improve efficiency without this software solution.

However, the real confusion can happen if you overestimate the Excel capabilities and start using this software as the single data repository – as a database. Excel provides rather weak reporting capabilities and takes a lot of time for data processing, sampling, and analysis.

SSRS Reporting

SQL Server Reporting Services or SSRS for short is a server-side reporting solution included in the MS SQL Server package. This tool serves as a wonderful and much more professional alternative to Excel spreadsheets or, moreover, to ordinary MS Office documents. SSRS is used by companies to create, view, and share reports in a variety of formats, from traditional tabular forms to mixed reports with graphics, charts, and even interactive elements.

When you create an SSRS report, all work takes place in the web interface – both editing and viewing reports. To store reports, you will need to first install the SQL Server database. In SSRS, reports are generated in the Report Definition Language (RDL) XML markup language. In this case, either the latest versions of Microsoft Visual Studio or the Report Builder (a simplified version of Visual Studio for working with reports) can be used for composing reports.

Users can access the Report Server web service either directly or through the Report Manager. The latter option also enables users to work with data sources and security settings. The generated documents are stored either in the cloud (users who got access to the file receive a link to their email) or locally, on the PC of a specific user. You can also use ASP.NET ReportViewer for file viewing. It will help you integrate reports with web pages or .NET applications.

Lastly, the major features of SSRS include:

  • creation of tabular reports – both in traditional formats and as custom solutions with interactive elements that can be enhanced collectively;
  • no need to set up a web server – starting from the version launched in 2008, SSRS offers a built-in IIS web server.
  • export of reports into formats like MS Office, PDF, XML, MHTML, and others;
  • compatibility with databases SQL Server, SQL Azure, OLE DB, ODBC, Oracle, etc.;
  • control of reports access rights;
  • an ability to generate reports in an intuitive editor Business Intelligence Development Studio (there is also standard documentation that tells in detail how to create SSRS report);
  • tracking of changes in reporting data (e.g., via email);
  • caching;
  • integration with SharePoint and other third-party solutions.

Quite a distinctive set of capabilities, especially if you are sick of cumbersome Excel tables in everyday work.

…A few words about updates

When MS Dynamics AX was a relevant solution, SQL Server Reporting Services lacked some of the features that are currently available for the latest version of MS Dynamics – 365.

Although SSRS still supports standard paginated report formats (which is very convenient for storing and printing because such reports can be easily loaded into PDF and Word documents), there are also new reporting tools among SQL Server Data Tools (SSDT). For instance, there are features for exporting data into PowerPoint presentations.

Moreover, there is an option to create a hybrid environment with Power BI services. This allows users to capture and track changes in visual components from reports in Power BI dashboards in real-time.

The elaboration of SSRS availability deserves special attention – the solution has become well-adapted for mobile devices. Thus, instead of report layouts with a fixed size and a fixed orientation, you get responsive templates that can be placed on the screen of any format, orientation, and physical parameters.

This is where Datazen found its way into SSRS – a powerful enterprise-scale mobile monitoring platform hosted on a server that functions flawlessly on any mobile OS (iPad and iPhone, Windows for desktops, tablets and phones, Android, and HTML5 devices).

Datazen doesn’t require SharePoint, cloud subscriptions, Office, etc. In addition, the product is provided free of charge to owners of SQL Server Enterprise 2008 and newer and to Software Assurance members.

Each dashboard can have three levels, optimized for work on classic computers, tablets, and smartphones. Ideally, the application installed on the client device should be used to view the dashboards, but they can also be viewed in a browser when connected to the server.

Client-side applications cache the most recent data, which means they can be accessed offline. By the way, Datazen’s server architecture is somewhat similar to SQL Server Reporting Services, so there shouldn’t be any problems working with Datazen after migrating from SSRS.

Are SSRS Reports worth using?

Why should you use SSRS?

At first, in addition to printing and exporting reports to PDF/Word, SSRS reports can be exported as Excel, XML, CSV, MHTML, or TIFF documents, which provides more flexibility. Such capabilities make SSRS reports generally printable. In addition, SSRS reports can be emailed to users after they have been generated by configuring destination settings. Another significant advantage of SSRS reports is that you can configure data generation per minute, hour, day, week, month, or even year in the SSRS report designer.

However, some users still complain about the lack of personalization. Despite the advanced filtering capabilities of fields, users cannot change the SSRS report format on their own – this can be changed only at the stage of product implementation (which means that only IT specialists can do it).

Summary

As we can see, SSRS can be considered the most versatile and cost-effective reporting tool. However, there are more methods of generating reports in MS Dynamics AX / 365, so stay tuned for our next blog posts – we’ll review BI and EDI reporting.

If you are looking for an SSRS report developer to implement or modify the existing MS Dynamics system – contact us! We have been accumulating expertise in the field for years, working with readymade MS Dynamics solutions, and completing turnkey projects.

Leave a Reply

Your email address will not be published. Required fields are marked *