How To: Expanding App Suite report data sets

Microsoft Dynamics 365 for Operations now offers an expanded set of tools to support custom solutions. This article focuses on the expansion of an existing report data set produced using X++ business logic in a Report Data Provider (RDP) class. Use custom delegate handlers and table extensions to include additional field data and/or calculations without over-layering the Application Suite. Then create custom designs that replace the standard application solutions to present the data to end-users.

Microsoft Dynamics 365 for Operations (Platform Update3)
______________________________________________________________________________

The following diagram illustrates a common application customization described here…

extendingdatasets

WHAT'S IMPORTANT TO KNOW?

There are a few basic assumptions that will need to be confirmed before applying this solution.

  1. You cannot directly extend RDP classes. However, the platform provides extension points that enable data set expansion without duplicating business logic in the standard application.
  2. There are two methods of expanding report data sets. Choose the strategy that's right for your solution.
    a.  Data Processing Post Handler - this method is called only once after the ProcessReport method is complete and before the data set is returned to the report server. Register for this post-handler to perform bulk updates on the temporary data set produced by the standard application solution.
    b.  Temp Table 'Inserting' Event - this method is called for each row that is added to the temporary table and is more suitable for calculations and inline evaluations. Try to avoid expensive queries with many joins & look-up operations.
  3. Use event handlers to redirect menu items to your new report design. You can customize all aspects of an application reporting solution using event handlers. Add a 'PostHandler' event for the Controller class to re-route user navigations to a custom report design.

Expanding report datasets

The following walk-thru demonstrates the process of expanding an existing application data set using a 'pure' extension based solution.

Scenario - My solution includes a custom 'Rentals list' report for the Fleet Management application. The new report includes additional rental charge data in the rental details. The application customizations are defined in an extension model. The following screen shot compares the standard design against the custom solution.

BEFORE fleet-extension-rentals-list-before

AFTER fleet-extension-rentals-list-after

Step 1) Create a new model for you application customizations. For more information on Extension models review the article Customization: Overlayering and extensions.  For this example, I'll be adding custom reports to the 'Fleet Management Extensions' model to demonstrate the solution.

Step 2) Create a new project in Visual Studio. Make sure that the project is associated with your extension model. Here is a screen shot of my project settings…

fleet-extension-vs-project-settings

Step 3) Add a table extension to store the custom report data. Locate the temporary cache for the data set 'TmpFMRentalsByCust' populated by the RDP class and create an extension in your model. Define the field(s) that will be used to store the data for the report server, and then Save the changes.  Here's a screen shot of the table extension required for this example…

fleet-extension-table-extension

Step 4) Add your custom report to the project. In my case, the custom design is very similar to the standard solution. So, I simply duplicated the existing application report in my Fleet Management Extension model. I then updated the report design to include the custom title and additional text box in the Rental Charges container.

Step 5) Rename the report to something meaningful. For this example, I've named my custom report FERentalsByCustomer to distinguish it from the standard solution.

Step 6) Restore the report data set references. Open the report designer, expand the Datasets collection, right + click the dataset named FMRentalsByCustDS, and select Restore.  This action expands the dataset to include the newly introduced columns making them available in the report designer.

Step 7) Customize the report design. The Precision designer offers a free-form design surface that can be used to create the custom solution.

Here's a screen shot of the custom design used for this example…

fleet-extension-custom-design

Step 8) Add a new Report Handler (X++) class to the project. Give the class a name that appropriately describes it as a handler for an existing application report. For this example, I've renamed the class to FERentalsByCustomerHandler to distinguish it from other report handlers.

Step 9) Add a PostHandler method to begin using your custom report. In this example, we'll extend the Controller class in the standard solution FMRentalsByCustController using the following X++ code…

Code Snippet:
class FERentalsByCustomerHandler
{
[PostHandlerFor(classStr(FMRentalsByCustController), staticMethodStr(FMRentalsByCustController, construct))]

    public static void ReportNamePostHandler(XppPrePostArgs arguments)
{
FMRentalsByCustController controller = arguments.getReturnValue();
controller.parmReportName(ssrsreportstr(FERentalsByCustomer, Report));
}
}

At this point, user navigations in the application will be re-routed to the custom reporting solution. Take a minute to Deploy the custom report to the Report Server and verify that it's being used by the application.  All that's missing is the business logic used to populate the custom field(s) introduced in step #3.  In the next step, you'll need to select the method of dataset expansion that's appropriate for your solution.

Step #10)  Add X++ business logic to populate the custom field data.   Select the data processing technique that makes sense for the type of transformation required for the solution.

Option 1 - Add a Report Processor Extension. Apply this technique for bulk insert operations using a single pass over the result set of the standard solution. This method will be called immediately after the ProcessReport method completes.  Here's sample code for post-processing handler…

Code Snippet:
class FERentalsByCustomerHandler
{

[PostHandlerFor(classStr(FMRentalsByCustDP), methodstr(FMRentalsByCustDP, processReport))]
public static void TmpTablePostHandler(XppPrePostArgs arguments)
{
FMRentalsByCustDP dpInstance = arguments.getThis() as FMRentalsByCustDP;
TmpFMRentalsByCust tmpTable = dpInstance.getTmpFMRentalsByCust();
FMRentalCharge chargeTable;

ttsbegin;
while select forUpdate tmpTable
{
select * from chargeTable where chargeTable.RentalId == tmpTable.RentalId;
tmpTable.ChargeDesc = chargeTable.Description;
tmpTable.update();
}
ttscommit;
}
}

-- OR --

Option 2 - Add a Temp Table 'Inserting' Event. Apply this technique for row-by-row calculations. Here's the sample that expands using a table look-up…

Code Snippet:
class FERentalsByCustomerHandler
{
[DataEventHandlerAttribute(tableStr(TmpFMRentalsByCust), DataEventType::Inserting)]
public static void TmpFMRentalsByCustInsertEvent(Common c, DataEventArgs e)
{
TmpFMRentalsByCust tempTable = c;
FMRentalCharge chargeTable;

// update the value of the 'ChargeDesc' column during 'insert' operation
select * from chargeTable where chargeTable.RentalId == tempTable.RentalId
&& chargeTable.ChargeType == tempTable.ChargeType;
tempTable.ChargeDesc = chargeTable.Description;
}

}

You're done. Once compiled, the application will begin re-routing user navigations to the new report design using the custom X++ business logic defined in the report class handler defined in the extension model.

Comments

  • Anonymous
    November 07, 2017
    The first option doesn't seem to work. The code in the handler never gets executed. Do you have any idea why this is?Thanks,Filip
    • Anonymous
      November 17, 2017
      I love the fact that I'm always learning something new about this platform. Latest discovery is that the GetTable method is NOT called by the SSRS process at run-time. This method is only used for reflection in Visual Studio to get table schema. Filip, this explains your observation.I've updated the sample above to reflect the revised guidance for using extensions to expand a standard report data set. Sorry for the confusion and thanks a ton for the feedback.
  • Anonymous
    September 11, 2018
    Thanks for useful links, but I want to know how this class is invoked while no caller has been set.accully I create a new class handler for Purchase Order Report, but after building Project and deploying new Design of SSRS Report, the original one has been shown. Kindly please let me if I miss something. Thanks