0

The pivot table 'PivotTable1' could not be refreshed. The following error was reported: 'Reference isn't valid.'

Theodore Gemmer

July 17, 2018 10:04

None

Follow

Hi Jet Reports team,

I am getting this error when trying to run report "NAV014 - Trial Balance by Month" from Jet Express.

The pivot table 'PivotTable1' could not be refreshed. The following error was reported: 'Reference isn't valid.'

I have clicked the cell with #VALUE and clicked Show Error (Debug), but then I get to Execution Timeout Expired.

There have not been any modifications to the G/L Account table and the Excel file was downloaded with the Report Player as of the past week.

The Data Source is a direct connection to SQL, with NAV 2016 W1 Build 44974. The report runs for about 25 minutes before hitting the error. It runs for about five minutes when debugging and then hits the Execution Timeout.

Thanks for any support you can offer.

 

• Facebook
• Twitter
• LinkedIn

10 comments

• 0
  
  Harry Lewis July 17, 2018 19:48

  Hello Theodore -

  Suggestion #1:  re-download the NAV Sample Reports and try the same report from that.

  Question #1:  How many records are in your G/L Account table?

  Question #2:  If you test your data source connection, how fast is it?

  

• 0
  
  Theodore Gemmer July 18, 2018 04:51

  Hello Harry,

  I tried Suggestion #1, but that did not work (I already had the latest copy of the Excel file).

  • Question #1: There are 358 records in the G/L Account table
  • Question #2: The connection has a 118 ms response time.

  Also, I noticed that in Excel, when I create a table using Table Builder, the NL formula is like in this screenshot

  

  But the NL formula in the report from the Report Player looks like this:

  

  Also, please note that the DataSource when creating a Pivot Table from the table made with Table Builder is called G_L_Account, but the DataSource for the PivotTable1 in the Report Builder report is GLAccount.

  From my point of view, if I change the NL function to be the same as in the Table Builder report, then the report should run correctly. However, I cannot change the data source to G_L_Account before running the report, because the "reference isn't valid".

• 0
  
  Harry Lewis July 18, 2018 14:19

  Hello Theodore -

  When you refer to the "data source", I assume you are referring to the TableName assigned in the NL(Table) function:

  

  Whatever name is used in that function *must* be the name that is used when creating the pivot table:

  

  If you change it in the NL(Table) function, you must also change it in the Pivot Table's settings (and vice versa):

  

   

• 0
  
  Harry Lewis July 18, 2018 14:21

  FYI... a 118ms response time seems slow for a direct SQL connection.  Is that consistent if you test the connection a few times?

  Edited by Harry Lewis July 18, 2018 14:21
• 0
  
  Theodore Gemmer July 19, 2018 09:35

  I deleted the Pivot table from this report and also changed the NL formula to =NL("Table","G/L Account",$E$9:$S$9,"Headers=",$E$8:$S$8,"TableName=","G/L Account","IncludeDuplicates=","True","Account Type","Posting")

  With these settings, I can run the report (and create a new Pivot Table).

  However, if I delete the filter on Account Type the report takes forever to run (it has been running forever and I don't know yet whether it will finish).

  The response time is consistent from my PC at 80 to over 100 ms. From my Azure virtual machine it has a response time of 21 ms.

• 0
  
  Harry Lewis July 19, 2018 14:59

  Theodore -

  If you:

  1) open a blank Excel workbook

  2) enter this function in cell C4

  =NL("Table","15 G/L Account",{"1 No.","2 Name","4 Account Type","31 Balance at Date"},"28 Date filter","31-01-18")

  3) Run the report

  How long does it take for the report to complete?

• 0
  
  Theodore Gemmer July 20, 2018 02:07

  It ran successfully in 18 minutes.

• 0
  
  Harry Lewis July 20, 2018 14:45

  In that case, let's take Jet out of the picture entirely and test just the connection to the data.

  If you have a regular installation of Excel (i.e., *not* Office 365) use these steps:

  Go to Data | From Other Sources | From SQL Server:

  

  enter your server name and click Next:

  

  Select your NAV database and then find the G_L Account table:

  click Finish

  On the Import Data window, click OK

  

   

   

  How long does it take for SQL Server to transfer the data to Excel?

   

   

  Note:  If you have Office 365, the interface is somewhat different.

  From the Data menu, select Get Data | From Database | From SQL Server Database:

  

  The rest of the interface is similar (but with some differences).

   

• 0
  
  Theodore Gemmer July 21, 2018 01:12

  Hi Harry,

  I was able to load that table into Excel very smoothly (maybe less than 30 seconds). No problem.

• 0
  
  Theodore Gemmer July 25, 2018 04:49

  Hi Harry, I am sorry if it sounds like my problem is solved. It still is not solved. The report still cannot load. Do you have any more ideas?

  Please note that I increased the execution timeout. Whereas previously it would run for 25 minutes and hit that error now it has been running for over 40 minutes and still no error.

  Regards,

  Ted