How to view report over million records in NAV 2013

oudam2009@hotmail.com

I have report for Detail Trial Balance that has over million records, it take too much time to running the report,
some time spend 1 day still cannot get any result and some time it hang

Server SPEC
-Windows: Server 2012 R2 Standard 64bit
-Proessor: Intel(R) Xeon(R)
-CPU: ES-2420 v2 @ 2.20GHz (10 CPUs)
-RAM: 20GB

Does anyone have solution pls kindly help to share, thanks

parm

Hi,
I think you should break the problem in small pieces.
If you have 1.000.000 records to print and a page have 50 lines you will have 20.000 pages (that's a lot of paper).
Maybe you don't need to print every detail and print only some grouping values. If this is true, make the grouping inside the dataset instead of sum and grouping at RDLC level.

Regards and good luck,
parm

oudam2009@hotmail.com

Hi Parm,

Thank you for your reply.
but my customer's requirement need to show the detail information like "Detail Trial Balance" for 1 year. approximately record is 1M to 4M records.

so do you have any idea on this?

currently, i use "SAVEASPDF" function to run as background.
it can generate around 900K records. if over this, it will hang.

parm

Hi,
Try to export by month or quarter or half-year.
Another solution, but strange, is export the detail trial balance records in a xmlport (csv file) and open with Excel.

parm

oudam2009@hotmail.com

Hi,
if we export to csv file or excel file, it is depend on excel limitation right?
example, maximum row in excel is only 1,048,576 rows.
any solution?

thank you,
Oudam.

ara3n

You can open CSV files in notepad.

ara3n

You can also use power query from excel and store the data in data model instead of worksheet.
https://powerbi.microsoft.com/en-us/blog/new-updates-for-power-query/

archer89

option 1: export data as csv using xmlport or simply by code. then open the csv file using a csv viewer for large data. follow
http://delimitware.com/
http://recsveditor.sourceforge.net/
http://csved.sjfrancke.nl/

option 2: import the csv file in ms access.

option 3: write the data in a new table in nav, then view the table directly or create a page based on table. you could also view the data the in sql server mgmt. studio.

Wisa123

To be honest i would just run the report monthly or quarterly. Shouldn't make any difference in the end.

Also you could try to optimize the reports dataset, this may very well help you getting more lines faster.

rajavenkat74@gmail.com

if there are 1million You should Definitely try to optimize the Code . Maybe You can use PDF Merger http://mibuso.com/downloads/navpdfmerge

archer89

do you run the report without any filters? if yes, set filters (especially No. and Date Filter), so that the result is smaller. run the report with different filters to get all needed results.