Does anyone else need to run a report over mutiple datasources and companies at one time.
I have built some great reports that do this but I'm running into some excel slow issues because to of my datasources are connected to servers that are in INTL countries.
I'm hoping maybe someone else has done this also and has a solution.
Excel is giving my an error saying that "Excel is waiting for another program to stop OLE" everytime i try to make the smallest change to an of jmy design views and then my report takes about 6.5hrs to calculate and run.
Any advice would be great
Sydney
Date
Votes
6 comments
-
Jet Reports Historic Posts Hi Sydney,
In terms of it taking a long time to design reports because every time you make a formula change, there is a long recalculation, you could put Excel into manual calculation mode in the Excel Options (exactly where the setting is depends on the version of Excel). This way, when you change a cell, Excel won't recalculate all the cells which are dependent on that cell. This will probably help, although you'll have to manually calculate the cells when you want values to update when you're in design mode.
As far as the report taking 6.5 hours to run, it sounds like that's mostly due to taking a long time to get results back from the data sources. However, if you want to attach the report, maybe we could look at it to see if there are any formulas which could be made more efficient to speed it up.
Regards,
Hughes -
Jet Reports Historic Posts Not sure if my last reply went through.
I worked with the excel manual calculations a little but this morning but will look at it again.
I'm attahcing the Europe connection for this report, this report alone takes about 40min to run but if i run it in citrix for that server it takes 20sec.
I'm hoping there is sometihng i can do improve preformance as i need to be able to run it here along with the other connections.
And advice is appreciated.
Also note i know there two NL(rows) on one row which is not the preferred method but i need the company information on each row of the AP details and there is no connection to use a different formaula.
Sydney -
Jet Reports Historic Posts Hi Sydney,
Well I recreated your report using the Jet NL(Table) functionality. This should work assuming you are on a fairly recent version of Jet Essentials (and assuming I typed everything correctly). This should be significantly faster when running remotely since it will only send 1 function call per company to retrieve all the data instead of many function calls (which is what is happening currently and the reason that it is so slow when running remotely). Each company will come in as an Excel table in this report. Does something like this work for you?
Regards,
Hughes -
Jet Reports Historic Posts My excel's are tied up running reports right now, so I will run this in the morning. But i'm curious can i build this one on top of the other so i can still get one report for all 19 companies?
Sydney -
Jet Reports Historic Posts Hi Sydney,
I'm not sure what you mean by "build this one on top of the other". You could certainly add more companies to the report. You could even add something like NL("Rows=4",NP("companies")) to replicate all your companies with the Jet formulas for the tables inside the replicated region. Does that answer the question?
Regards,
Hughes -
Jet Reports Historic Posts Yes it does, I ran the report and it took 9hrs. so as great as it looks i dont think it will work.
But it did give me some great ideas to try on my report.
thanks for all the help.
Sydney
Please sign in to leave a comment.