How to use cell contents in a formula

building reports webintelligence
1

Hello,

I have a requirement to total percentage based on the input values given in another column in a webi report.

Example Data:

Total Sample %Value
26 36 Sample/Total
45 98
65 76
98 73

I Have three columns in my table 1)Total 2) Sample 3) % Value

%Value can be calculated by using Sample/Total formula
Sample is coming from Sample dimension, now the tricky part is Total is entered manually by user in webi report, So now the user wants to enter the values for total manually and the %Value should be calculated based on the value entered.

How can I use cell contents of total in %Value formula to get the desired values, Please help me out its a very urgent requirement.

Please help with alternative solutions too.

we are using Webi 3.1

Thank you!!

Mosesbobj (BOB member since 2016-05-19)

2

First thing to clarify is BOXI is not Excel.

There is no concept of cell D5, etc.

View structure and you’ll see that the report is a representation of that structure based on the data returned by the queries.

By very urgent, I take it you’ve said that you can do it and now you’ve found out that you can’t? :wink:

3

Thank you Mark!!

I understand it is a structure based. Can I use BlockName(), ColumnName(),RowIndex() to get the value in a respective cell and use it in the formula?

Mosesbobj (BOB member since 2016-05-19)

4

Have a play and see what you can pull out. I’m not sure what the complete requirement is so I’m not certain that there is something definite that I can give you.

Percentage value is easy as you’ve hopefully figured out.

You could also use input controls and if then else logic possibly but again, without the complete requirement, I’m making shot-in-the-dark suggestions.

5

Hi Mark

Thank you for your response I tried your suggestions, but I could not achieve what I need.

I am confused I thought of implementing the total values in prompts so that user can give the values there then I will pull the UserResponse and use them accordingly but it makes more complicated making me to create more variables. Anyways thanks for your reply, please let me know if you come across any simple solution.

Thank you once again!!!

Mosesbobj (BOB member since 2016-05-19)

6

Hello Mosesbobj, Could you please clear the below line which you have mentioned.
“now the tricky part is Total is entered manually by user in webi report”.
I dont understand how the user is directly entering the value manually.

ravindersinghsond (BOB member since 2013-03-04)

7

yes ravinder apparently that’s the problem :hb:

User is having full access to webi, so he want to manually enter the values for total in Report layer so that the % values should change based on his inputs to total column.

yeah I know that user wont have access to webi and we only give him end result. but my client is having access and he wants it in this way, Please let me know for any possible solutions.

Thank you very much ravinder

Mosesbobj (BOB member since 2016-05-19)

8

Rather than using a multirow table, use a single row table and multiple footer rows.

Either that or take a look at BO’s Live Office for Excel; it may be more suitable for what you want.