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

(0) Share

Report

Posted on by jt1024

197

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?

I have the same question (0)

All responses (21)

Answers (2)

Suggested answer

Mohit Rampal 12,563 Moderator on at

Like (0)

Report

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

Hi Jim, I believe there is a new custom field in PurchTable that you want to add in this custom report. If Yes, then don't add PurchTable dataset in report, instead add new field in table linked with dataset 'PurchPurchaseOrderDS (If field was added in purchtable) or add it in PurchPurchaseOrderLine (if field was added in PurchLine table).

After you added the field, you need to right click report dataset and refresh. Other existing datset fields would be populating from DP class, you need to map your new field there.

Was this reply helpful? Yes No
GirishS 27,825 Moderator on at

Like (0)

Report

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

Hi Jim,

Have you selected the fields from the field selection window after selecting query?

Thanks,

Girish S.

Was this reply helpful? Yes No
jt1024 197 on at

Like (0)

Report

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

The query is not connecting, I'm going to try Mohit's suggestion.

Was this reply helpful? Yes No
Suggested answer

Bharani Preetham Pe... 3,634 Moderator on at

Like (0)

Report

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

For this particular development there is nothing to do with that query given. Even the fields are added to temp and if dataset is not refreshed also it will show same query.

The dataset here in report is nothing but a temp table which will be added when you select RDP in report when adding dataset. So on selecting the dp class, it will show the temp tables and fields will be added and the query which you are showing in properties will appear.

As suggested by Mohit, just add the field in that temp table, report design and in dp, set the data for that field.

Please let us know if you face any issues.

Was this reply helpful? Yes No
jt1024 197 on at

Like (0)

Report

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

Hello Mohit,

I'm uncertain how to follow your suggestion: " add new field in table linked with dataset 'PurchPurchaseOrderDS "

Though I do see others before me have achieved this when I examine the fields already present in PurchPurchaseOrderDS dataset in the report:

- Jim

Was this reply helpful? Yes No
jt1024 197 on at

Like (0)

Report

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

Okay I dug a bit further, but still a bit stuck. For the benefit of readers I discovered this

When you click that elipsis button (...) it takes you to this:

From there I can look at all the fields in PurchPurchaseOrderHeader:

But the field I want is in an extension of PurchTable. I'm going to look at linking the field from there to PurchPurchaseOrderHeader as Mohit suggested.

Was this reply helpful? Yes No
Mohit Rampal 12,563 Moderator on at

Like (0)

Report

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

I am assuming most of fields in PurchPurchaseOrderHeader would be populating value from PurchTable. It's fine that your new field is in purchTable because user will add value in Purchase order but you need to copy paste same field in PurchPurchaseOrderHeader.

Let me know if still any issues.

Was this reply helpful? Yes No
Verified answer

Bharani Preetham Pe... 3,634 Moderator on at

Like (0)

Report

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

The report is to show the data when click on button using given parameters. So the custom fields will be in the tables like PurchTable or SalesTable. But it is not required to link the PurchTable via query or dataset. You need to add the same field which is in PurchTable to either the 2 temp tables here.

Once that is done, you will already have the PurchTable buffer in dp class. From there you need to pick this field value and insert that here in temp. There is nothing kind of linking here. What Mohit is suggesting is we have 2 tables. 1 would be header and 2 would be line. So you will add PurchTable field in header temp.

Please let us know if you are stuck anywhere.

Was this reply helpful? Yes No
jt1024 197 on at

Like (0)

Report

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

Hello Bharani,

Thank-you for your time in trying to help me with this problem. It is much appreciated.

When you say "You need to add the same field which is in PurchTable to either the 2 temp tables here." forgive my lack of knowledge, but how do I do this?

- Jim

Was this reply helpful? Yes No
Mohit Rampal 12,563 Moderator on at

Like (0)

Report

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

Create Table extension of PurchPurchaseOrderHeader table.

Open PurchTable table extension in Designer, Right click and copy the field from PurchTable table extension.

Open the PurchPurchaseOrderHeader table extension then right click and paste the field.

Was this reply helpful? Yes No