BusinessObjects Board

Xcelsius not returning full results via QAAWS

other Xcelsius - Crystal Dashboard Design
#1

I’m seeing something very strange here…
When I run a query through QAAWS via Xcelsius, the resultset is INCOMPLETE.

Here’s the full details:
I’ve created a QAAWS with a top 36 rank and one optional prompt within that rank.

When I view the output in the QAAWS preview (during the edit phase) I see all 36 results when I leave the optional prompt blank.

If I put any valid value in that prompt, I also get 36 results, but ONLY IN QAAWS application.

When I try to utilize that same webservice within Xcelsius, it only returns a portion of that result set… typically 7-11 rows depending on the value I use.

The initial load of the Xcelsius query, which ignores the prompt, pulls the correct 36 rows.

I recreated the same query in WEBI, and all 36 rows every time.

Now the only debugging I know how to do in this is to use Fiddler to see what is being passed, and it all looks CORRECT… with the exception of the result I receive from the Webservice, containing only 7 rows (or 9, or 11 etc…)

Again when I view it within the EDIT/preview of the QAAWS tool it shows all 36 just fine…

I tried to use Fiddler to see if the requesting call to QAAWS was identical between Xcelsius vs QAAWS Tool, but it appears the preview tool is using some other form of connection than what it would do ‘outside’ the tool and the calls are very different. (Though I can see the parameters are the same)

Is there any other application besides Xcelsius where I could call the QAAWS to see if the issue is QAAWS vs Xcelsius?

Has anyone seen this odd behaviour?

JPetlev (BOB member since 2006-11-01)

#2

hi!

can you post your xcelsius file?

thanks,
r.A.

R.A. :philippines: (BOB member since 2008-10-28)

#3

Actually there is no need to anymore… I confirmed the Exact cause and filed a ticket with SAP.

In case others have this issue here are the facts…

When I created the QAAWS query, I utilized a Rank object in the filter window. However in order to populate the rank correctly I had to add a WHERE statement in the rank object. (Totally supported, and is a drag-n-drop option like anything else).

I noticed the problem by playing with the SQL generated via a WEBI document with the same query statement… basically I played with it bit by bit until I could duplicate the bad resultset produced by QAAWS…

What I found was that the WHERE statement in the Rank Object was being DROPPED when passed to BOBJ. The odd thing is that it is not dropped when you use the Preview Pane in QAAWS, but only when the web service itself calls the query.

As soon as I was able to narrow it down to this fact and before I could update my ticket, I got a call from SAP and they confirmed that this is a known issue with QAAWS and Rank. It is currently slated to be fixed in SP3 Fixpack 1, sometime before end of May 2010.

So bottom line:
KNOWN issue (though hard to find unless you know what your looking for)
FixPack coming sometime in the next 5 months :frowning:

Until then, my Xcelsius objects are going to have to be just a little bit ‘less’ robust.

JPetlev (BOB member since 2006-11-01)

#4

Hi,

We seem to have the same problem. Probably when publishing the QAAWS the where clause is put just after the ranking giving other results then expected when testing the QAAWS inside the tool itself.

We recently installed SP3 and then Fixpack 1 and 2. The problem is still not solved.

armie :netherlands: (BOB member since 2010-02-17)

#5

Hi Guys,

We seem to have the same problem.

Do you have any news about it?

Cheers

szenatti :new_zealand: (BOB member since 2011-02-16)

#6

Hi,

No, the problem still exists. As a workaround we have created a standard filter containing the ranking in the universe and then use that filter in QAAWS.

armie :netherlands: (BOB member since 2010-02-17)

#7

Hi

Facing the same issue…
is there any updates on this.

Armie: Can you please tell me how you created the filters in the Universe
or else is there any work around for it

Please help me out on this

rizwan syed :us: (BOB member since 2010-07-08)

#8

guys -

This is not a problem to hold up your project.

Make a derived table in the universe. Rank the records in your Derived Table SQL.

Cairmor :us: (BOB member since 2008-06-05)

#9

Wow, I forgot all about this post.

Cairmor is correct however on the best workaround. Simply rank your data in either a new DB table, DB View or Derived table manually and filter on that new rank field, instead of the QAAWS ranking option.

I have moved companies since the original post here, and that company had pretty much abandoned QAAWS anyway, so I cannot comment on whether or not it was fixed. We were on Fixpack 2.7 when I left things.

JPetlev (BOB member since 2006-11-01)

#10

Hi Cairmor,

Thanks for the reply, In my dashboard all my prompts are optional and so in the derived table ,when we put the sql with the prompts does it take as optional ?
Can you please tell more of it to create it

rizwan syed :us: (BOB member since 2010-07-08)

#11

All cairmor was suggesting is you create your derived table with it’s own ranking built into the table.
In MS SQL you can use a Rank Over function to create a column, I’m not sure what the function call is in oracle or other dbs.

Once your derived table has that ranking, you build an object in the universe for that rank column and use it like you would any other object.

Now the only issue with this approach is in the time it takes for most sytems to rank large amounts of data.

If you’re trying to let users dynamically rank on MULTIPLE fields, such as one user ranks by MTD sales, another by YTD sales etc… in that case you might need to have your derrived table come up with multiple rank columns, but by then your response time will go down the tubes and you’d be better off having your ETL process create the ranks once per night or something.

Of course your other option is to pester your BOBJ rep/support and find out if in fact it was fixed yet in a service pack and if not try to push them for another date. I was given May 2010, but it sounds like it was never corrected from the posts above.

EDIT: You MIGHT be able to use a universe prompt within a derived table to populate the ‘rank over’ value, but I’ve never done that. I’ve used universe side prompts in joins just fine, but never needed to put it inside the actual derived table. If it works then you should be just fine doing that and maintaining your dynamic ranking abilities.

JPetlev (BOB member since 2006-11-01)

#12

Thanks JPetlev,
Well i have 8 combo boxes based on the combo boxes selection i need ranking to be done.
If the choose with the ranking by QAWS in the latest sevice pack 3 and fixpack 3.5 ,bug still exists now.

In my dashboard i have like 140 QAWS queries ranking based ,so i need to create a 140 derived tables ? What would be the performance ?

If I choose to create derived tables then my prompts should be “Optional” how could i write it ?

Can you please tell me.Or any kind of suggestions is appreciated.

rizwan syed :us: (BOB member since 2010-07-08)

#13

140 QaaWS? I think you need to ask the modeler to redesign the schema. Also, Rizwan, I believe Cairmor & JPetlev are telling you to creat the optional prompts at the query level of Qaaws, same as you do in webi, press the icon next to the operand, & select optional prompts.

katullus :us: (BOB member since 2009-08-21)

#14

Hi,

Like JPetlev I forgot about this post too. Beneath you will find an example of the standard filter I created in the universe. It is based on the syntax of an Oracle database.

What you can do is build in Webi a report with the classification. Then copy the SQL statement and adapt it to what you need. That is what I have done. I am also a expert on Oracle, so adapting was easy, but could be more difficult then you would expect.

Example:

@Select(Klt NAV\Klt NAV oid) IN
(
– Select the highest ranking (rangorde)
Select view_1.oid
from
(

-- Ranking added to find the last Net Asset Value
Select @Select(Klt NAV\Klt NAV oid)
, RANK() OVER (PARTITION BY (@Select(Klant\Klant nummer)) ORDER BY (@Select(Klt NAV\Klt NAV peildatum)) desc) rangorde
from TOT_NAV A_TOT_NAV
, KLANTEN A_KLANTEN
where 
   A_TOT_NAV.KLT_OID=A_KLANTEN.MFD_OID
and
  @Select(Klt NAV\Klt NAV peildatum) <= last_day( to_date( to_char( ( ( @Prompt('Jaar:','N',,Mono,Free) * 100 ) + @Prompt('Periode:','N',,Mono,Free) ) * 100 + 1  ), 'yyyymmdd' )  )
-- Ranking added to find the last Net Asset Value

) view_1
where view_1.rangorde <= 1
– Select the highest ranking (rangorde)
)

The meaning of this to find the last Net Asset Value for a customer based on a given period. The “peildatum” is the date when the Net Asset Value was registered.

I hope this helps you?

Kind regards Armie

armie :netherlands: (BOB member since 2010-02-17)

QAAWS filter on ranking
#15

Hi Armie,

I know the the different ways to do it ,but my question how can we acbieve through QAWS ,or creating the optional prompts in the derived tables.

Solution for this problem is create the webi reports/crystal reports.If you are not good at crystal then build in webi and then pull it through Live office.My problem is client doesnot have a live office license.

rizwan syed :us: (BOB member since 2010-07-08)

#16

Hi,

Standard filters in the universe which you can use in QAAWS is the only solution. We too cannot use LiveOffice.

Kind regards,
Armie

armie :netherlands: (BOB member since 2010-02-17)