I have the excel work book in which 4 sheets , of these 4 sheets only 2 sheets have the jet functions say one for the options and another for the report where we get values from the system with the jet functions, other two sheets pure excel sheets one for the "excel table data" in which most of the data is static except a few values referenced to jet functions sheet for the updated values, other sheet is for pivot table from the excel table data . after ensuring all the date in design mode when refreshed the data in pivot table and pivot table gets lost ???
Please some one help to keep the non jet functions sheets data intact.
Warm regards
Ravi
Date
Votes
13 comments
-
Jet Reports Historic Posts absolutely no jet functions those two sheets , except one sheet cells referenced to jet functions sheets ( even this jet functions sheet also only GL balance functions are there no replicators etc.
-
Jet Reports Historic Posts only when the excel table is connected to pivot table it's getting lost , otherwise just keep the excel table its fine, strange !!!!
-
Jet Reports Historic Posts Please some Experts / JET Trainers , your help here is highly appreciated
-
Jet Reports Historic Posts I'm guessing that you will need to include an example file (in design mode) so that we can see how the pivot table data is linked from the Excel table.
Any of the Jet Reports with pivot tables that I have built are referencing sheets with replicated data rows, so I'm surprised this is causing problems.
One question - even on sheets that don't have any Jet formulas, you still need to reserve Row 1 and Column A - did you do that?
the reason I ask, is because if you are asking Jet to add the HIDE into cell A1 - it actually shifts the entire report down one cell and over one column - this could cause the link on the Pivot table to break when the report runs. -
Jet Reports Historic Posts Hello Heather
Sample files sent by email
Yes may be many jet reports pivot tables referencing no rows data is fine , here in my pivot referencing data is in the sheet hardly any jet functions except some values are referenced from other sheet in the work book there again no no rows/columns only gl functions , no replicators.
Yes jet automatically Takes first cell in the sheet for auto hide , there by one row and one column is gone automatically, the pivot table when referencing data range in the sheet automatically accounts for first cell inserting, it works fine . Only when you want keep the data as excel table and reference it to pivot table only the excel table all data get lost and becomes empty excel table -
Jet Reports Historic Posts sample files
jet functions sheet is included , all data is static data only -
Jet Reports Historic Posts Neither of the attached have any Jet functions to them. Can you include the files with Jet functions?
-
Jet Reports Historic Posts Hello
Thanks for having the look at the file, jet functions are not an issue.
These are only some example, in my scenario IO keep another sheet where we get the balances thru GL functions etc and gets updated to the excel table with static information.
What I need to know is excel table used by the pivot table is getting lost when refreshed with jet reports. at the same time instead of using excel table for pivot range , just using the data range for the pivot , the data remains as it is. Also the excel table without referencing by the pivot table also fine. Only the excel table used by the pivot table, the excel table data gets lost.
Is it not possible to use the simple excel table and Pivot table in jet reports? -
Jet Reports Historic Posts please see the three scenarios i am referring to
-
Jet Reports Historic Posts Please treat this as closed
Conclusion : any excel file with excel table as source for pivot table, the excel table static data gets lost completely when refreshed with jet even though without any jet functions in the file? At the same time pivot tables with data range as a source ( instead of excel table) or just excel table without any corresponding pivot table etc. , data remain intact. -
Jet Reports Historic Posts Ravi,
I just wanted to comment on this as what a great discovery. Only a Jet developer could step into the code to tell us why, but I was able to recreate the problem and I am not sure why it does that. I'll get it over to Jet Support and let them take it from there.
Again, great find.
Best, -
Jet Reports Historic Posts Hi
I did check check with jet support and they too confirm this, may be they kept this for the reason to ensure all the pivot tables and excel tables get refreshed fully whenever jet function is run, not only this a few other lose ends they have tightened it in the recent two updates to jet reports which were not the case in the earlier versions , as always need to go for work around by giving up excel table function instead just use the range for pivot table as in any case it's the static data which is supporting the pivot table. -
Jet Reports Historic Posts This issue has been fixed with new version just released.
Please sign in to leave a comment.