Hi there,
Another question for you guys because there's something I find quite strange.
I am busy making a kind of report/worksheet to show the cash flow of a company. The report should be editable (=adding rows and making calculations in that row) after the reports has been refreshed, because the person who it's for want to make specific calculations in it. And that's the problem.
When I add a row and then I refresh my report, the content of the added row (and calculations) disappears and all the data under this row moves one row up without the layout apparently because of the use of the NP Datefilter formula. When I delete this formula and just use the values in my report adding a row and refreshing is not a problem.
I've just noticed that it's not the NP Datefilter that causes this problem, but the NF function that refers to the NL function to get the Period Start Date: =NL(;"Date";;"Period Type";"Week";"Period Start";"010112..311212"). When this formula is somewhere in my Excel file, the row problem appears again… :evil:
I searched quite a while on this problem and now I found the problem maker (datefilter formula), but I'm curious why this formula causes this problem?
Thanks!
Bert
Date
Votes
2 comments
-
Jet Reports Historic Posts Official comment Hi Hughes,
Thanks for the information.
Somewhere I find it normal that you can edit a Jet Report after you have ran it with the possibility to refresh the data in it. In particularly the financial reports. You can't get all the calculated figures out of NAV (or you want to calculate them on a different way), so you must be able to calculate them in your Excel and update your report again to get the latest info. At least, that's my opinion. :)
I tried your solution (unchecking the box "Enable NF function optimization"), but without any luck…
Then I just changed the NF formulas to NL formulas and the problem was gone. :D
Case closed! ;) -
Jet Reports Historic Posts Hi Bert,
So using Jet this way (adding extra rows and adding data in report mode) is not generally a supported way to use the product. Due to the way NL(Rows) and NL(Columns) work, extra data entered in those rows/columns can just get deleted when the rows/columns are deleted and recreated. Also, if you are preparing a report for a viewer, you have to put +Values or +Lock in cell A1. In this case, when you run the report, the Jet formulas get moved to a hidden worksheet and get replaced with the value. Then when you refresh the report or enter design mode, those formulas all get copied back in, which can overwrite changes you make while the report is in report mode.
It sounds like you are not running into any of these scenarios though. I'm guessing you are not using +Lock or +Values on your report and thus the Jet formulas remain in report mode. Since it seems related to the presence of an NF formula, I think it's related to a speed optimization made for NF formulas. Due to the nature of this optimization, when an NF formula is present on the sheet, the formulas on the sheet get copied to a hidden worksheet similar to how +Values and +Lock work. Thus when those formulas get copied back in order to refresh the report, it would overwrite changes you have made to the worksheet in report mode. You can actually disable this optimization in the Jet application settings on the Advanced page (the "Enable NF function optimization" setting). I'm guessing if you uncheck this box, your problem may go away.
I should warn you that you are using Jet in a way it is not intended to be used, and this sort of use may break again in the future. There is a way for you to enter data into a report in report mode and have Jet preserve it, but it doesn't generally involve creating new rows/columns so I'm not sure it would be right for what you're trying to do.
Regards,
Hughes
Please sign in to leave a comment.