BO generated emails

system · March 18, 2010, 12:33am

Hi All,

We schedule some webi reports and send out the reports by email. Sometimes the reports do not have data return. Are there any methods to generate emails that only the reports contain data?

Please give some advices

many thanks.

jiang (BOB member since 2007-01-12)

system · March 18, 2010, 7:20am

I suspect there is no way. Even if there is no data and the report is scheduled, blank report will go. You can hide blocks if those are null, rest let’s wait for someone if can help on this. Thank you.

aniketp (BOB member since 2007-10-05)

system · March 18, 2010, 8:02am

Hi,

This can be achieved if conditionally make the report fail when there is no data.

So for instance, if your report query is Q, something like:

SELECT ...columns...
  FROM tables
 WHERE ...joins and conditions...

And this query Q does not retrieve any data. Then create another data provider in the report that needs to fail when Q has no data. For instance this query will fail:

SELECT CASE WHEN ( SELECT count(*)
                     FROM ( Q goes here)
                 ) = 0
                 THEN 1/0
            ELSE 1
       END
  FROM DUAL

So now when Q has no data then the 2nd data provider with the above syntax will fail (because of division by 0) so the whole report fails and will not finish in scheduler as successful so no email to users will be sent.

Marek Chladny (BOB member since 2003-11-27)

system · March 26, 2010, 1:41am

Thank you so much for your reply.

Could you please advise where to write the code that you mentioned below in a webi report?

SELECT CASE WHEN ( SELECT count(*)
FROM ( Q goes here)
) = 0
THEN 1/0
ELSE 1
END
FROM DUAL

Many thanks.

jiang (BOB member since 2007-01-12)

system · March 26, 2010, 7:23am

Hi,

You can create it as a custom SQL.

Marek Chladny (BOB member since 2003-11-27)

system · March 26, 2010, 8:59am

Or, if you want to reuse this, create it as a derived table in the universe.

Mak 1 (BOB member since 2005-01-06)

system · March 30, 2010, 1:54am

Thanks all of you for your helps. It works!

jiang (BOB member since 2007-01-12)

system · April 6, 2010, 8:35am

Marek Chladny:

Hi,

This can be achieved if conditionally make the report fail when there is no data.

So for instance, if your report query is Q, something like:
SELECT ...columns...
  FROM tables
 WHERE ...joins and conditions...
And this query Q does not retrieve any data. Then create another data provider in the report that needs to fail when Q has no data. For instance this query will fail:
SELECT CASE WHEN ( SELECT count(*)
                     FROM ( Q goes here)
                 ) = 0
                 THEN 1/0
            ELSE 1
       END
  FROM DUAL
So now when Q has no data then the 2nd data provider with the above syntax will fail (because of division by 0) so the whole report fails and will not finish in scheduler as successful so no email to users will be sent.

Hi, let says i have a simple query like Q is:-
Select * from TableA

So in the report custom SQL, i will hardcode it as
SELECT CASE WHEN ( SELECT count(*)
FROM (Select * from TableA)
) = 0
THEN 1/0
ELSE 1
END
FROM DUAL

But when i validate this SQL, there is an syntax error. Is my way correct?

ylai20 (BOB member since 2008-05-21)

system · April 6, 2010, 8:25pm

So in the report custom SQL, i will hardcode it as
So in the report custom SQL, i will hardcode it as
SELECT CASE WHEN ( SELECT count(*)
FROM (Select * from TableA)
) = 0
THEN 1/0
ELSE 1
END
FROM DUAL

try this
the above should be

So in the report custom SQL, i will hardcode it as
SELECT CASE WHEN ( SELECT count(*)
FROM (Select * from TableA)
) = null
THEN 1/0
ELSE 1
END
FROM DUAL

dsrikanth (BOB member since 2008-11-06)

system · April 7, 2010, 1:37am

Hi,still syntax error, im using teradata script.
Error like below:-
Syntax error, expected something like name or Unicode delimited indetifier or an UDFCALLNAME keyword between ‘)’ and ‘)’

But this one work, is it the same thing?
sel case when b.b1=0 then 1/0 else 1 end
from
(
sel count(*) as b1 from
(sel * from TableA)a
) b

I still wondering how this code can work? What is dual means?
If in a report i have 1 column.
So i custom the SQL like
sel case when b.b1=0 then 1/0 else 1 end
from
(
sel count(*) as b1 from
(sel col1 from TableA)a
) b

But there is an error “column (1) has as invalid type (WIS 10811).”

ylai20 (BOB member since 2008-05-21)

system · April 8, 2010, 7:11pm

sel case when b.b1=0 then 1/0 else 1 end
from
(
sel count(*) as b1 from
(sel * from TableA)a
) b

this one is the same thing. if this works u can use this…

dsrikanth (BOB member since 2008-11-06)

system · May 26, 2010, 5:22pm

I’ve simplified this idea of dividing by 0,in such a way that it neatly fits into a BO measure:
Fail on empty:

case when count(*) = 0 then 1/0 end

Fail on non-empty:

case when count(*) > 0 then 1/0 end

Create these two objects, make them into measures, and in the advanced tab, set them to only be used in the Result section.

Usage:
if you only want the report to be distributed when there are results, add Fail on empty to the results of your query.
In the event that the query rerurns 0 records, the 1/0 will be invoked, and the report will fail.
Likewise with Fail on non-empty, if you want the report to be sent only when there are no results (can be used to alert you of 0 sales, for instance).

Since no one else proposed this solution till now, I’m not entirely trusing myself on it.
Can you please try to tear this solution apart and see if there are any problems with it that I’m not seeing?

-Ethan

ethan1701 (BOB member since 2004-05-05)

system · November 4, 2010, 12:14pm

Hello,

I tried the last mentioned solution:
I created a universe measure with following content:

case when count(1) = 0 then (1/0) end

Unfortunately this does not work.

This does work:

case when count(*) > 0 then (1/0) end

So it seems that when there is no data this measure object is not rendered and thus generates no eror.
When there is data, the measure object does get rendered and the report generates an error.

I’m still looking for a solution to:
‘not sending a report if empty when scheduled’

Best regards,
Ruben

rsa (BOB member since 2008-07-11)

system · November 4, 2010, 1:17pm

case when count(*) > 0 then (1/0) end

Thats because if there is no data retrieved your row count is not greater than 0.

case when count(1) = 0 then (1/0) end

Shouldn’t this be count(*)?

Mak 1 (BOB member since 2005-01-06)

system · November 4, 2010, 1:36pm

Hi,

to clarify my previous post:

In the universe I created a measure containing:

case when count(1) = 0 then (1/0) end

I included this measure in my deski query which I made sure did not return any results. There was no error. So as I said this measure is not ‘executed’.

I now also tried the same with:

case when count(*) = 0 then (1/0) end

This gave the same results.

(I initially tried with count() as a user created object in deski. But I had to change this in count(1) as deski did not accept count().)

When I adjusted my query to contain data and I changed my measure object to :

case when count(*) > 0 then (1/0) end

then the report did generate an error. So now the measure object was ‘executed’.

My initial post was a bit unclear.

Best regards,
Ruben

rsa (BOB member since 2008-07-11)

system · November 4, 2010, 1:56pm

Hi Reuben,

No problem, clearer now :).
I think the main issue is, is that you aren’t incorporating this object in a seperate query, therefore the query fails and the measure does not evaluate.

https://bobj-board.org/t/99674

There an example here, that I like more than any of these, although the principles remain the same.

All the Best,

Mark.

Mak 1 (BOB member since 2005-01-06)

system · November 4, 2010, 3:10pm

I also found that you needed to create a separate query for the Fail on Empty.
What I do is this:

Build my report as usual.
add another query, with a condition along the lines of customer_ID in list (result from another query) and put in it the customer_ID from my main query.
add the Fail on empty object to the results.

case when count(*) = 0 then (1/0) end

-Ethan

ethan1701 (BOB member since 2004-05-05)

system · February 10, 2012, 8:27am

yes

badesudh2 (BOB member since 2009-05-04)

system · February 10, 2012, 11:16am

Click on the blue SQL button on the top, left of center.

-Ethan

ethan1701 (BOB member since 2004-05-05)

system · March 23, 2012, 4:47pm

This looks like a very cool thing but I have a question.

What will happen when the user opens up the report? It will still fail, correct?

This is not good since the users should still be able to refresh the report and see the other or information they want.

i.e.
I only want to send out an email when a certain column is greater than 50.
However, the users should still be able to refresh the report regardless of getting an email.

Joealyche (BOB member since 2012-02-29)