Forum Discussion

jonasdedual_SP's avatar
jonasdedual_SP
Frequent Visitor
2 years ago
Solved

Stepped Layout / Hierarchy

Hi there

 

I am trying to create a matrix table that is showing the finance data of Business Central.

 

Unfortuantely I get stock with the Hierarchy / stepped layout.

 

I am trying to create a stepped layout that is calulating all numbers that starts with 1-9. For that I have created a number table:

NumTable = 
DATATABLE (
    "Num", INTEGER,
    {
        {1},
        {2},
        {3},
        {4},
        {5},
        {6},
        {7},
        {8},
        {9}
    }
)

 

I 've then created a calcualted column:

DisplayNumber = 
VAR CurrentAccountNumber = accounts[Number]
VAR Result =
    CALCULATE (
        MAXX (
            FILTER (
                NumTable,
                LEFT ( CurrentAccountNumber, 1 ) = FORMAT ( NumTable[Num], "" )
            ),
            FORMAT ( NumTable[Num], "" )
        )
    )
RETURN
    IF ( ISBLANK ( Result ), "Does not start with", Result )

 

 

This Column will give mee the first character of number.

 

I am using the fields as below:

 

After that I will create a hierarchy. The final result looks like this:

 

I am having now two issues.

 

1. The Display Name is showing me always the first name. This works fine when I am drilled down. But when everything is close it looks like this:

 

 

For Number "2" it is showing "abrechnung ......" instaed of "Passiven" as it is the first alphabetic entry.

 

 

My second issue is that I need to do multiple hierarchies. Not sure if I am using the right aproach. In the database I was not able to find any locic that is seperating the diffrent hierarchies. The only thing I have found was the AccountType in the Account Table. In this column I have the values as below:

 

IT seems like that businesss central is calculating everything that is between begin_x002D-Total and End_x002D_Total..

 

Any suggestion how solve my issues are higly apreciated.

 

kind regards

Jonas