BusinessObjects Board

Avoid sending email if report is blank

building reports webintelligence
#1

Hi,

I have scheduled a report that send out mail on daily basis with an attachment. Problem is even if there is no data, email goes out with blank attachment.

I am using BO XI R2, is there any way to do this.

While searching on this forum, I came across similar topic suggesting a object to be built in universe. I am not interested to do this. Please let me know if there is any work around.

Regards,
Girish

girishoak (BOB member since 2007-02-14)

If the record count = 0 report will fail
Events
Timeout report does not return an error
How to send email alert when data is returned in the report
Schedule report?
Empty Report Shouldn't be Scheduled
Burst Sending Blank Emails
Web intelligence report scheduling based on report data
Dont send report when no data to fetch in scheduling.
BO Edge Crystal report no data
SCHEDULING
Scheduling reports
BO generated emails
E-mail report only when data exists?
Send report in Email only when there is data in the report..
Avoid publish empty reports with Webi XI
How do I not send scheduled reports with no data?
Report scheduling based on the data in webi report
#2

Did you ever get a response from this post. I am having the same issue. My user doesn’t want a blank email, but results only when there are some.

Thanks

tvanpatten (BOB member since 2008-08-15)

#3

The problem with that is that failure to receive an email can mean one of two things.

  1. No data to fetch.
  2. Report/Email failure.

If you explain that to your users they should prefer to receive an empty data set.

Damocles :uk: (BOB member since 2006-10-05)

#4

Thanks for your reply, let me clarify. The email is getting sent as a blank PDF report, because nothing qualified.

I don’t want to send report by email if nothing qualified.

So, from the customer point of view they get an email only when there will be results in the report.

I hope I have explained it better.

tvanpatten (BOB member since 2008-08-15)

#5

You explained it fine the first time. What Damocles is saying is that it’s a user training\expectation issue. Better to receive a report with no data rather than not receive the report at all. That way you know the report ran successfully.

dessa :madagascar: (BOB member since 2004-01-29)

#6

Hmmm, I see what you are saying, it’s a validation that the report ran.

But say we had full confidence that the report ran (maybe another log for the system admin) :lol:

The recipient of the report doesn’t want to get the annoying blank report emails.

How do we restrict the email to not be sent if there is no data for that day? I couldn’t find anything in Webi schedule.

Ted

tvanpatten (BOB member since 2008-08-15)

#7

I was able to implement the same kind of thing using File event and batch jobs.
I setup the job to wait for a trigger file using File Event.
I extracted the SQL from the report in question then I setup a batch job that runs the SQL. If there is data the batch job FTPs a trigger file into the server.
You have to make sure of two things though:

  1. Your report should be scheduled to start before your batch job finishes.
  2. Make sure you have another batch job that deletes the trigger file before your schedule starts.

Hope this helps.
Rachid

rachidb :morocco: (BOB member since 2006-07-06)

#8

How many points of failure in that? Not knocking, but if one of those fails they might not get a report when they should.

Damocles :uk: (BOB member since 2006-10-05)

#9

Thanks for your post.

Wow, I’ll I have take this apart slowly. Sounds complicated. :shock:

tvanpatten (BOB member since 2008-08-15)

#10

I agree there is risk in any of those batches failing, but we usually have paging capability in all our batches. In case of failure they do notify support team.

Rachid

rachidb :morocco: (BOB member since 2006-07-06)

#11

I am using Oracle DB and XIR2. I successfully created similar reports, with the help some previous posting and some of my thinking.

I tried the file event and thought I can do in a different way.

  1. I created a variable in Univerese.
    Name: RowCount
    Type: Dimension or Measure
    Data Type - Number
    Comment: Returns row count if more than 1 row. Otherwise, throws database error. Forces a query to fail if NO rows returned. Helps to control report distribution based on rows returned

CASE count(1)
WHEN 0 THEN TO_NUMBER(‘error’ )
ELSE 1 END
AS int_type

  1. In the report, duplicate the query and replace the selected objects with the new “rowcount” object. (This is to ensure the filter conditions are retained)

I named this query as “force to fail”

  1. In the CMC, Object properties -> Schedule (tab) ->Notification (sub tab) , set notification as “Email notification based on” and configure the person to whom it should notify in case of ERROR

  2. then shedule the doc as usual.

Hope this is simple than file event and guess, it should fit most of the scenarios.

Thanks
Guna

vigi_guna :australia: (BOB member since 2008-07-09)

#12

Guna,
This looks like a clever way to force the failure. Only reservation I have is when the report fails to run you won’t know if it’s because of no data returned or some other reason.

Rachid

rachidb :morocco: (BOB member since 2006-07-06)

#13

I’m looking for a way to force Webi reports to fail if they exceed a specific row count. We want this to apply to ad hoc as well as scheduled reports. We want to force the reports to fail because we don’t trust that users will pay attention to the “partial results” warning that displays on reports if you set the limit on the report on or the universe. Do you know of a good way to do this? We’re on XIr2 with Oracle 10g. It apparently cannot be done on the database side.

collcc (BOB member since 2008-08-04)

#14

Hi Guna,

I tried the steps you mentioned.However, I still get an email when teh report has no data

Can you please let me know what needs to be done

Pragna (BOB member since 2007-07-25)

#15

I am trying to implement vigi_guna’s suggested approach. My universe object looks like this (SQL Server 2005):

 CASE count(1) 
 WHEN 0 THEN CONVERT(int,'error') 
 ELSE 1 
 END

The object does not parse in Designer. When I refresh the report in Webi 3.0, it returns an error "A database error occured. The database error text is: [Microsoft SQL Native Client] : Conversion failed when converting the varchar value ‘error’ to data type int… (WIS 10901).

My query is a “Union All” query that brings back results from an active table and an archive table in the same data provider. I only want to throw the error if I have 0 records returned from “BOTH” queries. The problem with this approach is it will fail if either query returns 0 records.

Any thoughts on how I might use this with union queries in Webi?

I miss Full Client VBA. :frowning:

tdatri :us: (BOB member since 2002-08-29)

#16

Hello,
Does anyone know whether XI3 brings new solutions to this problem?
thanks,
Meutte

meutte :belgium: (BOB member since 2007-12-06)

#17

I have been using a technique that is not WOW but it works. The basic idea is based on the fact that if the report fails then it is not sent it. So, if you force a failure when no records are returned you get the solution.
Now, if you put this simple formula in the report footer:

1/if isnull (Count ({yourdatabase.yourfield}))= true then 0 else 1

// if no records the report will fail because a division by zero and the email will not be sent.
// if at least one record the report will not fail and the email will be sent.

I call this formula “Conditioned error to prevent the delivery of an empty report”.

Optional consideration:
Because I use the report for both “On demand” and “schedule” modes, I added a parameter asking for something like "Do you want to see blank reports? Y/N. The above formula will be evaluated or not based on the answer.

I hope this can help.

Marito (BOB member since 2011-08-18)

#18

Hi,

I’ve been trying to do the same thing and the report still sends an email when there is no data. Any idea why the report doesn’t fail to send email?

I have this formula in the page footer:

=1/If IsNull (Count ([Exception No])) Then 0 Else 1

Appreciate your help.

Thanks,
Stara

stara (BOB member since 2005-12-02)

#19

Try the below approach instead of creating a variable in the footer.
In Webi Rich Client, create a new Data provider(DP2) with one measure object only. Goto ‘View SQL’ and select ‘Use Custom SQL’ and override the sql with the below code.

SELECT
CAST(CASE count() WHEN 0 THEN ‘0 rows returned forced SQL error’ ELSE count() END AS int)
FROM
(copy and paste the FROM and WHERE condition of Data provider 1)

When DP2 runs and returns 0 records, the report fails and doesn’t send it to the user.
When DP2 runs and returns the count of rows then it sends report to the user.

HTH

zigzag13 (BOB member since 2008-08-20)

#20

HI zigzag13,

  Based on your information, i have created the data provider and copy the sql query and paste it. It will work with static input, But where the user inputs are dynamic(prompt), the query,

(copy and paste the FROM and WHERE condition of Data provider 1)

give an error like “Cannot generate the SQL, Please contact your BO Administrator.”

sridharvpy :india: (BOB member since 2011-02-11)