Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics AX (Archived) / SSRS Report: The Loadi...
Microsoft Dynamics AX (Archived)

SSRS Report: The Loading Time is Too Long and Then It Shows an Error Lost Connection

(0) ShareShare
ReportReport
Posted on by Momochi 878

Hello awesome community! :)

I'm still a newbie in AX so please bear with me explaining haha.

I've created a report that shows the expired items.

My query is :

InventTrans > InventDim > InventBatch

So, I want to check all the transaction of the items to look for expired items that is still remaining.

The data is big so it takes a long time in Loading screen and then it gives me an error saying that "connection is lost"

But If I used a filter like for example: item number, it will work fine. So my problem is when I want to get all the data.

I used a query Object in AOT with a DP Class.

Here is my prcoessReport method code:

 qr = new QueryRun(query);

    while(qr.next()){
        invTrans    = qr.get(tableNum(InventTrans));
        InvDim      = qr.get(tableNum(InventDim));
        InvBatch    = qr.get(tableNum(InventBatch));

        if(InvBatch.expDate < today() || InvBatch.expDate - InventTable::find(invTrans.ItemId).NearExpiry < today()){
                reportLine.ItemId           = invTrans.ItemId;
                reportLine.ItemName         = invTrans.itemName();
                reportLine.Qty              = invTrans.Qty;
                reportLine.inventBatchId    = InvDim.inventBatchId;
                reportLine.InventLocationId = InvDim.InventLocationId;
                reportLine.expDate          = InvBatch.expDate;
                reportLine.insert();
        }
}

Please, if you can advice it would be appreciated :)

Maybe using a select and a while select is faster and better than using a "query object"?

NOTE*: I check if the items still remaining or not in the design. If the Sum of quantity in the batch id is "0 Zero" that means no items are remaining so I will hide the records from the table. Maybe there is a better work around? since it will fetch un-needed data.

Thanks in advance!

*This post is locked for comments

  • Sachin Mittal Profile Picture
    Sachin Mittal 490 on at
    SSRS Report: The Loading Time is Too Long and Then It Shows an Error Lost Connection
    Hi Monochi , I have similar requirement with Invent Trans and my query is taking too long to execute so that report fails with message connection lost, did you find any workaround for this issue??
  • Martin Dráb Profile Picture
    Martin Dráb 230,198 Most Valuable Professional on at
    RE: SSRS Report: The Loading Time is Too Long and Then It Shows an Error Lost Connection

    You should start with identifying which part is slow. If it's the query, optimizing the inserts won't help you. I don't say it's a bad idea, but if you have a much more serious problem elsewhere, you should fix that first.

    When you know you have a problem with the query, I suggest you look at the actual SQL code (which sometimes reveal wrong relations or so), execute in SSMS and collect statistics and the actual execution plan. This will tell you how the query is executed by SQL Server, which part consumes the most time, how many records are fetched and so on. It might also suggest an index.

  • Suggested answer
    Sheikh Sohail Profile Picture
    Sheikh Sohail 6,125 on at
    RE: SSRS Report: The Loading Time is Too Long and Then It Shows an Error Lost Connection

    Exactly Sir your understanding is correct, But make sure your job Insert data Incrementally. You can make not exists join between your staging and Invent Trans this join will help you to improve your batch.

    Just Create simple regular table with the required fields.

    Create batch job and write your query and insert data in staging table then set the recurrence as per your requirement (1st time it will take time in execution)

    d365technext.blogspot.com/.../batch-job-in-just-4-steps.html

  • Momochi Profile Picture
    Momochi 878 on at
    RE: SSRS Report: The Loading Time is Too Long and Then It Shows an Error Lost Connection

    Thanks Mr. Sohail for the help!

    I'm still new and learning in AX and don't know much yet.

    What you are saying is interesting me. So basically as what I understood from your comment is to make a table and a job, the job will fill the table with the data I need, and then let the report read from that table?

    Is that correct? because it's new to me so I don't know haha.

    If you have a link that has a tutorial or a guide to "Staging Tables", please share.

    Thanks again!

  • Momochi Profile Picture
    Momochi 878 on at
    RE: SSRS Report: The Loading Time is Too Long and Then It Shows an Error Lost Connection

    Here is my updated query, please look:

    3660.Capture.PNG

    I still get the same Long loading screen into the error "Connection Failed".

    The changes I did are:

    1- excluded "InventBatch" data source from the query and used InventBatch::find() in code instead.

    2- did as what you suggested to me, to put all the fields in the "group by" and applied "Sum()" function to "Quantity".

    If I used a filter for example: I used an "Item Number" in the select button above the report screen, it will work fast and the data are correct.

    the problem is if I wanted to use the report to scan all the items with no filters, hence it will time out because of long loading.

    I'm sorry but I don't know what should I try next haha.

    I'm trying to find a way to avoid InventTrans in the query but I need the quantity and Item ID.

    Thanks!

  • Martin Dráb Profile Picture
    Martin Dráb 230,198 Most Valuable Professional on at
    RE: SSRS Report: The Loading Time is Too Long and Then It Shows an Error Lost Connection

    If I simplify it for a moment, let's say you want merely quantity and warehouse at the moment.

    In select statement, you can do this:

    select sum(Qty) from invTrans
    join InventLocationId from invDim
    group by invDim.InventLocationId

    With the query framework, you can use addSelectionField() and addGroupByField():

    invTransDs.addSelectionField(fieldNum(InventTrans, Qty), SelectionField::Sum);
invDimDs.addGroupByField(fieldnum(InventDim, InventLocationId));

    Note that all fields must either be used in GROUP BY or they must have an aggregation function applied (sum, maxOf or so).

  • Suggested answer
    Sheikh Sohail Profile Picture
    Sheikh Sohail 6,125 on at
    RE: SSRS Report: The Loading Time is Too Long and Then It Shows an Error Lost Connection

    Hi Momochi,

    As we all knows that invent trans contain huge amount of transaction so it will take time. what I can suggest you for improvement.

    Improve your query performance by selecting only required column instead of selection of all.

    Create staging table and keep cook data(Already prepared data) in staging table using Batch Job and run your report from staging table with minimum column.

    Use Record inserted list instead of direct insertions.

  • Momochi Profile Picture
    Momochi 878 on at
    RE: SSRS Report: The Loading Time is Too Long and Then It Shows an Error Lost Connection

    Thanks Mr. Martin for your time to help!

    It does take too long like 10 minutes and then a time out occurs.

    My requirements are to show:

    Item Id, Item Name, Quantity, Batch Id, Warehouse Id, Expiration Date.

    And yes, you are correct it should be grouped by the warehouse with summing the quantity for each warehouse.

    Please, suggest me to how I should modify the query?

    Thanks again!

  • Prasad.V Profile Picture
    Prasad.V 185 on at
    RE: SSRS Report: The Loading Time is Too Long and Then It Shows an Error Lost Connection

    Momochi,

    Try to use SrsReportDataProviderPreProcess class if you are not using it already and change the query to use bulk insert statement instead of looping through the result set.

  • Martin Dráb Profile Picture
    Martin Dráb 230,198 Most Valuable Professional on at
    RE: SSRS Report: The Loading Time is Too Long and Then It Shows an Error Lost Connection

    I suggest you forget reports for a moment and focus on the query. How long does it take to execute it? If too long, there is probably no point in using it in a report, because that won't make it any better. You should optimize the query first.

    And I would start by reviewing your requirements. If you want only a warehouse ID and quantity, there is no point in returning all the other fields. Also, don't you actually want to summarize quantity (grouped by warehouse ID) instead of returning every individual transaction.

    There is also a way how you can execute long-running queries without running into the timeout (by using pre-processing), but in this case, it seems that you really should improve your query instead.

    By the way, a select statement and a Query object producing the same SQL code will take the same time to execute, so there is no point in changing that.

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

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Suggested Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

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

#2
Martin Dráb Profile Picture

Martin Dráb 230,198 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans