Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics AX (Archived) / AOT Query -> SSRS :...
Microsoft Dynamics AX (Archived)

AOT Query -> SSRS : One of the data sources is showing blank values in my SSRS report

(0) ShareShare
ReportReport
Posted on by Momochi 888

Hello awesome community!

I am making an SSRS report to show the items Expiration Date. I made the following query in the next screenshot:

6177.Capture-2.PNG

The strange thing is, all the other 2 data sources (InventTrans & InventDim) are showing values in my report's table while "InventBatch" values are showing blank values in my report's table.

What is strange is that in my DP class, I added a range for the "InventBatch" table and the report does run correctly according the range assigned to the "InventBatch" data source.

I tried restarting the SQL reporting service and Dynamics Object Service but nothing happened.

I tried clearing the cache through tools sub menu too.

I tried removing the range code too and the issue still persists. 

What should I do?

Any advice would be appreciated :)

Thanks in advance!

*This post is locked for comments

  • Momochi Profile Picture
    Momochi 888 on at
    RE: AOT Query -> SSRS : One of the data sources is showing blank values in my SSRS report

    Hi Mr. Sohaib,

    When I use InventBatch::find (itemdID, inventbatchID).ExpDate I do get data.

    But when I try to get it from the query InventBatch.ExpDate it gives me a blank ExpDate.

    It's not only ExpDate, all of InventBatch fields give blanks if I used the object from the query. While InventTrans and InventDim are working fine they give correct data.

  • Sohaib Cheema Profile Picture
    Sohaib Cheema 46,610 User Group Leader on at
    RE: AOT Query -> SSRS : One of the data sources is showing blank values in my SSRS report

    One thing that i can think of

    Every InventTrans will not have InventBatch, which means only those transactions can show batch number where those were processed with Batch Record. such as if you have received some inventory using batch number. I would not expect each InventTrans to have a related record for batch. Watch your join type please and also requirement of what you want to display

  • Momochi Profile Picture
    Momochi 888 on at
    RE: AOT Query -> SSRS : One of the data sources is showing blank values in my SSRS report

    Hello Mr. Martin,

    I am using the query inside a DP class and the data I am getting is correct, it's just when I try to use any field in InventBatch it gives blank vlaues. But when I add a range for InventBatch using Querybuilddatasource.addRange(), it works, the query gets affected and the data is correct. But as I said, can't get any InventBatch data.

    I tested the query on the SQL server and it does return correct data and I did get data from InventBatch, but inside my DP I'm getting blanks from InventBatch.

  • Martin Dráb Profile Picture
    Martin Dráb 230,466 Most Valuable Professional on at
    RE: AOT Query -> SSRS : One of the data sources is showing blank values in my SSRS report

    It seems that you're trying to test both the query and your report at the same time and then you don't know which part is wrong.

    Also, you didn't tell us anything about your report, namely whether it's based directly on the query or it is using a data provider class.

    You should split your debugging to smaller pieces. First check if your query returns what you expect. If it doesn't, there is no point in using such a query in a report, because the report will never work. If it does return the right data, you'll now that you should stop focusing on the query and rather review how you're using the data in your report.

    If you use an RDP class, you can also change it to return dummy data, which will allow you to test the report independently on the query.

    And don't forget that you may be currently getting an older version of your query, stored in SysLastValue.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Congratulations 2024 Spotlight Honorees

Kudos to all of our 2024 community stars! 🎉

Meet the Top 10 leaders for December

Congratulations to our December super stars! 🥳

Start Your Super User Journey

Join the ranks of our community heros! 🦹

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,735 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,466 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans