Since Feature Pack 3, Xcelsius allows us to use the Query Browser route to access data. But I ran into a problem. The query prompt can no longer be pointing to a cell in Excel that contains a formula. Does anyone know how to work around that limitation?
I have a requirement to allow multiple values to go to the prompt (i.e. In List) and therefore, the cell where the query prompt is pointing to must contains a formula. Is there an Excel function that can copy the value of a cell that contains a formula to a blank cell such that my query prompt can point to that blank cell instead? Is it possible?
Any help will be very much appreciated. I opened a ticket to SAP Support and the Support Engineer has absolutely no idea on what I am talking about. It is so frustrating.
Let me clarify a little bit. I have performed 3 test scenarios:
Query prompt pointing to a blank cell --> it returns data properly
Query prompt pointing to a cell with hard-coded value --> it returns data properly
Query prompt pointing to a cell with formula --> NO DATA RETURNED and NO ERROR MESSAGE
Please note that in test scenario 3, the query prompt is optional prompt, and the formula is simply “=IF(J17=”","",J17)" where J17 is just a blank cell. So the actual result value of the formula is blank. But it seems that the query prompt is picking up the formula and not the actual result value of the cell.
Has anyone encountered such problem? I wonder if it is a bug on the Query Browser. The same formula works when accessing the data through the Netweaver OLAP connection. It just does not seem to work with Query Browser. But I want to publish the dashboard to BI Launchpad and not to BI/JAVA.
That is a good idea. But from a user experience point-of-view, it is not good for the user to have to do that. This might be a good workaround if there is no other alternative.
There are ways you could make it seamless from the user’s perspective. I’ve used the Icon with some success in similar situations. If the two cells you use for the Source Data are copies of each other, and it is set to Insert on Mouse Over, then the value would get pushed, and then stay there. It might not be elegant, but it’d work! You just have to find a place to put an transparent Icon where you’d be sure it would be moused over.
I tried the Push Button. It does not work. I display the two cells on the dashboard. The cell with the formula that receives value from the dropdown combo box shows the correct value. But the blank cell that gets pushed to does not display anything at all.
Anyway, I came up with a different approach so that the destination cell no longer contains any formula. So it is working now.
(BOB member since 2004-01-16)
But from a user experience point-of-view, it is not good for the user to have to do that. This might be a good workaround if there is no other alternative.