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?
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.
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.
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
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).”
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?
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’
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’.
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.
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.
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.