Skip to Content
Actions

Avoid Sending Email if Report is Empty

August 11, 2014 | 2,485 Views |
 4.0analyticsbi platformbi4bi4.0bi4.1bobj
Follow RSS

You have to complete 4 steps.

1. Create an event

2. Create a script file for database table record count

3. Create the schedule

4. Create a windows task schedule

1- Create an event:

Log on to CMC and create an event.

/wp-content/uploads/2014/08/2014_08_11_12h07_50_517704.png

Go to “System Events” folder and  click on “Create an event”

/wp-content/uploads/2014/08/2014_08_11_12h09_30_517705.png

Fill the text boxes. Choose “File” as type and the path of your “ok” file. Note that the “ok” file (which is “f:\a.txt” for this example) is not created yet. The text file will be created by a script mentioned below. The “f” drive is a drive on your BO server.

/wp-content/uploads/2014/08/2014_08_11_12h11_11_517759.png

2- Create a script file for database table record count

Ok. Now we need a script. This script will;

  • connect to the database
  • check the number of rows in our table
  • if there is record which is corresponding to our query, create the “ok” file

You can find an example file with this kind of script as an attachment.

3- Create the schedule

Go to your BI portal and create your schedule. Don’t forget to select your event.

/wp-content/uploads/2014/08/2014_08_11_12h47_47_517715.png

So, your schedule will never run, until there is a file named as “a.txt” on your “F” drive.

PS: Don’t forget that the schedule looks for the “ok” file which is created after the schedule creation time.

4- Create a windows task schedule

The last step is , create a windows schedule task, which automatically runs your script file every hour or quarter or etc. If your ETL process fails there will be no record in your table and the script won’t create the “ok” file “a.txt”. When the ETL process succeed your script will create the “ok” file and your schedule will run.

To report this post you need to login first.

12 Comments

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

  1. Former Member

    Hi,

    I suggest a somehow simpler solution if you are using Webi dynamic recipient report bursting facility. I established a query extracting the dynamic recipients and “inner join” it with the report source, so if no record exists in the report source, no corresponding record appears in the recipient source. Off course if data is big, double-querying may cause resource problems.

    (0) 
    1. Bulent Ozgul Post author

      Husnu you are right but the disadvantage of your solution is publication runs only once. So if you have reloaded the data, you have to manually schedule the report again.

      With events you don’t have to do that. Events checks the ok file in a certain period until it sends the report. The only thing you have to do is reloading your data (managing your ETL process).

      (0) 
  3. Former Member

    Hi Admin,

    I need a urgent help, I got stuck on the second step, Please let me know where i have to code this script,

    and what are changes i have to do in script, Like “File name” etc??

    Thanks in advance.

    (0) 
  4. Former Member

    Thanks Mahboob,

    My question was where i have to write this code, i mean where in WEBI report, I will be highly thankfull to you if you please provide me the screenshot where i have to write this code, if you don’t mind can you please share your gmail with me on ahmedkhan29789@gmail.com

    (0) 
    1. Bulent Ozgul Post author

      Hi Ahmed,

      You have to put that sql code into the same universe as a “derived table”. Then open your webi report add a new “query” (data provider) and put only the dimension from your derived table.

      This solution is very simple. The main difference with the solution I’ve explained above is that with events you can control your schedule continuously until you get the right data. But with this method the report runs only 1 time

      (0) 
    1. Bulent Ozgul Post author

      Hi Daniel,

      It seems like after the new UI of blogs.sap.com the attached files are removed. I’m communicating with the SAP team to put the file back.

      (0) 

Leave a Reply