How to perform Union in Reports?

Hi Friends,

I have two tables Table1 and Table2 with same set of columns and datatype, i have added these two tables as a datasources in the query of report ,I set Query type as Union and i dint write any code in report.

My problem is If i set Union in datasource property same set of duplicate records are showing in Report. There is no diff b/w union and unionall!! How to solve this issue? Do the needful.

Do I understand correctly that although you ask “How to perform Union?” but you already know that and your question should be “How to select distinct records?”

Yes for union i need distinct records but i am getting duplicate records also.

Unfortunately I don’t know how to reproduce your problem. Please give us more information.

Do you get the same behavior when executing the query in a job? If it’s somehow related to the report, please tell us how you’re using the query in the report and what kind of report it is. You haven’t even told us which version of Dynamics AX you’re using.


I added two tables in the datasources of the report and i set Query type as Union. I dint write any code i added some fields from the Table1 in generated design body section. It is working for UnionAll(with duplicate records from two tables) but not for Union.

Unfortunately I don’t have any AX 2009 on hand.

I strongly recommend you to test your query before using it in any form. Now you can’t say which part is wrong. Doing too many things together often means achieving nothing.