"WHERE" function in WebI?

building reports webintelligence
1

Hi,

Does anyone have an update on when the “WHERE” function used to define context of measurement in formulas will be included in WEBI?

Secondly, is there any workaround for the same?

I’m having a report where in i want to calculate the count of occurences of certain variable which has values “Y” or “N”

To give more details, i have a column (database object) named Claim Number and want to count the claim number WHERE some condition is met.
In my report i’m not displaying all the details of the claim number but i’m aggregating the total (count of claims) at the region and division level.

let us say out of 50 claims 25 are pending (identified by the flag v_pending as Y or N), the lay out of report should display as follows:

=====================================
Region Division Claim Count --Table Heading

RGN_NM1 DVSN_1 20
RGN_NM1 DVSN_2 30 --Body Cells

Totals: 50 Footer 1: Grand Total

Pending Count: 25 Footer 2: Pending Totals

Ideally had it been in thick client (6.5 and before) the formula for the pending count would have been:
Count() Where (<v_pending>=“YES”)

Now in WEBI, i’m trying to rewrite the formula as follows but it gives me #MULTIVALUE error

=Sum( If([v_pending]=“Y”;1;0))

or as Count(If([v_reserve_pending]=“Y”;[Claim Number];0);Distinct)
which gives me improper results.

Please help.

vaidhyee (BOB member since 2005-12-20)

2

Where is avalable in XI R2.

Anil Kumar :india: (BOB member since 2005-04-19)

3

Hi vaidhyee,

Check this link regarding the #Multivalue Error…

http://www.mis.admin.uq.edu.au/resourceLibrary/Formulae%20and%20Variables.shtml

Thanks ,
Starter

Starter Of Bo :india: (BOB member since 2005-07-21)

4

I don’t see “Where” function in Webi 4.0, though it was very much available in XI 3.1.

Has it been dropped? :!:

naveenu :india: (BOB member since 2012-07-14)

5

Hi,

No, it’s still there.

What kind of trouble do you experience?

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

6

Then why don’t I see it in the list of function when I try to create a formula in WEBI?

Actually I wanted to create a formula having both SELECT clause and WHERE clause.

For e.g

select database_name
from databases
where 1=1

naveenu :india: (BOB member since 2012-07-14)

7

Are you creating this as a derived table? Or do you intend to use the above syntax in a definition of an object?

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

8

In a formula in Webi not in IDT… can’t I do it if both SELECT and WHERE are available in Webi?

naveenu :india: (BOB member since 2012-07-14)

9

You are mixing to types of syntaxes:

  1. SQL - it contains the keywords SELECT as well as WHERE.
  2. WebI - the syntax that’s used for creating report-level variables. The language does NOT contain the keyword SELECT. However, it does contain the keyword WHERE.

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

10

Thanks indeed Marek… This was the answer I was looking for.
Appreciate it!!! :mrgreen:

naveenu :india: (BOB member since 2012-07-14)

11

Hi, you won’t see Where under functionsbecause it is an Operator. When building a formula, you will see it on the right most panel.
Cheers,
Rogerio

rgoulart :brazil: (BOB member since 2011-08-21)

12

My Bad… :shock: Thanks Rogerio

naveenu :india: (BOB member since 2012-07-14)