We had just completed an upgrade of our customer from NAV 4 to NAV 2013 R2. As a part of our first phase, we had gone live with all processes and certain reports which would be needed from the first day itself.
As a part of our second phase we were to complete the remaining reports, many of which would include huge amounts of data.
As we all are aware, a resident problem with report rendering in cases which include huge amounts of data is that quite often you would face one of the below issues:
(1) Time taken to generate output
(2) System out of memory exception
We tried arguing with the client that this is an issue which even Microsoft has not addressed, however, the clients argument was quite simply that if the same report did not have any such issues in the earlier version, why should there be an issue with the latest version (which apparently should be better than the previous ones).
And yes, the client is right, more so in this case as it is a logic counter explanation.
This is when I set about identifying avenues for speeding up the report for starters and then to overcome the memory issue.
I had 2 options (1) To create an SSRS report (2) Use SQL views to create the report in NAV itself.
You could use any option, as both shall give you the same performance, but I opted for the latter as my team comprises of more NAV technical consultants and fewer SQL consultants. This would give us the flexibility of coding more in NAV and less in SQL thereby making issue resolution simpler and faster for my team.
So this is what I did:
(1) I created an SQL view (let me tell you, this is very simple and it also has a visual way of coding which could be used by even those who do not have full knowledge of SQL). My view would join 4 tables (1) Value Entry (b) Item Ledger Entry (c) Item (d) A customized table. Here you need to make sure of one very important point. While naming the query be sure to name it like this: COMPANYNAME$NameOfTheVIEW e.g. Leaping Frog Solutions$ValueEntryMultiJoin
(2) As a second step, I created a table with the same name as my view, i.e. ValueEntryMultiJoin. This table would have the same fields as that in the SQL View.
(3) In the table created in step 2, I set the LinkedProperty = Yes and LinkedTransaction = Yes
And thats it!. You have a table which will always give you the most updated view based on the View created in SQL. So, in essence, I had let the VIEW make data based on grouping required for me, which otherwise would have been done in NAV. Also, the number of lines for sifting of data had been significantly reduced.
I can now, use that table as a data item in my report and the results are almost bewildering. A report that would take 3 hours to complete would now take only 2 minutes. I had actually created a report that was even faster than the one in the customers older version!
Now, for the second issue of memory. All you need to do here is to convince the customer that the report would not have a preview option. I convinced my customer and created a CU which would transfer any report into either Excel or PDF based on the selection. Doing this I overcame the memory issue too!
Thanks for Reading!
As a part of our second phase we were to complete the remaining reports, many of which would include huge amounts of data.
As we all are aware, a resident problem with report rendering in cases which include huge amounts of data is that quite often you would face one of the below issues:
(1) Time taken to generate output
(2) System out of memory exception
We tried arguing with the client that this is an issue which even Microsoft has not addressed, however, the clients argument was quite simply that if the same report did not have any such issues in the earlier version, why should there be an issue with the latest version (which apparently should be better than the previous ones).
And yes, the client is right, more so in this case as it is a logic counter explanation.
This is when I set about identifying avenues for speeding up the report for starters and then to overcome the memory issue.
I had 2 options (1) To create an SSRS report (2) Use SQL views to create the report in NAV itself.
You could use any option, as both shall give you the same performance, but I opted for the latter as my team comprises of more NAV technical consultants and fewer SQL consultants. This would give us the flexibility of coding more in NAV and less in SQL thereby making issue resolution simpler and faster for my team.
So this is what I did:
(1) I created an SQL view (let me tell you, this is very simple and it also has a visual way of coding which could be used by even those who do not have full knowledge of SQL). My view would join 4 tables (1) Value Entry (b) Item Ledger Entry (c) Item (d) A customized table. Here you need to make sure of one very important point. While naming the query be sure to name it like this: COMPANYNAME$NameOfTheVIEW e.g. Leaping Frog Solutions$ValueEntryMultiJoin
(2) As a second step, I created a table with the same name as my view, i.e. ValueEntryMultiJoin. This table would have the same fields as that in the SQL View.
(3) In the table created in step 2, I set the LinkedProperty = Yes and LinkedTransaction = Yes
And thats it!. You have a table which will always give you the most updated view based on the View created in SQL. So, in essence, I had let the VIEW make data based on grouping required for me, which otherwise would have been done in NAV. Also, the number of lines for sifting of data had been significantly reduced.
I can now, use that table as a data item in my report and the results are almost bewildering. A report that would take 3 hours to complete would now take only 2 minutes. I had actually created a report that was even faster than the one in the customers older version!
Now, for the second issue of memory. All you need to do here is to convince the customer that the report would not have a preview option. I convinced my customer and created a CU which would transfer any report into either Excel or PDF based on the selection. Doing this I overcame the memory issue too!
Thanks for Reading!
