Custom message to replace ‘No Data in the query’
I have come across lot of requirements where there is a need for suppressing the “No Data to Retrieve” message in WebI. So sharing how it could be accomplished when using RDBMS Based data sources such as Oracle, MS SQL Server. I am looking out for a solution to achieve the same when using BICS connection (Will Update this post when i find a solution).
Use Cases:
1. User required a custom message instead of “No Data Retrieved”
2. Scheduled report in PDF/XLS should always reach the user even if there is no data in the data source. Unlike earlier, if data is not retrieved, the report does not get sent when scheduled.
By using a combined query, following are the steps to add custom message.
1. Create a Dummy Object in the Universe with a value which will never appear in the report:
For Example,
2. Union the existing query in query panel using Dummy Object.
Ex. If existing query had Source, Value objects, then add a combined query with Dummy Source, Value
SQL Query:
3. Now apply a report level filter to filter out the dummy value from messing around the report blocks.
4. Now add single standing cell for a custom error message, say on the header part. And camouflage the font & Background.
5. Create a conditional formatting rule, an Alerter, to make the text visible, when the Dimension value is equal to the Dummy value.
Hoping that the above steps helps. Let me know in case you need any help on this. Or if you find any issues.
Great Webi trick !
You've been added to Webi 4.x tricks : summary for a better visibility. Keep posting !
William
Hi,
A little optimization hint.
In your example you use the field data.value in the dummy select. It is better to select from DUAL (in Oracle) instead because then you don't have to access data in the DB. If your data table have millions of rows, then it would take some time to receive the dummy row.
Br Martin
Thanks Martin. Good addition to the doc.
I could use 2 Dummy objects from universe to avoid the performance hit.
SELECT 'Custom Value', 1 from DUALRgds
Nagavaibhav
If you add UNION, then it will degrade the performance of the report query.
Hi
My data source is Bex, so how i can avail the above feature, can u pl suggest...
thx
mahi
You can't do this in BEx as it requires creation of dummy object in a universe.
Also, this example is very simple i.e only two objects selected, but if you have say 10 selected you need to replicate the other 9 after selecting the dummy.
I did something similar against SQL Server 2008 and did some pre-work to find 1 record which met specific criteria and set up my union query to bring that back along with the dummy i.e no matter what number of rows are returned in the first part of the union the second only returns 1 row. Oracle DUAL does the same thing but I'm not sure if SQL Server has an equivalent.