Introduction:
In some reports business needs to populate a blank row or column, or needs to hide some of the Cells data from a particular Row or Column in a BEx Query. For explaining how to achieve this type of requirement I will take some scenarios which will be self explanatory & will provide you the clear understanding of where to use & how to use this.
Scenario 1: How to create a Blank Row
If the business requirement is such that, there is a Row which will display as Heading of some sub items having data. I will explain this scenario by taking a small example as shown below.
Here we have to create a heading “Managed Deployed Payroll” which contains 2 sub items Payroll 1 & Payroll 2 which will display data for achieving this type of requirement follow the below steps.
1) 1) Create the row structure by creating a formula for “Managed Deployed Payroll”.
2) Provide the name of the formula as “Managed Deployed Payroll” & click on edit.
1) 3) In the Detail View of the formula just put a 0 or any number.
1) 4) Then go to the display setting of the formula & select Always Show.
1) 5) Once you have completed the query structure including the rows & columns we will have to create the cell definition, right click on the cell & select New Cell Reference from the available options then click on edit & change the display setting of the cell by selecting Always Hide.
1) 6) Copy the cell reference which we have created in step 5 & paste it in all the cells for hiding the entire row.
2) 7) Once you have completed the query check for warnings & Errors & save it.
3) 😎 After executing the query you will get the blank row which will be used as Heading “Managed Deployed Payroll” as shown in the below output.
In the second part of the 1st scenario I will explain the requirement in which we have to create a blank row not for creating a heading but for separating some rows from each other.
1) 1) Create a formula without any description in the row structure as shown below.
1) 2) Then follow from step 3 of the 1st part of the scenario.
3) Output of the query will look like as shown in the below screenshot.
Scenario 2: How to create a Blank Column
1) 1) Complete the desired row structure & create formula for which you have to create blank column.
2) 2) Provide the name to the formula & put a 0 or any number in the formula definition.
3) 3) Change the display setting of the formula as Always Show.
4) 4) Create cell definition by selecting New Cell Reference, edit the display setting of the cell & change it to Always hide.
5) 5) Copy the cell definition & paste it in all the cells of the columns to hide the entire Column which we have to display as blank.
Once you have completed the query check for warnings & Errors & save it. After executing the query you will get the blank Column which is showing in the below screen shot for “Growth in 2012” & “Growth n 2013”
Scenario 3: How to hide some specific cells data in Row/ Column
In this scenario if the business requirement is such that we have to display data in some specific rows/ columns & hide or display as blank the rest of the cells. Follow the below steps to achieve this requirement.
1) 1) Create the desired row & column structure of the query. In the column where some blank cell is required create the formula whatever calculations needs to be perform & display as result in the specific row.
1) 2) Apply blank cell definition as explained in the previous examples for all the cells except in the row where we have to populate the result calculated by the formula.
1) 3) The final result of the query will looks as shown in the above screen, here you can see the output calculated by the formula in the row named total & rest all the cells will display as blank.
The above 3 scenarios are enough to understand the different types of requirement & the steps by which it can be achieved in the query designer.
1
3)
- SAP Managed Tags:
2 Comments
