News and Announcements icon

Dynamics 365 CommunityForums Small and medium business | Business Central, N... Grouping for Totals fo...

Small and medium business | Business Central, N...

Answered

Grouping for Totals for direct query into the GL

(0) Share

Report

Report

Posted on by Peter Jones - BIDA

104

Hi All,

I am wondering if anyone has already written and published the snippet of code we are looking for.

In the GL Account you have the totaling column specifying what sub accounts total to the total account.

In a data warehouse we create a table that records these hierarchies so we have the rows to join.

But in direct query I am not aware of anywhere in BC these relationships are stored other than the totaling column.

Has anyone out there written and published the snippet of code that decodes this totaling column?

Thank you in advance for any assistance you may be able to offer us.

Best Regards

Peter

All responses Img

All responses (3)

Answers Img

Answers (1)

Suggested answer

Peter Jones - BIDA 104 on at

Like (0)

Report

Grouping for Totals for direct query into the GL

Just for those interested. This is the same query using left joins rather than outer apply.

With the 230 demo database it gives the same results. But that might only be luck.

with query01_cte as
(
select
cast(dq_bc_g_l_account.pk_no_col as int)   as gl_account_int
,dq_bc_g_l_account.pk_no_col               as gl_account_text
,dq_bc_g_l_account.name_col                   as gl_account_name
,dq_bc_g_l_account.totaling                   as totaling_string
from dbo.dq_bc_g_l_account
where not (totaling = '')
)
--select * from query01_cte
,query02_cte as
(
select
query01_cte.gl_account_int                                   as gl_account_int
,query01_cte.gl_account_text                               as gl_account_text
,query01_cte.gl_account_name                               as gl_account_name
,query01_cte.totaling_string                               as totaling_string
,cast(split_totaling_string.totaling_string_lower as int)   as totaling_string_lower
,cast(split_totaling_string.totaling_string_upper as int)   as totaling_string_upper

from query01_cte

outer apply
(
select
left(gl_account_split.value, isnull(nullif(charindex('..', gl_account_split.value), 0) - 1, len(gl_account_split.value))) as totaling_string_lower
,right(gl_account_split.value, len(gl_account_split.value) - isnull(nullif(charindex('..', gl_account_split.value), 0) + 1, 0)) as totaling_string_upper
from string_split(query01_cte.totaling_string, '|') gl_account_split
) split_totaling_string
)
--select * from query02_cte
,query03_cte as
(
select
query02_cte.gl_account_int
,query02_cte.gl_account_text
,query02_cte.gl_account_name
,query02_cte.totaling_string
,query02_cte.totaling_string_lower
,query02_cte.totaling_string_upper
,dq_bc_g_l_account_02.pk_no_col               gl_account_text_02
,dq_bc_g_l_account_02.name_col               gl_account_name_02
from dbo.dq_bc_g_l_account dq_bc_g_l_account_02
left join query02_cte on cast (dq_bc_g_l_account_02.pk_no_col as int) between query02_cte.totaling_string_lower and query02_cte.totaling_string_upper
where not (query02_cte.gl_account_int is null)
and not (query02_cte.gl_account_text = dq_bc_g_l_account_02.pk_no_col )
)
--select * from query03_cte order by gl_account_int , gl_account_text_02
,query04_cte as
(
select
query03_cte.gl_account_text_02           as gl_account_text_child
,query03_cte.gl_account_text           as gl_account_text_parent
from query03_cte
)
,query05_cte as
(
select
dq_bc_g_l_account.pk_no_col
,dq_bc_g_l_account.name_col
,dq_bc_g_l_account.search_name
,dq_bc_g_l_account.account_type
,dq_bc_g_l_account.global_dimension_1_code
,dq_bc_g_l_account.global_dimension_2_code
,dq_bc_g_l_account.account_category
,dq_bc_g_l_account.income_balance
,dq_bc_g_l_account.debit_credit
,dq_bc_g_l_account.no_2
,dq_bc_g_l_account.blocked
,dq_bc_g_l_account.direct_posting
,dq_bc_g_l_account.reconciliation_account
,dq_bc_g_l_account.new_page
,dq_bc_g_l_account.no_of_blank_lines
,dq_bc_g_l_account.indentation_col
,dq_bc_g_l_account.last_modified_date_time
,dq_bc_g_l_account.last_date_modified
,dq_bc_g_l_account.totaling
,dq_bc_g_l_account.consol_translation_method
,dq_bc_g_l_account.consol_debit_acc
,dq_bc_g_l_account.consol_credit_acc
,dq_bc_g_l_account.gen_posting_type
,dq_bc_g_l_account.gen_bus_posting_group
,dq_bc_g_l_account.gen_prod_posting_group
,datalength(dq_bc_g_l_account.picture)                                as picture_num_bytes
,dq_bc_g_l_account.automatic_ext_texts
,dq_bc_g_l_account.tax_area_code
,dq_bc_g_l_account.tax_liable
,dq_bc_g_l_account.tax_group_code
,dq_bc_g_l_account.vat_bus_posting_group
,dq_bc_g_l_account.vat_prod_posting_group
,dq_bc_g_l_account.exchange_rate_adjustment
,dq_bc_g_l_account.default_ic_partner_g_l_acc_no
,dq_bc_g_l_account.omit_default_descr_in_jnl
,dq_bc_g_l_account.account_subcategory_entry_no
,dq_bc_g_l_account.cost_type_no
,dq_bc_g_l_account.default_deferral_template_code
,dq_bc_g_l_account.id_col
,dq_bc_g_l_account.api_account_type
,dq_bc_g_l_account.bc_internal_system_id_guid
,dq_bc_g_l_account.bc_internal_system_created_timestamp
,dq_bc_g_l_account.bc_internal_system_created_by_guid
,dq_bc_g_l_account.bc_internal_system_modified_at_timestamp
,dq_bc_g_l_account.bc_internal_system_modified_by_guid
,dq_bc_g_l_account.timestamp_col
,coalesce(sub_net_change.amount_col,0.0)                           as net_change_amt
,coalesce(sub_balance.amount_col,0.0)                               as sub_balance_amt
,coalesce(gl_account_sub_category.description,'')                   as gl_account_category
,coalesce(sub_debits.debit_amount,0.0)                               as debits_amt
,coalesce(sub_credits.credit_amount,0.0)                           as credits_amt
,coalesce(sub_balance_at_date.amount_col,0.0)                       as balance_at_date_amt
,coalesce(sub_acy_net_change.additional_currency_amount,0.0)       as acy_net_change_amt
,coalesce(sub_acy_balance_at_date.additional_currency_amount,0.0)   as acy_bal_at_date_amt
,coalesce(sub_acy_balance.additional_currency_amount,0.0)           as acy_balance_amt

from dbo.dq_bc_g_l_account with(readuncommitted)

left join (
select
net_change_g_l_entry.g_l_account_no                               as g_l_account_no
,net_change_g_l_entry.amount_col                                   as amount_col
from
(
select
dq_bc_g_l_entry.g_l_account_no                                   as g_l_account_no
-- ,dq_bc_g_l_entry.posting_date                                   as posting_date
,sum(dq_bc_g_l_entry.amount_col)                                   as amount_col
from dbo.dq_bc_g_l_entry with(readuncommitted)
where 1=1
group by
dq_bc_g_l_entry.g_l_account_no
-- ,dq_bc_g_l_entry.posting_date
) as net_change_g_l_entry
) as sub_net_change on sub_net_change.g_l_account_no = dq_bc_g_l_account.pk_no_col

left join (
select
balance_g_l_entry.g_l_account_no                                   as g_l_account_no
,balance_g_l_entry.amount_col                                       as amount_col
from
(
select
dq_bc_g_l_entry.g_l_account_no                                   as g_l_account_no
-- ,dq_bc_g_l_entry.posting_date                                   as posting_date
,sum(dq_bc_g_l_entry.amount_col)                                   as amount_col
from dbo.dq_bc_g_l_entry with(readuncommitted)
where 1=1
group by
dq_bc_g_l_entry.g_l_account_no
-- ,dq_bc_g_l_entry.posting_date
) as balance_g_l_entry
) as sub_balance on sub_balance.g_l_account_no = dq_bc_g_l_account.pk_no_col

left join (
select
gl_account_category.pk_entry_no                                   as pk_entry_no
,gl_account_category.description                                   as description
from
(
select top 10000000
dq_bc_g_l_account_category.pk_entry_no                           as pk_entry_no
,dq_bc_g_l_account_category.description                           as description
from dbo.dq_bc_g_l_account_category with(readuncommitted)
where 1=1
group by
dq_bc_g_l_account_category.pk_entry_no
,dq_bc_g_l_account_category.description
order by
dq_bc_g_l_account_category.pk_entry_no
) as gl_account_category
) as gl_account_sub_category on dq_bc_g_l_account.account_subcategory_entry_no = gl_account_sub_category.pk_entry_no

left join (
select
debits_g_l_entry.g_l_account_no                                   as g_l_account_no
,debits_g_l_entry.debit_amount                                       as debit_amount
from
(
select
dq_bc_g_l_entry.g_l_account_no                                   as g_l_account_no
,sum(dq_bc_g_l_entry.debit_amount)                               as debit_amount
from dbo.dq_bc_g_l_entry with(readuncommitted)
where 1=1
group by
dq_bc_g_l_entry.g_l_account_no
) as debits_g_l_entry
) as sub_debits on sub_debits.g_l_account_no = dq_bc_g_l_account.pk_no_col

left join (
select
credits_g_l_entry.g_l_account_no                                   as g_l_account_no
,credits_g_l_entry.credit_amount                                   as credit_amount
from
(
select
dq_bc_g_l_entry.g_l_account_no                                   as g_l_account_no
,sum(dq_bc_g_l_entry.credit_amount)                               as credit_amount
from dbo.dq_bc_g_l_entry with(readuncommitted)
where 1=1
group by
dq_bc_g_l_entry.g_l_account_no
) credits_g_l_entry
) as sub_credits on sub_credits.g_l_account_no = dq_bc_g_l_account.pk_no_col

left join (
select
balance_at_date_g_l_entry.g_l_account_no                           as g_l_account_no
,balance_at_date_g_l_entry.amount_col                               as amount_col
from
(
select
dq_bc_g_l_entry.g_l_account_no                                   as g_l_account_no
,sum(dq_bc_g_l_entry.amount_col)                                   as amount_col
from dbo.dq_bc_g_l_entry with(readuncommitted)
where 1=1
group by
dq_bc_g_l_entry.g_l_account_no
) balance_at_date_g_l_entry
) as sub_balance_at_date on sub_balance_at_date.g_l_account_no = dq_bc_g_l_account.pk_no_col

left join (
select
acy_net_change_g_l_entry.g_l_account_no                           as g_l_account_no
,acy_net_change_g_l_entry.additional_currency_amount               as additional_currency_amount
from
(
select
dq_bc_g_l_entry.g_l_account_no                                   as g_l_account_no
,sum(dq_bc_g_l_entry.additional_currency_amount)                   as additional_currency_amount
from dbo.dq_bc_g_l_entry with(readuncommitted)
where 1=1
group by
dq_bc_g_l_entry.g_l_account_no
) acy_net_change_g_l_entry
) as sub_acy_net_change on sub_acy_net_change.g_l_account_no = dq_bc_g_l_account.pk_no_col

left join (
select
acy_balance_at_date_g_l_entry.g_l_account_no                       as g_l_account_no
,acy_balance_at_date_g_l_entry.additional_currency_amount           as additional_currency_amount
from
(
select
dq_bc_g_l_entry.g_l_account_no                                   as g_l_account_no
,sum(dq_bc_g_l_entry.additional_currency_amount)                   as additional_currency_amount
from dbo.dq_bc_g_l_entry with(readuncommitted)
where 1=1
group by
dq_bc_g_l_entry.g_l_account_no
) as acy_balance_at_date_g_l_entry
) as sub_acy_balance_at_date on sub_acy_balance_at_date.g_l_account_no = dq_bc_g_l_account.pk_no_col

left join (
select
acy_balance_g_l_entry.g_l_account_no                               as g_l_account_no
,acy_balance_g_l_entry.additional_currency_amount                   as additional_currency_amount
from
(
select
dq_bc_g_l_entry.g_l_account_no                                   as g_l_account_no
,sum(dq_bc_g_l_entry.additional_currency_amount)                   as additional_currency_amount
from dbo.dq_bc_g_l_entry with(readuncommitted)
where 1=1
group by
dq_bc_g_l_entry.g_l_account_no
) as acy_balance_g_l_entry
) as sub_acy_balance on sub_acy_balance.g_l_account_no = dq_bc_g_l_account.pk_no_col
where dq_bc_g_l_account.pk_no_col > '1000'
)
,query06_cte as
(
select
query04_cte.gl_account_text_parent            as gl_account_text_parent
,sum(query05_cte.net_change_amt)           as net_change_amt
,sum(query05_cte.sub_balance_amt)           as sub_balance_amt
,sum(query05_cte.debits_amt)               as debits_amt
,sum(query05_cte.credits_amt)               as credits_amt
,sum(query05_cte.balance_at_date_amt)           as balance_at_date_amt
,sum(query05_cte.acy_net_change_amt)           as acy_net_change_amt
,sum(query05_cte.acy_bal_at_date_amt)           as acy_bal_at_date_amt
,sum(query05_cte.acy_balance_amt)           as acy_balance_amt
from query05_cte
inner join query04_cte on query05_cte.pk_no_col = query04_cte.gl_account_text_child
group by query04_cte.gl_account_text_parent
)
,query07_cte as
(
select
query05_cte.pk_no_col
,query05_cte.name_col
,query05_cte.search_name
,query05_cte.account_type
,query05_cte.global_dimension_1_code
,query05_cte.global_dimension_2_code
,query05_cte.account_category
,query05_cte.income_balance
,query05_cte.debit_credit
,query05_cte.no_2
,query05_cte.blocked
,query05_cte.direct_posting
,query05_cte.reconciliation_account
,query05_cte.new_page
,query05_cte.no_of_blank_lines
,query05_cte.indentation_col
,query05_cte.last_modified_date_time
,query05_cte.last_date_modified
,query05_cte.totaling
,query05_cte.consol_translation_method
,query05_cte.consol_debit_acc
,query05_cte.consol_credit_acc
,query05_cte.gen_posting_type
,query05_cte.gen_bus_posting_group
,query05_cte.gen_prod_posting_group
,query05_cte.picture_num_bytes
,query05_cte.automatic_ext_texts
,query05_cte.tax_area_code
,query05_cte.tax_liable
,query05_cte.tax_group_code
,query05_cte.vat_bus_posting_group
,query05_cte.vat_prod_posting_group
,query05_cte.exchange_rate_adjustment
,query05_cte.default_ic_partner_g_l_acc_no
,query05_cte.omit_default_descr_in_jnl
,query05_cte.account_subcategory_entry_no
,query05_cte.cost_type_no
,query05_cte.default_deferral_template_code
,query05_cte.id_col
,query05_cte.api_account_type
,query05_cte.bc_internal_system_id_guid
,query05_cte.bc_internal_system_created_timestamp
,query05_cte.bc_internal_system_created_by_guid
,query05_cte.bc_internal_system_modified_at_timestamp
,query05_cte.bc_internal_system_modified_by_guid
,query05_cte.timestamp_col
,case
when query05_cte.income_balance = 1 then 'Balance Sheet'
when query05_cte.income_balance = 0 then 'Income Statement'
else 'Unknown'
end as income_balance_text
,query05_cte.gl_account_category
,coalesce(query06_cte.net_change_amt       ,query05_cte.net_change_amt)       as net_change_amt
,coalesce(query06_cte.sub_balance_amt       ,query05_cte.sub_balance_amt)       as sub_balance_amt
,coalesce(query06_cte.debits_amt       ,query05_cte.debits_amt)       as debits_amt
,coalesce(query06_cte.credits_amt       ,query05_cte.credits_amt)       as credits_amt
,coalesce(query06_cte.balance_at_date_amt   ,query05_cte.balance_at_date_amt)   as balance_at_date_amt
,coalesce(query06_cte.acy_net_change_amt   ,query05_cte.acy_net_change_amt)   as acy_net_change_amt
,coalesce(query06_cte.acy_bal_at_date_amt   ,query05_cte.acy_bal_at_date_amt)   as acy_bal_at_date_amt
,coalesce(query06_cte.acy_balance_amt       ,query05_cte.acy_balance_amt)       as acy_balance_amt
from query05_cte
left join query06_cte on query05_cte.pk_no_col = query06_cte.gl_account_text_parent
)

select * from query07_cte order by pk_no_col
Verified answer

Peter Jones - BIDA 104 on at

Like (0)

Report

Grouping for Totals for direct query into the GL

Hi @gdrenteria,

I am not sure how @s work here.

I have figured this out today and so I thought I would post it here in case anyone else wants it.

Below is the working code to reproduce the chart of accounts report from the underlying database using direct query.

I have done the query with both outer apply and left joins.

Outer apply is what I traced from BC 210 just on a local demo instance.

Direct Query Against The BC Underlying Database (dynamics.com)

Of course, outer apply is going to be much slower than using left joins when you want to read large numbers of GL Entries.

The view used is CRONUS UK Ltd_$G_L Entry$437dbf0e-84ff-417a-965d-ed2bb9650972$VSIFT$Key2

I am somewhat bemused that this view has the posting data in it and returns all GL entries for the GL account grouped by posting date.

So the use of top 1 will only retrieve the sum of the GL entries for the latest posting data.

I don't see how that works because to get a balance for a GL account you have to sum all the transactions over all time in BC.

Or, at least, you had to do that in Navision.

I don't believe the latest posting date contains the balance in BC, does it?

Anyway, in this example I have copied what BC does faithfully and it seems to work.

I can not get my BC230 demo machine running and I will put up another post asking about that to keep it separate.

In the code below, what it does is go and get all the parents that a GL account contributes to in totaling.

Then it sums up all the rows for all those GL accounts for all the parents.

Then it inner joins the sums with the result set that was summed.

What you wind up with is a result set that emulates the chart of accounts report.

Obviously the report is doing the summing in BC and not the database.

If anyone knows why the key 2 view groups by posting date and only the latest posting date is being used?

I would be very interested to know why that is.

In the left join version I got the same answers leaving posting date out of the query.

But it could be that because it is just the demo data there are not multiple postings to various GL accounts so the numbers come out the same by luck / chance.

Anyway, this has been interesting.

I thought we could start with GL reporting because that's always important.

Best Regards

Peter

with query01_cte as
(
select
cast(dq_bc_g_l_account.pk_no_col as int)   as gl_account_int
,dq_bc_g_l_account.pk_no_col               as gl_account_text
,dq_bc_g_l_account.name_col                   as gl_account_name
,dq_bc_g_l_account.totaling                   as totaling_string
from dbo.dq_bc_g_l_account
where not (totaling = '')
)
--select * from query01_cte
,query02_cte as
(
select
query01_cte.gl_account_int                                   as gl_account_int
,query01_cte.gl_account_text                               as gl_account_text
,query01_cte.gl_account_name                               as gl_account_name
,query01_cte.totaling_string                               as totaling_string
,cast(split_totaling_string.totaling_string_lower as int)   as totaling_string_lower
,cast(split_totaling_string.totaling_string_upper as int)   as totaling_string_upper

from query01_cte

outer apply
(
select
left(gl_account_split.value, isnull(nullif(charindex('..', gl_account_split.value), 0) - 1, len(gl_account_split.value))) as totaling_string_lower
,right(gl_account_split.value, len(gl_account_split.value) - isnull(nullif(charindex('..', gl_account_split.value), 0) + 1, 0)) as totaling_string_upper
from string_split(query01_cte.totaling_string, '|') gl_account_split
) split_totaling_string
)
--select * from query02_cte
,query03_cte as
(
select
query02_cte.gl_account_int
,query02_cte.gl_account_text
,query02_cte.gl_account_name
,query02_cte.totaling_string
,query02_cte.totaling_string_lower
,query02_cte.totaling_string_upper
,dq_bc_g_l_account_02.pk_no_col               gl_account_text_02
,dq_bc_g_l_account_02.name_col               gl_account_name_02
from dbo.dq_bc_g_l_account dq_bc_g_l_account_02
left join query02_cte on cast (dq_bc_g_l_account_02.pk_no_col as int) between query02_cte.totaling_string_lower and query02_cte.totaling_string_upper
where not (query02_cte.gl_account_int is null)
and not (query02_cte.gl_account_text = dq_bc_g_l_account_02.pk_no_col )
)
--select * from query03_cte order by gl_account_int , gl_account_text_02
,query04_cte as
(
select
query03_cte.gl_account_text_02           as gl_account_text_child
,query03_cte.gl_account_text           as gl_account_text_parent
from query03_cte
)
,query05_cte as
(
select
dq_bc_g_l_account.pk_no_col
,dq_bc_g_l_account.name_col
,dq_bc_g_l_account.search_name
,dq_bc_g_l_account.account_type
,dq_bc_g_l_account.global_dimension_1_code
,dq_bc_g_l_account.global_dimension_2_code
,dq_bc_g_l_account.account_category
,dq_bc_g_l_account.income_balance
,dq_bc_g_l_account.debit_credit
,dq_bc_g_l_account.no_2
,dq_bc_g_l_account.blocked
,dq_bc_g_l_account.direct_posting
,dq_bc_g_l_account.reconciliation_account
,dq_bc_g_l_account.new_page
,dq_bc_g_l_account.no_of_blank_lines
,dq_bc_g_l_account.indentation_col
,dq_bc_g_l_account.last_modified_date_time
,dq_bc_g_l_account.last_date_modified
,dq_bc_g_l_account.totaling
,dq_bc_g_l_account.consol_translation_method
,dq_bc_g_l_account.consol_debit_acc
,dq_bc_g_l_account.consol_credit_acc
,dq_bc_g_l_account.gen_posting_type
,dq_bc_g_l_account.gen_bus_posting_group
,dq_bc_g_l_account.gen_prod_posting_group
,datalength(dq_bc_g_l_account.picture)                    as picture_num_bytes
,dq_bc_g_l_account.automatic_ext_texts
,dq_bc_g_l_account.tax_area_code
,dq_bc_g_l_account.tax_liable
,dq_bc_g_l_account.tax_group_code
,dq_bc_g_l_account.vat_bus_posting_group
,dq_bc_g_l_account.vat_prod_posting_group
,dq_bc_g_l_account.exchange_rate_adjustment
,dq_bc_g_l_account.default_ic_partner_g_l_acc_no
,dq_bc_g_l_account.omit_default_descr_in_jnl
,dq_bc_g_l_account.account_subcategory_entry_no
,dq_bc_g_l_account.cost_type_no
,dq_bc_g_l_account.default_deferral_template_code
,dq_bc_g_l_account.id_col
,dq_bc_g_l_account.api_account_type
,dq_bc_g_l_account.bc_internal_system_id_guid
,dq_bc_g_l_account.bc_internal_system_created_timestamp
,dq_bc_g_l_account.bc_internal_system_created_by_guid
,dq_bc_g_l_account.bc_internal_system_modified_at_timestamp
,dq_bc_g_l_account.bc_internal_system_modified_by_guid
,dq_bc_g_l_account.timestamp_col
,coalesce(sub_net_change.amount_col,0.0)               as net_change_amt
,coalesce(sub_balance.amount_col,0.0)                   as sub_balance_amt
,coalesce(gl_account_sub_category.description,'')           as gl_account_category
,coalesce(sub_debits.debit_amount,0.0)                   as debits_amt
,coalesce(sub_credits.credit_amount,0.0)               as credits_amt
,coalesce(sub_balance_at_date.amount_col,0.0)               as balance_at_date_amt
,coalesce(sub_acy_net_change.additional_currency_amount,0.0)       as acy_net_change_amt
,coalesce(sub_acy_balance_at_date.additional_currency_amount,0.0)   as acy_bal_at_date_amt
,coalesce(sub_acy_balance.additional_currency_amount,0.0)       as acy_balance_amt

from dbo.dq_bc_g_l_account with(readuncommitted)

outer apply (
select top 1
sum (net_change_g_l_entry.amount_col)                   amount_col
from
(
select
dq_bc_g_l_entry.g_l_account_no
,dq_bc_g_l_entry.posting_date
,count_big(*) as count_rows
,sum(dq_bc_g_l_entry.amount_col)                   amount_col
,sum(dq_bc_g_l_entry.debit_amount)                   debit_amount
,sum(dq_bc_g_l_entry.credit_amount)                   credit_amount
,sum(dq_bc_g_l_entry.additional_currency_amount)           additional_currency_amount
,sum(dq_bc_g_l_entry.add_currency_debit_amount)           add_currency_debit_amount
,sum(dq_bc_g_l_entry.add_currency_credit_amount)           add_currency_credit_amount
from dbo.dq_bc_g_l_entry with(readuncommitted)
where 1=1
and dq_bc_g_l_entry.g_l_account_no = dq_bc_g_l_account.pk_no_col
group by
dq_bc_g_l_entry.g_l_account_no
,dq_bc_g_l_entry.posting_date
) net_change_g_l_entry
) as sub_net_change

outer apply (
select top 1
sum (balance_g_l_entry.amount_col)                   amount_col
from
(
select
dq_bc_g_l_entry.g_l_account_no
,dq_bc_g_l_entry.posting_date
,count_big(*) as count_rows
,sum(dq_bc_g_l_entry.amount_col)                   amount_col
,sum(dq_bc_g_l_entry.debit_amount)                   debit_amount
,sum(dq_bc_g_l_entry.credit_amount)                   credit_amount
,sum(dq_bc_g_l_entry.additional_currency_amount)           additional_currency_amount
,sum(dq_bc_g_l_entry.add_currency_debit_amount)           add_currency_debit_amount
,sum(dq_bc_g_l_entry.add_currency_credit_amount)           add_currency_credit_amount
from dbo.dq_bc_g_l_entry with(readuncommitted)
where 1=1
and dq_bc_g_l_entry.g_l_account_no = dq_bc_g_l_account.pk_no_col
group by
dq_bc_g_l_entry.g_l_account_no
,dq_bc_g_l_entry.posting_date
) balance_g_l_entry
) as sub_balance

outer apply (
select top 1
gl_account_category.description                   description
from
(
select top 1
dq_bc_g_l_account_category.description
,dq_bc_g_l_account_category.pk_entry_no
from dbo.dq_bc_g_l_account_category with(readuncommitted)
where 1=1
and dq_bc_g_l_account.account_subcategory_entry_no = dq_bc_g_l_account_category.pk_entry_no
order by
dq_bc_g_l_account_category.pk_entry_no
) gl_account_category
) as gl_account_sub_category

outer apply (
select top 1
sum (debits_g_l_entry.debit_amount)                   debit_amount
from
(
select
dq_bc_g_l_entry.g_l_account_no
,dq_bc_g_l_entry.posting_date
,count_big(*) as count_rows
,sum(dq_bc_g_l_entry.amount_col)                   amount_col
,sum(dq_bc_g_l_entry.debit_amount)                   debit_amount
,sum(dq_bc_g_l_entry.credit_amount)                   credit_amount
,sum(dq_bc_g_l_entry.additional_currency_amount)           additional_currency_amount
,sum(dq_bc_g_l_entry.add_currency_debit_amount)           add_currency_debit_amount
,sum(dq_bc_g_l_entry.add_currency_credit_amount)           add_currency_credit_amount
from dbo.dq_bc_g_l_entry with(readuncommitted)
where 1=1
and dq_bc_g_l_entry.g_l_account_no = dq_bc_g_l_account.pk_no_col
group by
dq_bc_g_l_entry.g_l_account_no
,dq_bc_g_l_entry.posting_date
) debits_g_l_entry
) as sub_debits

outer apply (
select top 1
sum (credits_g_l_entry.credit_amount)                   credit_amount
from
(
select
dq_bc_g_l_entry.g_l_account_no
,dq_bc_g_l_entry.posting_date
,count_big(*) as count_rows
,sum(dq_bc_g_l_entry.amount_col)                   amount_col
,sum(dq_bc_g_l_entry.debit_amount)                   debit_amount
,sum(dq_bc_g_l_entry.credit_amount)                   credit_amount
,sum(dq_bc_g_l_entry.additional_currency_amount)           additional_currency_amount
,sum(dq_bc_g_l_entry.add_currency_debit_amount)           add_currency_debit_amount
,sum(dq_bc_g_l_entry.add_currency_credit_amount)           add_currency_credit_amount
from dbo.dq_bc_g_l_entry with(readuncommitted)
where 1=1
and dq_bc_g_l_entry.g_l_account_no = dq_bc_g_l_account.pk_no_col
group by
dq_bc_g_l_entry.g_l_account_no
,dq_bc_g_l_entry.posting_date
) credits_g_l_entry
) as sub_credits

outer apply (
select top 1
sum (balance_at_date_g_l_entry.amount_col)               amount_col
from
(
select
dq_bc_g_l_entry.g_l_account_no
,dq_bc_g_l_entry.posting_date
,count_big(*) as count_rows
,sum(dq_bc_g_l_entry.amount_col)                   amount_col
,sum(dq_bc_g_l_entry.debit_amount)                   debit_amount
,sum(dq_bc_g_l_entry.credit_amount)                   credit_amount
,sum(dq_bc_g_l_entry.additional_currency_amount)           additional_currency_amount
,sum(dq_bc_g_l_entry.add_currency_debit_amount)           add_currency_debit_amount
,sum(dq_bc_g_l_entry.add_currency_credit_amount)           add_currency_credit_amount
from dbo.dq_bc_g_l_entry with(readuncommitted)
where 1=1
and dq_bc_g_l_entry.g_l_account_no = dq_bc_g_l_account.pk_no_col
group by
dq_bc_g_l_entry.g_l_account_no
,dq_bc_g_l_entry.posting_date
) balance_at_date_g_l_entry
) as sub_balance_at_date

outer apply (
select top 1
sum (acy_net_change_g_l_entry.additional_currency_amount)       additional_currency_amount
from
(
select
dq_bc_g_l_entry.g_l_account_no
,dq_bc_g_l_entry.posting_date
,count_big(*) as count_rows
,sum(dq_bc_g_l_entry.amount_col)                   amount_col
,sum(dq_bc_g_l_entry.debit_amount)                   debit_amount
,sum(dq_bc_g_l_entry.credit_amount)                   credit_amount
,sum(dq_bc_g_l_entry.additional_currency_amount)           additional_currency_amount
,sum(dq_bc_g_l_entry.add_currency_debit_amount)           add_currency_debit_amount
,sum(dq_bc_g_l_entry.add_currency_credit_amount)           add_currency_credit_amount
from dbo.dq_bc_g_l_entry with(readuncommitted)
where 1=1
and dq_bc_g_l_entry.g_l_account_no = dq_bc_g_l_account.pk_no_col
group by
dq_bc_g_l_entry.g_l_account_no
,dq_bc_g_l_entry.posting_date
) acy_net_change_g_l_entry
) as sub_acy_net_change

outer apply (
select top 1
sum (acy_balance_at_date_g_l_entry.additional_currency_amount)       additional_currency_amount
from
(
select
dq_bc_g_l_entry.g_l_account_no
,dq_bc_g_l_entry.posting_date
,count_big(*) as count_rows
,sum(dq_bc_g_l_entry.amount_col)                   amount_col
,sum(dq_bc_g_l_entry.debit_amount)                   debit_amount
,sum(dq_bc_g_l_entry.credit_amount)                   credit_amount
,sum(dq_bc_g_l_entry.additional_currency_amount)           additional_currency_amount
,sum(dq_bc_g_l_entry.add_currency_debit_amount)           add_currency_debit_amount
,sum(dq_bc_g_l_entry.add_currency_credit_amount)           add_currency_credit_amount
from dbo.dq_bc_g_l_entry with(readuncommitted)
where 1=1
and dq_bc_g_l_entry.g_l_account_no = dq_bc_g_l_account.pk_no_col
group by
dq_bc_g_l_entry.g_l_account_no
,dq_bc_g_l_entry.posting_date
) acy_balance_at_date_g_l_entry
) as sub_acy_balance_at_date

outer apply (
select top 1
sum (acy_balance_g_l_entry.additional_currency_amount)           additional_currency_amount
from
(
select
dq_bc_g_l_entry.g_l_account_no
,dq_bc_g_l_entry.posting_date
,count_big(*) as count_rows
,sum(dq_bc_g_l_entry.amount_col)                   amount_col
,sum(dq_bc_g_l_entry.debit_amount)                   debit_amount
,sum(dq_bc_g_l_entry.credit_amount)                   credit_amount
,sum(dq_bc_g_l_entry.additional_currency_amount)           additional_currency_amount
,sum(dq_bc_g_l_entry.add_currency_debit_amount)           add_currency_debit_amount
,sum(dq_bc_g_l_entry.add_currency_credit_amount)           add_currency_credit_amount
from dbo.dq_bc_g_l_entry with(readuncommitted)
where 1=1
and dq_bc_g_l_entry.g_l_account_no = dq_bc_g_l_account.pk_no_col
group by
dq_bc_g_l_entry.g_l_account_no
,dq_bc_g_l_entry.posting_date
) acy_balance_g_l_entry
) as sub_acy_balance

where dq_bc_g_l_account.pk_no_col > '1000'
)
,query06_cte as
(
select
query04_cte.gl_account_text_parent            as gl_account_text_parent
,sum(query05_cte.net_change_amt)           as net_change_amt
,sum(query05_cte.sub_balance_amt)           as sub_balance_amt
,sum(query05_cte.debits_amt)               as debits_amt
,sum(query05_cte.credits_amt)               as credits_amt
,sum(query05_cte.balance_at_date_amt)           as balance_at_date_amt
,sum(query05_cte.acy_net_change_amt)           as acy_net_change_amt
,sum(query05_cte.acy_bal_at_date_amt)           as acy_bal_at_date_amt
,sum(query05_cte.acy_balance_amt)           as acy_balance_amt
from query05_cte
inner join query04_cte on query05_cte.pk_no_col = query04_cte.gl_account_text_child
group by query04_cte.gl_account_text_parent
)
,query07_cte as
(
select
query05_cte.pk_no_col
,query05_cte.name_col
,query05_cte.search_name
,query05_cte.account_type
,query05_cte.global_dimension_1_code
,query05_cte.global_dimension_2_code
,query05_cte.account_category
,query05_cte.income_balance
,query05_cte.debit_credit
,query05_cte.no_2
,query05_cte.blocked
,query05_cte.direct_posting
,query05_cte.reconciliation_account
,query05_cte.new_page
,query05_cte.no_of_blank_lines
,query05_cte.indentation_col
,query05_cte.last_modified_date_time
,query05_cte.last_date_modified
,query05_cte.totaling
,query05_cte.consol_translation_method
,query05_cte.consol_debit_acc
,query05_cte.consol_credit_acc
,query05_cte.gen_posting_type
,query05_cte.gen_bus_posting_group
,query05_cte.gen_prod_posting_group
,query05_cte.picture_num_bytes
,query05_cte.automatic_ext_texts
,query05_cte.tax_area_code
,query05_cte.tax_liable
,query05_cte.tax_group_code
,query05_cte.vat_bus_posting_group
,query05_cte.vat_prod_posting_group
,query05_cte.exchange_rate_adjustment
,query05_cte.default_ic_partner_g_l_acc_no
,query05_cte.omit_default_descr_in_jnl
,query05_cte.account_subcategory_entry_no
,query05_cte.cost_type_no
,query05_cte.default_deferral_template_code
,query05_cte.id_col
,query05_cte.api_account_type
,query05_cte.bc_internal_system_id_guid
,query05_cte.bc_internal_system_created_timestamp
,query05_cte.bc_internal_system_created_by_guid
,query05_cte.bc_internal_system_modified_at_timestamp
,query05_cte.bc_internal_system_modified_by_guid
,query05_cte.timestamp_col
,case
when query05_cte.income_balance = 1 then 'Balance Sheet'
when query05_cte.income_balance = 0 then 'Income Statement'
else 'Unknown'
end as income_balance_text
,query05_cte.gl_account_category
,coalesce(query06_cte.net_change_amt       ,query05_cte.net_change_amt)       as net_change_amt
,coalesce(query06_cte.sub_balance_amt       ,query05_cte.sub_balance_amt)       as sub_balance_amt
,coalesce(query06_cte.debits_amt       ,query05_cte.debits_amt)       as debits_amt
,coalesce(query06_cte.credits_amt       ,query05_cte.credits_amt)       as credits_amt
,coalesce(query06_cte.balance_at_date_amt   ,query05_cte.balance_at_date_amt)   as balance_at_date_amt
,coalesce(query06_cte.acy_net_change_amt   ,query05_cte.acy_net_change_amt)   as acy_net_change_amt
,coalesce(query06_cte.acy_bal_at_date_amt   ,query05_cte.acy_bal_at_date_amt)   as acy_bal_at_date_amt
,coalesce(query06_cte.acy_balance_amt       ,query05_cte.acy_balance_amt)       as acy_balance_amt
from query05_cte
left join query06_cte on query05_cte.pk_no_col = query06_cte.gl_account_text_parent
)

select * from query07_cte order by pk_no_col
Suggested answer

gdrenteria 12,183 Most Valuable Professional on at

Like (1)

Report

Grouping for Totals for direct query into the GL

Hi

The relationship in some cases could be identified via code review.

For example, the balance field of the chart of accounts table is the sum of the G/l entry table.

BR

GR

Helpful resources

Quick Links

Leaderboard

#1

André Arnaud de Cal... 291,280 Super User 2024 Season 2

#2

Martin Dráb 230,235 Most Valuable Professional

#3

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans