Skip to Content

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,

Dummy Source Object.png

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:

SQL Code Snapshot.png

3. Now apply a report level filter to filter out the dummy value from messing around the report blocks.

Query Filter.png


4. Now add single standing cell for a custom error message, say on the header part. And camouflage the font & Background.

No data retrieved.png


5. Create a conditional formatting rule, an Alerter, to make the text visible, when the Dimension value is equal to the Dummy value.

Alerter - Conditional Formatting.png

Hoping that the above steps helps. Let me know in case you need any help on this. Or if you find any issues.

To report this post you need to login first.

6 Comments

You must be Logged on to comment or reply to a post.

  1. Martin Dahl

    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

    (0) 
    1. Nagavaibhav Elluru Post author

      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 DUAL


      Rgds

      Nagavaibhav

      (0) 
  2. Dasari Prashanth

    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.

    (0) 

Leave a Reply