0

Slow report on Analysis view using GL function

David Caruana

March 28, 2018 13:37

None

Follow

Hi

Have prepared a report which uses the NL Rows function to read data from the Analysis View Entry and also the Analysis View Budget Entry. Up till here the report is fast as it's done in a few seconds.

 

The problem is when I need to show the amounts in columns rather than in rows, ie have the Jan - Dec for the Actuals and another 12 columns for the Budgets plus some comparatives. To do this I tried with the GL and also the NL Sum functions on the Analysis View Entries but both have slowed down the report drastically. The report has around 600 rows, having around 30 GL functions on each row and is taking nearly 30 mins to run!

Is this normal for around 18000 (600*30) GL functions calls and is there a faster way of preparing the report?

 

Thanks

Dave

• Facebook
• Twitter
• LinkedIn

1 comment

• 0
  
  Harry Lewis April 03, 2018 14:15

  Hello David -

  There are a number of factors that can affect the speed of a report.

  These include (but are, by no means, limited to) such things as:

  - the number of Jet functions
  - the number of Excel calculations
  - the version of the Jet Excel add-in
  - the version of Excel
  - the speed of the connection to the data (including database type, version, and connection driver)
  - the latency time of the connection to the data
  - the number of inter-sheet references in the workbook
  - having multiple NL(Rows) functions on a single line [or multiple NL(Columns) functions in a single column]
  - running a report with +Values
  - running a report with the progress details expanded
  - running a report with cells on an Options tab formatted as TEXT 
  - running a report with the application setting Preserve Page Breaks enabled
  - running a report with the application setting Preserve Conditional Formatting in Excel 2010 or later enabled
  - the sheer number of records in the database through which the report must search to accumulate the requested data

  From what you describe, running a report that returns 10 GL results per second from an analysis view sounds about right.  

  When you test the speed of your data source connection...

  

  what is the response time?