Walkthrough: Creating a Report from an External Data Source (Visual Studio Reporting Tools for Microsoft Dynamics AX)
A data source is a facility for storing data. You can use the predefined Dynamics AX data source, which connects to the Microsoft Dynamics AX application database. Or, you can define an external data source to retrieve data from a different location. In this walkthrough, you will create a report from data that is stored in a separate SQL database that you create. In your report model, you will define an external data source that specifies the connection information for the database so that you can retrieve data from that database to display in your report.
This walkthrough illustrates the following tasks:
-
Creating report data
-
Creating a reporting project
-
Defining an external data source
-
Creating a report
-
Modifying the look of a report
You will start by creating a SQL database that will hold the data for your report. You will execute a set of SQL statements to create a database and a table that is populated with data.
|
To create the data |
|---|
|
Next, you will create a reporting project in 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 that contains 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
-
Open Microsoft Visual Studio.
-
On the File menu, click New, and then click Project. The New Project dialog box displays.
-
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 SampleExternalDataSourceReport, and in the Location box, type a location.
-
Click OK.
A reporting project contains a model by default. A model is a file with 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
-
On 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 SampleExternalDataSourceReport 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. Next, you will define a new data source in your model so that you can access the data in the AXSampleData database.
To define the data source for the report
-
In Solution Explorer, right-click the SampleExternalDataSourceReport project, point to Add, and then click New Item.
-
In the Add New Item dialog box, select Report Data Source.
-
Type AXSampleData.moxl for the name.
-
Click Add. The model for the data source is added to the project. The model displays 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
Server=[YourServerName];Database=AXSampleData;Integrated Security=SSPI
Note Insert the name of your server for [YourServerName]. It must be the name of the server that contains the database that you created in the previous procedure. In this statement, Security Support Provider Interface (SSPI) is using Microsoft Windows user credentials for authentication. You may need to use a different authentication mode depending upon how you have set up your SQL Server instance.Name
AXSampleData
Provider
SQL
Next, you will create a report to display item data. You will start by creating a query for the report dataset using a TSQL statement. After you define the dataset, you will format the data. The status of an item takes on an integer value between 0 and 2. The value 0 means that the item is In Stock. The value 1 means that the item is On Order. The value 2 means that the item is Back Ordered. You will add an expression to the report to convert the integer value to text so that it is easy to read. You will also format the cost and selling price as currency. The following procedure explains how to create the report.
To create the 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 ItemListReport 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
AXSampleData
Data Source Type
Query
Default Layout
TopDownList
Name
Item
Query
Select ItemID, Name, Description, Cost, SellingPrice, Status from Item
-
In Model Editor, select the Cost node, and in the Properties window, set the Format String property to Currency.
-
Select the SellingPrice node, and in the Properties window, set the Format String property to Currency.
-
Drag the Item dataset onto the Designs node for the report. This creates an auto design for the report based on the data in the dataset.
-
Expand the AutoDesign1 node, expand the node for the list data region, and then expand the Data node.
-
Select the Status node, and in the Properties window, click <Expression…> from the drop-down menu for the Expression property.
The Edit Expression dialog box displays. In this dialog box, you will create labels for item status, and then enter an expression so that the label text displays for the item status.
-
In the Edit Expression dialog box, click Labels.
-
Create labels for the item status. To create a label, double-click <Add Label…>, enter the name and text for the label, and then click OK in the Add Label dialog box. Repeat this process for each label. Do not click OK in the Edit Expression window as you will add an expression in the next step. The following table specifies the name and the text for each of the labels.
Label name
Label text
ItemListReport_ItemStatusInStock
In Stock
ItemListReport_ItemStatusOnOrder
On Order
ItemListReport_ItemStatusBackOrdered
Back Ordered
-
Type the following expression =Switch(Fields!Status.Value = 0, Labels!ItemListReport_ItemStatusInStock.Value, Fields!Status.Value = 1, Labels!ItemListReport_ItemStatusOnOrder.Value, Fields!Status.Value = 2, Labels!ItemListReport_ItemStatusBackOrdered.Value), and then click OK.
-
In Model Editor, select the AutoDesign1 node.
-
In the Properties window, set the Layout Template property to ReportLayoutStyleTemplate and type Inventory items for the Title property.
-
In Model Editor, expand the AutoDesign1 node, and then select the list data region.
-
In the Properties window, set the Style Template property to ListStyleTemplate.
-
To preview the layout of the report, right-click the AutoDesign1 node, and then click Preview.