How to exclude some dimension values in WEBI report calc

building reports webintelligence
1

Hi Experts,

I am facing difficulty in writing a webi variable logic for below requirement.

In below data, for Singapore, we have 4 services but in WebI output user want to see only SRV1 and remaining values to be excluded (hide) from calculation of sales( actual sales is 400, but user want to see value as 100 only against to the SRV1 according to the below mapping aheet). When Country is Malaysia SRV2 and for Japan SRV1 & SRV3 to be included and SRV2 to be excluded. For your better understanding I have added last column (Include /Exclude)

Country Name Service Sales Inculde/ Exclude
Singapore SRV1 100 Include
Singapore SRV2 100 Exclude
Singapore SRV3 100 Exclude
Singapore SRV4 100 Exclude
Malaysia SRV1 100 Exclude
Malaysia SRV2 100 Include
Malaysia SRV3 100 Exclude
JAPAN SRV1 100 Include
JAPAN SRV2 100 Exclude
JAPAN SRV3 100 Include
India SRV1 100 Include
China SRV2 100 Include

Please help in writing this logic :hb:
Thanks in advance
1.png

PHK (BOB member since 2015-12-05)

2

Try a simple IF:

=IF [Country Name]="Singapore" And [Service]="SRV1" Then [Sales]
ELSEIF [Country Name]="Malaysia" And [Service]="SRV2" Then [Sales]
ELSEIF [Country Name]="Japan" And ([Service]="SRV1" Or [Service]="SRV3") Then [Sales]
ELSEIF [Country Name] inlist("Singapore";"Malaysia";"Japan") Then 0
ELSE [Sales]

The first three take care of your multi service countries. If that falls through set Sales to 0 for those same countries. Lastly, all other conditions = Sales.

datawizard (BOB member since 2015-01-20)

3

Did you want to exclude only the sales and still show the SRV2,3,4 lines, or suppress the SRV2,3,&4 lines entirely?

Bill K (BOB member since 2011-02-24)

4

Hi,
I have done this at Table level filter 8)
Thanks

PHK (BOB member since 2015-12-05)