Walkthrough: Creating a Report from an External Data Source (Visual Studio Reporting Tools for Microsoft Dynamics AX)

Dynamics AX 2009

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

To complete this walkthrough, you will need:

  • Microsoft Dynamics AX SDK

  • Microsoft SQL Server 2005

  • Microsoft Visual Studio 2008

  • Microsoft Dynamics AX Reporting Tools for Microsoft Visual Studio

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

  1. Open SQL Server Management Studio.

  2. When prompted to connect to a server, be sure to specify Database Engine for the Server type field.

  3. In SQL Server Management Studio, click the New Query button to display the query editor window.

  4. Copy the following SQL statements into the query editor.

    CREATE DATABASE AXSampleData;
    GO
    USE AXSampleData
    CREATE TABLE [dbo].[Item](
        [ItemID] [int] NOT NULL,
        [Name] [nvarchar](50) NULL,
        [Description] [nvarchar](50) NULL,
        [Cost] [numeric](18, 2) NULL,
        [SellingPrice] [numeric](18, 2) NULL,
        [Status] [int] NULL)
    INSERT INTO Item VALUES (1734, 'Clamp', 'Workbench Clamp', 12.48, 17.99, 0);
    INSERT INTO Item VALUES (1258, 'Hammer', '10 oz Hammer', 7.25, '11.99', 0);
    INSERT INTO Item VALUES (2783, 'Hammer', '12 oz Hammer', 9.50, 15.89, 2);
    INSERT INTO Item VALUES (1983, 'Saw', 'Wooden Handle Saw', 7.89, 11.99, 1);
    INSERT INTO Item VALUES (4920, 'Nails', '10 oz Flat Top Nails', 3.45, 4.99, 0);
    INSERT INTO Item VALUES (6728, 'Screwdriver', 'Standard Screwdriver', 2.75, 3.99, 1);
    INSERT INTO Item VALUES (9283, 'Nails', 'Roofing Nails 5 lbs', 12.45, 15.99, 0);
    INSERT INTO Item VALUES (4829, 'Tape Measure', '25 foot Tape Measure', 12.87, 16.99, 2);
    INSERT INTO Item VALUES (2893, 'Nails', 'Finish Nails', 3.90, 5.59, 1);
    
  5. Click Execute to execute the SQL statements.

  6. Verify that the database and table have been added to your server.

  7. Close SQL Server Management Studio.

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

  1. Open Microsoft Visual Studio.

  2. On the File menu, click New, and then click Project. The New Project dialog box displays.

  3. In the Project Types pane, click the Visual C# node, and in the Templates pane, click Dynamics AX Reporting Project.

  4. In the Name box, type SampleExternalDataSourceReport, and in the Location box, type a location.

  5. 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

  1. 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.

  2. In the dialog box, select Shared Library, and then click OK.

  3. In Solution Explorer, right-click the SampleExternalDataSourceReport project, and then click Add Reference.

  4. 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

  1. In Solution Explorer, right-click the SampleExternalDataSourceReport project, point to Add, and then click New Item.

  2. In the Add New Item dialog box, select Report Data Source.

  3. Type AXSampleData.moxl for the name.

  4. Click Add. The model for the data source is added to the project. The model displays in Model Editor.

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

  6. In the Properties window, specify the following values.

    Property

    Value

    Connection String

    Server=[YourServerName];Database=AXSampleData;Integrated Security=SSPI

    NoteNote
    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

  1. In Solution Explorer, double-click Report1.moxl to open it in Model Editor.

  2. In Model Editor, right-click the Report1 node, and then click Rename.

  3. Type ItemListReport as the name.

  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

    AXSampleData

    Data Source Type

    Query

    Default Layout

    TopDownList

    Name

    Item

    Query

    Select ItemID, Name, Description, Cost, SellingPrice, Status from Item

  8. In Model Editor, select the Cost node, and in the Properties window, set the Format String property to Currency.

  9. Select the SellingPrice node, and in the Properties window, set the Format String property to Currency.

  10. 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.

  11. Expand the AutoDesign1 node, expand the node for the list data region, and then expand the Data node.

  12. 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.

  13. In the Edit Expression dialog box, click Labels.

  14. 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

  15. 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.

  16. In Model Editor, select the AutoDesign1 node.

  17. In the Properties window, set the Layout Template property to ReportLayoutStyleTemplate and type Inventory items for the Title property.

  18. In Model Editor, expand the AutoDesign1 node, and then select the list data region.

  19. In the Properties window, set the Style Template property to ListStyleTemplate.

  20. To preview the layout of the report, right-click the AutoDesign1 node, and then click Preview.

Community Additions

ADD
Show: