Forum Discussion
Financial Reporting - Parent summation
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.
3 Replies
v-juanli-msftCommunity Support
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
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.
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-server-2017
How can that be utilized in BI?
