Financial Reporting - Parent summation

[dt_mc]

Hi - 

new to BI.  Have a table construction via a function in SQL as such:

account_id	parent_account_id	parent_account_id	account_type_code	depth	adj_amount
1000	NULL	CATEGORY	Assets	0	NULL
1001	1000	CATEGORY	Current Assets	1	NULL
1002	1001	Account	Inventory	2	300
1002	1000	Account	Intangibles	1	500

Goal is to create a financial report where say everything depth <1 is visible by default but can drill down into the other components via the parent account (so get to Inventory through Current Assets).  

Question is how can I get the parents to inherit the children's adj amounts? Any suggestions on the best control for this? I have to assume matrix.  

[v-juanli-msft]

Hi @dt_mc

“a financial report where say everything depth <1 is visible by default”

This can be achieved by adding a filter in Visual level filters.

"drill down into the other components via the parent account"

"get the parents to inherit the children's adj amounts"

"I have to assume matrix"

What results on the matrix visual would you like?

As I tested, columns added to Row field can be drilled down into others.

Additionally, please refer to Matrix article to know how to use it.

Best Regards

Maggie

[dt_mc]

Hi thanks for the reply.

Output would be based on account_type_code and the roll-up of the amount - here's an example in excel that i was looking to replicate.

  

[dt_mc]

Hi - 

i made modifications to use the Hierarchical data type in SQL now, so i have an inherent path for my nodes.   https://docs.microsoft.com/en-us/sql/relational-databases/hierarchical-data-sql-server?view=sql-serv...

How can that be utilized in BI?