Cell definition in query with business example.
Cell definition is functionality in Business Explorer – Query Designer which helps us to uniquely define each cell that is present at the intersection of two structures. Hence, this feature becomes enabled in Query Designer only when we are using two structures in the report. By Default, key-figures are always displayed in a Report as part of a structure. To activate cell definitions, we use one more structure and then explore the functionalities of Cell definitions.
We have a scenario where we want to display the result a/b *100 where either of a and b formulas. Now since either a or b is a formula they have scaling factor of 1000 but when we calculate the result of a/b and display it as %.Here BEX has a limitation and it calculates a/b and keeps the factor of 1000 such that the output becomes negligible.
For example result of a is 40 and b (formula) is 50. Now a/b *100 = 0.8*100 =80.but due to scaling factor of 1000 for b, BEX displays the output as 0.080 i.e. 80/1000 and thus the output in the report is rounded off to 0.Thus to avoid this scenario we can use Cell definition and then define the formula for a/b and then define the scaling factor of the cell formula as 1. In that case we get the correct output displayed in the report.
We have a query for sales analysis. This report displays in a tabular format Key figures and KPI’s and shows the Month to date and Year to date data for them. Now as shown below we have the data captured for the report.
Now we can see that for a few key figures and columns like key figure gross revenue and KPI MTD Act % to AOP the value displayed is 0.But the same should not be zero .Now if we go to the cell and change the scaling factor to 1 then the correct value is displayed.
After a lot of research and analysis we found that the root cause of the problem is that for the key figure gross revenue the scaling factor is 1000.We can’t change this key figure as this is being used in other reports as well.
One possible solution to the above issue can be to change the cell individually as depicted but the requirement of the user is such that they are using this data and copying the same in excel and then further using it. Hence we proposed the use cell definition to solve the issue and define the scaling factor for the cell to display the correct data.
We can click on the cells tab and then for each formula we have to first define the cells which would be used in formula and then generate the formula for the concerned cell. We have to make sure that the cells corresponding to the KPI’s used in formula should not be blank and we have to enable them by double clicking on them. Once it’s done a blue icon appears in the cell .It is shown in the screenshot below.
Here we have defined the cells and entered the formula correctly. Once the same is done we have to change the scaling factor to 1 for the formula.Once we complete these changes, we get the correct output displayed for the concerned cells. We can see the sample output for the concerned query as shown below.