Walkthrough: Displaying Cube Data in a Report

  • Article

Important

This content is archived and is not being updated. For the latest documentation, see Microsoft Dynamics 365 product documentation. For the latest release plans, see Dynamics 365 and Microsoft Power Platform release plans.

Applies To: Microsoft Dynamics AX 2012 R2, Microsoft Dynamics AX 2012 Feature Pack, Microsoft Dynamics AX 2012

In this walkthrough, you will create a report that displays data from an analysis cube.

This walkthrough illustrates the following tasks:

  • Creating a reporting project

  • Defining a data source to connect to an Analysis Services database

  • Creating a report to display cube data

  • Accessing the report through a menu item

  • Displaying the report in an Enterprise Portal Role Center

Prerequisites

To complete this walkthrough, you will need:

  • The default Analysis Services project, deployed and processed

  • Visual Studio 2010

  • Reporting Services extensions for Microsoft Dynamics AX

  • Visual Studio Tools

Creating a Reporting Project

Use the Report Model template in Visual Studio to create a new reporting project. For more information about reporting projects, see Reporting Project Overview.

To create a reporting project

  1. Start Visual Studio. If User Account Control (UAC) is active, be sure you start Visual Studio with administrative privileges.

  2. In Visual Studio, click File > New > Project. The New Project dialog box is displayed.

  3. In the Installed Templates section, click the Microsoft Dynamics AX and then click Report Model.

  4. In the Name box, type SampleCubeReport, and in the Location box, specify the location where you want to save the project.

  5. Click OK.

Defining a Data Source to Connect to an Analysis Services Database

A data source contains information about a connection to a database. This includes information such as the server name, the database name, and user credentials. The following procedure explains how to define a data source so that you can access the data from the Analysis Services database that contains the data for the General ledger cube.

To define a data source for the Analysis Services database

  1. In Solution Explorer, right-click the SampleCubeReport project, point to Add, and then click Report Datasource. The model opens in Model Editor.

  2. In Model Editor, select the node for the data source.

  3. In the Properties window, specify the following values:

    Property

    Value

    Connection String

    Data Source=[YourServerName];Initial Catalog=Dynamics AX;Integrated Security=SSPI

    Note

    Insert the name of your server for [YourServerName]. It must be the name of the server that contains the Analysis Services database that has the General ledger cube. The Initial Catalog attribute specifies the name of the Analysis Services database to connect to. Dynamics AX is the default name of the Analysis Services database. The Integrated Security attribute specifies that Support Provider Interface (SSPI) is using Microsoft Windows user credentials for authentication.

    Name

    ProfitAnalysisOLAPData

    Provider

    Olap

Creating a Report to Display Cube Data

Next, create a report to display data from the General ledger cube. This process involves creating a dataset based on a multidimensional expression (MDX) query to retrieve data from the General ledger cube, creating a column chart report based on the dataset, and then applying layout and style templates to the report.

To create a report

  1. In Solution Explorer, right-click SampleCubeReport, point to Add, and then click Report.

  2. Select the Report1 node.

  3. In the Properties window, type ProfitChart as the value for the Name property.

  4. Expand the node for the report if it is not already expanded.

  5. Right-click the Datasets node, and then click Add Dataset.

  6. Select the node for the dataset.

  7. In the Properties window, specify the following values:

    Property

    Value

    Data Source

    ProfitAnalysisOLAPData

    Data Source Type

    Query

    Default Layout

    ColumnChart

    Name

    Profit

    Query

    		 
    SELECT {[Measures].[General ledger gross profit - accounting currency]} ON COLUMNS,
{[Transaction date].[Year].&[2008-01-01T00:00:00],[Transaction date].[Year].&[2009-01-01T00:00:00],
[Transaction date].[Year].&[2010-01-01T00:00:00]} ON ROWS
FROM "General ledger cube"

    Note

    You may have to modify the MDX query depending on the data that is available in your cube. For example, you may not have data for all the years listed in the query. This query retrieves the profit for 2008, 2009, and 2012. You can use SQL Server Management Studio to help you create an MDX query. In Object Explorer, right-click the database, point to New Query, and then click MDX. You can use the metadata and functions to help you write the query.

  8. In Model Editor, expand the Fields group for the Profit dataset. Select the Year field.

  9. In the Properties window, make sure that the Field Type property is set to Grouping and the Grouping Type property is set to Category.

  10. In Model Editor, select the Measures_General_ledger_gross_profit_ field.

  11. In the Properties window, make sure that the Field Type property is set to Data. Type Gross profit in the Caption property.

  12. In Model Editor, drag the Profit dataset onto the Designs node for the report.

    An auto design is generated for the report. Expand the node for the auto design and notice that it contains a category based on the Year field.

To apply layout and style templates

  1. In Model Editor, select the AutoDesign1 node.

  2. In the Properties window, set the LayoutTemplate property to ReportLayoutStyleTemplate. Type Gross profit for the Title property.

  3. In Model Editor, expand the AutoDesign1 node, and then select the ProfitXYChart node.

  4. In the Properties window, set the Style Template property to ColumnChartStyleTemplate. Delete the text for the Title property so that it does not display a title for the data region.

  5. In Model Editor, right-click the AutoDesign1 node, and then click Preview to preview the report. Close the preview and save your changes.

To deploy and save the report

  1. On the Build menu, click Deploy SampleCubeReport. After a few moments, the report will be deployed to the Reports Server that has been set up for Microsoft Dynamics AX.

  2. Right-click the SampleCubeReport project and then click Add SampleCubeReport to AOT. Close Visual Studio.

Accessing the Report through a Menu Item

You can display a link to the report in a Microsoft Dynamics AX menu by creating an output menu item that generates the report. Then you can add the menu item to the General ledger module.

To display the report in a menu

  1. Open the development workspace in Microsoft Dynamics AX. If it was already open, close and re-open it to refresh the metadata.

  2. Open the AOT. Expand the Menu Items node, right-click Output, and then click New Menu Item.

  3. On the properties sheet for the new menu item, set the following values:

    Property

    Value

    Name

    SampleCubeReport

    Label

    Gross profit

    Object Type

    SSRSReport

    Object

    ProfitChart

  4. Save the menu item.

    Note

    Under the Output node, right-click SampleCubeReport and then click Open to preview the report.

  5. Open a second AOT. Expand the Menus node, expand the GeneralLedger node, and then drag the SampleCubeReport menu item from the first AOT onto the Reports node in the second AOT.

  6. Save the changes and restart the Microsoft Dynamics AX client. Navigate to the General ledger module and then click Gross profit to display the report.

Displaying the Report in an Enterprise Portal Role Center

You can add the report to an Enterprise Portal Role Center page by adding a web part to the Role Center.

To display the report in an Enterprise Portal Role Center

  1. With a web browser, navigate to the CEO role center that is located at http://<server>/sites/DynamicsAx/Enterprise%20Portal/RoleCenterCEO.aspx.

  2. On the Site Actions menu, click Edit Page. Locate the Footer section, and then click Add a Web Part

  3. Select Microsoft Dynamics AX, click Report, and then click Add.

  4. On the drop-down menu for the web part, click Edit Web Part. The properties for the web part are displayed.

  5. In the Select a report list, select Gross profit.

  6. For the Toolbar size and Report drillthrough target toolbar size properties, select None.

  7. For the Should the Web Part have a fixed hight? property, select Yes and set the height to 6 Inches. Click OK.

  8. Click Stop Editing. The page will update and display the report in the new web part you added.

See also

Development Tasks for Analytics

Development Tasks for Reporting

Reports in Enterprise Portal Overview