I am using Xcelsius 2008, Enterprise version 5.4.0.0, build number 12,4,0,1166.
I am pulling in data using a web service. The data is coming in properly, and I have a list view displaying the data. Once the data is in I am doing some calculations in Xcelsius and then want to display these results in another list view/spreadsheet table/ grid/value/whatever. I can see when I export the Excel snapshot that the calculations take place properly. However I cannot get them to appear on my canvas. I have tried list view, spreadsheet, grid view, and value box. None of them display the calculations. Tying a value field to a spreadsheet cell is pretty straight forward, yet after doing that, the value box on the canvas remains blank even though the spreadsheet shows the calculation is in the cell.
Does anyone know if this is happening because it’s displaying the results before the calculations have completed? That seems unlikely, but I suppose possible. I can’t think of another explanation. Is there a way to refresh after the intial data pull from the web service? (By that I mean refresh my canvas, not refresh the web service with the refresh connection button).
Any thoughts, ideas?
Anytime you try to preview/publish a swf, and the swf wont display your figures…there is a mathematical compatibility problem with Adobe and Excel. But here are a few things Ive learned:
You have a * in the middle of a SumProduct. That looks odd to me, but you are saying it works outside the SWF, so Im guessing it does. SumProduct is an array based function. Xcelsius ( or the resulting actionscript) cant do arrays (or pivot tables. which are .also arrays). I dont know why its in the supported function list.
My point is…try something new:
Instead of using Sumproduct, which is a very efficient function outside of xcelsius…Two step your formula, and drop a component on that new result. DSUM and DPRODUCT work excellent, but i think you’ll have to do a bit of setup. Also, SUMIF() would work too…but again requires some setup.
I would not waste any more time on Sumproduct…because it is an array based function.
Thanks very much for your review and advice. Unfortunately I started off using SUMIF. With the same results. ALthough everything calculates fine in the spreadsheet (reviewed by exporting the Excel snapshot), the calculations never populate on my canvas. No matter what I try and display them with, the canvas container shows “#VALUE”.
I also read some postings saying sumif degredated performance in Xcelsius and SUMPRODUCT was preferable. So I tried switching. I’d use anything I can get to display my values at this point!
Does the “#VALUE” provide a clue to what the issue is?
The #value error is something I remember from back in the day…with xcelsius 2008 sp1. SAP had a whole bunch of corrupt code for alot of functions.
Given that you have sp4, and its caused alot of different problems for alot of different people, the corrupt code might be back.
anyway, if you can, upload some of your worksheet. Hide/markover the juicy bits, and Ill see if I can get your model Since your modeling is somewhat complex, type the answer of what the formula is “supposed” to be, so if I come up with an alternate way of doing the math, Ill know its right.
Thanks for your kind offer. I have attached my spreadsheet. I added several comments to help sort it out. Please let me know if you need anymore info. WCQA.xls (43.0 KB)
Be kind to your SWF files…they arent made for complex math (Actionscript can make great movies, but not great spreadsheets). For anyone else looking at this thread, the problem, I think, was involving SUMIF and SUMPRODUCT on cells at the same time.
I used DSUM, a much simpler method (in my opinion) to do the same result. I did not test on sp4 (gonna hold off on intalling that one). It works now (tested on sp3 / fp5 An sp3 / fp6 ) WCQA.zip (12.0 KB)
Thanks very much for taking the time to do this. I have not had a chance to review yet - first of the month and all that. I hope to get to it tomorrow. Thanks again for your efforts and feedback. They are much appreciated!
Thanks. Your solution works like a charm! I have not used DSUM before, so this has been a valuable learning experience. I will try and keep things simpler in the future.
Hi, I’m back again. You mentioned this worked on SP3 FP5 and SP3 FP6. When you say it worked, do you mean it displays properly on the canvas or just that the DSUM calculations work?
Although the DSUM calculations work fine in the spreadsheet, the values in cells M15:P20 do NOT appear on my canvas, even though all the data in cells A1:F75 does appear on the canvas.
Over the weekend I took a machine that never had Xcelsius before and installed Xcelsius up to fp35. So the new install is Xcelsius Enterprise 5.3.5.0, build 12,3,5,1128.
Using your spreadsheet, (latest version attached), the DSUM calculations still don’t appear. Did I go too far installing up until 3.5? I thought I was safe up to 4.0.
Let me know what you think. It appears I may need to go back, reinstall once again, and stop at fp3.
Thanks for you help and advice! WCQA1.xls (38.0 KB)
Hi, I am having a similar issue as above. I have a spreadsheet table in my xlf file. The data is coming from 4 columns which can have numbers or the text “NA”. So, I have the following fuctions referencing those 4 cells. The values are coming up fine in the snapshot but not showing up in the GUI when I preview the dashboard. If I take the same formulas and just create a new dashboard with 4 columns of data inserted (having numbers and “NA”). The preview shows the calculated values in the spreadsheet. I have tried removing the component and adding it and remapping the cells again. Appreciate anyone who can help with this
(BOB member since 2008-06-05)