Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics AX forum / SSRS Report: The Loadi...
Microsoft Dynamics AX forum
Suggested answer

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

(0) ShareShare
ReportReport
Posted on by Momochi 847

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!

Categories:
Performance
Development / Customization / SDK
  • Sachin Mittal Profile Picture
    Sachin Mittal 482 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 225,985 Super User 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 847 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 847 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 225,985 Super User 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 847 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 225,985 Super User 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.

Helpful resources

Quick Links

Replay now available! Dynamics 365 Community Call (CRM Edition)

Catch up on the first D365 Community Call held on 7/10

Community Spotlight of the Month

Kudos to Saurav Dhyani!

Congratulations to the June Top 10 community leaders!

These stars go above and beyond . . .

more Community News

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 288,768 Super User

#2
Martin Dráb Profile Picture

Martin Dráb 225,985 Super User

#3
nmaenpaa Profile Picture

nmaenpaa 101,148

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans