Axapta 2009 AOT Reports

Question

Currently I am working in a Report sections. In this I have one doubt.

In my Report I have 3 tables with relation on one field. I want to filter the data based on that common field I used in the JOIN query.

Answer 1

Create a new range on the top level datasource for the field you want to filter by. This will create a parameter for the report that can be used to filter the data. If the joins are set up properly on your report, this should filter your dataset to only contain records containing the selected value.

Answer 2

Just use a range on the specific field. If you equal join on the field in the relations node, it does not matter on which table you put the range on.

Example:

select inventTable
     where inventTable.ItemId == "000100"
     join inventTrans
     where inventTrans.ItemId == inventTable.ItemId 
     join inventTransPosting
     where inventTransPosting.ItemId == inventTrans.ItemId &&
           inventTransPosting.Voucher == inventTrans.Voucher &&
           inventTransPosting.TransDate == inventTrans.TransDate &&                                
           inventTransPosting.InventTransId == inventTrans.InventTransId
{
    ....
}

In this query you might as well use:

where inventTransPosting.ItemId == "000100"