Cell Restrictions in SAP Business objects WebInntelligence

[Former Member]

Hi All,

I have the below requirement in Webi. Can any one please help me out how can I achieve this requirement in SAP WEBi.

I am using SAP BW back-end and connecting by BICS. I can not use Cell Definition functionality in BEx due to profit centers list is not constant.

Group and Amount values comes from BEx Query		WEBi Calculation
Group	Amount	%	Formula
Food Sales	$ 270,657.00	78.20%	B3/B5
Liquor Sales	$ 75,439.00	21.80%	B4/B5
Total Sales	$ 346,096.00	100.00%	SUM(C3:C4)
Food Costs	$ 71,072.00	26.26%	B6/B3
Liquor Costs	$ 16,428.00	21.78%	B7/B4
Cost of Goods Sold	$ 87,500.00	25.28%	B8/$B$5
Payroll-Admin	$ 22,804.00	6.59%	B9/$B$5
Payroll-Operating	$ 76,542.00	22.12%	B10/$B$5
Total Direct Labor	$ 99,346.00	28.70%	B11/$B$5
Total Prime Cost	$ 186,846.00	53.99%	B12/$B$5
Payroll Related	$ 16,388.00	4.74%	B13/$B$5
Laundry & Linen	$ 474.00	0.14%	B14/$B$5
Advertising and Sales Promo	$ 17,624.00	5.09%	B15/$B$5
Supplies	$ 7,203.00	2.08%	B16/$B$5
Heat, Light, and Water	$ 6,695.00	1.93%	B17/$B$5
Maintenance	$ 6,246.00	1.80%	B18/$B$5
General Operating	$ 4,997.00	1.44%	B19/$B$5
Total Operating Expense	$ 59,627.00	17.23%	B20/$B$5

Source data screen shot:

Expected Webi Report.

[Former Member · Accepted solution]

HI Swapnil, Arjith,

I applied the formulas you mentioned. I am getting UNAVAILABLE error in % column.

Thanks

Murali

[Former Member · Accepted solution]

For every columns you are getting error??

Please provide the snapshot and also mention exact formula you are using

[Former Member · Accepted solution]

Hi Swapnil,

Please check the attached screenshot.

[Former Member · Accepted solution]

In your formula Actual is a measure object?

Please try following formula

=[actual]/nofilter([actual] where([gl sets] inlist("Food Sales";"Liquor Sales")))

Also please check the [Actual] measure projection on universe .. It should not be 'Database delegate'

If it is then change it to Sum .

Thanks,
Swapnil

[Former Member · Accepted solution]

Check out this link:

https://websmp230.sap-ag.de/sap(bD1lbiZjPTAwMQ==)/bc/bsp/sno/ui_entry/entry.htm?param=69765F6D6F6465...

Thanks,
Swapnil

[Former Member · Accepted solution]

Thanks

Swapnil.

[arijit_das]

If you want a single table, then you can have the first column hard-coded and use separate formula for each cell. For example,

Group	Amount	%
="Food Sales"	=[Amount] Where ([Group]="Food Sales")	=[Amount] Where ([Group]="Food Sales")/[Amount] Where ([Group] InList("Liquor Sales";"Food Sales"))
="Liquor Sales"	=[Amount] Where ([Group]="Liquor Sales")	=[Amount] Where ([Group]="Liquor Sales")/[Amount] Where ([Group] InList("Liquor Sales";"Food Sales"))
="  Total Sales"	=[Amount] Where ([Group] InList("Liquor Sales";"Food Sales"))	=[Amount] Where ([Group] InList("Liquor Sales";"Food Sales"))/[Amount] Where ([Group] InList("Liquor Sales";"Food Sales"))

[Former Member]

Hi ,

For food sales & liquor sales apply formula
=[amount]/sum(amount where (group inlist ("Food Sales";"Liquor Sales")))

Likewise for food costs apply formula
=[amount]/(amount where(group = "Food Sales"))

For Liquor costs
=[amount]/(amount where(group = "Liquor Sales"))

For costs of good sold
=sum([amount where(Group inlist ("Food Costs";"Liquor Costs")))/
sum(amount where (group inlist ("Food Sales";"Liquor Sales")))

for payroll-admin
=[amount] /sum(amount where (group inlist ("Food Sales";"Liquor Sales")))

like wise create for remaining fields...

Thanks,
Swapnil