Banking Report Template

[Former Member]

Hi All

am creating a Deposit report.

currently I have done it in this layout.

Deposit Account I Deposit Account Name I Amount

12020401               Barclays Bank               1000

12020402               KCB Bank                     1000

12020403               Guardian  Bank              1000

12020404               Equity Bank                   1000

i wanted to convert it to the following layout.

Deposit Source I Account Name         I     Barclays Bank     I     KCB Bank     I     Guardian Bank     I     Equity Bank

12020301         Chq Control A/c                Amount                      Amount               Amount                    Amount  

12020201         Cash Control A/c              Amount                      Amount             Amount                    Amount

12020302         Chq Control A/c                Amount                    Amount               Amount                      Amount

12020202          Cash Control A/c             Amount                     Amount               Amount                      Amount                             

12020303          Chq Control A/c               Amount                    Amount               Amount                       Amount

12020203        Cash Control A/c                Amount                    Amount               Amount                      Amount

12020304          Chq Control A/c                Amount                    Amount               Amount                      Amount

12020204          Cash Control A/c              Amount                    Amount                Amount                       Amount

__________________________________________________________________________________________________

                              Total                     Total Deposited             Total Deposited     Total Deposited     Total Deposited    

Source is the Deposit Source- 1202030* are Control Accounts for Cheque Deposits

                                             1202020* are Control Accounts for Cash Deposits

Anybody who can help to go about this.

[Former Member]

Hi Evans

Achieving this is a 2 step approach for the 1st option:

1. SELECT DepositSource, AccountName, CASE WHEN BarclaysBank THEN Amount END AS [BarclaysBank], CASE WHEN KCB Bank THEN Amount END AS [KCB Bank], CASE WHEN GaurdianBank THEN Amount END AS [GaurdianBank], CASE WHEN Equity Bank THEN Amount END AS [Equity Bank].

2. Summarize all the lines into single lines.

The problem is to get all the information on 1 line with the query in 1 above. So the answer is to add all the data into a temp table and then summarize from there. This requires a stored procedure.

The 2nd option is using sub queries as follows:

SELECT DepositSource, AccountName, (SELECT Amount FROM .... WHERE Account = Barclays and DepositSource = ''), etc, ...

The downside to the second query is adding the DepositSource in one go and knowing how many there will be across the 4 banks.

If you send me your existing query I can have a look and see which might work the best.

Kind regards

Peter Juby

[Former Member]

Hi Peter,

Currently am Having two Reports

one for Cash Deposits and another for Cheque Deposits.

this is for Cash deposited

SELECT T0.[AllocAcct], T1.[AcctName],  SUM(T0.[LocTotal]) AS 'Total Cash Deposited', T0.[BanckAcct] FROM ODPS T0 , OACT T1 WHERE T0.[AllocAcct] = T1.[AcctCode]  and T0.[DeposType]  = 'c' and  T0.[DeposDate] >= '[%0]' and  T0.[DeposDate] <= '[%1]' GROUP BY T0.[AllocAcct], T1.[AcctName], T0.[BanckAcct]

this is for Cheques deposited

SELECT T1.[CashCheck], T2.[AcctName],  sum(T1.[CheckSum]) as 'Total Amount of Cheques Deposited', T0.[BanckAcct] FROM ODPS T0  INNER JOIN OCHH T1 ON T0.DeposId = T1.DpstAbs, OACT T2 WHERE T1.[CashCheck] = T2.[AcctCode]  and T0.[DeposType]  = 'k' and  T0.[DeposDate] >= '[%0]' and  T0.[DeposDate] <= '[%1]' GROUP BY T1.[CashCheck], T2.[AcctName], T0.[BanckAcct]

[Former Member]

Hi Evans

Try this:

SELECT T0.[AllocAcct], T1.[AcctName], CASE WHEN T0.[BanckAcct] = '12020401' THEN SUM(T0.[LocTotal]) ELSE 0 END AS 'Barclays Bank',

CASE WHEN T0.[BanckAcct] = '12020402' THEN SUM(T0.[LocTotal]) ELSE 0 END AS 'KCB Bank',

CASE WHEN T0.[BanckAcct] = '12020403' THEN SUM(T0.[LocTotal]) ELSE 0 END AS 'Guardian Bank',

CASE WHEN T0.[BanckAcct] = '12020404' THEN SUM(T0.[LocTotal]) ELSE 0 END AS 'Equity Bank'

FROM ODPS T0 , OACT T1

WHERE T0.[AllocAcct] = T1.[AcctCode]  and T0.[DeposType]  = 'c' and  T0.[DeposDate] >= '[%0]' and  T0.[DeposDate] <= '[%1]'

GROUP BY T0.[AllocAcct], T1.[AcctName], T0.[BanckAcct]

SELECT T1.[CashCheck], T2.[AcctName], CASE WHEN T0.[BanckAcct] = '12020401' THEN sum(T1.[CheckSum]) ELSE 0 END AS 'Barclays Bank',

CASE WHEN T0.[BanckAcct] = '12020402' THEN sum(T1.[CheckSum]) ELSE 0 END AS 'KCB Bank',

CASE WHEN T0.[BanckAcct] = '12020403' THEN sum(T1.[CheckSum]) ELSE 0 END AS 'Guardian Bank',

CASE WHEN T0.[BanckAcct] = '12020404' THEN sum(T1.[CheckSum]) ELSE 0 END AS 'Equity Bank'

FROM ODPS T0  INNER JOIN OCHH T1 ON T0.DeposId = T1.DpstAbs, OACT T2

WHERE T1.[CashCheck] = T2.[AcctCode]  and T0.[DeposType]  = 'k' and  T0.[DeposDate] >= '[%0]' and  T0.[DeposDate] <= '[%1]'

GROUP BY T1.[CashCheck], T2.[AcctName], T0.[BanckAcct]

Kind regards

Peter Juby

[Former Member]

Hi Peter,

Thanks for the Query.

when i run the whole query, it is showing All Cash Deposits which is fine, but

Cheque Deposits are being displayed from only One Cheque Control Account.

when i break down the query and i run the first bit it is showing all cash and when i run the second bit it is showing all cheque deposits.

[Former Member]

Hi Evans

Could you screenshot the second queries results for me?

Kind regards

Peter Juby

[Former Member]

Hi Peter,

these are the two screenshots when i break down the query.

Thanks!

[Former Member]

Hi Evans

You'll remember the following from my first reply above:

"The problem is to get all the information on 1 line with the query in 1 above. So the answer is to add all the data into a temp table and then summarize from there. This requires a stored procedure."

I will now send you a SP for the 2 queries as well as the 2 queries to add in SAP Business One to execute them, this will put all the info on to one row per GL account.

On the first line replace USE [DBNAME] with your database name. If you want you can also change the name of the stored procedures. Look for the CREATE proc [SPNAME] line and change the name in square brackets. If you do change this name, also change the name in the SAP Business One query e.g. EXEC [SPNAME].

I have added the 4 text files for you in dropbox:

https://www.dropbox.com/sh/6vlo5zgrro94drx/VJpxx10QxR

The files are all .txt

Kind regards

Peter Juby