Sum in Prepare Scripts

Lisa Callahan · 2024-10-24T16:28:05+00:00

There was an error rendering this rich post.

How does Sum work in Prepare scripts? I have tried a join and a sub-select without success. :(

My end goal is to use one script to get different balances on one row - Beginning Balance (everything before a particular month), Current Activity (everything in the month), and Ending Balance (Beginning Balance + Current Activity).

For example, for the period 04/2023 there would be one record for the below parameters -

My first try was to join the main query to itself using aliases and different date criteria in the WHERE clause. The main query grabs all transactions prior to 04/01/2023 for abeginning balance and the sub query pulls the data from the same table but for all transactions that equal 04/30/2023.

SELECT

[GL.Account],
[GL.AccountingEntity],
[GL.FinanceDimension1],
Sum([GL.NetTransactionAmount]),
[GL.FinanceEnterpriseGroup]

FROM

[FSM_GeneralLedgerTotal] 'GL'

WHERE [GL.Ledger]='CORE' AND [GL.PeriodEndingDate]<'04/01/2023'
AND [GL.Account]='1111000010'
AND [GL.AccountingEntity]='3100'
AND [GL.FinanceDimension1]='10000'

INNER JOIN
SELECT

[BB.Account],
[BB.AccountingEntity],
[BB.FinanceDimension1],
Sum([BB.NetTransactionAmount])

FROM

[FSM_GeneralLedgerTotal] 'BB'

WHERE

[BB.Ledger]='CORE'
AND [BB.PeriodEndingDate]='04/30/2023'
AND [BB.Account]='1111000010'
AND [BB.AccountingEntity]='3100'
AND [BB.FinanceDimension1]='10000'

ON [GL.Account]=[BB.Account]
AND [GL.AccountingEntity]=[BB.AccountingEntity]
AND [GL.FinanceDimension1]=[BB.FinanceDimension1]

But the resulting sums (GL.NetTransactionAmount and BB.NetTransactionAmount) are giving me all kinds of weird figures.

When I remove the INNER JOIN Sum([GL.NetTransactionAmount]) is correct so it is definitely a calculation happening with the JOIN.

My second try was to change tactics and try a subselect directly in the Script (like you can do in SQL) but that did not even validate -

SELECT

[GL.Account],
[GL.AccountingEntity],
[GL.FinanceDimension1],
SELECT Sum([FSM_GeneralLedger.NetTransactionAmount]) FROM [FSM_GeneralLedger] WHERE [FSM_GeneralLedger.PeriodEndingDate]<=04/01/2024',
SELECT Sum([FSM_GeneralLedger.NetTransactionAmount]) FROM [FSM_GeneralLedger] WHERE [FSM_GeneralLedger.PeriodEndingDate]='04/30/2023',
[GL.FinanceEnterpriseGroup]

FROM

[FSM_GeneralLedgerTotal] 'GL'

WHERE [GL.Ledger]='CORE'
AND [GL.Account]='1111000010'
AND [GL.AccountingEntity]='3100'
AND [GL.FinanceDimension1]='10000'

Please and thank you,

Lisa

Find more posts tagged with

Accepted answers

Enrico Menotti

Hi Lisa,

To my knowledge, Birst queries get translated to the underlying SQL database language, which in cloud is a Transact SQL. The syntax of BQL is pretty different than T-SQL. Again to my knowledge, end users have no way to see the actual T-SQL code generated by input queries in Prepared Sources.

However, having observed the behavior of such queries, I guess that often the translation leads to unexpected results. In particular, I think that when you use joins in BQL, the resulting T-SQL does first the join, then the select. So:

SELECT [TableA.Field1]
FROM [TableA]

INNER JOIN

SELECT [TableB.Field2]
FROM [TableB]

ON [TableA.Field1] = TableB.Field2]

becomes:

SELECT [TableA.Field1], [TableB.Field2]
FROM
[TableA] JOIN [Tableb]
ON [TableA.Field1] = [TableB.Field2]

Indeed, if e.g. you try a DISTINCT, that will only work if it is in the first BQL SELECT, and will operate on the second as well.

Similarly, WHERE statements get aggregated at the end of the T-SQL, so if you do a WHERE in one of the BQL SELECTs, that will operate on both. (Try with a LEFT JOIN and a WHERE in the second SELECT…)

In your case, you get:

SELECT

[GL.Account], [GL.AccountingEntity], [GL.FinanceDimension1], Sum([GL.NetTransactionAmount]), [GL.FinanceEnterpriseGroup],
[BB.Account], [BB.AccountingEntity], [BB.FinanceDimension1], Sum([BB.NetTransactionAmount])

FROM

[FSM_GeneralLedgerTotal] 'GL' JOIN [FSM_GeneralLedgerTotal] 'BB'

ON [GL.Account] = [BB.Account] AND [GL.AccountingEntity] = [BB.AccountingEntity] AND [GL.FinanceDimension1] = [BB.FinanceDimension1]

WHERE [GL.Ledger] = 'CORE' AND [GL.PeriodEndingDate] < '04/01/2023' AND [GL.Account] = '1111000010' AND [GL.AccountingEntity] = '3100' AND [GL.FinanceDimension1]='10000'
AND
[BB.Ledger] = 'CORE' AND [BB.PeriodEndingDate] = '04/30/2023' AND [BB.Account] = '1111000010' AND [BB.AccountingEntity] = '3100' AND [BB.FinanceDimension1] = '10000'

GROUP BY <…all non-summed columns in the select…> (GROUP BY is implicit in BQL)

So all GL lines before 04/01/2023 get joined to all BB lines on 04/30/2023, and then they get summed, thus being multiplicated.

Again, there's quite a bit of guessing in my reasoning above - I don't have any proof of what I just said except for the behavior of the program - but maybe your tests may confirm my ideas, or maybe not. Anyway, definitely one option is to first do the SUMs in two separate Prepared Sources, and as a second step do the JOIN in a third Prepared Source, reading from the previous ones. Right now I have no ideas on how to go in one single shot - it may be even not possible.

*** If anybody is willing to subscribe an Enhancement Request to let end users see what's going on in the backend (e.g. by opening the Repository Admin module, which currently is available to support only), I'd be glad to create one. But first we need to group at least 10 people from 10 different companies for Infor to consider the ER. ***

Enrico

Dan_Rosen

I've had best results with aggregates when I create separate scripts for them so I know that the aggregation happens first, and I can join to the results. Not intuitive as to what you'd expect in SQL, but seems to be how it works on the Birst side

All comments

Enrico Menotti

Hi Lisa,

SELECT [TableA.Field1]
FROM [TableA]

INNER JOIN

SELECT [TableB.Field2]
FROM [TableB]

ON [TableA.Field1] = TableB.Field2]

becomes:

SELECT [TableA.Field1], [TableB.Field2]
FROM
[TableA] JOIN [Tableb]
ON [TableA.Field1] = [TableB.Field2]

Indeed, if e.g. you try a DISTINCT, that will only work if it is in the first BQL SELECT, and will operate on the second as well.

In your case, you get:

SELECT

FROM

[FSM_GeneralLedgerTotal] 'GL' JOIN [FSM_GeneralLedgerTotal] 'BB'

ON [GL.Account] = [BB.Account] AND [GL.AccountingEntity] = [BB.AccountingEntity] AND [GL.FinanceDimension1] = [BB.FinanceDimension1]

GROUP BY <…all non-summed columns in the select…> (GROUP BY is implicit in BQL)

So all GL lines before 04/01/2023 get joined to all BB lines on 04/30/2023, and then they get summed, thus being multiplicated.

Enrico

Lisa Callahan

https://community.developer.infor.com/discussion/comment/296#Comment_296

Thank you for the explanation. It is definitely behaving in the manner you describe - summing based on all records regardless of the directives in the query.

And my first thought was to do 3 separate scripts but I was hoping I could do it in one - c'est la vie I guess.

I would love to have repository admin - I used to have access to it at my old job before Infor bought Birst and it was VERY helpful.

Enrico Menotti

Well if we find another 8 people from 8 different companies to sign off an ER, we may try to push on Infor for opening the Repository Admin. I think however many have already tried to convince them and failed.

You know, I understand Infor wants Support to handle the technical things, but it happens too often that we have to ask Support to access the backend data and then figure out what's going on on our own. Often there is no way to solve problems, with such a setup.

Dan_Rosen