BusinessObjects Board

Partial Results

building reports webintelligence
#1

Why would I get partial results from a query that is running counts for each state? So, I only end up with 50 rows
(ex. AL 17
AZ 11
etc)

Yet, Webi is giving me a partial results message

bull76 (BOB member since 2010-04-08)

#2

There are a number, off the top of my head:-

Row level security either universe or DB.
Universe row restriction.
Universe connection time restriction.
Webi query is restricted under query properties to only return a certain amount of rows.

What do you get using the BO log in in a SQL tool?

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

#3

Thanks Mak. Where would I find the BO log?

bull76 (BOB member since 2010-04-08)

#4

I meant if you use the BO log in details, Username and PW as Designer uses, you will be able to prove it is something within Business Objects, causing the rows to be restricted.
I should have added, then run the BO SQL in the query editor…

However, I have given you the main ones that I can think of…

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

#5

Is it because you’re doing the count at the report level rather than at the universe level?

select
state, count(state_info.column)
from
state_info
group by
state

will return 50 rows

select

state, state_info.column
from
state_info

will return many rows, possibly busting your row restriction.

#6

Ok, thanks. I looked in Universe Designer and it says the Max Number of Rows Fetched is 100. Could that be an issue?

bull76 (BOB member since 2010-04-08)

#7

Yep, as well as not defining the measure object as

Count(TableName.FieldName)

as Mark suggested…

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

#8

Most definitely.

Do you have the count defined at universe level or report level?

#9

I created a variable to get the count. I didn’t do it in the SQl as I’m new to this. Here is the SQl from Webi:

SELECT
TRANSFER_ORBIT.tid,
TRANSFER_OLD_HOME.old_state
FROM
TRANSFER_ORBIT,
TRANSFER_OLD_HOME
WHERE
( TRANSFER_OLD_HOME.tid=TRANSFER_ORBIT.tid )
AND
(
TRANSFER_ORBIT.stype_desc In ( ‘Inventory’ )
AND
TRANSFER_ORBIT.ccode Not In ( ‘888’ )
AND
TRANSFER_ORBIT.initiation_date >= ‘01/01/2009 00:0:0’
)

bull76 (BOB member since 2010-04-08)

#10

So what did you want to see as the result?

If it’s just state and count, then you could create a count specific to this requirement in the universe.

SUM(CASE WHEN TRANSFER_ORBIT.stype_desc  In  ( 'Inventory'  )
   AND
   TRANSFER_ORBIT.ccode  Not In  ( '888'  )
   AND
   TRANSFER_ORBIT.initiation_date  >=  '01/01/2009 00:0:0' THEN 1 ELSE 0 END)

You could then just bring this new count object and state into a query and run it.

#11

You should define the Count as a measure in the universe.

Count(TRANSFER_ORBIT.tid)

There is a good post here, by Andreas, with regards to SQL VS report level aggregation, well worth a read if you are just getting started…

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

#12

Yes, it’s just state and count. Can I just manually put the query in the sql and run it?

bull76 (BOB member since 2010-04-08)

#13

Sorry, I saw where you said Universe. Thanks.

bull76 (BOB member since 2010-04-08)