Menu

Looking for the latest industry news and events?  Want to learn more about Microsoft Dynamics?  You've come to the right place!

How to Create a SubReport in Dynamics AX 2012 R3

A subreport is a report which can be placed within another report to either appear once or repeat with a dataset. The subreport is built as a regular SSRS report, however consideration must usually be given for parameters and formatting so that it will function and appear appropriately. Some uses of a SSRS subreport include:

  • Creating a specially formatted terms and conditions subreport that can be inserted into the desired reports to provide a consistent look and feel.
  • Create reusable report sections which can be easily added to other reports and shown or hidden with parameters on the parent report to provide users with the option to get only the data they want.
  • Simply the customization of existing reports to which additional child level detail is added. Rather than adding a bunch of fields and repeating a bunch of line data in the staging table to have it grouped at the report, the subreport can simply be added to show the child level data for each group.

Creating a Subreport

To demonstrate the use of the subreport, I’m going to modify the Vendors report found in the reporting section of the Accounts Payable module. The requirement for this report is to see any open purchase orders for each vendor below the vendor information. Rather than make a bunch of customizations to the existing report, we’re going to create a subreport to appear on the vendor report, which will only necessitate a couple of changes to the current report, and could be hidden with a parameter. The stock report is shown below.

Stock Report

Create the Subreport Data Objects

The first step to create the subreport is to create the query which will pull the required data, and the staging table into which the data will be inserted. The data which we need to show on the vendors report is the information which is available on the purchase orders list page. The list page filtered for the report vendors is shown below.

subreport 2

Create the staging table VendOpenPOSubreportTmp as shown below:

Create Staging Table

Name the table VendOpenPOSubreportTmp and set the TableType property to: InMemory fields:

DocumentState Enum: VersioningDocumentState
InvoiceAccount EDT: VendInvoiceAccount
PurchaseType Enum: PurchaseType
Purchld Purchld
PurchName PurchName
VendAccount VendAccount

Next, create the query which will pull the data for our report. The query in this example is rather simple as all the information shown on the purchase orders list page is available in the purchase orders table (PurchTable). Name the query VendOpenPOSubreport.

Drag the PurchTable into the Data Sources node of your new query and adjust the name if desired. Add the following fields:

PurchTable

  • DocumentState
  • InvoiceAccount
  • OrderAccount
  • PurchaseType
  • PurchId
  • PurchName
  • PurchStatus
  • Recid
  • RecVersion

Create a range on the PurchStatus field, set the value to 1, and the status to Lock. The value set is what will limit our subreport to only open purchase orders.

PunchStatus creation

Create the Subreport Code Objects

We next need to create the code objects which will populate the subreport with data and run it. This will require the use of a data contract class, and a data provider class.

We’ll first create the contract class which will take care of passing the requested vendor information to the data provider class so that it can filter appropriately.

Create a new class and name it VendOpenPOSubreportContract. Decorate the class with the DataContractAttribute and create a variable to hold the passed vendor id.

[DataContractAttribute]
class VendOpenPOSubreportContract
{
AccountNum vendId;
}

Add a parm method to the contract class to pass the vendor
[DataMemberAttribute(“VendID”)]
public AccountNum parmVendID(AccountNum _vendID = vendID)
{
vendID = _vendID;
return vendID;
}

Next we’ll need to create the data provider class. Create a new class and name it VendOpenPOSubreportDP which will extend SRSReportDataProviderBase. We’ll also decorate the class with the name of the contract class and query which we created (see code below). We’ll create variables to hold the purchase order buffer, the data contract, and the staging table.

[
SRSReportQueryAttribute(queryStr(VendOpenPOSubreport)),
SRSReportParameterAttribute(classStr(VendOpenPOSubreportContract))
]
public class VendOpenPOSubreportDP extends SRSReportDataProviderBase
{
VendOpenPOSubreportTmp vendOpenPOSubreportTmp;
PurchTable purchTable;
VendOpenPOSubreportContract reportContract;
}

Create a method decorated with the name of the staging table created earlier called getVendOpenPOSubreportTmp. This method will return the data processed to the SSRS report.

[SRSReportDataSetAttribute(‘VendOpenPOSubreportTmp’)]
public VendOpenPOSubreportTmp getVendOpenPOSubreportTmp()
{
select * from vendOpenPOSubreportTmp;
return VendOpenPOSubreportTmp;
}

Next create a method which will insert a row into the staging table for each record retrieved by the query called insertIntoTempTable.

private void insertIntoTempTable()
{

vendOpenPOSubreportTmp.DocumentState = purchTable.DocumentState;
vendOpenPOSubreportTmp.InvoiceAccount = purchTable.InvoiceAccount;
vendOpenPOSubreportTmp.PurchaseType = purchTable.PurchaseType;
vendOpenPOSubreportTmp.PurchId = purchTable.PurchId;
vendOpenPOSubreportTmp.PurchName = purchTable.PurchName;
vendOpenPOSubreportTmp.VendAccount = purchtable.OrderAccount;

vendOpenPOSubreportTmp.insert();
}

Lastly, create a method called processReport while will execute when the report is called. This is the method that will filter our dataset and loop through calling the method to insert the rows. The code to retrieve the vendor ID and filter is shown below.

VendorID retrieval

We retrieve the data contract from the parm on the base class, and then retrieve the vendor ID from it. With that vendor id, a range can be added to the query to retrieve the open purchase orders for only a single vendor.

Full code for the method below:

[SysEntryPointAttribute(false)]
public void processReport()
{
QueryRun queryRun;
Query query;
QueryBuildDataSource qbds;
QueryBuildRange qbr;

reportContract = this.parmDataContract();
query = this.parmQuery();
qbds = query.dataSourceTable(tableNum(purchTable));
qbr = qbds.addRange(fieldNum(purchTable, OrderAccount));
qbr.value(queryValue(reportContract.parmVendID()));

queryRun = new QueryRun(query);

ttsbegin;
while(queryRun.next())
{
vendOpenPOSubreportTmp.clear();

purchTable = queryRun.get(tableNum(purchTable));
this.insertIntoTempTable();
}
ttscommit;
}

Note: Prior to continuing, you must perform an incremental CIL in order for visual studio to see your new objects.

Create the Subreport

With all the AOT objects now created and compiled, everything is in place to create the subreport. Create the subreport by opening Visual Studio and going to File -> New -> Project. Name the project VendOpenPOSubreport.

subreport 7

With the project created, right click on the project and select Add -> Report.  Name the report VendOpenPOSubreport.

Application Explorer

Also in Application explorer, Browse SSRS Reports – > Reports and locate the Vend report. Drag that report over the top of the project in solution explorer to add it to the report model project.  When you’re done, the project should look like this:

Drag Vend report

Double click on the VendOpenPOSubreport to open it. Right click on Datasets and select Add Dataset. Name the dataset OpenPOListDP.

Adding Datasets

In the properties window, set the Data Source Type to Report Data Provider, and then click on the ellipses in the Query box to launch the prompt to select your data provider class VendOpenPOSubreportDP.

Properties setting

OpenPOListDP

Click “Next,” select all fields, and click “Ok” to save.

Select all

Now our subreport is almost complete, we just need to now create the layout and add the fields to the report. Right click on designs and select Add – > Precision Design. (Note, there is no reason an auto design will not work if that is your preference.)

Most of the default report designs provided by Microsoft are called Report so I called it the same. Note: whatever you name the design, it will be needed when you specify the report name property on the subreport control. Open the design by double clicking on it.

Drag a Table onto the report design. Set the DataSetName property on the table to OpenPOListDP. The table should have four columns. Also, add a row above the header row to show the subreport title. See the screenshot below for the table design. Note: The design is pulled snug against the table. This is because I know it is intended to be shown in other reports, and the parent report should be responsible for setting the whitespace around the subreport.

The expression shown in the header area for the subreport title is:

=”Open Purchase Orders for ” & Parameters!OpenPOListDP_VendID.Value

Header for subreport

Save and close the report. Now the subreport is complete. Deploy the project back to the AOT and deploy the subreport to SSRS.

Modify the Vendor Report to Show the Subreport

With the subreport created and deployed, the only thing that remains is to add the subreport to the vendor report design.  Double click on the Vend report in the project to open it.  Expand the designs node and double click on the Report design.  The standard format is shown below.

Modifying vendor report

Start by dragging the design bottom down to create more vertical space, and then expand the table cell by dragging down as shown by the yellow area below.

Create more vertical space

Drag a subreport control into the area in yellow above, and size it appropriately. I decided to indent the subreport a bit to help the eye delineate between vendors.  As noted earlier, I also left a little whitespace around the subreport since I didn’t have any surrounding the design of the subreport.

Creating Report Space

In the subreport properties – set the report name to VendOpenPOSubreport.Report.  Click on the ellipses in the Parameters box to bring up the parameters prompt. Click Add to add the vendor parameters. You will have to copy the parameter name from the subreport.  In this case it is OpenPOListDP_VendID. Match that to the VendAccount field from the current table record.

Change subreport parameters

Lastly, I decided to add a solid border to the List_0_Contents rectangle to help group the records.
Now the main report is ready to show the open purchase orders for every vendor shown. Save the report, deploy to the AOT, and deploy to SSRS.

Finished Report

I’m going to test the report with the vendors shown below. The list shows all purchase orders for those vendors. The subreport should display only the subreport.

subreport 2

Run the Vendors report.

Vendors report

Run for vendors: CN-001,1001,US-111,JP-001.

Running report for vendors

And our vendor report now includes the open purchase orders for each.

Report with open orders

Vendor report

Conclusion:

Reusable subreports can help greatly cut down on customization time and standardize the look and feel between reports. It can also help introduce child data to a report with a minimum of customization to the original report. Hopefully this blog has helped you to both understand how to implement a subreport, and to think of new uses for a subreport that you may not have considered before.


Kevin EndresWritten by Anthony Endres
Technical Solution Architect, mcaConnect