Can anyone show me an example of how to do a while select count joining multiple tables and also returning the counts with 0?
Thanks,
Please give us more information. Should it be in an X++ query, an SSRS report or where? What’s your version of AX? What do you mean by “returning the counts with 0”? What problem did you run into when trying to implement it?
I am using Microsoft Dynamics AX R2 the problem is that I am fairly new to AX.
I am trying to create an SSRs report using Report Data Provider but my data is
not matching my SQL query which is correct.
I am trying to output a schedule delivery report in AX like this
I am joining DistributionCenterMessageJour, RetailChannelTable, Purchline, DistirbutionCenterMessageJourHdr, and SiteDelivery
but site delivery I had to do an unpivot because the days of the week are columns.
AX Code
Info Log is showing me the following -but is missing the cases with 0 also because the days of the week are separate column I need to figure out a way to unpivot just like my SQL query. I tried using an If statement so that when the ENUM is equal to YES translate that to a string day of the week.
Thank you for taking the time to answer my question.
The code for days looks familiar - let me add a link to your other thread for reference.
I don’t know much about your query, but think you don’t see cases with Count(Cases) == 0 because such records don’t have data in some of related tables. Don’t you agree?
Note that looking at the actually SQL query (generated from your X++ code) might help you with debugging.
Sorry I was trying to delete the other post.
How can I see the SQL query generated from code?
Thanks,
It’s very easy in AX 2012:
select generateOnly forceLiterals … your query …
info(distributionTable.getSQLStatement());