I have set up a web service connection to pull in 4 columns of data: 1. CATEGORY 2. SUBCATEGORY 3. PRODUCT TYPE 4.#OFCALLS. There will be anywhere from 1 to 500 rows of data depending on day of week and time of day. The web service sorts the list by #OFCALLS descending. The OUTPUT VALUES of the web service connection are mapped to cells A19:D518.
Formulas in the spreadsheet take the Top 10 from the list and populate it to cells A4:D13. A formula in cell D15 attempts to sum the #OFCALLS for the entire web service connection output (A19:D518).
The data in A4:D15 is mapped to a spreadsheet table on my canvas. When the report generates, the data from the TOP 10 of the web service connection populates properly, but the cell that should reflect the TOTAL#OFCALLS (D15) shows zero.
Is it not possible to do additional calculations in the spreadsheet based on data generated by the web service connection? A major difference between using an XML Map and a Web Service Connection is the XML Map actually populates the spreadsheet with the new data when it is refreshed. The Web Service connection data never actually appears in the spreadsheet itself, even though it updates to the appropriate vessel on the canvas. This makes me think that no additional calculations can be done in the spreadsheet based on the web service connection data.
Can anyone tell me if that is correct? If so, then the web service connection would also have to pull in the TOTAL#OFCALLS along with the other data. This would also seem to greatly limit what you could do with data generated by a web service connection, so I am hoping I am wrong and just don’t understand how to do this.
Can anyone clarify this for me? If I failed to give any pertinent info let me know what it is, and I will update my post.
Hit preview, and than to file / snapshot / current excel data.
Then you should see all the data that you have in the excel.
You can maybe have a look at it, or post it here.
Probably there’s some mistake in the excel formula, or in the data connection, because if I understand your question correctly, this should be possible.
Hi all. It has taken me several days to get back to this. Thanks very much for the replies. Wimvg, thanks alot for the file/snapshot/current excel data tip. I didn’t even know that existed.
I have looked at that, and am even more confused that ever. In the current excel data spreadsheet the calculations are there. Even though they don’t appear in the value box on my canvas tied to that spreadsheet cell. I am attaching both the xlf and the current excel data spreadsheet as requested.
In the Xcelsius spreadsheet the data brought in by the web connectivity populates cells A19:E520 (number of rows varies each time it refreshes). Cells A4:D13 read the tOP 10 located in A19:a28.
Cell d15 sums D19:D520. (There is a matching formula in cell D521.) Cell D14 sums D4:D13 and subtracts that from the total in D15.
On the canvas, there are value boxes containing the values in D4:D15. When you preview the report, the values in D4:D13 appear as they should. However the values in D14 and D15, which are calculated from the data brought in by the web service, show 0. They should show the values 656 and 1026 shown in cells D14 and D15.
I have no idea why they don’t show as calculated in the spreadsheet. Am I using a wrong type of container for them on the canvas? Is there something similar to Crystal Reports’ “While Reading Records” where I need to set an order somehow?
OK, I am challenged. Didn’t know we couldn’t attached XLFs. Please let me know if you need more info since you don’t have the XLF to review. All the containers on the canvas linked to the spreadsheet are VALUE boxes.
BOxcelsius, thanks for your answer. I thought we were limited to one web service per spreadsheet? In the book Xcelsius 2008 Dashboard Best Practices it says a visualization can only have one “Web Service” connection, one “XML Data” connection, and one “FS Command”.
I read your post to mean add a second Web Service connection. Is that correct, or did you mean add another field to the existing Web Service connection that sums up the ticket count?
Thanks for your reply! I am new to this and really appreciate your help!
(BOB member since 2007-09-12)
(BOB member since 2010-02-12)
Didn’t know we couldn’t attached XLFs. Please let me know if you need more info since you don’t have the XLF to review. All the containers on the canvas linked to the spreadsheet are VALUE boxes.