Percentage question 1

[dbomrrsm]

I am using webi 6.5.1.

I am trying to calculate a percentage using a count that I have to get as part of a seperate query so I have:

Query 1
Dimension name from tablea
count dimension id from tablea

Query 2
count of something unrelated to the above tableb i.e no direct join and dont want to use a join

I need to show

dim name, count dimension, and then
count dimension/count something unrelated * 100

when I create a variable as above I get a wierd percentage figure that is the same for every dimension name.

How do I get webi to use the count of each dimension/count something unrelated rather than it doing count dimension in total / count something unrelated which is what it is doing now.

Is there a certain function to do this ?

Many thanks

[blue]DBomrrsm[/blue]

[blue]Software code, like laws and sausages, should never be examined in production[/blue][black] - [/black][purple]Edward Tenner[/purple]

 

[blom0344]

I am not familiar with WebI, but in full client this is possible with 2 dataproviders, where the second dataprovider contains only the measure.
This computes okay..

Ties Blom

 

[dbomrrsm]

I am doing just what you suggest Blom but in webi.

I have the two queries and the second returns a count which I want it to use as a constant in the calculation of percentages for the first queries dimensions with there associated measures.

It seems that webi can not syncronise the "data providers" which is a bit disappointing because if I create the variable using a typed in value (therefore constant) for the second query result it works out the percentages correctly.

I am looking at the use of ForAll and ForEach functions in BO - they come up as being available in Webi but I cant find any docs on their use so I dont know the syntax to use - will post back if I find anything and can get it working.

Thanks for your time.

[blue]DBomrrsm[/blue]

[blue]Software code, like laws and sausages, should never be examined in production[/blue][black] - [/black][purple]Edward Tenner[/purple]

 

[blom0344]

If WebI does not allow synchronization, then you cannot mix objects from both dataproviders. I do not think that ForEach / ForAll will be of any use.
[I hope I am proven wrong]

Ties Blom

 

[xlbo]

nope - we had someone from BOBJ in a little while ago and they confirmed that it is only in the latest version of Webi that you can link data providers (v 11 I think they are calling it)

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[dbomrrsm]

it wasnt a seperate data provider it is a seperate query from the same data provider.

[blue]DBomrrsm[/blue]

[blue]Software code, like laws and sausages, should never be examined in production[/blue][black] - [/black][purple]Edward Tenner[/purple]

 

[xlbo]

definitions of "data provider". What I mean is a data provider to the report - ie a query

AFAIK, you can only synchronise queries in the latest version of WeBi

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[skrandel]

Bottom line on this one. There is no way to have WebI (prior to XIR2) do what you want. You cannot successfully combine data from multiple queries. The problem you're having is absolutely predictable.

The count of something unrelated has dimensions in the query.

If you were to do this in the client, you could try the MultiCube function as a way around this.

Steve Krandel
Symantec

 

[dbomrrsm]

using bo 6.5.1 with oracle backend and webi not full client.

Query 1
Dimension A
Count A

Query 2
Count B

Created variable:

Variable A as

=([Count B] ForAll ([Dimension A]))

Then created another variable:

Percentage as:
=[Count A]/[Variable A]*100

Then in report had

Dimension A - Count A - variable Percentage.

Figures displayed worked out correctly !! i.e. for each Count A it displayed this as a percentage of the total Count B.

[blue]DBomrrsm[/blue]

[blue]Software code, like laws and sausages, should never be examined in production[/blue][black] - [/black][purple]Edward Tenner[/purple]

 

[blom0344]

That confirms my initial reply if 2nd dataprovider fetches only a measure..

Ties Blom

 

[dbomrrsm]

Blom

You were correct - didnt realise it from your reply but now you mention it

Have a star.

[blue]DBomrrsm[/blue]

[blue]Software code, like laws and sausages, should never be examined in production[/blue][black] - [/black][purple]Edward Tenner[/purple]