Custom message to replace 'No Data in the query'

1.Relational Universe(Oracle,Netezza,SQL Server)

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.

2.OLAP Universe(SAP BW)

1. Stop Tomcat.
2. Delete the folder <Tomcat Installation Dir>\work\Catalina\localhost\BOE. This is a temporary folder and will be re-created once we start tomcat.
3. Navigate to <Tomcat Installation Dir>\webapps\BOE\WEB-INF\eclipse\plugins\webpath.AnalyticalReporting\web\webiDHTML\viewer\language\en\scripts.
4. Take a backup of the file viewPerspective.js and keep it in a safe location.
5. Open the file in notepad and search for the string displayNoDataToFetch.
6. Change the function definition to set your custom message.
   
   

   Original	function displayNoDataToFetch(s,cb){     showAlertDialog("No data to retrieve in "+s,"Retrieving Data",0,cb); }
   Custom message	function displayNoDataToFetch(s,cb){     showAlertDialog("No data to fetch in "+s,"Retrieving Data",0,cb); }
   Prevent the message from appearing	function displayNoDataToFetch(s,cb){     /* showAlertDialog("No data to retrieve in "+s,"Retrieving Data",0,cb); */ }

7. Save the file.
8. Start Tomcat.
9. Clear browser cache.
10. Wait till the temporary BOE folder is fully re-created in <Tomcat Installation Dir>\work\Catalina\localhost folder.
11. Test by running a WebI report from BI Launchpad in HTML view which has no data to retrieve.
12. Here is the customized message :

Sourcce : https://blogs.sap.com/2014/01/13/custom-message-to-replace-no-data-in-the-query/