0

Jet Web report aktualization error message in report content

Michal Peták

October 12, 2018 14:22

None

Follow

Hello,
please can you give me advice to this error message. When I Run report from Jet web I get in report content this two error message:

 1. Field '=SCALARCELL(20,$D$6,NF(22,"Number"),$D$3,EXCELFORMULACELL(24,$D$2,".."&$D$2))' cannot be used as a sort field because the data contained within the field cannot be compared. (Exception ID: 1528c495-dc1b-4fea-a94e-da5f1e6a116c).

This error message go from this NL function: (It is for sorting costumer  via Balance - descending)

=NL("Rows";"Customer";"No.";"0";$D$6;"-=NL(""Sum"";""Cust. Ledger Entry"";""Balance (LCY)"";""Prepayment"";""FALSE"";""0"";$D$6;""Customer No."";NF(;""Entry No."");""Document Type"";$D$3;""Filtr dat"";""..""&$D$2)";"*";"Limit=";$D$5)

and 2. Invalid filter "..30.9.2018". (Exception ID: 36073084-8c3d-4105-8cd1-d4192ec039f0).

This error message go from this NL function:

 =NL(;"G/L Account";"Net Change";"no.";$I$24;"Filtr dat";".."&$D$2)/1000

The both report after Run (in excel x Jet web) are folows:

This error is not only cosmetic. Also I had every time when I open this report (from Jet Web) go thru two error message from excel.

And the last one - Before poblish I must make new format in the cell for "date" in report option, otherwise failed all report content.

We won't this report migrate to the jet Cube but now is still like that.

Thank you for advice

 

• Facebook
• Twitter
• LinkedIn

4 comments

• 0
  
  Harry Lewis October 15, 2018 19:36

  Hello Michal -

   

  In attempting to replicate your issue, I first needed to make some adjustments.

  • First, I'm using a US English format... so my functions have commas instead of semicolons.
  • Next, my Cust. Ledger Entry tables does not have a Balance (LCY) field, so I used Amount (LCY) for my example.
  • Next, I changed Filtr Dat to Date Filter
  • I use "Company=" instead of "0" to make it easier to understand.

   

  Now that the formats and fields matched my database, I still have an issue...

  When including cell references within a quoted sort-by-sum, I have to make sure that they are recognized as cell references.

  As you can see, if I edit my function, not all the cell references are recognized:

  

  The three references within the NL(Sum) are just text (notice that they are *not* a different color from the rest of the text).

   

  In order to fix that, I have to add extra quote marks and ampersands:

  

  Now, all my cell references are references the appropriate cells.

  I am able to upload this sample to the Jet Hub and run the report:

  

   

  And I can  open it in Excel Online:

   

  

  Let's start there.  If you update you function to have the needed extra quote marks and ampersands, are you able to upload it to the Jet Hub and open it in Excel online?

  If not, is the error message the same or different?

• 0
  
  Michal Peták October 16, 2018 12:54

   

  Hello Harry,
  thanks for advice. I have fix the NL function like you said. There is:

   

  (Sorry for language (its Czech localization) and my language skills…

   

  

   

  This funktion run well only if I type in option on jet web Date in format for example this 30/9/2018:

   

  Output after run is correct:

   

  

   

  • But the original report is still with the same errors.

  1/2

   

• 0
  
  Michal Peták October 16, 2018 13:03

  2/2

   

  I have tested the function in report from brand new excel sheet and something in option "date" have special behavior in jet web - its down…

   

  • if I type in jet web date 30.9.2018 - run the report with error in content and without data:

   

  

   

  1. If I publish the report with date option in format with ="":

   

  

   

  and run this report in jet web, get out report with content error without data. Its the same if I type 30.9.2018 in jet web.

   

  2. If I publish  date without ="" in design mode and in Jet Web look like this 30/9/2018 ==> goes report well in this test case. In the original report are there still errors (even if I type 30/9/2018) with content and in online excel get not open.

  excel online: (Sheet get not open, its broken or is another type)

  

   

  Its some kind of rule for the option "date" ?

   

  Thank you for help!

   

• 0
  
  Michal Peták October 16, 2018 13:53

  3/3

  So I have try publish the cleaned original report (delete all error comments and with your NL fix) with option definition without ="", and in jet web option is a 30/09/2018 - the result is perfect (open from excel online):

  

  I think it´s in the option "date".

   

  Thanks Michal