Can you create a subquery on an another query within the AOT?

(0) Share

Report

Posted on by Community Member

I have a created a query within the AOT and I need to add a subquery using the tsql below. This will be the datasource for a SSRS report, so I can not use a view. Is it possible to add subquery to a query? Thanks!

left outer join

(

select t1.ITEMID ,sum(t1.qty)Balance

from INVENTTRANS t1

join InventTransPosting t2

on t1.INVENTTRANSORIGIN = t2.INVENTTRANSORIGIN and

group by t1.ITEMID ) s

*This post is locked for comments

I have the same question (0)

All responses (7)

Answers (1)

André Arnaud de Cal... 301,037 Super User 2025 Season 2 on at

Like (0)

Report

Hi SSRSGuy,

It is possible to create a view in the AOT with several tables and also tsql. Then you can create a query based on this view as datasource.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Thanks Andre, I am creating this to be a datasource for a SSRS report and you can not use views as a datasouce for ssrs reports (that I know of)

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Andre, I just looked at your comment again. So I can base the query on a view? I will give that a try. Thanks!!!

Was this reply helpful? Yes No
Verified answer

André Arnaud de Cal... 301,037 Super User 2025 Season 2 on at

Like (0)

Report

Hi,

I meant create a query in the AX AOT based on the view. Have you tried that? You can also create a dataprovider class with a temporary table to use as datasource for your report.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

I did try that, but I could not copy the summed field in the query to the view. It would not let me drag it. I could drag the field that it was grouped by (itemId). Any idea why?

Was this reply helpful? Yes No
Suggested answer

Malcolm Burtt 1,475 on at

Like (0)

Report

There shouldn't be any reason why you can't use a view here. I would create the subquery as a view and then build a query that includes a join to that view.

Was this reply helpful? Yes No
Suggested answer

Community Member on at

Like (1)

Report

Thanks guys, I did not realize you could base a query on a view.

Thanks again!!!!

Was this reply helpful? Yes No