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.
Go to “System Events” folder and click on “Create an event”
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.
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.
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.
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.
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).
Hi Bulent,
Nice and helpful workaround.
Thanks,
Mahboob Mohammed
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.
Hi Ahmed,
This work around is simple, I’m not taking into consideration the very little performance effect it’ll have, for counting the number of rows in that result set.
BO generated emails
Thanks,
Mahboob Mohammed
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
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
good one… thanks for sharing the logic!!!
Nice workaround. Its very helpful 🙂
Where can I find the script mentioned?
Regards
Daniel
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.