Walkthrough: Displaying Cube Data in a Report
In this walkthrough, you create a report that displays data from an Analysis Services cube. You use a column chart report to display the data.
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
To complete this walkthrough, you will need:
-
Microsoft Dynamics AX with sample data
Note The SalesAnalysis cube created in Walkthrough: Creating a Cube.
-
Microsoft SQL Server 2005
-
SQL Server Business Intelligence Development Studio
-
Analysis Services extensions for Microsoft Dynamics AX
-
Microsoft Visual Studio 2008
-
Microsoft Dynamics AX Reporting Tools for Microsoft Visual Studio
When you create a reporting project, you can choose from two project templates: Visual Basic and Visual C#. In this walkthrough, you will use the Visual C# template. After you have created a project, you will add another project to your solution so that you can reference several predefined layout and style templates that you will use to define the layout of your report. These templates are the standard templates for Microsoft Dynamics AX reports.
To create a reporting project
-
From the File menu in Microsoft Visual Studio, point to New, and then click Project. The New Project dialog box is displayed.
-
In the Project Types pane, click the Visual C# node, and in the Templates pane, click Dynamics AX Reporting Project.
-
In the Name box, type SampleCubeReport, and in the Location box, type a location.
-
Click OK.
A reporting project contains a model by default. A model is a file that has the .moxl file name extension. When a reporting project first opens in Microsoft Visual Studio, the model automatically displays in Model Editor. It contains a default report named Report1.
To reference the project that contains the standard layout and style templates
-
From the File menu, point to Add and then click Existing Project from Dynamics AX.
A dialog box displays where you can select a project that currently exists under the Report Library node in the AOT.
-
In the dialog box, select Shared Library, and then click OK.
-
In Solution Explorer, right-click the SampleCubeReport project, and then click Add Reference.
-
In the Projects tab, select SharedLibrary, click Add, and then click OK.
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 Sales Analysis cube.
To define a data source for the Analysis Services database
-
In Solution Explorer, right-click the SampleCubeReport project, point to Add, and then click New Item.
-
In the Add New Item dialog box, select Report Data Source.
-
Type SalesAnalysisOLAPData.moxl for the name.
-
Click Add. The model for the data source is added to the project. The model opens in Model Editor.
-
In Model Editor, select the node for the data source.
-
In the Properties window, specify the following values.
Property
Value
Connection String
Data Source=[YourServerName];Initial Catalog=SalesAnalysis;Integrated Security=SSPI
Note Insert the name of your server for [YourServerName]. It must be the name of the server that contains the database for the Sales Analysis cube. In this statement, Security Support Provider Interface (SSPI) is using Microsoft Windows user credentials for authentication.Name
SalesAnalysisOLAPData
Provider
Olap
Next, create a report to display data from the sales Analysis cube. Create a dataset based on a multidimensional expression (MDX) query in order to retrieve data from the Sales Analysis cube. Create a column chart report based on the dataset and then apply layout and style templates to the report.
To create a report
-
In Solution Explorer, double-click Report1.moxl to open it in Model Editor.
-
In Model Editor, right-click the Report1 node, and then click Rename.
-
Type SalesRevenueChart as the name.
-
Expand the node for the report if it is not already expanded.
-
Right-click the Datasets node, and then click Add Dataset.
-
Select the node for the dataset.
-
In the Properties window, specify the following values.
Property
Value
Data Source
SalesAnalysisOLAPData
Data Source Type
Query
Default Layout
ColumnChart
Name
SalesRevenue
Query
SELECT {[Measures].[Revenue]} ON COLUMNS, {[Time].[Calendar 2006], [Time].[Calendar 2007], [Time].[Calendar 2008]} on ROWS FROM "Sales Analysis"
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. -
In Model Editor, select the Year field.
-
In the Properties window, make sure that the Field Type property is set to Grouping and the Grouping Type property is set to Category.
-
In Model Editor, select the Revenue field.
-
In the Properties window, make sure that the Field Type property is set to Data.
-
In Model Editor, drag the SalesRevenue 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.
-
Expand the Data node, and select the Revenue field.
-
In the Properties window, set the Format String property to Currency, and then select <Expression…> for the Expression property. Expression Editor is displayed.
-
In Expression Editor, type =CDec(Fields!Revenue.Value) for the expression.
The MDX query you supplied when defining the dataset returns the value of the Revenue field as a string. The CDec function converts the string to a decimal so that it can be displayed as numeric data in the column chart report.
-
Click OK to close Expression Editor.
To apply layout and style templates
-
In Model Editor, select the AutoDesign1 node.
-
In the Properties window, set the LayoutTemplate property to ReportLayoutStyleTemplate. Also, type Sales Revenue for the Title property.
-
In Model Editor, expand the AutoDesign1 node, and then select the node for the chart data region.
-
In the Properties window, set the Style Template property to ColumnChartStyleTemplate. Also, delete the text for the Title property so that it does not display a title for the data region.
-
In Model Editor, right-click the AutoDesign1 node, and then click Preview to preview the report.
To deploy the report
-
Open the SampleCubeReport solution in Visual Studio. On the build menu, click Deploy SampleCubeReport.
You can display 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 Accounts Receivable module.
To display the report in a menu
-
On the build menu, click Build SampleCubeReport.
-
Right-click the SampleCubeReport project and then click Save to AOD. Close Visual Studio.
-
Open Microsoft Dynamics AX. If it was already open, close and re-open it to refresh the metadata.
-
Open the AOT.
Note A new SampleCubeReport node is now under the Report Libraries node.
-
Expand the Menu Items node, right-click Output, and then click New Menu Item.
-
On the properties sheet for the new menu item, set the following values:
Property
Value
Name
SampleCubeReport
Label
Sales Revenue
Object Type
SQLReportLibraryReport
Object
SampleCubeReport.SalesRevenueChart.AutoDesign1
-
Save the menu item.
Note Under the Output node, right-click SampleCubeReport to preview the report.
-
Open a second AOT. Expand the Menus node, expand the Cust node, and then drag the SampleCubeReport menu item from the first AOT onto the Reports node in the second AOT.
-
Save the changes and restart the client. Navigate to the Accounts receivable module and then click Sales Revenue.
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
-
Navigate to the CEO role center that is located at http://<server>/sites/DynamicsAx/Enterprise%20Portal/RoleCenterCEO.aspx.
-
On the Site Actions menu, click Edit Page. Locate the Footer section, and then click Add Web Part
-
In the Add Web Parts dialog box, select Dynamics Report Server Report, and then click Add.
-
On the Edit menu for the Web part, click Modify Shared Web Part. The properties for the Web part is displayed.
-
For the Report name property, select Sales Revenue.
-
For the Toolbar Size property, select None.
-
For the Should the Web Part have a fixed height? property, select Yes and set the height to 6 Inches. Click OK.
-
Click Exit Edit Mode to save the changes that you made to the page.