How to Schedule those reports only with Data

system · January 7, 2011, 8:43pm

Hello All,

I have one requirements to schedule those reports only which has data.we want to fail schedule those reports which don’t have data.

How can i Achieve this requirements?

Thanks for your help.

Pooja

ktm (BOB member since 2008-11-12)

system · January 7, 2011, 9:20pm

Add a query that results in a /0 when there is no data. For example:

select 1/count(*)
  from the_table
where ...

If the conditions produce no rows, then the query will result in a /0 error, causing the report to fail.

Joe

joepeters (BOB member since 2002-08-29)

system · January 7, 2011, 9:26pm

Joe,

Thank you for your help.Where should i need to add this Query to ?

Can you please explain in detail.

Thanks

Pooja

ktm (BOB member since 2008-11-12)

system · January 7, 2011, 10:54pm

[Moderator Note: Moved from CMC to XI Scheduler]

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

system · January 8, 2011, 5:16am

If I am guessing it right, then you could edit the query SQL and add a query similar to what joepeters said using a UNION (combined query) operator.

BO_Chief (BOB member since 2004-06-06)

system · January 10, 2011, 12:38pm

A UNION, or another object in the same query, or a separate dataprovider. Anywhere that will cause the query to fail if the required data isn’t there.

joepeters (BOB member since 2002-08-29)

system · January 10, 2011, 2:22pm

Thank you all for response, so my understanding is as below:

1)Edit query

Add Combined Query

3)Make it Union

4)Save the report and Schedule.

Final output, if the report has data it will schedule, if not than it will fail.

So, to meet this requirements i need to do all of my reports?

I did the above steps with and without data both is scheduled into my inbox.

We just want to scheduled those reports which has data.

Thanks

Pooja

ktm (BOB member since 2008-11-12)

system · January 13, 2011, 2:06am

Hello All,

Can you guys advice me what should i need to do.As i have mentioned in my previous post that i used union but it does not seems working.

Any thoughts?

Thanks

Pooja

ktm (BOB member since 2008-11-12)

system · January 13, 2011, 1:33pm

Run the report interactively. If you’ve done it right, and there’s no data, you should get a SQL error.

Joe

joepeters (BOB member since 2002-08-29)

system · January 13, 2011, 2:12pm

Does anyone know if there is a more elegant solution in V4? Maybe the Job\webi\crystal service is smart enough to know when there is no data and passes back a code to the Job service that sends the email to an alternate source or does not send at all.

tmcd (BOB member since 2005-10-02)

system · January 13, 2011, 3:43pm

Hi !!

In the first query result I have Detail object, Payment

Query filter:CustomerName

Union

In the second query result I have Detail object, Payment

Query filter:CustomerName

When i Scheduled into my InBox it is successful and I can see the Data.I have tried to scheduled with data and without data both is scheduled fine.

.This is our requirement.

Any help?

Thanks
Pooja

ktm (BOB member since 2008-11-12)

system · January 13, 2011, 4:27pm

What’s the SQL?

joepeters (BOB member since 2002-08-29)

system · January 13, 2011, 5:55pm

Query1

Union

Query2.

Thanks
Pooja

ktm (BOB member since 2008-11-12)

system · January 13, 2011, 6:26pm

No, seriously.

joepeters (BOB member since 2002-08-29)

system · January 17, 2011, 1:46pm

I created one object in the universe which is measure

And the Code is Below:

case when count(Customer Number) > 0 then 1/0 end

Now, in the Query of webi I have taken the above measure along with the another objects but it does not work.It Scheduled with and without Data.

I have also taken the above object in a separate Query but there is no effect.

Don 't know how we can achieve this Req:

Thanks
Pooja

ktm (BOB member since 2008-11-12)

system · January 18, 2011, 1:26pm

If you run the query interactively when there is no data, do you get results or a SQL error?

joepeters (BOB member since 2002-08-29)

system · January 18, 2011, 1:51pm

No, I am not getting any SQL error or anyting.I scheduled the report with Data and No data still it can be scheduled into my Inbox.

So, Is there anyting I need to do to make this happen “Scheduled only those reports which has data”.

Thanks

Pooja

ktm (BOB member since 2008-11-12)

system · January 18, 2011, 1:58pm

I asked if you got the error when running the report interactively (not scheduled).

case when count(Customer Number) > 0 then 1/0 end

I assume this isn’t a direct copy/paste, since there’s a space in the field name. But as it is, it’ll fail only when there is data.

joepeters (BOB member since 2002-08-29)

system · January 18, 2011, 2:31pm

No, When i run the Query there is no SQL error.

I have customer Number Object, Payment and in the second query i have taken the measure which i created in the universe.

case when count(Customer Number) > 0 then 1/0 end.

When i ran the Query there is no effect.

FYI- we have Space in the Customer Number object.

Thanks
Pooja

ktm (BOB member since 2008-11-12)

system · January 18, 2011, 2:34pm

What does the complete query look like?

joepeters (BOB member since 2002-08-29)