How to hide empty row from grouping in SAP Business One HANA "Excel Report" Add-In?

[anastasiya_kovaleva]

Hi Dear Experts!

May be you know if it is possible via "Excel Report" Add-In in SAP Business One HANA to hide empty grouping rows?

I have a requirement from the Customer to create a report with grouping data by Department - Level 1, Department - Level 2, Department - Level 3.

But not all departments have 3 levels, some of them have only 1 or 2 levels.

We manage departments via custom UDTs: the first one UDT - @DEPARTMENT contains a list of all possible entities, the second one - @ORGSTRUCTURE contains relations (parent, child...) between departments for each financial year.

The calculation view has the following fields:

• Date
• Region
• Department - Level 1
• Department - Level 2
• Department - Level 3
• AMOUNT ....

If I create report with groups in rows, in preview mode it shows empty rows for departments that do not have level 3 or level 2.

We need to group data by Year, Month, Region, Department - Level 1, Department - Level 2, Department - Level 3 and do not show empty rows if departments Department - Level 2 and/or Department - Level 3 are empty.

Do you know is it possible or not to do in in "Excel Report" Add-in? Thank you in advance.

Kind regards, Anastasia

[anastasiya_kovaleva]

Thank you, Tammy.

[anastasiya_kovaleva]

Hello Abdul,

Thank you for the responde.

Region - is a parameter. In report rows we need to show details by department, level 2 (if exists), level 3 (if exists).

Have a look at the current report template report-template.png and report preview report-preview-before-tricks.png

As you can see if department doesn't have level 2 or level 3, the rows for level2 and level3 are added automatically. At the current moment I used the formula not to show cell's values in extra rows level 2 and level 3 (for departments without levels).

report-preview.png

But may be there is any any other way to solve the task?

[Abdul]

If you hide how you will know a particular region do not have level 2 or level 3