Walkthrough: Creating a Drillthrough Report (Visual Studio Reporting Tools for Microsoft Dynamics AX)

Dynamics AX 2009

In this walkthrough you will create a drillthrough report. A drillthrough report is a report that a user opens by clicking a linked item in another report. It allows users to drill through to additional data.

This walkthrough illustrates the following tasks:

  • Defining a query

  • Creating a reporting project

  • Creating a top-level report and a drillthrough report

  • Adding a report drill through action

To complete this walkthrough, you will need:

  • Microsoft Dynamics AX with sample data

    NoteNote

    This walkthrough uses the AssetTable table. In order to view data in the reports, this table must be populated with data.


  • Microsoft SQL Server 2005

  • Microsoft Visual Studio 2008

  • Microsoft Dynamics AX Reporting Tools for Microsoft Visual Studio

There are several ways to retrieve data for reports. In this walkthrough, you will use a query that is defined within the Microsoft Dynamics AX development environment. This query will be based on the AssetTable table. Both the top-level report and the drillthrough report will be based on the same query; however, the drillthrough report will display more fields to show detailed information about a selected asset. The following procedure explains how to define the query that will be used to retrieve data for the reports.

To define a query

  1. Open Microsoft Dynamics AX.

  2. In the AOT, right-click the Queries node, and then click New Query.

  3. Right-click the node for the query, click Rename, and then type Assets.

  4. Expand the node for the Assets query.

  5. Right-click the Data Dictionary node, and then click Open New Window.

  6. In the new window, expand the Tables node.

  7. Locate the AssetTable table and drag it onto the Data Sources node for the Assets query.

  8. Save the query.

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 reports. 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, point to 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 SampleDrillthroughReport. 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 SampleDrillthroughReport project, and then click Add Reference.

  4. In the Projects tab, select SharedLibrary, click Add, and then click OK.

Next, you will create two reports: a top-level report and a drillthrough report. The top-level report will be a basic list of all assets in the AssetTable table. The drillthrough report will be a more detailed report that will display information about the asset that is selected in the top-level report. You will add a parameter and filter to the drillthrough report. The parameter will be used to pass data from the top-level report to the drillthrough report. The filter will filter the data in the drillthrough report based on the value of the parameter. The following procedures explain how to create the reports.

To create a top-level report

  1. In Solution Explorer, right-click Report1.moxl, and then click Rename.

  2. Type AsseList.moxl as the name.

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

  4. Type AssetList as the name.

  5. Expand the AssetList node if it is not already expanded.

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

  7. Select the node for the dataset.

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

    Property

    Value

    Data Source

    Dynamics AX

    Data Source Type

    Query

    Default Layout

    Table

    Name

    Assets

    Query

    Click the ellipsis button (…). A dialog box displays where you can select a query that is defined in the AOT and identify the fields that you want to use. Select the Assets query, and then select the AssetId, and Name fields. Be sure the All check box is not selected. When you are finished selecting the fields, click OK.

  9. In Model Editor, select the Assets node and drag it onto the Designs node. An auto design named AutoDesign1 is created for the report.

  10. Select the AutoDesign1 node.

  11. In the Properties window, set the LayoutTemplate property to ReportLayoutStyleTemplate and type Assets for the Title property.

  12. In Model Editor, expand the AutoDesign1 node, and then select the node for the table data region.

  13. In the Properties window, set the style template to TableStyleTemplate and type Asset list for the Title property.

To create a drillthrough report

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

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

  3. Type AssetDetails.moxl as the name, and then click Add. The model displays in Model Editor with a report named AssetDetails.

  4. Expand the AssetDetails node 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

    Dynamics AX

    Data Source Type

    Query

    Default Layout

    TopDownList

    Name

    Details

    Query

    Click the ellipsis button (…). A dialog box displays where you can select a query that is defined in the AOT and identify the fields that you want to use. Select the Assets query, and then select the AssetId, AssetReplaceCost, InsuredValue, Make, Model, Name, SerialNum, and TechInfo1 fields. Be sure the All check box is not selected. When you are finished selecting the fields, click OK.

  8. In Model Editor, select the node for the AssetReplaceCost field, and in the Properties window, set the FormatString property to Currency.

  9. In Model Editor, select the node for the InsuredValue field, and in the Properties window, set the FormatString property to Currency.

  10. In Model Editor, select the node for the Name field and then click the Move up button on the Model Editor toolbar. Continue this process to move the Name field directly below the AssetId field.

    NoteNote

    You can also press ALT+UP ARROW or ALT+DOWN ARROW to move an element up or down in a collection in Model Editor. For more information, see Keyboard Shortcuts in Model Editor (Visual Studio Reporting Tools for Microsoft Dynamics AX).


  11. Select the Details node and drag it onto the Designs node. An auto design named AutoDesign1 is created for the report.

  12. Select the AutoDesign1 node.

  13. In the Properties window, set the LayoutTemplate property to ReportLayoutStyleTemplate and type Assets for the Title property.

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

  15. In the Properties window, set the style template to ListStyleTemplate and type Asset details for the Title property.

To create a parameter that will be used to pass data to the drillthrough report

  1. In Model Editor, right-click the Parameters node for the AssetDetails report, and then click Add Parameter.

  2. Select the node for the parameter.

  3. In the Properties window, set the Data Type property to System.String, type SelectedAssetID for the Name property, and type Asset ID: for the Prompt String property.

To define a filter for the drillthrough report

  1. In Model Editor, expand the Designs node for the AssetDetails report, and then expand the AutoDesign1 node.

  2. Right-click the node for the list data region, point to Add, and then click Filter.

  3. Select the node for the filter.

  4. In the Properties window, specify =Fields!AssetId.Value for the Expression property, specify Equals for the Operator property, and specify =Parameters!SelectedAssetID.Value for the Value property.

Now that you have created a top-level report and a drillthrough report, you are ready to add a report drill through action to link the two reports together. A report drill through action is defined on the top-level report. The following procedure explains how to add a drill through action.

To add a report drill through action

  1. In Solution Explorer, double-click AssetList.moxl to open the model in Model Editor.

  2. Expand the Designs node for the AssetList report, expand the AutoDesign1 node, expand the node for the table data region, and then expand the Data node.

  3. Right-click the Name node, point to Add, and then click Report Drill Through Action.

  4. Select the node created for the report drill through action.

  5. In the Properties window, click the ellipsis button (…) for the Report Design property. A dialog box displays.

  6. In the dialog box, locate the AssetDetails report, select the AutoDesign1 node for that report, and then click OK.

    NoteNote

    After you select a design, the parameters for the drillthrough report display below the node for the drill through action in Model Editor.


  7. In Model Editor, select the node for the SelectedAssetID parameter that is located below the node for the drill through action.

  8. In the Properties window, specify =Fields!AssetId.Value for the Value property.

  9. In Model Editor, select the node for the AX_CompanyName parameter that is located below the node for the drill through action.

  10. In the Properties window, specify =Parameters!AX_CompanyName.Value for the Value property.

To preview the drillthrough report

  1. In Model Editor, right-click the AutoDesign1 node for the AssetList report, and then click Preview. The AssetList report displays.

  2. Click the name of one of the assets that displays in the AssetList report. The AssetDetails report displays showing more details about the selected asset.

Community Additions

ADD
Show: