Good morning,
Part of a NPrinting excel report I'm creating is a dump of raw data split up across multiple excel files by product category, along with a long list of calculations based off each data set.
The full data 82 columns wide and about 125,000 rows long and less than 100 MB in size split up across seven or so categories ending up with seven files between 1MB to 50MB in size.
Without calculations, the data is loaded within 5 minutes, however when I add a few (6) excel calculations the preview / report fails to generate. No error, the preview button cycles for minutes (left it running over an hour once) and eventually just stops. If I publish as a task, I'll get a "Some reports failed to generate:" email over an hour later. One time it cycled through one category and failed across the rest. I've attempted to place a filter to limit the data to have it fail one the calculation is added.
Sample Calculation:
=IF(Data!$A$1="","",SUMPRODUCT(((Dealer_State="MD")+(Dealer_State="WA")+(Dealer_State="NE"))*((Reins_Retro_Flag="N")+(Reins_Retro_Flag="R"))*((Transaction_Code="A")+(Transaction_Code="R"))))
I tried it with Name Manager to start, then reduced it to straight range of data when that failed. If I generate the data first, then manually add the same calculations, it's calculated immediately. The end report has dozens of similar calculations.
Stranger still, once it fails, if I remove the calculations, it continues to fail and will no longer generate a report of just the raw data. I have to create a brand new report template. Having said that, I just tried a previously failed report from two days ago and it generated the raw data.
The report is currently being created via a multitude of excel macros and we want to move away from that to something a bit easier to modify and manage while keeping the client's requirements.
Thank you for your time.
