AX 2009 Report Query / Filter

Hi All

I am having an issue with a report currently under development.

Basically, we have several filters so that the user can filter on various fields.

I am having an issue where-by if I set 2 datasources against the master table, the filters do not work.

One of the child datasources is a 1:1 Inner Join, the other is a 1:N Outer Join - when I set a filter against the child table on the 1:1 the data is valid, however when I then set a filter against the 1:N child table it does not work and data is missing or sporadic.

Is there a way to code in ranges based on a “where” clause?

What version of AX do you use? AX2012 use query filters (not query ranges) may help you.

It would be also good to look at resulting query, to understand exactly what failed.

AX 2009, I can either send XPO or screenshots is as below.

WarrantyTable is a custom built table.

1234.AX Query.png

You mentioned query filters?

Are these coded into the Fetch method?

Apologies not used them before [:(]

Query filters are new in AX2012, they have been added especially to resolve problems with ranges and outer joins. In another words, you seem to run into a limitation of AX2012. You’ll have to use another approach. (Sorry, I missed last time that AX2009 is even in the thread title.)

What I meant by looking at the resulted query was to check the SQL query sent to database. It would tell you what’s wrong with your query, not just that “filters do not work”. Nevertheless I believe that the link above describes the the same problem as you have.

Yes bascially what happens is:-

If I use a filter range on my 1:1 child table with Inner Join, all data is returned.

If I then use a filter range on my 1:N child table with Outer Join, the data returned is sporadic.