Drill Through SSRS Report in AX 2012

Today I will demonstrate you that How to create Drill Through reports in AX 2012.

Defining a Query

To define a query

1. In Microsoft Dynamics AX, open a New Development Workspace.
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. In the Assets query node, expand the Data Sources node, expand the AssetTable_1 node, select the Fields node, in the Properties window, set the Dynamic property to Yes.
9. Save the query.

Creating a Reporting Project

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 Installed Templates pane, click Microsoft Dynamics AX, and in the Templates pane, click Report Model.
4. In the Name box, type SampleDrillthroughReport. In the Location box, type a location.
5. Click OK.

Creating Reports

To create a top-level report

1. In Solution Explorer, right-click the SampleDrillThroughReports project, point to Add and then click Report.
2. In the Properties window, for the Name property type AssetList. The model displays in Model Editor with a report named AssetList.
3. In Model Editor, right-click the Datasets node, and then click Add Dataset.
4. Select the node for the dataset.
5. In the Properties window, specify the following values.

Property	Value
Data Source	Dynamics AX
Data Source Type	Query
Default Layout	Table
Dynamic Filters	False
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, click Next, expand the node for All Fields, and then select the AssetId, and Name fields. When you are finished selecting the fields, click OK.

6. In Model Editor, select the Assets dataset node and drag it onto the Designs node. An auto design named AutoDesign1 is created for the report.
7. Select the AutoDesign1 node.
8. In the Properties window, set the LayoutTemplate property to ReportLayoutStyleTemplate and type Assets for the Title property.
9. In Model Editor, expand the AutoDesign1 node, and then select the node for the AssetsTable data region.
10. 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 SampleDrillThroughReports project, point to Add, and then click Report.
2. In the Properties window, for the Name property type AssetDetails. The model displays in Model Editor with a report named AssetDetails.
3. Right-click the Datasets node, and then click Add Dataset.
4. Select the node for the dataset.
5. In the Properties window, specify the following values.

Property	Value
Data Source	Dynamics AX
Data Source Type	Query
Default Layout	TopDownList
Dynamic Filters	False
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, click Next, expand the node for All Fields, and then select the AssetId, Name, SerialNum, InsuredValue, Make, Model, AssetReplaceCost, and TechInfo1 Fields. When you are finished selecting the fields, click OK.

6. In Model Editor, in the Details dataset, expand the Fields node, select the node for the AssetReplaceCost field, and in the Properties window, set the FormatString property to Currency.
7. In the Fields node, select the node for the InsuredValue field, and in the Properties window, set the FormatString property to Currency.
8. In the Fields node, select the node for the Name field and then click the Move up button on the Model Editor toolbar until it is first in the list.

Note:-

You can also press ALT+UP ARROW or ALT+DOWN ARROW to move an element up or down in a collection in Model Editor.

9. Select the Details 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 DetailsList data region.
13. 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, point to Add, and then click Parameter.
2. Select the node for the parameter.
3. In the Properties window, set the Data Type property to 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 the AssetDetails design, right-click the node for the DetailsList data region, point to Add, and then click Filter.
2. Select the node for the filter.
3. 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.

Adding a Report Drill Through Action

To add a report drill through action

1. In Solution Explorer, double-click AssetList 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 AssetsTable 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, expand the AssetDetails report, select the AutoDesign1 node for that report, and then click OK.

Note:-

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. You will see links in the report that indicate the drillthrough was created but you must verify the link to the report detail in the client.