Chapter 2:SQL Server Reporting Services

Objectives

The objectives are:

Describe the installation procedure and prerequisites.
Identify the tools that are used to develop and deploy reports for Microsoft Dynamics® AX 2012.
Explain the process for developing reports by using Visual Studio.
Review the three options to deploy reports.
Describe how SSRS reports can be used within Microsoft Dynamics AX 2012.

Introduction

SQL Server Reporting Services (SSRS) is the primary framework for developing reports in Microsoft Dynamics AX 2012. The installation requirements include completing tasks such as verifying prerequisite components are installed, completing the installation setup wizard and deploying default reports. With SSRS, users can develop and deploy reports and integrate reports with Microsoft Dynamics AX 2012.

SSRS provides data that can be displayed in many different clients including: Microsoft Dynamics AX clients, Enterprise Portal, and batch jobs.

Figure 2.1 Microsoft Dynamics AX 2012 Reporting Framework

Using SSRS with Microsoft Dynamics AX 2012 provides users professional-looking reports, more export formats, and a more capable report designer. When you use SSRS for developing Microsoft Dynamics AX 2012 reports it results in the following benefits:

Increased use of the Microsoft technology stack
Business logic is kept in Microsoft Dynamics AX 2012
Smooth transition for X++ and C# developers to the new reporting architecture

All the standard reports that are provided with Microsoft Dynamics AX 2012 use the SSRS reporting framework. When you upgrade from an earlier version of Microsoft Dynamics AX, legacy reports and cubes are not upgraded. Reports that use the X++ reporting framework and existing SQL Server Reporting Services reports will be copied to the Microsoft Dynamics AX 2012 system. However, they will not be upgraded.

Installation

Microsoft SQL Server Reporting Services (SSRS) is installed as part of the SQL Server installation. Microsoft Dynamics AX 2012 employs Reporting Services Extensions to make use of SSRS capabilities. The Reporting Services Extensions are installed and configured as part of the Business Intelligence installation during the Microsoft Dynamics AX 2012 installation. Refer to the Microsoft Dynamics AX 2012 Installation Guide for more information. The Business Intelligence components can be installed separately if they were not installed during the Microsoft Dynamics AX 2012 installation.

Prerequisites

Before you install the Business Intelligence components, check that the prerequisites are met. Run the Microsoft Dynamics AX prerequisite validation utility to determine whether a computer meets the requirements to install a Microsoft Dynamics AX component.

Specific requirements for installing Reporting Services extensions include the following:

Microsoft SQL Server 2008 Reporting Services
Microsoft SQL Server 2008 R2, Cumulative Update 3 or a later version
Windows PowerShell 2.0

Refer to the Microsoft Dynamics AX 2012 System Requirements document for more information.

To install the Reporting Services Extensions, you must be a member of the System administrator role in Microsoft Dynamics AX. Reporting Services extensions must be installed on the computer that is running SSRS.

Installation

To install the Business Intelligence components, run the Microsoft Dynamics AX 2012 setup. Refer to the Microsoft Dynamics AX 2012 Installation Guide for more information. The installation setup wizard will perform the following actions:

Verify that prerequisite software is installed on the server
Install SQL Server Reporting Services extensions
Deploy the default reports that are included with Microsoft Dynamics AX 2012
Configure SQL Server Analysis Services

If the default reports are not deployed during the installation, they can be deployed later using Windows PowerShell.

Deploy Default Reports

Microsoft Dynamics AX 2012 includes default reports that must be deployed to Microsoft SQL Server before they can be used. The default reports can be deployed during the Reporting services installation by using the Microsoft Dynamics AX 2012 setup.

If you did not deploy the reports when you installed the Reporting Services extensions, you can deploy them by using Windows PowerShell. The following procedures can help you deploy the reports:

Open PowerShell and view a list of reports
Filter the list of reports
Deploy the reports

Open PowerShell and View a List of Reports

Complete the following procedure to open PowerShell and view a list of the reports that are included with Microsoft Dynamics AX 2012.

Open PowerShell as an administrator by clicking StartAdministrative Tools.
Right-click the Microsoft Dynamics AX 2012 Management Shell option.
Click Run as administrator.
Retrieve default reports that are included with Microsoft Dynamics AX, and store the list in a local variable using the following command: $reports = Get-AXReport -ReportName *

View the list of reports by entering the following command: $reports

Figure 2.2 Windows PowerShell

Filter the List of Reports

To modify and filter the list of default reports use the following commands:

To modify the list so that only the Name and ChangedDate fields are displayed, enter the following command: $reports | Select-Object Name,ChangedDate
To filter the list so that only specific reports are listed, enter keywords or report names. For example, to filter the list so that only reports that contain the word CustTrans are listed, enter the following command: $reports | Select-Object Name,ChangedDate | Where { $_.Name -like "CustTrans*" }

Deploy the Reports

After you have retrieved a list of reports, you can deploy them. The Publish-AXReport command is used to deploy the reports. The following examples show how to use this command:

To deploy a specific report, enter the name of the report. For example, to deploy the CustTransList report, enter the following command: Publish-AXReport -ReportName CustTransList
To deploy two or more specific reports, enter the names of the reports. For example, to deploy the CustTransList and CustTransOpenPerDate reports, enter the following command: Publish-AXReport -ReportName CustTransList, CustTransOpenPerDate
To deploy all reports, enter the following command: Publish-AXReport -ReportName *

Report Development Tools

Several tools are available to help you create reports, configure data sources, and deploy and display report data in Microsoft Dynamics AX 2012. Integration between these tools makes report development more streamlined than ever and reduces the time that is required to create and modify reports.

MorphX and the Application Object Tree

MorphX is the integrated development environment that is included in Microsoft Dynamics AX 2012. The programming language X++ is used within the MorphX development environment. X++ is an object-oriented programming language that is used to develop the objects that are used in the Microsoft Dynamics AX application.

The Application Object Tree (AOT) holds all the application objects that are used to build Microsoft Dynamics AX. The AOT contains objects that will be used to collect data for a report and objects that give users access to display reports.

The objects in the AOT are grouped into nodes according to their type. The AOT objects include:

Tables - made of fields that contain business data
Views - grouped data from multiple tables
Classes - source code for application logic
Visual Studio Projects - report definitions copied from Visual Studio
Queries - select data to display in a form or in a report
Menu items - used to access reports and forms
Menus - groups of menu items

Other objects in the AOT are used to manage application processes and for various other functions.

When you develop a report, objects from the AOT will be used to access data and to display the reports.

Visual Studio

Microsoft Visual Studio is an integrated development environment that is used to develop applications. Development in Visual Studio can be done using Visual Basic, Visual C#, Visual C++, and other programming languages. Whereas many development tasks in Microsoft Dynamics AX 2012 can be completed by using X++ in the MorphX development environment or the Visual Studio development environment, report development can only be done in Visual Studio.

Visual Studio consists of several features that support development of Microsoft Dynamics AX applications. Visual Studio Tools for Microsoft Dynamics AX is a collection of tools and functionality that allow you to manage code development for Microsoft Dynamics AX. Visual Studio Tools integrates Microsoft Dynamics AX with Visual Studio. Objects that are developed in Visual Studio can be saved to the AOT and AOT objects can be used in Visual Studio development projects.

Figure 2.3 Visual Studio

Application Explorer is a tool in Visual Studio that provides a view into the AOT in Microsoft Dynamics AX. In Application Explorer, you can view and debug X++ code in the AOT.

Solution Explorer displays a collection of items for a specific solution. A solution contains one or more projects, and a project contains one or more items. From Solution Explorer, you can open files for editing, add new files to a project, and view solution, project, and item properties.

SQL Server Reporting Services

SQL Server Reporting Services (SSRS) provides tools and services to help you create, deploy, and manage reports. SSRS also provides programming features that allow you to extend and customize your reporting functionality.

Reporting Services is a server-based reporting platform that provides comprehensive reporting functionality for a variety of data sources. Reporting Services includes a complete set of tools for you to create, manage, and deliver reports, and provides APIs that enable developers to integrate or extend data and report processing in custom applications. Reporting Services tools work within the Microsoft Visual Studio environment and are fully integrated with SQL Server tools and components.

With Reporting Services, you can create interactive, tabular, graphical, or free-form reports from relational, multidimensional, or XML-based data sources. You can publish reports or access reports on demand. Reporting Services also enables you to create customized reports based on predefined models, and to interactively explore data within the model. Additionally, you can select from a variety of viewing formats or export reports to other applications. The reports that you create can be viewed over a web-based connection or as part of a Microsoft Windows application or SharePoint site.

SQL Server Analysis Services

Microsoft SQL Server Analysis Services (SSAS) delivers online analytical processing (OLAP) and data mining functionality for business intelligence applications. Analysis Services supports OLAP by letting you design, create, and manage multidimensional structures that contain data that is aggregated from other data sources, such as relational databases. For data mining applications, Analysis Services lets you design, create, and visualize data mining models that are constructed from other data sources by using a wide variety of industry-standard data mining algorithms

Analysis Services helps you analyze large quantities of data. With it, you can design, create, and manage cubes that contain detail and aggregated data from multiple data sources.

For more information, see Chapter 3: SQL Server Analysis Services of this manual.

Business Intelligence Development Studio

Business Intelligence Development Studio is Microsoft Visual Studio 2008 with additional project types that are specific to SQL Server business intelligence. Business Intelligence Development Studio is the primary environment that you will use to develop business solutions that include Analysis Services and Integration Services projects. Each project type supplies templates for creating the objects that are required for business intelligence solutions, and provides a variety of designers, tools, and wizards to work with the objects.

Develop Reports

All reports in Microsoft Dynamics AX 2012 are developed by using Visual Studio. Default reports that are installed with Microsoft Dynamics AX 2012 can be customized and new reports can be created by using Visual Studio.

Create New Reports

To create a new report, you must create a report project in Visual Studio, add a new report and report design to the project, and then define data and parameters. The report project is then imported in to Microsoft Dynamics AX 2012 and then the report is deployed to the Report Server. A menu item can then be created to launch the report, or the report can be added to a Role Center or Enterprise Portal web part.

Create a Reporting Project

To create a new report, begin by creating a new reporting project in Visual Studio. Follow these steps to create a new reporting project:

In Visual Studio, select FileNewProject from the menu.
Select Microsoft Dynamics AX for the project template type and then select the report model template.
Enter a project and solution name.

Verify the path for the location where the project file will be saved.
Click OK.

Figure 2.4 Visual Studio New Project Dialog Box

To add a new report to the project, select the Project in Solution Explorer in Visual Studio. Right-click the project and select Add > Report.

Instead of creating a new Reporting Project, you might want to use an existing, pre-defined Reporting Project. Follow these steps to add an existing Reporting Project from another solution:

Select the solution in Solution Explorer in Visual Studio. Right-click the solution and then click Add > Existing Project.
Select the reporting project to add, and then click Open.

When you add an existing reporting project from another solution, the project files are not copied to the directory that contains the files for the solution. Instead, the project files from the original solution are used.

You can also add an existing report from the AOT that is displayed in the Application Manager in Visual Studio. Follow these steps to add an existing report to the current project:

In Visual Studio, open a solution and select a project in the Solution Explorer.
In Application Explorer, expand the SSRS Reports node and then expand the Reports node. Find the report that you want to add to the project.
Right-click the report and select Add to Project. The report is added to the current project.

Define a Data Source

To add data for your reports, a data source must be added to the report project. An existing data source is used to connect to the Microsoft Dynamics AX 2012 application database. When the predefined Microsoft Dynamics AX data source is used, all data access requests go through the MorphX security system.

If you want to use a database other than the Microsoft Dynamics AX 2012 application database, you must define a new data source to specify connection information and a provider type for that database. Follow these steps to define a report data source:

In Visual Studio Solution Explorer, right-click the reporting project that you want to add a report data source to, select Add, and then click Report Data source. This is a model that contains a single report data source.
Type a name for the model. The file name extension of a model is .moxl.

Figure 2.5 Add New Data Source in Visual Studio

Define a Dataset

A report dataset selects data from the data source to be displayed in a report. You can use a query, stored procedure, enum, or a data method to select data.

Double-click the report in the project in Visual Studio Solution Explorer to open the report in the Model Editor.
In the Model Editor, expand the node for the report that you want to work with.
Right-click the Datasets node, and then click Add Dataset.
Select the node for the dataset.

In the Properties window, set the following properties.

Data Source - Select the Microsoft Dynamics AX data source, or a new data source that was created.
Data Source Type - Select the type of data source that you will use to select data. If the existing Microsoft Dynamics AX data source is selected, you can choose from the following:

Query - use a query from the AOT
Business Logic - use a data method from the report project
Report Data Provider - use the results of an X++ class
AX Enum Provider - use an enum from the AOT

Name - Enter a name for the dataset.
Query - Select a query, method, class or enum to use to retrieve the data from the data source.
Default Layout - default layout to be used for Auto Design reports. Dynamic Filters - enables filters to be used in the report dialog.

Figure 2.6 Dataset Properties in Visual Studio

Define a Data Region

A data region is an area in a report that displays data from a dataset. To add a data region to an auto design report, drag a dataset onto a report design. A data region can also be added manually. Follow these steps to add a data region to an auto design report: