counting records in report

building reports webintelligence
1

i have a requirement that i need to count cases when goalcnt>0 or barriercnt>0

id countcases goalcnt barriercount product

237 1 0 0 hapa

131 1 0 0 hapa

133 1 0 0 hapa

122 1 1 1 hapa

121 1 1 0 hapa

the above data is pulled using my query in toad.

in my report :i will display product and condition called

checklist .for cheklist variable i used if condition

if ((goalcnt>0) or (barriercnt>0) ;sum(cntcases);0)

i am getting the following data in my report as below

product checklist

hapa 5

i have to get value 2 instead of 5 because only 2 ids has goals .

please let me know if you any ideas

thanks

89sthi (BOB member since 2009-06-24)

2

Hi,

Create the measure using this syntax:

SUM ( CASE WHEN goalcnt>0 OR barriercnt>0 THEN countcases
           ELSE 0
      END
    )

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

3

Hi Mark

Thanks for your reply .i am getting count as 3 instead of 2 .

i have to get 2 because only 2 ids has goal and barrier.

my query is a derived table query .i have used the following by creating a

measure .

SUM ( CASE WHEN GOALcnt >0 OR barriercnt>0 THEN cntcases
ELSE 0
END
)

89sthi (BOB member since 2009-06-24)

4

Hi ,
Try this…
Change the formula for checklist
checklist= Sum(If ([goalcnt]>0 Or [barriercnt]>0;Sum([cntcases]);0) In ([id]))
Instead of
if ((goalcnt>0) or (barriercnt>0) ;sum(cntcases);0)

Thanks
-Satish

forgotUN (BOB member since 2006-12-13)