Troubleshoot Issues with Printing and Emailing Reports

Article
11/29/2021

Important

This content is archived and is not being updated. For the latest documentation, see Microsoft Dynamics 365 product documentation. For the latest release plans, see Dynamics 365 and Microsoft Power Platform release plans.

This topic provides guidance and workarounds for problems using the Print ranges, Group totals, and Print grand totals print options, formatting dates and numbers, and emailing archived reports. The way to implement these options is different in Microsoft Dynamics AX 2012 than in Microsoft Dynamics AX 2009.

In this topic

Printing ranges and totals

Print Ranges
Group totals
Print Grand totals

Formatting dates and numbers

Date format correct in header but incorrect in report body
Currency or number format incorrect in report body

Emailing reports

Emailing archived reports

Print Ranges

Microsoft Dynamics AX 2009 provided UI for defining Print ranges and Print grand totals options in the Print option tab of a query form.

In Microsoft Dynamics AX 2012 reports are designed using SSRS. Here the design of the reports is static. Changes to the report body cannot be applied dynamically as seen in Microsoft Dynamics AX 2009. The above features are removed from the options for displaying reports. Instead, you can define print ranges programmatically.

The following steps walk you through an example of how to implement print ranges for a report in Microsoft Dynamics AX 2012.

Print Range Implementation in Microsoft Dynamics AX 2012

Open the AOT and expand the node for the report under \Visual Studio Projects\Dynamics AX Model Projects\. The following image shows the report object for the report AssetListing.
Right-click on the report object and click Edit to open the SSRS report design. The following image shows columns that can be used to form ranges.
In this example, we will specify three ranges for the report:
1. AssetGroup
2. AssetId
3. BookId
The values for these ranges are to be captured and passed on to the report. For this purpose we create three dummy parameters in the parameter section of the report. These parameters should not be shown on the parameter dialog. The Visible property for the parameters is set to false. They are only used to receive the range values from the sysQuery form.

Use a controller class to get range values from the SysQuery form. In this example it is named AssetListingController.

AssetListingController

In the method PreRunModifyContract we define code for getting range values from the query and assign these values to the range parameters created in the report. The properties of the menu item AssetListing used for this report should be modified as shown in the following.

\Classes\AssetListingController\classDeclaration

public class AssetListingController extends SrsReportRunController
{
    SrsReportRdlDataContract rdlContract;
}

\Classes\AssetListingController\main

public static void main(Args _args)
{
    SrsReportRunController controller = new AssetListingController();
    controller.parmArgs(_args);
    controller.parmReportName(ssrsReportStr(AssetListing, Report));
    controller.startOperation();
}

\Classes\AssetListingController\preRunModifyContract

protected void preRunModifyContract()
{
    #define.AssetGroupParm   (‘AssetGroup’)
    #define.AssetIdParm    (‘AssetId’)
    #define.BookIdParm     (‘BookId’)
    #define.Blank          (‘’)

    Query   query;
    QueryBuildRange   queryBuildRange;
    QueryBuildDataSource    queryBuildDataSource;

    // Initializizing the SrsReportRdlDataContract object.
    // This is used for getting parameters in the report definition.
    rdlContract = this.parmReportContract().parmRdlContract() as SrsReportRdlDataContract;

    // Clearing off the older values from the report parameters.
    rdlContract.setValue(#AssetGroupParm, #Blank);
    rdlContract.setValue(#AssetIDParm, #Blank);
    rdlContract.setValue(#BookIDParm, #Blank);

    // Getting the query used for the report.
    query = this.getFirstQuery();

    // Getting range values for the Fixed Asset group.
    queryBuildDataSource     = query.dataSourceTable(tableNum(AssetTable));
    queryBuildRange          = queryBuildDataSource.findRange(fieldNum(AssetTable, AssetGroup));

    // Setting the range values to the AssetGroup parameter in the report.
    if (queryBuildRange.value())
    {
        rdlContract.setValue(#AssetGroupParm, queryBuildRange.value());
    }

    // Getting range values for the Fixed Asset number.
    queryBuildRange = queryBuildDataSource.findRange(fieldNum(AssetTable, AssetId));

    // Setting the range values to the AssetId parameter in the report.
    if (queryBuildRange.value())  
    {
        rdlContract.setValue(#AssetIdParm, queryBuildRange.value());   
    }
    // Getting range values for the Book range.
    queryBuildDataSource   = query.dataSourceTable(tableNum(AssetBookMerge));
    queryBuildRange        = queryBuildDataSource.findRange(fieldNum(AssetBookMerge, BookId));

    // Setting the range values to the BookId parameter in the report.
    if (queryBuildRange.value())
    {
        rdlContract.setValue(#BookIdParm, queryBuildRange.value());
    }
}

AssetListing menu item

In the report body design, use textboxes to display the Range parameter values.
To control the display of the ranges on the report, we create a Boolean parameter named PrintRanges on the report. The visibility of the textboxes is toggled by setting this parameter to true or false.

Note

We cannot add ranges dynamically to the sysQuery form. Whatever ranges are present initially will be considered and accordingly parameters are added to the report. Dynamic addition of parameters to SSRS report is not supported.

Group Totals

If no grouping is automatically provided for a report, introduce groupings into the report design according to your requirements. The options for grouping the report can be specified by grouping on fields like the following:

Fixed asset group
Fixed asset number
Book

We have taken these values as these are the ranges for the report. Usually the fields used for ranges are used for sorting and grouping. We have to create three Boolean parameters corresponding to the above three grouping fields.

Asset listing report with group totals

In the report design, create three groupings on the existing table.

The grouping hierarchy should follow the parameter hierarchy, which is the order in which the above parameters are displayed.

Asset listing report design grouping hierarchy

As per the above screenshot, set Row Visibility expression for 1

=iif(Parameters!FixedAssetGroup.Value = false and Parameters!FixedAssetNumber.Value = false and Parameters!Book.Value = false, false, true)

As per the above screenshot, set Row Visibility expression for 2

=iif(Parameters!FixedAssetGroup.Value = true and Parameters!FixedAssetNumber.Value = false and Parameters!Book.Value = false, false, true)

As per the above screenshot, set Row Visibility expression for 3

=iif(Parameters!FixedAssetNumber.Value = true and Parameters!Book.Value = false, false, true)

As per the above screenshot, set Row Visibility expression for 4
```
=iif(Parameters!Book.Value = true, false, true)
```

The grouping should be based on the chosen parameter. A sample expression for grouping is given as:

=iif(Parameters!FixedAssetGroup.Value = true, Fields!AssetGroup.Value,1)

If the corresponding checkbox is checked, the grouping should be done on the corresponding value. Otherwise no grouping should be done. In the expression the value “1” is used for no grouping since it is a constant value.

The group headers are used to display the information as the value on which the data is grouped.

The group footers are used to display the value and the total values for the amount columns. In this way, grouping totals can be implemented.

Note

We cannot specify grouping fields dynamically to the sysQuery form. Whatever fields present initially will be considered and accordingly parameters are added to the report. Dynamic addition of parameters to SSRS report is not supported. This feature can be implemented only for reports which do not have any grouping defined in the design. Simple reports without groupings can make use of this option. This behavior is seen in Microsoft Dynamics AX 2009 reports. This is limitation for both Microsoft Dynamics AX 2009 and Microsoft Dynamics AX 2012 reports.

Print Grand Totals

In the report design, create a Boolean parameter named GrandTotal.

Asset listing group total parameters

Edit the report design and create a static row at the bottom of the tablix. This serves as table footer. Use the summation expression for amounts to get the Grand total.

The summation expression for calculating grand totals is as follows:

=sum(Fields!assetNetBookValue.Value)

Manage the visibility of the row by using the GrandTotal parameter. If it is checked, this parameter makes the row visible. Otherwise, the row is hidden.