Debugging Queries errors with Alert
I am sure there are better ways, and probably this is well known already. But as I could not find one, I thought to put it on forum. So here are my two cents.
I must mention, my inspiration is the error log & and the tiny error window in the sap design studio application at run time. So looking at that I thought maybe we can have something similar in SAP dashboards tool to know where the problem lies when the error window pops up.
Generally in my experience the error popups does not have much of a reference, (yes looking at it many times, as a developer, you know where to look, and most errors are repetitive and common, and solutions are easy to find on scn aswell.) But how about knowing where exactly the error is, in other words which particular query is creating the problem.
So when the error pops ups it looks something like in the figure below. And looking at it does not gives me any reference to the query. Now considering I have a quite a number of queries in my dashboard, and manually checking each and every one of them will take time.
So here a way of handling this situation if error pops up. I decided to use hide error message while making queries in the dashboard. This allows me to bind the error message in a cell. So if there is any error, i can use this to know where to look
to start with, I have each query inserted to a different cell. And another column is showing the names of the queries: for example:
As a result of this, any error caused by any of the query, will not pop up in the dashboard designer in the run time but instead will be inserted into the cell it is referred to.
Now, we can use a text insert into any of these cell with a combination of some excel formulas to produce an alert on the dashboard to ensure if there is an error, it is identified and attended.
Using formula =if(cell<>””,1,0), or =if(cell>0,1,0) will return a value 1, whenever an error message is inserted in the cell.
Now another formula that will return 1, if the sum of all these formulas is greater than 0 will make sure, if any single one of these query contains an error message, my dynamic visibility for the alert component changes its value to 1.
Then I can bind this “dynamic visibility alert” formula cell to a component, which can be any image of anything(e.g red exclamation mark) which pops up on the dashboard to attract my attention or icon component for example.
I have used red icon component here, and used its insertion to display a spreadsheet on click (a image and toggle button can achieve the same result).
The red icon appeared on my dashboard as one of the query contained error, because of which it passed value 1 to the formulas column, in the same row. Which lead to the sum of formulas to 1, and it consequently triggered the dynamic visibility of the alert icon.
The figure above shows the error message when icon button is clicked to display a spread sheet containing the information of the queries names and their error messages.
This not only, helps me hide the error messages which does not looks too attractive, but also helps me find the exact error with a reference to which query caused the error.