BusinessObjects Board

BO generated emails

building reports webintelligence
#11

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)

#12

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 :israel: (BOB member since 2004-05-05)

#13

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 :belgium: (BOB member since 2008-07-11)

#14 
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 :uk: (BOB member since 2005-01-06)

#15

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 :belgium: (BOB member since 2008-07-11)

#16

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 :uk: (BOB member since 2005-01-06)

#17

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

  1. Build my report as usual.
  2. 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.
  3. add the Fail on empty object to the results.
case when count(*) = 0 then (1/0) end

-Ethan

ethan1701 :israel: (BOB member since 2004-05-05)

#18

yes

badesudh2 (BOB member since 2009-05-04)

#19

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

-Ethan

ethan1701 :israel: (BOB member since 2004-05-05)

#20

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)

#21

one option would be to instruct them to only run the queries that retrieve the data, and not the failsafe query.
another (more complex) option is adding an optional prompt to the failsafe object, so that it does something along the lines of

case when count(*) = 0 then (1/@propt(failsafe,'N',{0;1},1)) end

(note that I have no way of verifying that [probably wrong] prompt syntax at the moment)

The point would be that when running the report, you’d be prompted to enter a value in order to either run successfully in any case (1, default) or fail based on your condition (0, to be used in the prompt).

I’d be curious to know if you use this and how it works out for you.

-Ethan

ethan1701 :israel: (BOB member since 2004-05-05)

#22

Cool Idea but I tried this

Select case when count(*) = 0 then 1/@Prompt(‘This is a Test’, ‘N’, (‘0’,‘1’), mono, free) end

And it is throwing a SQL generation failed message.

I will continue to try but so far doesn’t look good… :frowning:

Joealyche (BOB member since 2012-02-29)

#23

it throws an error because it doesn’t reference any tables.
You’d get the same error from an object that simply SELECTs count(*)

I suggest you give it a try in the report itself.

-Ethan

ethan1701 :israel: (BOB member since 2004-05-05)

#24

I just tried it with specific information as follows:

SELECT
case when Count(( DateDiff(Hour, ( GETDATE() ), ( dbo.Z_SM_TASKS_HDR.ERDAT + dbo.Z_SM_TASKS_HDR.ERZEIT )) )) > 1000 then 1/@Prompt(‘This is a Test’, ‘N’, (‘0’,‘1’), mono, free)) end
FROM
dbo.Z_SM_TASKS_HDR

I also tried adding a column or object befroe the case.

Both times the query failed as well :frowning:

But still trying and thanks, let me know if you have another idea. I will continue to post until I either find a way or it just can’t be done.

Joealyche (BOB member since 2012-02-29)

#25

I think you have an extra parenthesise at the end of that sentence. Either way, I’ll give this a try of my own when I get back to the office next week.

-Ethan

ethan1701 :israel: (BOB member since 2004-05-05)

#26

Thanks for your continued efforts Ethan.

I checked the parenthesis and still got the error message. I will continue to see what I can do to get this working(I think its a great solution).

But as mentioned if I cannot I will need to probably duplicate the report and make if fail in some other way.

i.e.
Keeping just this stattement within the other Query has no affect. Its just null ?!

case when Count(( DateDiff(Hour, ( GETDATE() ), ( dbo.Z_SM_TASKS_HDR.ERDAT + dbo.Z_SM_TASKS_HDR.ERZEIT )) )) > 1000 then 1/0 end

Joealyche (BOB member since 2012-02-29)

#27

Okay, I got the SQL to work based on it not returning any data. I actually had to remove the Count() from the line.

Now I am back at square one where the report fails as expected so scheduling it won’t be a problem. However, if the user runs the report manually the report fails as well.

The user can’t get the failure of a generated column I am using for scheduling. So, checking and playing again.

Too bad that the Event part of the Scheduled does not work. Or at least I can’t get it to work. I would not need to go through this but cannot see any other way since I cannot associate a Custom Event to a SQL Query. I think it just can’t be done in Webi !!!

thanks.

Joealyche (BOB member since 2012-02-29)

#28

Here we go again…

I am able to get the object measure to work and the report schedules out fine.

However, I am having an issue since the object within the report can be equal or an acceptable value or not.

Not is when I don’t want to send out the email. I tried creating a nested Case statement but it always seems to work and the emails continue to go out.

I need to desperately figure out how to deal with multiple values within the object so when scheduled if a line meets or does not meet the condition than the email will or won’t go out. Confusing I know… :?

HELP !!!

Joealyche (BOB member since 2012-02-29)

#29

Joealyche, this is how I understand your requirement:
You have a report with several types of information. One of which is the time till tasks are due (Or something like that).
You want to be able to run the report manually and always see the results.
You want to schedule the report, and have it emailed only if there are tasks that were due more than 1000 hours ago.

Here’s how I’d do that:

  1. build your report as usual, with no scheduling failsafes.
  2. add another query, that will return the task ID for tasks that were due more than 1000 hours ago.
  3. build a query that returns only the failsafe object, and has a condition that says task ID in list results from another query, and select the returned object from the previous step.

Note that all this is involves only editing queries, and should have no impact on the presentation of the report.

So now what’s meant to happen, is your earlier queries run as usual. then the query that returns the task IDs runs, and either retreives data or does not (depending on if you have any tasks that are 1000 hours overdue).
finally, the failsafe query runs, and if you selected 0 in its promt, and if there were no results from the previous query, the refresh will fail, and the report will not be sent out.
In any other situation (you selected 1 in the failsafe prompt or there were tasks that are more than 1000 hours overdue), the report will run successfully.

Please try this and let us know how you manage.

-Ethan

ethan1701 :israel: (BOB member since 2004-05-05)

#30

So if I am understanding you correctly. If I have 50 different variations or situations where an email needs to go out then I would create 50 sub-querries?

i.e.
Group 1 = 0 - 10
Group 2 = 11 - 19
Group 3 = 20 - 150
…
Group 50 = 1000 - 3500.

Joealyche (BOB member since 2012-02-29)