X++ Count & Group by Question

[JoelHJames]

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,

[MartinDrab]

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?

[JoelHJames]

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.

[JoelHJames]

Here is my SQL code

[MartinDrab]

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.

[JoelHJames]

Sorry I was trying to delete the other post.
How can I see the SQL query generated from code?

Thanks,

[MartinDrab]

It’s very easy in AX 2012:

select generateOnly forceLiterals … your query …
info(distributionTable.getSQLStatement());