How to add data to a report from a table extension. Is a Query extension required?

(0) Share

Report

Posted on by jt1024

185

Hello esteemed community members,

I am trying to learn how to place a custom field I've created onto an existing custom report.

In a table extension called PurchTable.WWISupplyChain I have a field called WWINamedPlace.

Our Purchase Order Report runs from a custom report called WWIPurchPurchaseOrder.

That report, WWIPurchPurchaseOrder, already has these two Datasets:

PurchPurchaseOrderLine using Query: SELECT * FROM PurchPurchaseOrderDP.PurchPurchaseOrderTmp

and

PurchPurchaseORderDS using Query: SELECT * FROM PurchPurchaseOrderDP.PurchPurchaseOrderHeader

That looks like this:

and

I figure I need to add another Dataset called PurchTable and somehow create a Query on the PurchTable for that. I've found this existing Query in the [Application Suite] in the AOT:

I created an extension of this in my project, it shows under 'Simple Query Extensions' as PurchTableDocument.WWISupplyChain. That automatically has my WWINamedPlace field that I need.

But when I try to guess at how to use the Simple Query Visual Studio gives a Properties Window errer: "Property value is not valid." Details: "The query string SELECT * FROM PurchTableDocument.WWISupplyChain is invalid. The valid format is 'SELECT [fields] FROM [Query name]', where [fields] can be either '*' or a comma separated field list."

What is the correct thing to do here?

All responses (21)

Answers (2)

Verified answer

jt1024 185 on at

Like (0)

Report
How to add data to a report from a table extension. Is a Query extension required?

Hello all,

So the final solution that seems to be working likely represents a good example of there's more than one way to achieve my goal. I'll try to remove the 'verified answer' I saw was on a different reply since in my case that was not the final answer and I can't attest to that reply answer working.

I found pre-existing similar business goals implemented in the following AOT objects. Using the suggestions from people on this thread, and making a leap of faith I solved the issue. This morning I played around removing and adding parts of the solution to make sure I understood the impact of each element.

For the benefit of others reading this post here's what I did:

AOT Modified item: AxTableExtension: PurchTable.WWIExtension: New Field: WWINamedPlace

Created WWINamedPlace in a new extension, PurchTable.WWIExtension (Table), and what’s more I added it to the Field Group in Delivery in that table.

Note this required as the primary storage location for WWINamedPlace. That way it naturally shows up in the right spot in the AXFormExtension: PurchTable.WWIExtension as a Data Source field in PurchTable.

AOT Modified item: AxTableExtension: PurchPurchaseOrderHeader.WWIExtension

Note WWINamedPlace also created here, This is needed here, due to the Class WWIPurchPurchaseOrderDP_Extension pattern already in place from previous developers depends on Table Extension PurchPurchaseOrderHeader.WWIExtension

Then in the PurchTable.WWIExtension (form) (This is in project WWIPurchaseOrderLinesUpload) the PurchTable was already an existing data source:

AOT item shows effect: AxReport: WWIPurchPurchaseOrder [WWISupplyChain]

The Dataset PurchPurchaseOrderDS Field shows WWINamedPlace in PurchPurchaseOrderDS dataset in Report WWIPurchPurchaseOrder

AOT Modified item: Classes: WWIPurchPurchaseOrderDP_Extension

Added last line in partial class code shown here:

/// <summary> /// Initializes a Purchase Order Header. /// </summary> /// <param name = "_vendPurchOrderJour"> /// A <c>VendPurchOrderJour</c> record which is the Purchase Order header. /// </param> /// <returns> /// The initialized <c>purchPurchaseOrderHeader</c> Purchase Order header record. /// </returns> [ExtensionOf(classStr(PurchPurchaseOrderDP))] final class WWIPurchPurchaseOrderDP_Extension { protected PurchPurchaseOrderHeader initializePurchaseOrderHeader(VendPurchOrderJour _vendPurchOrderJour) { PurchPurchaseOrderHeader purchPurchaseOrderHeader; PurchTable purchTable; HcmWorker hcmworker; PurchTableVersion purchTableVersion; WorkflowVersionTable WorkflowVersionTable; WorkflowTable WorkflowTable; ProjTable projTable; #define.PurchCategory('PurchCategory') purchPurchaseOrderHeader = next initializePurchaseOrderHeader(_vendPurchOrderJour); purchTable = PurchTable::find(_vendPurchOrderJour.PurchId); purchPurchaseOrderHeader.WWIDlvModeId = purchTable.DlvMode; purchPurchaseOrderHeader.WWIProjID = purchTable.ProjId; purchPurchaseOrderHeader.WWIVendorID = purchTable.OrderAccount; purchPurchaseOrderHeader.WWIConfirmedDlvDate = purchTable.ConfirmedDlv; // 2023-06-14 JT: add WWINamedPlace from PurchTable: purchPurchaseOrderHeader.WWINamedPlace = purchTable.WWINamedPlace;

The net result is WWINamedPlace field shows in my Purchase Order UI in the Header View, and that field comes across to a Purchase Order Confirm Journal transaction report printout.

Best regards all and again thank-you all so very much for your time, suggestions an willingness to help my learn how this works. I've learned a bit more in solving this problem.

- Jim
Mohit Rampal 12,550 Super User 2024 Season 1 on at

Like (0)

Report

RE: How to add data to a report from a table extension. Is a Query extension required?

Is DP the same as DSP? If you are referring if its same as standard PurchPurchaseOrderDP class then Yes, this WWIPurchPurchaseOrderDP_Extension class is extending PurchPurchaseOrderDP standard class. In Line# 28 there is 'next' keyword, this will call the initializePurchaseOrderHeader method in standard class.
jt1024 185 on at

Like (0)

Report

RE: How to add data to a report from a table extension. Is a Query extension required?

Hello Mohit,

So I found an existing DP extension. Is DP the same as DSP?

The class in my project is WWIPurchPurchaseOrderDP_Extension.

I'm not entirely sure of what I'm seeing but I see stuff like this:

Is that what you meant?

When I build that and ran it my data was in my report!

Wohoo!

Now I have to figure out if I needed this relation afterall:

I'll try carving up what's there and see what makes it work or fail and this will reinforce my learning.
Mohit Rampal 12,550 Super User 2024 Season 1 on at

Like (0)

Report

RE: How to add data to a report from a table extension. Is a Query extension required?

It is still standard table and you have extended the existing standard table in your custom model. Custom tables are the new table created in custom model.
GirishS 27,816 Super User 2024 Season 1 on at

Like (0)

Report

RE: How to add data to a report from a table extension. Is a Query extension required?

If its a standard table or you extended that table in your custom model, you cannot override method directly. You need to write either COC or event handler.

Thanks,

Girish S.
jt1024 185 on at

Like (0)

Report

RE: How to add data to a report from a table extension. Is a Query extension required?

It's not a standard table I think? When it's name is this: PurchPurchaseOrderHeader.WWIExtension and it's under my own model, doesn't that mean it's not a standard table?
GirishS 27,816 Super User 2024 Season 1 on at

Like (0)

Report

RE: How to add data to a report from a table extension. Is a Query extension required?

You cannot directly add method to standard tables. You need to use either COC or event handler.

If you use COC -Create a new class and follow the sample code given by mohit.

If you use event handler - Refer to the below blog.

https://rajusdynamics365ax.home.blog/2019/09/16/example-of-using-oninserting-event-handler-d365fo/

Thanks,

Girish S.
jt1024 185 on at

Like (0)

Report

RE: How to add data to a report from a table extension. Is a Query extension required?

Hello Mohit,

Thanks again for your time. I was pulled away from this ticket for a while, but now I'm back on it.

When I look at the Methods of the PurchPurhaseOrdeHedaer.WWIExtension table I don't see an insert method, and the UI doesn't let me create one. I must be doing something wrong:

when I right click the method this is what I see:

When I try to 'view code' on the table: it's grayed out:

How do I add that code you suggested above?
GirishS 27,816 Super User 2024 Season 1 on at

Like (0)

Report

RE: How to add data to a report from a table extension. Is a Query extension required?

You can also use OnInsertingEvent handler of the table to populate values to the newly created field.

Thanks,

Girish S.
Suggested answer

Mohit Rampal 12,550 Super User 2024 Season 1 on at

Like (0)

Report
RE: How to add data to a report from a table extension. Is a Query extension required?

You have completed steps for adding field in SSRS report. However, the logic is still pending to populate the field value. Every time report is run, PurchPurchaseOrderDP class deletes all records in PurchPurchaseOrderHeader table and then insert with new data.

Please create extension class of PurchPurchaseOrderHeader table, CoC on insert method and write below code

[ExtensionOf(tablestr(PurchPurchaseOrderHeader))] public final class PurchPurchaseOrderHeader_T_Extension { public void insert() { this.WWINamedPlace = PurchTable::find(this.PurchId).WWINamedPlace; next insert(); } }