Returning a zero from a query

building reports webintelligence
1

Hello,
i’m running some queries for a report, and counting the records retrieved from the queries. For some of the queries, i’m expecting zero records; whenever i refresh data i get the message that “No records retrieved for xxx” and then when i want to show the count on a report, the cell is empty. I want to avoid this message and show zero (instead of empty). Can anyone help me with this? Thanks.

monty_mm (BOB member since 2007-11-22)

2

Hi,
I dont think so it is possible in Web-I. I mean suppressing the no data fetch message.

Omkar Paranjpe :us: (BOB member since 2006-02-13)

3

hi,
for some reason, it worked for some queries in BO 5.1.
i expect the same from WI XI.

monty_mm (BOB member since 2007-11-22)

4

Hi,
Did you do it in a Desktop reports?
Then the same thing you can do in the Desk-I reports. but it may not be available in Web-I.

I also expect many things from BO in Web-I but it’s not available :?

Omkar Paranjpe :us: (BOB member since 2006-02-13)

5

Show it on a free standing cell instead of a column in the data block
DeskI:

=NumberOfRows("DP_name")

WebI:

=NumberOfRows(DataProvider([universe_object]))

Please avoid cross-posting, there is another duplicate post here

.

haider :es: (BOB member since 2005-07-18)

6

I think you can amend a server side JSP file to surpress the no data to fetch message, obviously this will affect all reports. If you search you may be able to find a post on this here.

There are at least three ways that you could display your 0 in a block, firstly you could use an alerter to test for nulls and return 0s instead.

You try report formules in your table like:-

=Sum(If(NumberOfRows(DataProvider([universe_object]))=0;0;[universe_object]))

or

=Sum(IF(Isnull([universe_object]);0;[universe_object))

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

7

thanks guys,
i could put it in the format for returning 0 when no data is available. works fine. i am also reading the topic in the FAQ on how to suppress the ‘no data to fetch’ message,

thanks for directing the replies to everyone.
sorry for the cross post as i first posted there and then realised there was a saperate post for WI!!!

Thanks again,
monty

monty_mm (BOB member since 2007-11-22)