D365FO & AX 2012 - Data Source Join Types

 For Instance, We Created Two Tables.

• Student
• Student Attendance

Then Create a relation as per your requirement. I am using Normal relations.

Student

Student Attendance

Now create New Form and Add two Gird [StudentGrid] & [StudentAttendanceGird]

Let's begin

Passive Join Type

Passive form data source link type won't update the child data source automatically. For example, if we select the parent table order then order details child data source won't update. If we need to update the child data source we need to call the child data source to execute the query method by the program (code).

Active Join Type

Active link type updates the child data sources without any delay when you select the parent table record. When you deal with more records it will affect application performance.

Delay Join Type

Delay form data source link type is also same as active method the different is delay method won't update immediately when you select the parent record. It will update the child data source when you select the parent table, Ax uses a pause statement before update the child data source. 

Inner join Type

Inner join form data source link type displays the rows that match with parent table and child table. 

Outer join Type

Outer join form data source link type will return all parent records and matched child records. It will return all rows in the parent table. 

Exists Join Type

Exist join form data source link type return matched rows of the parent table. It behaves like an inner join but the difference is once the parent row is matched with child records then stops the process and updates in the grid, Ax won't consider how many records are in the child table for the parent row.

Not Exists Join Type

Not exist join form data source link type is a totally opposite method to exist join. It will return the not-matched parent records with child records.

Ref 

D365FO Create a new Data Provider custom SSRS report

 

Requirements for the new report

Let’s say that we want to develop a new report that will show the data from a selected sales agreement but instead of using the existing SSRS report for Sales agreement confirmation (AgreementConfirmation.Report), we will create a completely new one from scratch, which will look like a real-world agreement document. We will name this report as Sales agreement document.

The report data

Sales agreement document should contain the data from the source sales agreement’s header, a list of sold items but also a list of the customer’s contacts.

The report parameters

The report doesn’t have any visible parameters since it should always run in the context of a single sales agreement. Thus, the only parameter (which is hidden) is RecId of the currently selected sales agreement.

The report menu items

The requirements regarding printing the report: a user can preview the report by clicking a menu item placed directly on the Sales agreement form. Additional menu item that opens the Print destination settings form should also be introduced in order to enable emailing the report or sending it to a particular printer.

Which artifacts are we going to create and why

We are going to create the following artifacts:

• A new SSRS report with a single (Precision) design.
• A DP (Data Provider) class that will provide three data sets for the SSRS report. The same class will also provide the information about report parameters to the report’s RDL through an attribute that links a DP class with the corresponding report Data Contract.
• A Data Contract class that will define and carry the values of the report parameters. We will have one hidden parameter: a sales agreement’s RecId.
• Three temporary tables to define and carry the report data. They will be filled and exposed to the SSRS report by the DP class.
• A Controller class that will handle the report dialog form, setting the SSRS report design and the value of the hidden parameter.
• A menu item that opens the report in the viewer.
• A menu item that opens the report dialog form.
• Security artifacts for these two menu items.

Step 1: Create report temporary tables

For this report the data are divided into three data sets: Header data, Lines data and Customer contacts data, so we need to create three temporary tables.

Create temporary tables by clicking Add > New Items > Dynamics 365 Items > Data Model > Table:

Add the following three temporary tables:

• DocAgreementHeader – used for a sales agreement’s header data.
• DocAgreementLine – used for a sales agreement’s lines data.
• DocAgreementContact – used for a sales agreement’s customer contact data.

Set the TableType property to InMemory for each table. This way the tables will act as temporary tables.

We will now add all needed fields to our temporary tables.

   

Step 2: Create the Controller class

• A Controller class extends SrsReportRunController.
• A Data Contract class should be decorated with the DataContractAttribute attribute.
• A Data Provider class extends SRSReportDataProviderBase and implements at least the processReport() method. Each Data Provider class is decorated with the SRSReportParameterAttribute attribute that points to a particular Data Contract class, and optionally with the SRSReportQueryAttribute attribute that points to a dynamic query if such exists. Additionally, for each of the data sets a method decorated with the SRSReportDataSetAttribute attribute should be implemented.

In order to add a new class, go to Add > New Items > Dynamics 365 Items > Code > Class.

We are going to create three classes here at once:

• DocAgreementDocumentController – the Controller class.
• DocAgreementDocumentContract – the Data Contract class.
• DocAgreementDocumentDP – the Data Provider class.

Implementation of the Controller class

A Controller class orchestrates the report execution. In our case, the controller will handle whether the report dialog form should open, set up the SSRS report design and the value of the hidden parameter. Each controller class should extend SrsReportRunController (SrsPrintMgmtController or SrsPrintMgmtFormLetterController for Print Management reports).

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41

class DocAgreementDocumentController extends SrsReportRunController {     public static DocAgreementDocumentController construct()     {         return new DocAgreementDocumentController();     }       public static void main(Args _args)     {         DocAgreementDocumentController docAgreementDocumentController =             DocAgreementDocumentController::construct();                                                 docAgreementDocumentController.parmReportName(ssrsReportStr(DocAgreementDocument, Report));         docAgreementDocumentController.parmArgs(_args);         docAgreementDocumentController.parmDialogCaption("Sales agreement document");         docAgreementDocumentController.startOperation();     }       protected void prePromptModifyContract()     {         DocAgreementDocumentContract contract = this.parmReportContract().parmRdpContract();           // Set the hidden parameter value.         contract.parmSalesAgreementRecId(args.record().RecId);           // Set the report design name.         this.parmReportContract().parmReportName(ssrsReportStr(DocAgreementDocument, Report));           boolean isPreview = args.menuItemName() == menuItemOutputStr(DocAgreementDocumentPreview);           // Set the target print destination to Screen if we are previewing the report.         if (isPreview)         {            this.parmReportContract().parmPrintSettings().printMediumType(SRSPrintMediumType::Screen);         }           // Don't show the report dialog if the report menu item is DocAgreementDocumentPreview         // and don't save to SysLastValue either.         this.parmShowDialog(!isPreview);         this.parmLoadFromSysLastValue(!isPreview);     } }

Step 3: Create the Data Contract class

A Data Contract class defines and stores the values of the report parameters. In our case, we will have one hidden parameter: Sales agreement’s RecId. Note that each Data Provider class is decorated with the DataContractAttribute attribute, and each of the methods representing a particular report parameter is decorated with the DataMemberAttribute attribute.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

[DataContractAttribute] public class DocAgreementDocumentContract {     RecId  salesAgreementRecId;          [         DataMemberAttribute('SalesAgreementRecId'),         SysOperationLabelAttribute(literalStr("@SYS190134")),         SysOperationControlVisibilityAttribute(false)     ]     public RecId parmSalesAgreementRecId(RecId _salesAgreementRecId = salesAgreementRecId)     {         salesAgreementRecId = _salesAgreementRecId;         return salesAgreementRecId;     } }

Step 4: Create the Data Provider class

A Data Provider class should extend SRSReportDataProviderBase (or SrsReportDataProviderPreProcess for pre-processed reports) and implement at least the processReport() method. Each Data Provider class is decorated with the SRSReportParameterAttribute attribute that points to a particular Data Contract class, and optionally with the SRSReportQueryAttribute attribute that points to a dynamic query if such exists. Additionally, for each of the data sets a method decorated with the SRSReportDataSetAttribute attribute should be implemented.

The processReport() method is called by Reporting Services to fetch the report data and fill the temporary tables. The same temporary tables should be exposed via methods decorated with the SRSReportDataSetAttribute attribute, for example:

[SRSReportDataSetAttribute(tablestr(DocAgreementHeader))]
public DocAgreementHeader getDocAgreementHeader()

Implementation of the Data Provider class

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63

[SRSReportParameterAttribute(classStr(DocAgreementDocumentContract))] class DocAgreementDocumentDP extends SRSReportDataProviderBase {     // Contains Agreement header data.     DocAgreementHeader  docAgreementHeader;          // Contains Agreement lines data.     DocAgreementLine    docAgreementLine;       // Contains Agreement customer contact data.     DocAgreementContact docAgreementContact;            [SRSReportDataSetAttribute(tablestr(DocAgreementHeader))]     public DocAgreementHeader getDocAgreementHeader()     {         select docAgreementHeader;         return docAgreementHeader;     }          [SRSReportDataSetAttribute(tablestr(DocAgreementLine))]     public DocAgreementLine getDocAgreementLine()     {         select docAgreementLine;         return docAgreementLine;     }       [SRSReportDataSetAttribute(tablestr(DocAgreementContact))]     public DocAgreementContact getDocAgreementContact()     {         select docAgreementContact;         return docAgreementContact;     }       /// <summary>     /// Process report data.     /// </summary>     public void processReport()     {         DocAgreementDocumentContract contract;         SalesAgreementHeader salesAgreementHeader;           contract = this.parmDataContract() as DocAgreementDocumentContract;           // Get sales agreement header record from the contract parameter         salesAgreementHeader =             SalesAgreementHeader::find(contract.parmSalesAgreementRecId());                  if (salesAgreementHeader)         {             // Populate sales agreement header             this.populateSalesAgreementHeader(salesAgreementHeader);               // Populate sales agreement line             this.populateSalesAgreementLine(salesAgreementHeader);               // Populate sales agreement customer contacts             this.populateSalesAgreementCustomerContact(salesAgreementHeader);         }     }       ... }

Download the complete code >>

Step 5: Create the SSRS report

To add a new SSRS report, go to Add > New Items > Dynamics 365 Items > Reports > Report.

   

Set the report data source to a DP class

Now add a dataset to the report and set its Data Source Type property to Report Data Provider, then click the small button available on the Query property. The list of all DP classes available in the AOT will be shown; locate and select our DocAgreementDocumentDP class > DocAgreementTable. Set the properties as shown on the image below.

   

Now repeat the same procedure for two more data sets – for sales agreement lines and customer contacts.

Create the report design

Add a new precision design; a standard SSRS designer will open. You can see the Report Items toolbar from where you can drag and drop the controls onto the design surface.

   

Now we will design the report.

   

   

How to make SSRS report designs more readable

In our SSRS report design we used placeholders to display the value of each expression. Otherwise, our design would look very messy, hard to understand and afterwards, hard to update.

You can use placeholders by clicking on Expression or selecting and right-clicking it; in both cases Placeholder window will open.

This is how it looks when an expression uses a placeholder:

And this is the same expression without any placeholder:

Step 6: Create the report menu items

Create a new extension for the SalesAgreement form for adding two new menu items. First of them (Preview document) will be used for opening the report on Screen and the second one (Print document) should open the report dialog form, where a user can select the target print destination.

Step 7: Execute the report

Navigate to Accounts receivable > Orders > Sales agreements and open any sales agreement. Click Preview document to see the result.

   

Conclusion

This report took us around 12-15 hours to develop. The most time we spent designing the report.

Below is what we found to be the main drawbacks:

• It can be quite cumbersome to enter longer paragraphs containing multiple data fields.
• When updating calculated placeholders (mix of the static text and data fields) we should take care that we do it in both placeholder’s Label and Value properties.
• We cannot format particular parts of the same placeholder, i.e. parts of the same paragraph differently. For example, you cannot make bold or color differently the "Agreement" word in the following placeholder/paragraph:
  This Sales Agreement (this "Agreement") is entered as of the & Fields!EffectiveDate.Value.
• We cannot change at once the format of the whole document but we need to do it placeholder by placeholder.
• We cannot control page breaks and what will be written out on which page.
• Export to Word (from the viewer) doesn’t work well enough: only doc and not docx output format is supported, fidelity is not great (everything is put into a table, margins differs, etc.). This is important for this kind of documents, where after generation you want to edit certain parts.