Microsoft Dynamics AX (Archived)

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

(0) Share

Report

Posted on by Momochi

1,036

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

I have the same question (0)

All responses (14)

Answers (0)

Momochi 1,036 on at

Like (0)

Report
Hello Mr. Crispin!

First thank you for your time to help.

I changed the query to a while select in the DP processReport() method instead of using a query object in AOT, here:

while select * from invTrans join InvDim join InvBatch where invTrans.inventDimId == InvDim.inventDimId && InvDim.inventBatchId == InvBatch.inventBatchId && InvBatch.expDate < 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(); }

I still got the same error unfortunately :(
a picture of an error:

Sorry but if there are more advices, please enlighten me.

Thanks again!

Was this reply helpful? Yes No
udaY-ch 5,133 on at

Like (0)

Report

Hi,

Can you try using field list instead of querying all the fields from the table likely

While select itemId from inventTrans and you use find method to get the itemname, likewise use field list in all the buffers.

Best,

Uday.

Was this reply helpful? Yes No
Momochi 1,036 on at

Like (0)

Report

Thank you Udhaya for time to help!

I tried it and still got the same error :(

Thanks again!

Was this reply helpful? Yes No
Momochi 1,036 on at

Like (0)

Report

Is it possible to do that with the same requirements?

They asked me to show the warehouse id and quantity.

Is it possible to get the same information with avoiding the InventTrans?

Thanks!

Was this reply helpful? Yes No
Martin Dráb 237,965 Most Valuable Professional on at

Like (0)

Report

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.

Was this reply helpful? Yes No
Prasad.V 185 on at

Like (0)

Report

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.

Was this reply helpful? Yes No
Momochi 1,036 on at

Like (0)

Report

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!

Was this reply helpful? Yes No
Suggested answer

Sheikh Sohail 6,125 on at

Like (0)

Report

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.

Was this reply helpful? Yes No